SQL Server Performance Forum – Threads Archive
Which query should run fast?
HI all ,I have three version of a query that returns the same result .
Query used in , like and = operator for searching … I ran the three query but got almost the same execution time i.e. DBCC DROPCLEANBUFFERS
Select * from Tb_PPM_CurrentOutstandingAccount where createdby in (‘admin’)
–1 mts 58 secs for 1579950 rows ( first time)
—-2:12 for 1579950 rows (second time) DBCC DROPCLEANBUFFERS
Select * from Tb_PPM_CurrentOutstandingAccount where createdby = (‘admin’)
— 2 mts for 1579950 rows ( first time)
—-1:56 for 1579950 rows ( second time) DBCC DROPCLEANBUFFERS Select * from Tb_PPM_CurrentOutstandingAccount where createdby like (‘admin’)
— : 1:59 for 1579950 rows ( first time)
— : 1:57 for 1579950 rows ( secondtime) While i was thinking my second query which was finding exact match ,
will run much faster ..it doesnt..?? What could be the reason behind this?? Rohit Kochar
MCP .Net(Web and SqlServer)
Company : TCS ,Noida
With these fairly simple criteria, the execution plan will be identical. Just ask SQL to show you the execution plans, and compare. The variation in time is probably due to external factors.
I agree with you Rohit, I think second query should be running fast as per my experience. The link below tell you the poor performance of LIKE which i think also applies to IN Keyword. http://www.itjungle.com/mgo/mgo120602-story02.html Thanks,
Chaitanya
Hm, a little testing shows that LIKE indeed costs a little extra. Anyhow, you should use the appropriate operator for what you’re doing: LIKE — when you have wildcards
= — when there are no wildcards
IN — when you have a value list (or a subquery)
I agree with you Adriaan…Execution plan was also very similar…
just filter search percentage was different and that too with a very less percentage..
What are external factors u talking about ? Rohit Kochar
MCP .Net(Web and SqlServer)
Company : TCS ,Noida
SQL Server is not the only process running on the server. You have long running queries here, so other processes on the server can take up some ‘clock ticks’.
]]>