Steve,
Got your e-mail (replied directly as well). Essentially, that's what I
meant. Posting in NG in case it helps someone else later.
The scenario that we had with this was that we had a view which gathered
the data from 4 other views. Each of the 4 underlying views were a
little complex and took a while to run. Combined in the ‘top level’
view, the performance was slow. The application ran a simple summary
query of the top level view when opening. This took about 50 seconds to
run which was unacceptable to the users.
Imagine… (obviously change the names to something more meaningful)
myView1, myView2, myView3, myView4 all feed into myTopView. The
application looks to query the object myTopView.
I renamed the view from myTopView to myTopViewFull.
I created a table called myTopViewTable (the same name as the view and
the same structure as the data returned from the original view –
myTopView).
Then I created a view called myTopView (same name as my original top
level view) which pointed to the table myTopViewTable instead of the 4
lower level views. Simple ‘select *’ will do it.
All I had to do was a very simple SP which truncated the table
myTopViewTable and inserted the data from myTopViewFull. This still took
a while to run, but it only takes the hit on the server and the user
perception is changed and they think it’s run quicker. Instead of a
bunch of users all doing the same thing at a 50 second cost to each of
them, we only have a 50 second cost on the server every 10 minutes.
For our needs, it doesn’t matter for this data if we update it every 10
minutes, but the difference to the users was quite noticeable. The time
to open the application dropped from 50 seconds plus to consistently
lower than 5 seconds. I did add some indexes to the main tables
(referenced by the view) to try and speed things up and it helped a
little. I found more performance improvements in the application though
as a result.
The main benefit of this is shifting the perception of work from the
users to the server. You can also try using OPTION (NO LOCK) on the
select statements to reduce locking / blocking issues as you are taking
the data into a table directly.
Also, as SQL tables / views cannot have the same name, if you have a
view that runs far too slowly, you can sometimes get a bit of a
performance advantage by doing this. It’s crude, but it works provided
you apply it correctly. No changes to the application should be needed.
A simple test is, drop the data from the view into a table. Try a query
that you know takes a while against the view and against the table. See
if there is a performance improvement. Oh, and you could try indexes on
the new table provided you don’t take a hit re-building them.
Indexed views are possibly another option though.
Hope that helps
Ryan
*** Sent via Developersdex
http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
