SQL and Indexed Views

So we have a fairly complex table structure to deal with when it comes to determining how products are moved about a district.  A product can have numerous statuses from in use, available,  in transit and etc.. etc..etc…  Often our business annalist want to know what, when and who — plus a nice report or chart so others can view this information and while your at it pivot the custom fields…  In our website we have one page that gathers up data across 40 various tables, pivots and calculates information in a user friendly and searchable manner.  A medium sized customer will realize the table structure in memory on our server and load up the system with a possible 78 megs of simple data.   As you can imagine the loading time on the page was dismal, plus if you started to page and etc … well, you had to wait .. and wait while the pager index was recalculated and etc… This was just not making the developers and customers very happy at all… After much refactoring, streamlining, grooming and optimization we go the first load of the page down to a reasonable amount of time, but we were still not happy with the results.  Our next plan of attack was to look at the LINQ TO SQL statements that the page depended on.  In the end, we dumped the linq to sql moving instead to a series of indexed views which were aggregated to a simple user defined function in sql server 2008 r2.  This not only improved the pages performance, it greatly reduced the memory load on the server to boot.

This article; by simple talk, is something I highly recommend folks read if you are trying to squeeze a second or two from your website page load times…


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s