February 19, 2011 at 6:17 am
I am executing a test query on a local machine directly.
select col1 from Table1 where col1 is null
I am using SQL Server 2005, table contains 97762 rows out of which 92363 rows returned, col1 is of varchar type
I am the only user connected to machine and executing only one statement.
time statistics shows CPU time =1794 ms, elapsed time = 4549 ms.
when i checked wait_type, it is showing ASYNC_NETWORK_IO.
if created index on col1 as results the CPU times reduced very much but elapsed time increased to 4907 ms
any idea y?
February 19, 2011 at 8:58 am
You're retrieving 92k out of 95k from the table. There's no real way to speed that type of data access up. You're going to get scans, and you're dependent on the speed of your disks and the speed of your network, and of course, the load on each. To speed this up, move less data. Are you doing a ETL process or is this a report of some kind. If it's an ETL process, try exporting directly to file, local to the server and then move the data. That will reduce the amount that the network is involved. If it's a report, no one, and I mean no one, reads 92000 rows of data. Reduce the data being returned.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply