MS SQL views are slow if they have many columns, regardless of the number in select statement calling the view
Posted on 2008-10-31
It appears that a large number of columns in a view in MS SQL affects performance even if the query, when run against the view, contains only a couple of columns. I've tested the exact same query against the tables directly and it takes a couple of seconds. Select the same several columns from a view joining the tables with all columns, using the same criteria and it takes 2 minutes.
Why is this and any way to speed it up?
The joined view results in several hundred columns and about 50000 records. Most of the columns are empty varchar fields. I know the easy answer would be to run the select statement directly against the tables but there are a bunch of existing architectural reasons related to the app that would make that a problem.
Thanks for any feedback on this.