Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Performance in using Select "TOP 5000000 ..." in sql Expand / Collapse
Posted Wednesday, August 12, 2009 8:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 11, 2009 8:26 AM
Points: 1, Visits: 15
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.
Post #769344
Posted Wednesday, August 12, 2009 8:34 AM



Group: General Forum Members
Last Login: Today @ 4:50 PM
Points: 23,515, Visits: 37,731
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.

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #769356
Posted Wednesday, August 12, 2009 9:33 AM



Group: General Forum Members
Last Login: Today @ 1:56 PM
Points: 45,616, Visits: 44,144
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

Post #769419
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse