More on returning a subset of a recordset | ||||
|---|---|---|---|---|
| Last week's tip created quite a discussion on different techniques for returning a subset of a recordset: In this article, I want to briefly discuss these solutions as it relates to their performance and effectiveness. Here is the solution mentioned in the tip that generated so much discussion - Click here to view the stats for the above solution using Query Analyzer's Server Trace. You can see that the bulk of the time and resources used by this approach are during the declaration and opening of the cursor. This approach is by far the least efficient of the ones offered, but aside from that, it does possess some good qualities. One good quality is that it works. Sometimes just getting the job done is what matters. This approach is also intuitive. Logically, our brain works in the order that the first solution is presented in. We naturally think in a 'loop' when we try to solve paging problems or repetitive tasks. This solution works in that manner. This next approach is slightly modified. There is no cursor, and if you examine the , you can see that is it much less resource intensive. Another difference is that it builds the sql string dynamically. This isn't bad, but it doesn't take advantage of the precompile benefits that a stored procedure offers. Another point (offered by one of our readers), is that when @BatchesToSkip = 0, the query will return nothing. Despite some of the limitations presented in this method, it offers good insight into reworking an initial solution into one that is cleaner and more robust.
|