optimization, comparing execution plan

I have a sp that was taking longer than it should. I found a large
inefficiency in the code (a loop that executed the same expensive SQL
multiple times), and so I re-implemented a large portion of que code.
Because of this I'm having trouble comparing the 'efficiency' of the new sp
over the old one, because I'm not sure how to compare measurements from the
execution plan between the two functions. If I'm optimizing one sp, it's
easy to see the optimization is good when the cost numbers in the same code
paths start decreasing. But I don't think these numbers have any meaning
when comparing between two different functions with different execution
paths altogether.
So, how would you recommend I compare the two so that I can definitively say
the new sp is faster and more efficient than the old one?
Thanks.axis
To compare both sp
DBCC DROPCLEANBUFFERS
SET STATISTICS IO ON
SET STATISTICS TIME ON
--Run First one
DBCC DROPCLEANBUFFERS
SET STATISTICS IO ON
SET STATISTICS TIME ON
--Run second one
"axis" <none@.none.com> wrote in message
news:4253f751$1@.news.microsoft.com...
> I have a sp that was taking longer than it should. I found a large
> inefficiency in the code (a loop that executed the same expensive SQL
> multiple times), and so I re-implemented a large portion of que code.
> Because of this I'm having trouble comparing the 'efficiency' of the new
sp
> over the old one, because I'm not sure how to compare measurements from
the
> execution plan between the two functions. If I'm optimizing one sp, it's
> easy to see the optimization is good when the cost numbers in the same
code
> paths start decreasing. But I don't think these numbers have any meaning
> when comparing between two different functions with different execution
> paths altogether.
> So, how would you recommend I compare the two so that I can definitively
say
> the new sp is faster and more efficient than the old one?
> Thanks.
>|||Thanks. I had tried that and it comes back with 30 to 40 lines of time
measurements (it seems to measure each statement execution). Is there a way
to just get the sp execution time?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:esjQGnrOFHA.1476@.TK2MSFTNGP09.phx.gbl...
> axis
> To compare both sp
> DBCC DROPCLEANBUFFERS
> SET STATISTICS IO ON
> SET STATISTICS TIME ON
> --Run First one
> DBCC DROPCLEANBUFFERS
> SET STATISTICS IO ON
> SET STATISTICS TIME ON
> --Run second one
>
>
> "axis" <none@.none.com> wrote in message
> news:4253f751$1@.news.microsoft.com...
> sp
> the
> code
> say
>|||If I turn on TIME and execute one SP, would adding up all the "elapsed time
= #ms" messages I get give me a rough estimate of the sp's execution time?
"axis" <none@.none.com> wrote in message news:4253fb31@.news.microsoft.com...
> Thanks. I had tried that and it comes back with 30 to 40 lines of time
> measurements (it seems to measure each statement execution). Is there a
way
> to just get the sp execution time?
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:esjQGnrOFHA.1476@.TK2MSFTNGP09.phx.gbl...
new
from
it's
meaning
execution
definitively
>|||Uri,
Did you mean DBCC FREEPROCCACHE?
AMB
"Uri Dimant" wrote:

> axis
> To compare both sp
> DBCC DROPCLEANBUFFERS
> SET STATISTICS IO ON
> SET STATISTICS TIME ON
> --Run First one
> DBCC DROPCLEANBUFFERS
> SET STATISTICS IO ON
> SET STATISTICS TIME ON
> --Run second one
>
>
> "axis" <none@.none.com> wrote in message
> news:4253f751$1@.news.microsoft.com...
> sp
> the
> code
> say
>
>|||A quick way would be to compare the execution plan of the old and
optimized SP in one go. Highlight the two stored procedures and do
Ctrl-L, this should show you the relative query cost of one over the
other.
Aramid
On Wed, 6 Apr 2005 10:50:22 -0400, "axis" <none@.none.com> wrote:

>I have a sp that was taking longer than it should. I found a large
>inefficiency in the code (a loop that executed the same expensive SQL
>multiple times), and so I re-implemented a large portion of que code.
>Because of this I'm having trouble comparing the 'efficiency' of the new sp
>over the old one, because I'm not sure how to compare measurements from the
>execution plan between the two functions. If I'm optimizing one sp, it's
>easy to see the optimization is good when the cost numbers in the same code
>paths start decreasing. But I don't think these numbers have any meaning
>when comparing between two different functions with different execution
>paths altogether.
>So, how would you recommend I compare the two so that I can definitively sa
y
>the new sp is faster and more efficient than the old one?
>Thanks.
>|||On Wed, 6 Apr 2005 11:06:51 -0400, axis wrote:

>Thanks. I had tried that and it comes back with 30 to 40 lines of time
>measurements (it seems to measure each statement execution). Is there a way
>to just get the sp execution time?
Hi Axis,
Here's a method that I often use:
-- First, write dirty buffers to disk and clear the data cache,
-- so that measuring is not influenced by logical vs physical I/O
CHECKPOINT
DBCC DROPCLEANBUFFERS
-- Also clear out the procedure cache to get rid of old plans
DBCC FREEPROCCACHE
go
DECLARE @.started datetime, @.ended datetime
SET @.started = CURRENT_TIMESTAMP
/* Insert code to performance-test here */
SET @.ended = CURRENT_TIMESTAMP
SELECT DATEDIFF(ms, @.started, @.ended) AS "ms elapsed"
go
Perform your tests when noone else is using the system. Run the test a
couple of times and use the average execution time (even when noone is
using the server, there might be maintenance processes or OS overhead
kicking in from time to time).
If the code isn't very long-running, you might include a loop to execute
it 10 times before calculating elapsed time. The first one will run
slower (since data has to be read into cache), the next executions will
read data from cache, so that other efficiency aspects than just
physical I/O will influence the results.
Oh, before I forget - if the code to be tested includes queries that
return data to the front-end and the results are more than just a few
rows, than you should modify this to SELECT ... INTO #Temp FROM ...
This will route the output to a temp table instead of to the QA output
window. If you don't do this, you'll only measure how fast QA can
format, display and scroll the result set...
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Optimal SQL

Anybody know why Query B is so much more expensive than Query A? Would you agree that Query C is the best bet?

Goal:
Return 0 results

Given:
CustomerID will never be '99999'
CustomerID is the primary key

A - SELECT * FROM Customers WHERE CustomerID=99999
B - SELECT * FROM Customers WHERE CustomerID<>CustomerID?
C - SELECT TOP 0 * FROM Customersin A you are scanning a select range of rows from a clustered index. SQL server is doing a binary search and thus able to eliminate most of the data within a couple of evaluations.

in B you are scanning all rows of a clustered index. Since you are asking SQL server to compare two attributes of a record, each record must be evaluated.

in C you are scanning an internal table.

I personnaly prefer select * from <table name> where 1 = 2.|||HI,
Agree with Paul .
select * from <table name> where 1 = 0
Cheers
Gola
Originally posted by Paul Young
in A you are scanning a select range of rows from a clustered index. SQL server is doing a binary search and thus able to eliminate most of the data within a couple of evaluations.

in B you are scanning all rows of a clustered index. Since you are asking SQL server to compare two attributes of a record, each record must be evaluated.

in C you are scanning an internal table.

I personnaly prefer select * from <table name> where 1 = 2.|||Thanks Paul. What you said makes perfect sense. I knew how SQL impelemented indexing, but failed to see the obvious need to skip the indexing and go straight to a table scan and scan each record individually when comparing one field to another.

Gola's example would seem to be more efficient, as would option C or as a friend pointed out, even...

SELECT * FROM Customers WHERE CustomerID IS NULL.

...would work since we are looking at the primary key.

Thanks guys.

Moki|||I don't agree with your friend. You will still be scanning part of the index. If the table doesn't have an index you will be scanning the table. My suggestion will always scan an internal table regardless.

the only diffrence between my suggestion and Gola's is the numbers used to generate a false condition.|||I'll need to think about that last one...maybe I don't understand MSSQL's implementation like I thought. I would have thought the primary key was always indexed and so checking for NULL would be the same as running thru the B-Tree looking for a value.

In either case, how about option C, "SELECT TOP 0...."?|||Yes, in SQL server the primary key will have some type of index. My point was that your friends suggestion will cause a partial index scan when a table has a usable index OR a table scan if no index is present or no existing indexes are present to match your where clause.

Bottom line, C is the best choice of the three.|||Ok, thanks. I thought about it (Option C) and the suggestion from you (and Gola), and think I like y'alls better. Only because I'm questioning the use of "...TOP 0..." and its conformance (or possible lack thereof) to the ANSI-92 standard...just in case that becomes a customer's constraint in the future.

Thanks again.