Monday, December 29, 2008

joins on t-sql table valued functions

I love using functions to create re-usable code.  However I recently ran into a major issue with them.

I created one to return a simple list of ids, based on some authentication data, which I could then use to return only records that users had access to.  I read a couple of articles which said table valued functions were great for performance, especially over scalar valued functions.  They cautioned to use them in the joins though and not in the select or where clauses; the articles claimed that the select and where clauses would cause them to execute once for each record.

I followed the advice of the articles, which seemed logical, and we started having all sorts of speed issues with our application.

Through a little trial and error I discovered that my table valued function would often add minutes onto the execution time of a sproc when used in the join area of the sql statement.  I tried moving it down to the Where clause using the In keyword and the sprocs began executing in less than a second again.

Maybe I was reading the articles backwards, but I have learned to use table valued functions in my where clause and not in the join part of a sql statement.

On a side note, I have lots of really small sprocs with the function in their join clause, so it is possible that the optimizer only has problems with it when there are at least a hand full of tables it is working with.

matrix RowNumber doesn't work with even rows

I recently ran into a new issue with Microsoft Reporting Services 2005 matrix control.
When trying to color alternating columns with collapsible row groups, I discovered that when a group contained an even number of rows the typical alternating code didn't work:
iif(RowNumber("matrix_RowGroup") Mod 2, "LightGrey", "White")

This is due to RowNumber being multiplied by the number of rows when a group is collapsed.

After some research and playing around I discovered that CountRows() would give me the number of rows in the current context, so when a group was expanded it would always return 1, while when the group was collapsed it would give me the number of rows in the group.  After that a little simple division worked wonders and fixed my issue:
iif(RowNumber("matrix_RowGroup")/CountRows() Mod 2, "LightGrey", "White")