Long running update - looking to help speed it up

Question

Long running update - looking to help speed it up

MyDoggieJessie

SSC-Forever

Points: 44486
More actions
January 26, 2011 at 9:59 pm

#230850

First off, I didn't write this but did try to make it faster...I've tried looping through it snagging say a TOP 500 records and updating those, then fetching another 500 and so on but it really had no impact.
I'd think that updating a table with only a few columns, having less than 3,000,000 rows should only take a minute or two?
Any suggestions on how to make this faster would be greating appreciated!
/* Snippet */
DECLARE @mindate datetime SET @mindate = '2010-01-01 00:00:00.000'
DECLARE @maxdate datetime SET @maxdate = '2010-01-31 23:59:59.000'
CREATE TABLE #Update (id int identity,
tranid [varchar](20) NULL,
fleetnumber [varchar](20) NULL,
odometer [int] DEFAULT(0),
pan [varchar](32) NULL,
unit [varchar](40) NULL,
trantime [datetime] NULL,
prevodometer int NULL,
)
INSERT INTO #Update
SELECT DISTINCT
tranid, fleetnumber, odometer, pan, unit, trantime, prevodometer
FROM DW_LOAD.[dbo].[Mileage_Table] --> This table has about 3,000,000 rows
WHERE Odometer > 0
AND (prevodometer = 0 OR prevodometer IS NULL)
AND trantime BETWEEN @mindate AND @maxdate
-- This inserts about 175,000 rows into #Update
BEGIN TRANSACTION
UPDATE a
SET a.prevodometer =
CASE
WHEN (dbo.fx_GetLastOdometer(b.trantime,b.pan,b.unit,b.tranid,b.odometer) < 1) THEN 1
ELSE ISNULL(dbo.fx_GetLastOdometer(b.trantime,b.pan,b.unit,b.tranid,b.odometer), 1)
END
FROM dbo.Mileage_Table a
JOIN #Update b ON
a.tranid = b.tranid
AND a.pan = b.pan
WHERE
a.trantime BETWEEN @minDate AND @maxDate
AND (a.prevodometer IS NULL OR a.prevodometer = 0)
IF @@ERROR = 0
BEGIN
COMMIT TRANSACTION
END
ELSE
BEGIN
/* Send Email */
EXECUTE msdb.dbo.sp_send_dbmail @recipients = 'me@hotmail.com'
,@subject='There was an error - transaction failed'
,@body = ''
ROLLBACK TRANSACTION
END
DROP TABLE #Update
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Could you post some sample data, about 10 to 20 rows, as a series of INSERT INTO statements for the source table (and if you haven't provided the CREATE TABLE statement for the source table, please provide that as well). Also, then provide the expected results based on the sample data.

I have an idea as to what you are trying to accomplish, but it will help to actually see it.

WayneS SSC Guru Points: 95460 More actions · Answer 2

It will also help to see the code and DDL for the tables referenced in the fx_GetLastOdometer function.

(I think that this might be your culprit...)

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

WayneS (1/26/2011)
It will also help to see the code and DDL for the tables referenced in the fx_GetLastOdometer function.
(I think that this might be your culprit...)

Good call on the extra code. I guess I missed it in the jumble of code.

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 4

Sorry! Yes I guess that would help 🙂

CREATE FUNCTION [dbo].[fx_GetLastOdometer]

(@date varchar(40)

,@pan VARCHAR(32)

,@tranid int)

RETURNS int

AS

BEGIN

DECLARE @Odometer int

SET @Odometer = (

SELECT TOP 1 odometer

FROM dbo.[Mileage_Table] WITH(INDEX(idx_TranTime))

WHERE tranid <> @tranid

AND pan=@pan

AND trantime < @date

)

RETURN @Odometer

END

Here's some sample data:

CREATE TABLE #Mileage_Table (id int identity,

tranid [varchar](20) NULL,

fleetnumber [varchar](20) NULL,

odometer [int] DEFAULT(0),

pan [varchar](32) NULL,

unit [varchar](40) NULL,

trantime [datetime] NULL,

prevodometer int NULL,

)