September 4, 2003 at 8:22 am
I'm pretty strict about not allowing dynamic SQL within our applications, and using parameters instead, and up until now I've not come across a situation where I've not been able to achieve what I want with params. Today though I came across a problem. Check out the two queries below:
select *
From vHaulageJobsUnfinished u
Where exists (
select *
from haulageitems i
join jobs j on i.jobprefix=j.prefix and i.jobid=j.id
where j.jobref like 'jtbr9098/1' and u.prefix=i.hjprefix and u.id=i.hjid
)
and
Select *
FromvHaulageJobsUnfinished U
WHERE EXISTS (
SELECT *
FROM HaulageItems I
JOIN Jobs J ON I.JobPrefix=J.Prefix AND I.JobID=J.ID
WHERE J.JobRef LIKE @ShippingJobRef AND U.Prefix=I.HJPrefix AND U.ID=I.HJID
)
These are essentially the same query, except the second example uses a parameter (declared as varchar(25)) whereas the first uses a static string. When I run these and examine the execution plans, the first has a subtree cost of 0.257, and the second of 15.1
I realise it's difficult to comment without seeing schema, but does this make sense to anybody? Okay, I've effectively got a static lookup in the first example, but performance is just as good for each different reference I put in, so caching doesn't seem to be an issue. In fact, if I execute the following:
declare @sql nvarchar(2000)
SET @sql=N'Select *
FromvHaulageJobsUnfinished U
WHERE EXISTS (
SELECT *
FROM HaulageItems I
JOIN Jobs J ON I.JobPrefix=J.Prefix AND I.JobID=J.ID
WHERE J.JobRef LIKE ''' + @ShippingJobRef + ''' AND U.Prefix=I.HJPrefix AND U.ID=I.HJID
)'
It performs just as well as the first query 🙁
Kev
September 4, 2003 at 10:02 am
Very interesting. Haven't seen this, but I'll take a look.
Steve Jones
September 4, 2003 at 11:56 am
Hmmm, that is really interesting. I wonder if the query plan is taking the literal string that you have on the like and changing the like to = instead? What happens if you preceed and follow the litteral string with a wild card character on the first query?
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
September 5, 2003 at 12:28 am
"declare @sql nvarchar(2000)
SET @sql=N'Select *
From vHaulageJobsUnfinished U
WHERE EXISTS (
SELECT *
FROM HaulageItems I
JOIN Jobs J ON I.JobPrefix=J.Prefix AND I.JobID=J.ID
WHERE J.JobRef LIKE ''' + @ShippingJobRef + ''' AND U.Prefix=I.HJPrefix AND U.ID=I.HJID
)' "
because you execute the @sql you've created dynamic sql, with known search arguments. this is the same as in your first query !
Your second query gives you a higer subtree kost because of statistics. The access plan is generated using a statistical value for the @ShippingJobRef variable. If it uses the same access plan and you provide the same criteria, they should perform equaly.
The query that uses the @ShippingJobRef variable will only be precompiled once, where as the @sql statement needs to be parsed, audited, pricompiled ... every time.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 5, 2003 at 10:06 am
I remember reading a post with a comment about a difference between using a proc input parameter versus a local variable in a case like this. For some reason, using an input parameter in the query could prevent optimization in some cases. I have not ever seen it happen myself. Any truth to this? If so, perhaps it relates.
RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
September 7, 2003 at 7:50 pm
Article by Brian Morgan, SQL Server Magazine of Aug 2003
quote:
Why Do Similar Queries Have Different Execution Plans?
September 8, 2003 at 3:28 am
Adding a wildcard didn't make much difference (cost up to 0.266), however, switching from LIKE to '=' in the second query causes it to run as fast as the first query. Hmmm
quote:
Hmmm, that is really interesting. I wonder if the query plan is taking the literal string that you have on the like and changing the like to = instead? What happens if you preceed and follow the litteral string with a wild card character on the first query?Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply