Adam Haines (1/8/2009)
Peter,I love how you only return the totals in the first row of the output. this will definitely reduce the amount of data being returned to the application. I have a few things to note here about the functionality and you may have addressed them in your environment. First there should be some sort of filter in the first CTE. This way you can reduce the amount of data searched through. Secondly, have you looked at using a stored procedure to handle the code, instead of the application executing ad-hoc SQL? The reason I ask is, the db engine will never be able to reuse a query plan unless an exact binary match already exists. This means you are not benefiting from query plan reuse or parameterization. Also, by introducing a stored procedure solution, you can remove the nested cte and use variables to maintain the totals. You can then apply a case statement to the final result set to only put the totals on the first row. For example, case when @PgFirstRecord = RowNbr then @TotalPages else null end.
Thanks for the compliment 🙂
I simplified the (fictual) query as far as I could for demo purposes, thus no where clause. But you are right in making it clear that when a filter need to be used in the query, it should be done in the topmost CTE (this might not immediatly obvious for those new to CTE's).
In my production situation some of the base queries span several screen pages with complex (partial generated) conditions. The true variable values in the code are all send to SQL sever separate from the base query, it is a feature of the language I use this in (ColdFusion). So as long as a second request comes in with the exact same structure (which is quite likely when working on a grid that has default settings) the query plans wil get reused without a problem. There will just be several for each permutation / filtering options that are in use.
There are cases when stored procedures make more sense of course, but not in the environment I work in and the type/method of development we do. It is all too small scale, no dedicted DBA and every developer needs to make his own SQL code. Usualy that goes quite well as the average SQL knowledge level ain't too bad. If things to seem too slow (that happends sometimes), it's me who works on it to solve the issue (quite liking that part I have to say).