Large number of rows.

Hi all,

A select query returns around 1 million rows. The column in the WHERE condition is indexed. This query takes nearly 1 minute for returning the all the records. Is this normal ?

Does the number of records returned affect the performance inspite of the indexing ?

Thanks,

DBLearner

The index simply allows it to find the data it has to retrieve quickly. It then has to:

load that actual record data in from disk. Depending upon how the records are split across datapages this could be a lot of disk access. Then if you have any sorting or grouping/aggregating on the result it has to do this before it can start passing the results back. After that it has to transfer the data that you are retrieving across the link (shared memory if you are running on the server across the network if not). If you are retrieving say 20 bytes per record (quite small: a couple of ints, a bit of text and a real number can be this size or larger easily) then for a million records that is 20 megabytes.|||

The major 'chokepoints' will be the quality of the index vs. the WHERE clause criteria, amount of server memory, other activity, CPU power, and disk 'arrangement'. (Having the TempDb database on a dedicated array or LUN, for example.)

You might benefit from some tuning and optimization and perhaps that will increase the query responsiveness. But you definitely need to get some benchmarks in place.

Here is some information about Performance Audits, Monitoring, and Tuning:

Performance Audit
http://www.sql-server-performance.com/articles_audit.asp
http://www.sql-server-performance.com/sql_server_performance_audit10.asp

Performance -Link Server Performance Tips
http://www.sql-server-performance.com/linked_server.asp

Performance Monitoring
http://www.microsoft.com/technet/prodtechnol/sql/2005/library/operations.mspx Performance WP's
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx Troubleshooting Performance 2005
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp Hardware Performance CheckList
http://www.sql-server-performance.com/ss_performance_monitoring.asp Practical Solution for Monitoring
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=224587 Troubleshooting App Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp Disk Monitoring
http://sqldev.net/misc/WaitTypes.htm Wait Types
http://support.microsoft.com/?id=271509 Script to Monitor Blocking

Performance Tuning -Articles
http://www.sql-server-performance.com/articles_performance.asp

Performance Tuning –Hardware
http://www.sql-server-performance.com/sg_sql_server_performance_article.asp