I have a scenario where a Stored procedure calls several user defined functions inside and other statements which take a high execution time
what kind of optimization theory should i carry out to optimize this query ? and concerns ?
Add a comment
|
1 Answer
Optimization is a complex subject which cannot adequately be answered with no code or no access to the exact situation. However there are some guidelines. First user-defned functions by themselves can be slow particularly scalar ones. Replacing them with inline code may be much faster (not in all cases of course, this why you measure the baseline time and try different alternatives) because many UDFs cause the database to have to run row by row instead of acting on the set of data.
If you are going to be optimizing complex sql, first you need to learn how to read execution plans (search the web for some free ebooks on this) and then you need to use Profiler effectively (especially if your code is generated by the application). You need to learn about indexing, sargability and the ways to replace cursors and looping actions with set-based queries.
There are lots of good books on performance tuning, I'd suggest you buy them.
2 Comments
EBarr
Gonna have to agree here ... what's the SQL look like? What indexes exist on the underlying tables? What's your usage pattern( high reads, infrequent writes?)? What does SQL profiler show you -- reads?, duration? cpu? row count?
Stephanie Page
I'll ask this in a full question because I think the answer is important. Can you explain this statement, "because many UDFs cause the database to have to run row by row instead of acting on the set of data." AFAIK, at some point, every RDBMS operates row-by-row (rxr). Sometimes it can do more than one rxr operation at the same time i.e. parallel but...