SQL Optimization - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SQL Optimization

Author

Topic

jerryau
Starting Member

5 Posts

Posted - 2006-12-04 : 05:27:08

Hi,

I have written a stored procedure that retrieves records from a [TEMP] table and based on the record state, a "static" table [results] is updated accordingly. The problem is that with a lot of records being compared/updated in a while loop, this procedure takes a lot of time to complete.

Does anyone have any ideas how this procedure could be optimized, or what can be done to reduce the time it takes to complete this procedure with a lot of records in both tables? What is the usual way to handle such problems?

Tables:


CREATE TABLE [dbo].[results] (
  [id] int IDENTITY(1, 1) NOT NULL,
  [facility] varchar(30) NULL,
  [locName] varchar(16) NULL,
  [parName] varchar(16) NULL,
  [dTime] datetime NULL,
  [value] float NULL,
  [unit] varchar(10) COLLATE NULL,
  [perDecimal] smallint NULL,
  [oldValue] float NULL,
  [change] varchar(1) COLLATE NULL,
  [tStamp] datetime DEFAULT getdate() NOT NULL,
  PRIMARY KEY CLUSTERED ([id])
)
ON [PRIMARY]
GO

CREATE TABLE [dbo].[Temp] (
  [T_ID] int IDENTITY(1, 1) NOT NULL,
  [T_Facility] varchar(30) NOT NULL,
  [T_LocName] varchar(16) NOT NULL,
  [T_ParName] varchar(16) NOT NULL,
  [T_DTime] datetime NOT NULL,
  [T_Value] float NOT NULL,
  [T_Unit] varchar(10) COLLATE NOT NULL,
  [T_ParDecimal] smallint NOT NULL,
  PRIMARY KEY CLUSTERED ([T_ID]),
  UNIQUE ([T_ID])
)
ON [PRIMARY]
GO

Stored Procedure:


CREATE PROCEDURE dbo.update_results_tbl
AS
BEGIN
  DECLARE @next varchar(100),
          @facility varchar(30),
          @locName varchar(16),
          @parName varchar(16),
          @dTime datetime,
          @value float,
          @oldValue float,
          @unit varchar(100),
          @perDecimal smallint,
          @startTime datetime

  SELECT @next = ''
  SELECT @startTime = CURRENT_TIMESTAMP
  
  WHILE 1 = 1
    BEGIN
      SELECT @next = (SELECT MIN(T_ID) FROM dbo.Temp WHERE T_ID > @next)
      
      IF @next IS NULL
        BREAK
      ELSE
        BEGIN
          SELECT @facility = T_Facility,
                 @locName = T_LocName,
                 @parName = T_ParName,
                 @dTime = T_DTime,
                 @value = T_Value,
                 @unit = T_Unit,
                 @perDecimal = T_PerDecimal FROM dbo.Temp WHERE T_ID = @next
                 
          IF (SELECT count(*) FROM dbo.results WHERE facility = @facility AND locName = @locName AND parName = @parName) > 0
            BEGIN
              SELECT @oldValue = "value" FROM dbo.results WHERE facility = @facility AND locName = @locName AND parName = @parName
              
              IF @oldValue <> @value
                BEGIN
                  UPDATE dbo.results SET "value" = @value,
                                         change = 'U',
                                         tStamp = CURRENT_TIMESTAMP,
                                         oldValue = @oldValue
                  WHERE facility = @facility AND locName = @locName AND parName = @parName
                END
              ELSE
                BEGIN
                  UPDATE dbo.results SET change = '',
                                         tStamp = CURRENT_TIMESTAMP
                  WHERE facility = @facility AND locName = @locName AND parName = @parName
                END
            END
          ELSE
            BEGIN
              INSERT INTO dbo.results (facility,locName,parName,dTime,"value",unit,perDecimal,change)
              VALUES (@facility,@locName,@parName,@dTime,@value,@unit,@perDecimal,'I')
            END
        END
    END
    UPDATE dbo.results SET change = 'D' WHERE tStamp < @startTime
    TRUNCATE TABLE dbo.Temp
END

Thank you,
Jerry

Westley
Posting Yak Master

229 Posts

Posted - 2006-12-04 : 05:40:32

Why don't you join the table and perform the update? Then you do no need to check every single record?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-04 : 05:53:35

Try this SET-based solution...

CREATE PROCEDURE dbo.Update_Results_tbl
AS

SET NOCOUNT ON

DECLARE @StartTime DATETIME

SELECT	@StartTime = GETDATE()

-- Insert all missing records at once
INSERT		dbo.Results
		(
			Facility,
			LocName,
			ParName,
			dTime,
			Value,
			Unit,
			PerDecimal,
			Change
		)
SELECT		t.T_Facility,
		t.T_LocName,
		t.T_ParName,
		t.T_DTime,
		t.T_Value,
		t.T_Unit,
		t.T_PerDecimal,
		'I'
FROM		dbo.Temp t
LEFT JOIN	dbo.Results r ON r.Facility = t.T_Facility AND r.LocName = t.T_LocName AND r.ParName = t.T_ParName
WHERE		r.Facility IS NULL

-- Update all existing and changed records
UPDATE		r
SET		r.oldValue = r.oldValue,
		r.Value = t.T_Value,
 		r.Change = 'U',
 		r.tStamp = @StartTime
FROM		dbo.Results r
INNER JOIN	dbo.Temp t ON t.T_Facility = r.Facility AND t.T_LocName = r.LocName AND t.T_ParName = r.ParName
WHERE		t.T_Value <> r.Value

-- Update all existing and not changed records
UPDATE		r
SET		r.Change = '',
		tStamp = @StartTime
FROM		dbo.Results r
INNER JOIN	dbo.Temp t ON t.T_Facility = r.Facility AND t.T_LocName = r.LocName AND t.T_ParName = r.ParName
WHERE		t.T_Value = r.Value

UPDATE	dbo.Results
SET	Change = 'D'
WHERE	tStamp < @StartTime

TRUNCATE TABLE dbo.Temp

Peter Larsson
Helsingborg, Sweden

jerryau
Starting Member

5 Posts

Posted - 2006-12-04 : 05:54:00

Depending on the record in the [TEMP] table, I have to insert a flag into the [results] table (e.g. if the record in the [TEMP] table has a different value to the one in the [results] table I have to add a 'U' flag, meaning the value has been updated, and also write the old value into the "oldValue" field; if it is a new record the flag is 'I', etc.). Would that be possible using a JOIN?

jerryau
Starting Member

5 Posts

Posted - 2006-12-04 : 05:55:34

Thanks Peso, I'll give it a try :)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-04 : 06:08:07

BTW, you should change the order of the UpSert. Otherwise the newly inserted records are treated as existing and not changed

CREATE PROCEDURE dbo.Update_Results_tbl
AS

SET NOCOUNT ON

DECLARE @StartTime DATETIME

SELECT	@StartTime = GETDATE()

-- Update all existing and changed records
UPDATE		r
SET		r.oldValue = r.oldValue,
		r.Value = t.T_Value,
 		r.Change = 'U',
 		r.tStamp = @StartTime
FROM		dbo.Results r
INNER JOIN	dbo.Temp t ON t.T_Facility = r.Facility AND t.T_LocName = r.LocName AND t.T_ParName = r.ParName
WHERE		t.T_Value <> r.Value

-- Update all existing and not changed records
UPDATE		r
SET		r.Change = '',
		tStamp = @StartTime
FROM		dbo.Results r
INNER JOIN	dbo.Temp t ON t.T_Facility = r.Facility AND t.T_LocName = r.LocName AND t.T_ParName = r.ParName
WHERE		t.T_Value = r.Value

-- Insert all missing records at once
INSERT		dbo.Results
		(
			Facility,
			LocName,
			ParName,
			dTime,
			Value,
			Unit,
			PerDecimal,
			Change
		)
SELECT		t.T_Facility,
		t.T_LocName,
		t.T_ParName,
		t.T_DTime,
		t.T_Value,
		t.T_Unit,
		t.T_PerDecimal,
		'I'
FROM		dbo.Temp t
LEFT JOIN	dbo.Results r ON r.Facility = t.T_Facility AND r.LocName = t.T_LocName AND r.ParName = t.T_ParName
WHERE		r.Facility IS NULL

UPDATE	dbo.Results
SET	Change = 'D'
WHERE	tStamp < @StartTime

TRUNCATE TABLE dbo.Temp

Peter Larsson
Helsingborg, Sweden

jerryau
Starting Member

5 Posts

Posted - 2006-12-04 : 10:06:45

It's working, and it's much faster now. Thanks a lot for your help! :)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-04 : 10:10:16

How big improvement is "much faster"?

This is what databases are all about. Think in "sets" of data, instead of "row by row".
Think bigger!

Peter Larsson
Helsingborg, Sweden

jerryau
Starting Member

5 Posts

Posted - 2006-12-05 : 03:47:24

Before this script 4,000 rows took approximately 30 seconds. Now the same amount of data is transfered/compared in just under 5 seconds.
I'll have to get into SQL "thinking" from now on, since I'm a programmer; that's why I've tried to solve this problem in the "while" loop way.
Thanks for your help Peter :)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-05 : 03:56:19

With 4000 records only, the approach above should not take more than 1 second...
Check your indexes for better performance.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources