Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 4 of 4 total
Thread Execution plan
Mon, Jan 4 2010 8:42 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Tim

I appreciate this may be impossible but if it can be done I'd love it.

A lot of times I have several ways to get the same result eg

SELECT COUNT(*) FROM Emails
WHERE
_MsgID = '<RACKFORC-J1M2FQe268f2cc5e6d4df7a06b7f7cc3d85090@rackforc-j1m2fq>'
AND                     
_InOutInd ='«M'

and

SELECT COUNT(*) FROM Emails
WHERE
_MsgID = '<RACKFORC-J1M2FQe268f2cc5e6d4df7a06b7f7cc3d85090@rackforc-j1m2fq>'
AND
NOT _fkMailBoxes IN (-2,-3,-4,-7)

both give the same result and both seem to be equivalent in terms of execution

"_MsgID" = '<RACKFORC-J1M2FQe268f2cc5e6d4df7a06b7f7cc3d85090@rackforc-j1m2fq>'
[Index scan (EMails.MessageID):, 1 keys, 4096 bytes estimated cost] AND
"_InOutInd" = '«M' [Row scan (EMails): 1 rows, 656 bytes estimated cost]


"_MsgID" = '<RACKFORC-J1M2FQe268f2cc5e6d4df7a06b7f7cc3d85090@rackforc-j1m2fq>'
[Index scan (EMails.MessageID):, 1 keys, 4096 bytes estimated cost] AND
"_fkMailBoxes" NOT IN (- 2, - 3, - 4, - 7) [Row scan (EMails): 1 rows, 656 bytes
estimated cost]

If I'm just running once or twice a day who cares which is best but as in this case it will be run hundreds or thousands of times a day I'd like to be able to pick the genuinely most efficient one. Is there an indicator you can build in or is it simply add the estimated bytes and low number wins?

Roy Lambert
Mon, Jan 4 2010 11:25 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Roy,

<< If I'm just running once or twice a day who cares which is best but as in
this case it will be run hundreds or thousands of times a day I'd like to be
able to pick the genuinely most efficient one. Is there an indicator you can
build in or is it simply add the estimated bytes and low number wins? >>

When you say "geniunely", what do you mean ?  Are the execution timings that
much different between the two ?  The execution plans indicate that they are
virtually identical in terms of execution I/O.

--
Tim Young
Elevate Software
www.elevatesoft.com

Mon, Jan 4 2010 12:04 PMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Tim

>When you say "geniunely", what do you mean ? Are the execution timings that
>much different between the two ? The execution plans indicate that they are
>virtually identical in terms of execution I/O.

That's the problem. When you add in the various types of buffering and caching its difficult to spot the very minor differences. The dramatic ones where it goes from several minutes to less than a second I can just about decide which to use but when they're both running sub-second its pretty much impossible.

I have been using the sum of the bytes to give me an idea but I was hoping there was a better way.

As I said its only when I know its going to get run lots where even a miniscule difference adds up over the day that I'm bothered

Roy Lambert
Tue, Jan 5 2010 5:26 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Roy,

<< I have been using the sum of the bytes to give me an idea but I was
hoping there was a better way. >>

That's the best method when the times are so small as to be insignificant.
Two queries that have the same estimated I/O costs, will most likely execute
in about the same time, every time.

--
Tim Young
Elevate Software
www.elevatesoft.com

Image