Sunday, March 14, 2010

Indexed views, not using your indexes

I was working on an export where we were building a query in a string to be passed to a CLR through a stored proc to a small .NET assembly on the db server and ran into some issues with performance. Now, of course CF is slow at running queries...that's why we passed to it .NET to handle the execution, but I didn't think .NET would be slow. I couldn't figure out why my query was slow for the life of me. All the indexes were place, no extra joins were added and the query still took 20 minutes to run. After talking to a DB developer he told me that even though we indexed the sql "views" the indexes were not being used. I quickly freaked out wondering why SQL didn't use the indexes on the views like it uses the indexes on the tables. It should be assumed. Turns out the reason was that, "It is just one of those things". To fix the issue we added "with noexpand" to the joins to the indexed view and then query time went down to about 8 seconds. Ridiculous.

No comments:

Post a Comment