Compare Queries

I am creating an SP. I want to compare alternative ways of writing the query
to see which one is the most efficient. I am trying to use the execution
plan but it doesn't give an overall cost of the query so I can compare it to
the alternative query. Rather I get a break down of the various steps (this
query uses functions which produce aggregates and these steps are included
in the execution plan) . I want to explore the steps later, at this point I
just want to compare the two queries as a whole. What is the best way of
going about this? Regards, Chris.What I'd do to start is to fire up SQL Server Profiler, turn on the
SQL:BatchCompleted event, and collect the Reads, Writes, CPU, and Duration
columns. Run your queries and compare the output...
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Chris" <nospam@.nospam.com> wrote in message
news:emgxeRbuHHA.3796@.TK2MSFTNGP02.phx.gbl...
>I am creating an SP. I want to compare alternative ways of writing the
>query to see which one is the most efficient. I am trying to use the
>execution plan but it doesn't give an overall cost of the query so I can
>compare it to the alternative query. Rather I get a break down of the
>various steps (this query uses functions which produce aggregates and these
>steps are included in the execution plan) . I want to explore the steps
>later, at this point I just want to compare the two queries as a whole.
>What is the best way of going about this? Regards, Chris.
>|||Chris,
Put both queries in the same query window and run them. Then the
percentiles will reflect both queries. Therefore, if query1 and its
functions use 25% and query2 and its functions use 75%, that suggests that
query1 is more efficient. However, UDFs (and some other functions) do
degrade the reliability of these numbers. Still, it is a good first take.
However, running both queries repeatly and getting a set of actual execution
times for each will, in the final analysis, be a more accurate measure of
the queries.
RLF
"Chris" <nospam@.nospam.com> wrote in message
news:emgxeRbuHHA.3796@.TK2MSFTNGP02.phx.gbl...
>I am creating an SP. I want to compare alternative ways of writing the
>query to see which one is the most efficient. I am trying to use the
>execution plan but it doesn't give an overall cost of the query so I can
>compare it to the alternative query. Rather I get a break down of the
>various steps (this query uses functions which produce aggregates and these
>steps are included in the execution plan) . I want to explore the steps
>later, at this point I just want to compare the two queries as a whole.
>What is the best way of going about this? Regards, Chris.
>|||The wording is a bit unclear. My SP could potentially contain a variety of
variables obtained from sub queries etc. This means there will be more than
just two queries to compare. How would you deal with a more complex scenario
with SP's that use temp tables or subqueries etc.
Regards, Chris.
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:emDSzebuHHA.4916@.TK2MSFTNGP04.phx.gbl...
> Chris,
> Put both queries in the same query window and run them. Then the
> percentiles will reflect both queries. Therefore, if query1 and its
> functions use 25% and query2 and its functions use 75%, that suggests that
> query1 is more efficient. However, UDFs (and some other functions) do
> degrade the reliability of these numbers. Still, it is a good first take.
> However, running both queries repeatly and getting a set of actual
> execution times for each will, in the final analysis, be a more accurate
> measure of the queries.
> RLF
> "Chris" <nospam@.nospam.com> wrote in message
> news:emgxeRbuHHA.3796@.TK2MSFTNGP02.phx.gbl...
>>I am creating an SP. I want to compare alternative ways of writing the
>>query to see which one is the most efficient. I am trying to use the
>>execution plan but it doesn't give an overall cost of the query so I can
>>compare it to the alternative query. Rather I get a break down of the
>>various steps (this query uses functions which produce aggregates and
>>these steps are included in the execution plan) . I want to explore the
>>steps later, at this point I just want to compare the two queries as a
>>whole. What is the best way of going about this? Regards, Chris.
>|||On Jun 29, 1:02 am, "Chris" <nos...@.nospam.com> wrote:
> The wording is a bit unclear. My SP could potentially contain a variety of
> variables obtained from sub queries etc. This means there will be more than
> just two queries to compare. How would you deal with a more complex scenario
> with SP's that use temp tables or subqueries etc.
> Regards, Chris.
> "Russell Fields" <russellfie...@.nomail.com> wrote in message
> news:emDSzebuHHA.4916@.TK2MSFTNGP04.phx.gbl...
>
> > Chris,
> > Put both queries in the same query window and run them. Then the
> > percentiles will reflect both queries. Therefore, if query1 and its
> > functions use 25% and query2 and its functions use 75%, that suggests that
> > query1 is more efficient. However, UDFs (and some other functions) do
> > degrade the reliability of these numbers. Still, it is a good first take.
> > However, running both queries repeatly and getting a set of actual
> > execution times for each will, in the final analysis, be a more accurate
> > measure of the queries.
> > RLF
> > "Chris" <nos...@.nospam.com> wrote in message
> >news:emgxeRbuHHA.3796@.TK2MSFTNGP02.phx.gbl...
> >>I am creating an SP. I want to compare alternative ways of writing the
> >>query to see which one is the most efficient. I am trying to use the
> >>execution plan but it doesn't give an overall cost of the query so I can
> >>compare it to the alternative query. Rather I get a break down of the
> >>various steps (this query uses functions which produce aggregates and
> >>these steps are included in the execution plan) . I want to explore the
> >>steps later, at this point I just want to compare the two queries as a
> >>whole. What is the best way of going about this? Regards, Chris.- Hide quoted text -
> - Show quoted text -
Make each version of your SP. Then capture in profiler
SP:StatementCompleted
with read,writes,duration,CPU
You need to clear database and procedure cache before each
execution of SP to have accurate values
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE