execution time is high - 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

SQL Server Development (2000)

execution time is high

Author

Topic

rupesh02kumar
Starting Member

4 Posts

Posted - 2011-08-27 : 04:16:28

SELECT F.ID AS IDName,CASE WHEN CO.LongName = '' THEN L.LongName ELSE CO.LongName + ' <-- ' + '[' + L.LongName + ']' END AS Location ,FH.LastTimestamp,F1,F2,F3,F4,F5,F6 ,S.LongName AS Source FROM Folder AS F JOIN FolderHistory AS FH ON FH.FolderID = F.ID JOIN Location AS L ON L.ID = FH.LocationID JOIN Location AS CO ON CO.ID = FH.CheckoutLocationID JOIN Source AS S ON S.ID = FH.SourceID WHERE FH.Code = 1 AND F.DeptID =1 AND (( ((L.LongName LIKE 'In Transit to Boston%' AND CO.ID = '') OR CO.LongName LIKE 'In Transit to Boston%') ) ) order by F.ID

it is taking 2:30sec to execute i want to reduce the time as 20 sec please help me

thanks in advance

Kristen
Test

22859 Posts

Posted - 2011-08-27 : 06:18:03

Start with formatting your code properly, you'll have a much better chance of seeing what's wrong then.

How many rows are you expecting to get back? If its millions then its going to take a while regardless of how efficient your query is. If its less than 1,000 or so then it should take milliseconds, and not even 20 secs ...

Does your code even work? For example, is CO.ID = '' ever true? or even CO.LongName = '' ? Do both your JOINs to Location from FH.LocationID and FH.CheckoutLocationID always work? Perhaps you have some sort of dummy value in there - if so I expect you should actually have a NULL value to indicate an unknown / unknowable value and use an OUTER JOIN instead.

This may do what you want, but it won't give you the "<--" alternative value for L.LongName. You could LEFT JOIN to get that, when L.ID = FH.CheckoutLocationID.

Note that this will give you duplicate records where both FH.LocationID and FH.CheckoutLocationID have different values which both map to LongName LIKE 'In Transit to Boston%', but it may be that that could not happen?

You will need an index on Location.LongName. I presume you have indexes / Primary Key on FolderHistory.FolderID, Source.ID, FolderHistory.LocationID and FolderHistory.CheckoutLocationID already


SELECT	F.ID AS IDName,
	L.LongName AS Location,
	FH.LastTimestamp,
	F1,F2,F3,F4,F5,F6,
	S.LongName AS Source
FROM	Folder AS F
	JOIN FolderHistory AS FH
		ON FH.FolderID = F.ID
	JOIN 
	(
		SELECT	L.ID,
			L.LongName
		FROM	Location AS L
		WHERE	L.LongName LIKE 'In Transit to Boston%' 
	) AS L
		ON L.ID = FH.LocationID
		OR L.ID = FH.CheckoutLocationID
	JOIN Source AS S
		ON S.ID = FH.SourceID
WHERE	FH.Code = 1 
	AND F.DeptID =1 
ORDER BY F.ID

rupesh02kumar
Starting Member

4 Posts

Posted - 2011-08-27 : 06:58:32

thanks alot sir ,

u made my day...

sir one more trouble is with same query

SELECT F.ID AS IDName,CASE WHEN CO.LongName = '' THEN L.LongName ELSE CO.LongName + ' <-- ' + '[' + L.LongName + ']' END AS Location ,FH.LastTimestamp,F1,F2,F3,F4,F5,F6 ,S.LongName AS Source FROM Folder AS F JOIN FolderHistory AS FH ON FH.FolderID = F.ID JOIN Location AS L ON L.ID = FH.LocationID JOIN Location AS CO ON CO.ID = FH.CheckoutLocationID JOIN Source AS S ON S.ID = FH.SourceID WHERE FH.Code = 1 AND F.DeptID =1 AND (( ((L.LongName LIKE '12TH FLOOR CABINENT%' AND CO.ID = '') OR CO.LongName LIKE '12TH FLOOR CABINENT%') ) ) order by F.ID

insted of writing ((L.LongName LIKE 'In Transit to Boston%' AND CO.ID = '') OR CO.LongName LIKE 'In Transit to Boston%') ) ) order by F.ID

(( ((L.LongName LIKE '12TH FLOOR CABINENT%' AND CO.ID = '') OR CO.LongName LIKE '12TH FLOOR CABINENT%') ) ) order by F.ID

it is executing in 12 sec but when i select 'In Transit to Boston it is taking 2:30 sec

help me

rupesh02kumar
Starting Member

4 Posts

Posted - 2011-08-27 : 07:00:03

passively awating for ur response

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-08-27 : 08:20:34

Patience. We're all volunteers with our own jobs and our own problems.

Start by formatting your code so that it's readable.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources