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 - 2007-03-20 : 09:08:40
|
| dave writes "windows server 2003 R2 sp1 x86, mssql 2000 sp4I want to know what could cause a view to take 14 seconds to return its results when the exact same query will run directly in ISQL or as a stored procedure in under a second.We have several views that experience the same condition.I have tried several potential solutions including:1. different configuration combinations2. index changes3. database options None of my attempts have had any impact.Could you suggest any possible solutions?To work around this I have started writing many of my newest views as stored procedures which works fine, But I have close to a hundred that should benefit from the answer to this question.thanks!" |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-03-20 : 09:41:12
|
| You'll need to give some specific examples; pick one view and the stored proc you replaced it with, and let us see the relevant code and table structures.I suspect that you are comparing the performance of "SELECT * FROM SomeView" with a stored procedure that selects certain rows using criteria .... i.e., to "run" a view, you can just select all data from it, but to "run" a stored procedure you usually must provide parameters which filter the results, therefore returning much less data and much quicker.- Jeffhttp://weblogs.sqlteam.com/JeffS |
 |
|
|
|
|
|
view takes much longer then stored procedure