IDS Forum

RE: Estimated cost

Posted By: Michaelis, Daniel
Date: Monday, 27 June 2005, at 8:30 a.m.

In Response To: Estimated cost (PARAMESHWAR PATHRI)

Parameshwar,

I'd give you two pieces of feedback on this question. The first is that
while the estimated cost is one method of estimating query performance, it's
not always the best indicator that performance will be good or bad. Given
that, I'd look for "high-cost" statements, but then I'd do additional
analysis (including running and timing the statement) to see if there really
is a problem. If there isn't, I'd not worry too much about the numbers.

The second piece is that I'd hope that running the statement as a single
query would be "less expensive" than running multiple statements. Remember,
in the "cost" of the query is the cost of parsing, determining the best
access path, returning results; all of which only have to be done once in
the single statement, but once per statement in the multiple statement case.
As such, I'd expect that the sum of the costs to be greater than the cost of
the single statement.

That said, it does confuse me a bit that the cost on the x=1 case is larger
than the cost of the "in-clause" statement, but in order to make heads or
tails of that, we'd need a lot more information. I suspect that something
might have changed between the computations of those costs to skew the
results on the x=1 case negatively. The other cases might make sense if the
returned data for x=2 and x=3 is a minor portion of the original query.
There might also be a very different access path determined for those cases
than the x=1 case, but again, given the x=1 case in the "in clause"
statement, I'd assume that you'd have estimated costs that are closer than
you demonstrate here.

If I've answered your question, great. If not, we'll need more information
to help you out.

Thanks.

Dan Michaelis

Database Administrator/Developer
eOriginal
351 West Camden Street
Suite 800
Baltimore, MD 21201

410.625.5187 (phone)
410.659.9799 (fax)

-----Original Message-----
From: PARAMESHWAR.... [mailto:pcdudyala@yahoo.com]
Sent: Monday, June 27, 2005 3:09 AM
To: ids@iiug.org
Subject: Estimated cost [5258]

Hi,
I want to know why the sum of estimated cost in the second form of exection
is not same as in first SQL.

1. select * from test where x in (1,2,3)
estmated cost is 676

2nd method

select * from test where x=1
est cost is 1085
select * from test where x=2
est cost is 90
select * from test where x=3
est cost is 91

Ideally i feel the estimated cost of the serial execution of 3 selects
should match the est cost of first quuery.

If i am wrong pls let me know the mystry behind it.

Messages In This Thread

• Estimated cost
  PARAMESHWAR PATHRI -- Monday, 27 June 2005, at 3:09 a.m.
  • RE: Estimated cost
    Michaelis, Daniel -- Monday, 27 June 2005, at 8:30 a.m.
  • Re: Estimated cost
    Chakravarthy Pathri -- Monday, 27 June 2005, at 9:15 a.m.
    • RE: Estimated cost
      malcolm weallans -- Monday, 27 June 2005, at 2:28 p.m.
  • =?ISO646-US?Q?Re:_Re:_Estimated_cost_=3F?=
    vze2qjg5@verizon.net -- Wednesday, 29 June 2005, at 9:43 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.