Performance in using Select "TOP 5000000 ..." in sql

  • Can anybody help me in trying to resolve the performance issue in a simple sql using "select top 5000000 " with order by clause in sql server 2005? It ran 2 secs in development database but ran 12 mins or more in production datable using the exact sql. However, if I reduce the select top value from 5000000 to 50000, it ran about 4 secs. Regardless, can anyone give me a clue why the exact sql performance timing is different? I am new sql server database...are there database parameters setting the DBA need to be look at? Any help is much appreciated. Thank you.

  • Please read the second article in my signature block regarding asking for assistance on performance problems. Based on your post, there really isn't much we can do to help you.

  • TOP 5000000? That's an aweful lot of rows to be 'limiting' a result set to. What's the purpose of that TOP clause?

    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 3 posts - 1 through 2 (of 2 total)

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