CODEWITHSUNDEEP
sqlsql-serveroptimization
Sudantha
Sudantha

Reputation: 16224

Optimizing SQL Queries

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 ?

Upvotes: 0

Views: 212

Answers (1)

HLGEM
HLGEM

Reputation: 96640

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.

Upvotes: 4

Related Questions