Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-05-13 : 01:44:26
|
| DONNA writes "DOES ANYONE HAVE ANY STATS ON THE PERFORMANCE DIFFERENCES IN USING VIEWS VS BASE TABLE READS?? I KNOW IT IS SUBJECTIVE(ROW COUNTS, JOINS-INNER OR OUTER,ETC.). PLEASE IMAGINE WORST CASE SCENARIO VIEWS, LOTS OF JOINS, LEFT OUTERS ALL OVER THE PLACE PLUS KEY VALUE SELECTS USING 'OR'. SOMEONE THINKS ALL THE CRYSTAL REPORTS NEED TO BE RE-WRITTEN USING VIEWS. THEY ARE CURRENTLY USING STORED PROCS THAT DO ALL THE REAL WORK....GO FIGURE" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-13 : 07:21:58
|
quote:
THEY ARE CURRENTLY USING STORED PROCS THAT DO ALL THE REAL WORK....GO FIGURE"
Probably because that's the most efficient way to execute code in SQL Server. The execution plan for a stored procedure is cached, so that it doesn't need to be re-compiled each time it is called.Other than the generic statements you've made about views, there's no way to predict performance issues: A) without knowing the hardware involved, or the number of users;B) without the precise structure of the base tables and indexes involved in the views;C) without the precise SQL of the views, andD) without thoroughly testing, measuring, and tweaking the view's codeEven a worst-case view could perform acceptably. If a view is used a great deal the data it materializes will likely stay in the data cache, and it can perform just as well as a stored procedure. YOU need to test it. You need to test different indexes and index hints. There's no point in guessing when you can simply put a view together and run it side-by-side against the existing method. Look in Books Online for "performance tuning" and "showplan", it gives you the rundown on things to avoid (table scans) and how to avoid them. |
 |
|
|
donnaj
Starting Member
3 Posts |
Posted - 2002-05-13 : 09:28:28
|
| Thanks very much. I was hoping some stats were available. Right now the stored procs do about 6 to 10 table joins, many of them outer, with all kinds of pk and fk value selects. My fear is that the people who want to change the crystal reports to join views instead will be doing all that work inside the crystal report. I will not have any clout trying to make them understand that the joins, etc done inside the stored proc are more effecient than doing the joins inside the crystal code.Thanks again |
|
|
|
|
|
|
|
|
SQL SERVER 7 - VIEWS VS BASE TABLE READS