Today, I'll be working with 2 sql views joined togeather to build a report and a filter page. The problem is both of these views are huge and very slow. I'm talking about about 50 lines of code a piece and a bunch of case statements within it to form calculations. I would love to post the code but it's rather sensitive data. I can say it pulls in roughly 200,000 to 300,000 records, if not more.
The data actually gets pulled in through a DTS (data transformation services) and gets stored in raw staging tables. The views were created early so it's really hard to turn back now without affecting a lot of reports across the application.
The real problem lies within the amount of calculations performed on so many records and the reports are trying to pull the data on the fly. The solution is to copy the records the views pulls in and cache them in a sql table (makes pulling the data 10 times faster). However, since the DTS runs so often, a script (stored procedure) needs to be written to copy the records over to a table. I know it doesn't make sense since that's why a view was built to begin with. However in this case, there is no other way around it (unless you know of a way) without stripping the whole backend database down and doing the backend correctly. However, when working on time contraints and your client is more demanding than ever. Not an option at this point.
Moral of story: If you are using views and you are pulling in large amounts of data, consider caching the view in a table.
If you enjoyed this post, subscribe to our RSS feed.
Written: Mar 25, 2008Tags: dts, sql views
Leave a Reply
You must be logged in to post a comment.