comparing query results

1. What is the best way to compare efficiency (performance) of two different
queries that return the same result in general?
2. What if one contains user defined function one query and another without
user defined function? The reason I am asking this is that the optimizer's
cost model do not reflect the work done in UDF in the query execution plan.
ThanksJustin
Compare both exection plans first. In my experience ( sure it depends) that
queries which don't no UDF/s perform better
"Justin" <nospam@.nospam.com> wrote in message
news:O%23$Nz46jGHA.4828@.TK2MSFTNGP04.phx.gbl...
> 1. What is the best way to compare efficiency (performance) of two
> different queries that return the same result in general?
> 2. What if one contains user defined function one query and another
> without user defined function? The reason I am asking this is that the
> optimizer's cost model do not reflect the work done in UDF in the query
> execution plan.
> Thanks
>|||I'd like to use Profiler to see reads etc. Profiler *does* include the work
done inside an UDF
(something that execution plan or STATISTICS IO doesn't do).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Justin" <nospam@.nospam.com> wrote in message news:O%23$Nz46jGHA.4828@.TK2MSFTNGP04.phx.gbl.
.
> 1. What is the best way to compare efficiency (performance) of two differe
nt queries that return
> the same result in general?
> 2. What if one contains user defined function one query and another withou
t user defined function?
> The reason I am asking this is that the optimizer's cost model do not refl
ect the work done in UDF
> in the query execution plan.
> Thanks
>|||Justin a crit :
> 1. What is the best way to compare efficiency (performance) of two differe
nt
> queries that return the same result in general?
> 2. What if one contains user defined function one query and another withou
t
> user defined function? The reason I am asking this is that the optimizer'
s
> cost model do not reflect the work done in UDF in the query execution plan
.
> Thanks
>
The most effective parameters in order are :
1) IO, so execute your queries with this flag set :
SET STATISTICS IO ON /OFF
2) the time taken by CPU and other subsystems, so
SET STATISTICS TIME ON
The exection plan is not a good indicator because it is not quiclky
readable and the number of basic steps is not correlated with time or IO
consummation.
A +
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************