November 25, 2004 at 7:36 am
just wondering.....
One of our developer has asked me the next question;
We have a OLAP fact table (15.000.000 records) which has a view on top (for OLTP issues) gathering some SUM calulations. Now we have a Stored Procedure which is querying this view using "sp_executeSQL". The performance of this SP is 10 times slower as executing the same (compilated) selects-string, used in the sp_executeSQL, in the QueryAnalyzer.
The dev team forgot to "id" and "index" this view ( and set no schemabinding as well ) but the result will be probably the same -- 10 to 1 ( its to complex to add all these things )
What can be the cause of this slow action?.....anyone???
Guus kramer
The Netherlands
November 25, 2004 at 10:32 am
This is called "dynamic SQL" when you use sp_executeSQL to execute string . It's slows down query, because it parced on every execution. I think this is the main reason.
November 25, 2004 at 11:48 pm
Svetlana,
I know the purpose and use of sp_execute SQL but....
....my question is why this mechanisme is that slow comparing to a 'normal' parse through SQL Analyzer.....
....What does it do???....
....I know for instance that the 2 ways mentioned give a complete different execution plan....but why???
Guus
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply