September 1, 2009 at 6:51 am
Good morning all...
I'm in a bit of a debate right now with my developers, and wondering if you can help me out. I've Googled, Yahoo'd, and beat my head against the wall and cannot figure this out.
I've run a couple of profiler traces on my database and see that the typical format of the queries is
Select TOP 150 columnnames --Query I'm looking at has 32 columns
From tablename
Where ID = xxxxxxxx
My arguement is that running this query, which obviously returns 1 row because of the
My developer thinks that its some sort of overhead that SQL Server is creating on the Queries. He's also saying that when he closes and reconnects, he's getting different Values in the results column. I know the averages will refresh on close of the database, but will the values also change?
I've looked in my copy of "Inside SQL Server 2000", and have poked around on these fora and elsewhere with no luck...Appreciate your time!
Thanks,
Chris
September 1, 2009 at 7:12 am
Bytes sent and bytes received is just the network traffic. No indication at all which query is more optimal on the server. To investigate that, use the execution plan and the IO and TIME statistics.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 1, 2009 at 7:26 am
Hi Gail,
Thanks for your reply. After doing some preliminary checks, I'm not seeing any real differences in the Time Stats. The only difference in the Execution Plans is the TOP operator, which has a 0% impact (only a 0.03038 Subtree Cost).
As far as the Network Traffic, would this (albeit minimal) increase not have an overall impact on the application, especially as it would appear the developers have written all the Queries in this manner?
What is your experience using TOP x versus specific criteria in the WHERE clause?
Thanks again for your help.....
Chris
September 1, 2009 at 7:31 am
darth.pathos (9/1/2009)
As far as the Network Traffic, would this (albeit minimal) increase not have an overall impact on the application, especially as it would appear the developers have written all the Queries in this manner?
Depends on the quality of the network
What is your experience using TOP x versus specific criteria in the WHERE clause?
You can't compare TOP with WHERE. They have completely different purposes. If I wanted to get X records from the table, either ordered by some criteria or just any 10, then I'd use TOP. If I wanted specific records that satisfy a certain condition, then I would use WHERE.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply