August 11, 2004 at 5:44 am
What happens if I execute a procedure which would result in a large number of rows but I only view a small number of rows.
In above scenario I monitored using performance monitor - network, bytes sent. It looks like the data is sent in peaks only when I scroll down.
So from a network point of view I assume it is not an issue running a procedure for 1,000,000 rows when the user might only want to view the first 20.
What about from the SQL Server point of view. Does all this unwanted data get extracted anyway and get stored somewhere else or does SQL Server wait for the request for more data?
How would I monitor this?
August 12, 2004 at 8:12 pm
Depends.
Depends on the SQL client - it might choose to retrieve all rows in one hit, but it sounds like your example did not.
Depends on how the opimiser executes the query - it may or may not be able to 'pipe' rows out as soon as each one is read (and joined?).
If there is an Order By it is likely that an internal Sort must be completed on all rows, before the first row can be returned. Only if order by matches clustered index might it be avoided.
If you put the query in QueryAnalyser and "Display Estimated Execution Plan", if it has an item named "Sort", it probably reads all rows before returning anything.
You should be able to tell the difference in Performance Monitor with
SQLServer-BufferManager: PageReads, ReadAheadReads, PageLookups
Or PhysicalDisk:Reads
August 13, 2004 at 5:46 am
Clay, many thanks, that was very helpful. It's starting to make sense now.
I ran some tests over a 1 minute period with a sample every second and then imported the result into Excel.
1. |
Doing nothing on the server and the frontend
2.
Ran query on frontend filtered to 37 rows (screenfull)
3.
Ran unfiltered query on front end but did not page down
4.
Ran unfiltered query on frontend and then went to last record 10,297 rows
Do you agree with my conclusions:
Impact on WAN
Frontend seems to pull more data than it needs for the immediate screen but only a small proportion of the total data. However as most users will only require the first screen unecessary WAN traffic is being generated if no filtering!
Impact on Server
With this particular query and an unfiltered query, there is very little difference between the frontend viewing 37 rows and 10,297 rows. There is a more significant difference between when the data is filtered and unfiltered but not sure how to interpret these figures.
Bytes sent |
Disk reads
Page lookups
Page reads
Readaheads
1.
1,981
35
151
1
0
2.
4,148
57
511
1
0
3.
87,915
100
591
298
263
4.
442,543
128
516
314
247
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply