|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
how do I test the cost of udf?I am currently doing performance tuning, in particular, query tuning.
Before I used a user defined function to get the result set. The new query now uses "set-based" approach to get the result set. I am pretty sure the newer version is a lot more efficient. Since the new query has more joins to get the desired result, when I run the "Display estimated execution plan" to compare, the estimated cost is higher than the query that uses user defined function since the cost of user defined function was not taken into account. So how do I test quantatively to see the newer version is more efficient? Thanks I prefer to use Profiler to catch reads, CPU etc. Profiler does not exclude UDF work.
-- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "Justin" <nospam@nospam.com> wrote in message news:eQDBLGp0GHA.4920@TK2MSFTNGP06.phx.gbl... >I am currently doing performance tuning, in particular, query tuning. > > Before I used a user defined function to get the result set. The new query now uses "set-based" > approach to get the result set. I am pretty sure the newer version is a lot more efficient. > Since the new query has more joins to get the desired result, when I run the "Display estimated > execution plan" to compare, the estimated cost is higher than the query that uses user defined > function since the cost of user defined function was not taken into account. > > So how do I test quantatively to see the newer version is more efficient? > Thanks > <DIV>"Justin" <nospam@nospam.com> wrote in message
news:eQDBLGp0GHA.4920@TK2MSFTNGP06.phx.gbl...</DIV>>I am currently doing performance tuning, in particular, query tuning.> SET STATISTICS IO ON> Before I used a user defined function to get the result set. The new > query now uses "set-based" approach to get the result set. I am pretty > sure the newer version is a lot more efficient. Since the new query has > more joins to get the desired result, when I run the "Display estimated > execution plan" to compare, the estimated cost is higher than the query > that uses user defined function since the cost of user defined function > was not taken into account. > > So how do I test quantatively to see the newer version is more efficient? > Thanks > SET STATISTICS TIME ON And compare the Logical Reads and CPU time. David When you run the profile or SET STATISTICS IO ON, SET STATISTICS TIME ON, do
you need to clear buffer every time? I would think so but just want to make sure Thanks all! Show quote "David Browne" <davidbaxterbrowne no potted m***@hotmail.com> wrote in message news:uqiQaMp0GHA.2356@TK2MSFTNGP03.phx.gbl... > > > <DIV>"Justin" <nospam@nospam.com> wrote in message > news:eQDBLGp0GHA.4920@TK2MSFTNGP06.phx.gbl...</DIV>>I am currently doing > performance tuning, in particular, query tuning. >> >> Before I used a user defined function to get the result set. The new >> query now uses "set-based" approach to get the result set. I am pretty >> sure the newer version is a lot more efficient. Since the new query has >> more joins to get the desired result, when I run the "Display estimated >> execution plan" to compare, the estimated cost is higher than the query >> that uses user defined function since the cost of user defined function >> was not taken into account. >> >> So how do I test quantatively to see the newer version is more efficient? >> Thanks >> > > SET STATISTICS IO ON > SET STATISTICS TIME ON > > And compare the Logical Reads and CPU time. > > David <DIV>"Justin" <nospam@nospam.com> wrote in message
news:OjZpJVp0GHA.1548@TK2MSFTNGP02.phx.gbl...</DIV>> When you run the profile or SET STATISTICS IO ON, SET STATISTICS TIME ON, do> you need to clear buffer every time? I would think so but just want to No. Absolutely do not clear the buffer cache or the procedure cache. And > make sure > you might need to run it a couple of times to warm up the caches. You should generally assume that your data will be cached, and tune on that basis. For a number of reasons, it's a much more valid assumption than assuming that your data will not be cached. David The cost shown in the execution plans is a good guidline for tuning, but not
100%. Memory, IO, and CPU requirements are all taken into account when determining the cost, and thus the execution plan. Two queries with different plans but the same cost will not necessarily perform equally. The available memory, IO speed, CPU speed and number of processors are all variables that won't be reflected in the cost. A memory intensive plan may have twice the cost of an IO intensive plan, but still run in half the time if you have enough memory to support it. Even if you get an estimated plan that takes into account your UDF, the actual numbers are not as useful as testing and benchmarking the performance of each option. Show quote "Justin" <nospam@nospam.com> wrote in message news:eQDBLGp0GHA.4920@TK2MSFTNGP06.phx.gbl... > I am currently doing performance tuning, in particular, query tuning. > > Before I used a user defined function to get the result set. The new query > now uses "set-based" approach to get the result set. I am pretty sure the > newer version is a lot more efficient. Since the new query has more joins > to get the desired result, when I run the "Display estimated execution plan" > to compare, the estimated cost is higher than the query that uses user > defined function since the cost of user defined function was not taken into > account. > > So how do I test quantatively to see the newer version is more efficient? > Thanks > >  |
|||||||||||||||||||||||
Other interesting topics