Post reply

TOP 150 versus Where Clause

  • September 1, 2009 at 6:51 am

    #225651

    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 , will run more efficiently without the TOP 150. I'm basing on this on the Show Client Statistics results when I run either query in Query Analyser. When I run the query with TOP 150, I'm seeing Number of Bytes Sent = 2662 and Number of Bytes Received = 6085. Removing the TOP 150, I get Number of Bytes Sent = 2646 and Number of Bytes Received = 6065. Not a huge difference, but as the particular query I'm looking at is run an average of 1,284 times in a 2 hour period, this (I'm thinking) could have an impact.

    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

    #1047493

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 1, 2009 at 7:26 am

    #1047508

    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

    #1047512

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply