Which of these 2 queries would be faster? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Which of these 2 queries would be faster?

Author

Topic

davidM
Starting Member

2 Posts

Posted - 2005-04-06 : 21:26:21

Hi

Which of the 2 blocks of code below would be the most efficient in a stored procedure?

My limited understanding is that the assignment of the query result to @t in the first example will prevent the proc from being optimised since the value stored in @t is unknown.

Testing both of them in the query analyser, they perform pretty much the same for 50k records. For much smaller records, the first query has fewer logical reads.

PathID is a clustered index (PK) and Path is a non-clustered index.

Could someone shed some light on this?

Thanks, David

----------------------------------------------------
--(1)-- separate query & pass single result to variable @t
----------------------------------------------------
declare @t varchar(100)
select @t=path from paths where pathID=5953
select pathID, path, level from paths where path like @t+'%'
order by pathID
----------------------------------------------------

or

----------------------------------------------------
--(2)-- single query block with subquery
----------------------------------------------------
select pathID, path, level
From paths where path like
(select path from paths where pathID=5953)+'%'
order by pathID
----------------------------------------------------

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-06 : 22:54:49

The other factor to look at is how the execution plans compare. The main part of the work is very similar in those 2 statements. When you're doing you comparison try these as well for the heck of it:


Select	b.pathID, b.path, b.[level]
From	Paths a
JOIN	Paths b
		ON a.PathID = 5953
		and b.Path like a.Path + '%'
order by b.pathid

Select	b.pathID, b.path, b.[level]
From	Paths a
JOIN	Paths b
		ON a.PathID = 5953
		and a.Path = substring(b.Path, 1, len(a.Path))
order by b.pathid

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources