help optomize query - 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 2005 Forums

Transact-SQL (2005)

help optomize query

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-09-24 : 02:15:47

as part of a stored procedure i have

(select Max(LapsedTime) from @t t2 where t2.plate=t1.plate and t2.dayonly=t1.dayonly and t2.site=t1.site)

to do this i'm pulling select statement and I need the lapsed time to be the max number where the plate and dayonly and site match.

the problem is my query above makes the whole stored procedure take much longer

any suggestions?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-24 : 02:23:12

what's t1? can you post rest of query? its difficult to suggest an alternative without knowing what t1 means.

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-09-24 : 02:26:44

here's the whole sp

ALTER PROC [dbo].[uspGetPlatesFromTemp]
@AccType nvarchar(2) = null,
@AccValue INT = null,
@UserId int
AS
BEGIN
SET NOCOUNT ON;

declare @sql nvarchar(max),
@AccLimit nvarchar(max),
@CRLF char(1),
@DateMax SMALLDATETIME

declare @t TABLE (
ID INT PRIMARY KEY,
PlateID INT,
plate NVARCHAR(10),
date DATETIME,
picture NVARCHAR(50),
picture2 NVARCHAR(50),
picture3 NVARCHAR(50),
lane NVARCHAR(10),
accuracy SMALLINT,
[site] NVARCHAR(10),
PVInOut TINYINT,
dayonly SMALLDATETIME,
Code NVARCHAR(5),
Changed BIT,
origPlate NVARCHAR(10),
New BIT,
LapsedTime SMALLMONEY)

IF @AccType IS NULL
SET @AccLimit = ''
ELSE
SET @AccLimit = ' accuracy '+@AccType+cast(@AccValue as nvarchar(50))+' AND '

set @sql = N'
SELECT
row,
PlateID,
plate,
date,
picture,
picture2,
picture3,
lane,
accuracy,
site,
PVInOut,
dayonly,
Code,
Changed,
origPlate,
New
FROM vwTempPlateImages a where '+@AccLimit+'
UserId = @UserId'

--print @sql
INSERT INTO @t (ID,PlateID,plate, date, picture, picture2, picture3, lane, accuracy, [site], PVInOut,
dayonly, Code, Changed, origPlate, New)
EXEC sp_executesql @sql, N'@UserId int',@UserId

UPDATE @t SET LapsedTime = CAST(datediff(s,nextdate,date) AS SMALLMONEY )/60
FROM (select ID+1 nextID, date nextdate, plate nextplate, dayonly nextdayonly, [site] nextsite from @t) b
where ID=nextID and plate=nextplate and
dayonly=nextdayonly and
[site] = nextsite

SELECT
PlateID,
plate,
date,
picture,
picture2,
picture3,
lane,
accuracy,
site,
PVInOut,
(select Max(LapsedTime) from @t t2 where t2.plate=t1.plate and t2.dayonly=t1.dayonly and t2.site=t1.site) LapsedTime,
--(select Max(LapsedTime) from @t) LapsedTime,
--lapsedtime,
Code,
Changed,
origPlate,
New
from @t t1
order by date, plate
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-24 : 02:48:52

why are you using dynamic sql here? what does @AccLimit represent? Also how will you be passing value for @AccValue?Whats the purpose of below

SET @AccLimit = ' accuracy '+@AccType+cast(@AccValue as nvarchar(50))+' AND '

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-09-24 : 03:08:56

acctype is either <= or >= or =
accvalue is a number

i have went through the sp and if i comment the line
(select Max(LapsedTime) from @t t2 where t2.plate=t1.plate and t2.dayonly=t1.dayonly and t2.site=t1.site) LapsedTime,

then the sp runs in 1 second. With that line it takes 10 seconds and this is when the server is not being used a lot. At busy times it can take up to a minute

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-24 : 03:17:51

quote:
Originally posted by esthera

acctype is either <= or >= or =
accvalue is a number

i have went through the sp and if i comment the line
(select Max(LapsedTime) from @t t2 where t2.plate=t1.plate and t2.dayonly=t1.dayonly and t2.site=t1.site) LapsedTime,

then the sp runs in 1 second. With that line it takes 10 seconds and this is when the server is not being used a lot. At busy times it can take up to a minute

what about this slight modification?

SELECT
t1.PlateID,
t1.plate, 
t1.date, 
t1.picture, 
t1.picture2, 
t1.picture3, 
t1.lane, 
t1.accuracy, 
t1.site,
t1.PVInOut,
t2.LapsedTime,
t1.Code, 
t1.Changed, 
t1.origPlate,
t1.New
from @t t1
inner join (select plate,dayonly,site,Max(LapsedTime) AS LapsedTime 
            from @t 
            group by plate,dayonly,site) t2 
on t2.plate=t1.plate 
and t2.dayonly=t1.dayonly 
and t2.site=t1.site
order by t1.date, t1.plate
END

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-09-24 : 03:24:33

no that's much worse
instead of 10 seconds it takes 1:10

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-24 : 03:36:51

and this?

SELECT
t1.PlateID,
t1.plate, 
t1.date, 
t1.picture, 
t1.picture2, 
t1.picture3, 
t1.lane, 
t1.accuracy, 
t1.site,
t1.PVInOut,
t2.LapsedTime,
t1.Code, 
t1.Changed, 
t1.origPlate,
t1.New
from @t t1
outer apply(select Top 1 LapsedTime
            from @t  
            where plate=t1.plate 
            and dayonly=t1.dayonly 
            and site=t1.site
            order by LapsedTime desc) t2 
order by t1.date, t1.plate
END

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-24 : 04:01:32

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-24 : 04:08:51

quote:

Originally posted by Peso

SELECT		PlateID,
		plate, 
		date, 
		picture, 
		picture2, 
		picture3, 
		lane, 
		accuracy, 
		site,
		PVInOut,
		LapsedTime,
		Code, 
		Changed, 
		origPlate,
		New
FROM		(
			SELECT	PlateID,
				plate, 
				date, 
				picture, 
				picture2, 
				picture3, 
				lane, 
				accuracy, 
				site,
				PVInOut,
				LapsedTime,
				Code, 
				Changed, 
				origPlate,
				New,
				ROW_NUMBER() OVER (PARTITION BY plate, dayonly, site ORDER BY LapsedTime DESC) AS RecID
			from	@t
		) AS d
WHERE		RecID = 1
ORDER BY	date,
		plate

E 12°55'05.63"
N 56°04'39.26"

Not sure if OP is looking for only records with max elapsed time. The inital posted query seemed like he wants all records and wants to retrive max elapsed time of group with each of them. something like

SELECT	PlateID,
plate, 
date, 
picture, 
picture2, 
picture3, 
lane, 
accuracy, 
site,
PVInOut,
MAX(LapsedTime) OVER (PARTITION BY plate, dayonly, site) AS LapsedTime,
Code, 
Changed, 
origPlate,
New
from	@t
ORDER BY date,plate

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-24 : 05:03:13

Yes I know. I edited my post accordingly.

E 12°55'05.63"
N 56°04'39.26"

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-09-24 : 05:52:32

[code]SELECT PlateID,
plate,
date,
picture,
picture2,
picture3,
lane,
accuracy,
site,
PVInOut,
MAX(LapsedTime) OVER (PARTITION BY plate, dayonly, site) AS LapsedTime,
Code,
Changed,
origPlate,
New
from @t
ORDER BY date,plate[/code]

this is much faster. what does partition do - does this give me the max matching lapsed time for that plate,dayonly,site?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-24 : 05:57:53

The new windowed functions (ranking funtions) using PARTITION and OVER are very well described in Books Online.

E 12°55'05.63"
N 56°04'39.26"

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-09-24 : 06:12:44

thanks for your help:)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources