FN-FORUM: SQL Server Performance Problem

date posted 11th January 2006 09:39

Hi,
Got a SQL problem - a performance problem to be exact;

This is the Stored Procedure;




CREATE PROCEDURE dbo.System_Importer_UpsertRecordProperty

@regid int, @userid int, @key varchar(250), @userpropertyid [EMAIL REMOVED]
varchar(250),=20
@msg varchar(8000) OUTPUT, @added int OUTPUT, @updated int
[EMAIL REMOVED] int, @importlogitemid int

AS

set nocount on
set dateformat dmy

SELECT @msg =3D [EMAIL REMOVED] ''), @added=3D [EMAIL REMOVED] 0), @updated =
=3D
[EMAIL REMOVED] 0)

-- find the item
IF @userpropertyid is null
BEGIN
SELECT @userpropertyid =3D id
FROM
registration.dbo.userproperty
WHERE
[key] =3D @key
AND reg_id =3D @regid
AND deleted=3D'N'

SELECT @userpropertyid =3D [EMAIL REMOVED] 0)

IF @userpropertyid =3D 0
BEGIN
SELECT @msg =3D @msg + ', Key (' + @key +
') could not be found', @added =3D 0, @updated =3D 0
RETURN
END
-- END IF



END
-- END IF




-- ok, attempt to update record

DECLARE @oldvalue varchar(8000)
DECLARE @pendingupdate char(1)

SELECT @oldvalue =3D Datavalue, @pendingupdate =3D PendingUpdate
FROM
registration.dbo.userpropertydata
WHERE reg_id =3D @regid AND user_id =3D @userid AND userproperty_id
=3D @userpropertyid


-- IF THE VALUE HAS CHANGED OR OLD ITEM MARKED AS PENDING THEN UPDATE IT

IF [EMAIL REMOVED] @value) OR [EMAIL REMOVED] =3D @value AND @pendingupdate =3D
'Y') OR @oldvalue is null
BEGIN

UPDATE registration.dbo.userpropertydata
SET DataValue =3D @value, modifiedon =3D getdate(),
modifiedby_id =3D 0, pendingupdate=3D'N'
WHERE reg_id =3D @regid AND user_id =3D @userid AND
userproperty_id =3D @userpropertyid
=09
IF @@ROWCOUNT =3D 0
BEGIN
-- Update failed, so insert
INSERT INTO
registration.dbo.userpropertydata
(reg_id, user_id,
userproperty_id, datavalue, modifiedby_id, modifiedon, pendingupdate)
VALUES [EMAIL REMOVED] @userid,
@userpropertyid, @value, 0, getdate() , 'N')
=09
IF @@ROWCOUNT =3D 1
BEGIN
SELECT @added =3D @added +
1, @msg =3D @msg+', User property (' + @key + ') created'
RETURN
END
ELSE
BEGIN
SELECT @msg =3D @msg + ',
Problem creating new user property (' + @key + ')'
RETURN
END
-- END IF
END
ELSE
BEGIN
-- record updated
SELECT @updated =3D @updated + 1, @msg =3D
@msg + ', User property (' + @key + ') updated'
RETURN
END
-- END IF
END
ELSE
BEGIN
-- Data hasn't changed
END
-- END IF

set nocount off
GO




Starting from "no data" in the userpropertdata table, running the SP
takes 20-30ms

We have 2250 master records each with 59 of these userpropertydata
values meaning 132750 records. By the time we're towards the end of the
import, the SP is taking 300-400ms to run.

Now, the userpropertydata table has an index on it against reg_id,
user_id, userproperty_id. It's not set as Unique or clustered (should it
be set as unique ? I guess it can be if necessary)

There are no other relationships (the only other index being the primary
key against a field called UID which is an identity(1,1) field).


I suspect there's very little that can be done to make this go faster -
it's the old - have an index to make the initial lookup / update go
faster or don't have an index to make the insert quicker.

Suggestions on a post card !

I can't really be going live with a system that takes about 8.5 hours to
import the data.

Cheers
Andy