Estimated cost

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=1est cost is 1085 select * from test where x=2est cost is 90 select * from test where x=3est 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.

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=1est cost is 1085 select * from test where x=2est cost is 90 select * from test where x=3est 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.

hi, Here is the explain out delete from test where x in (19,10,1)Estimated Cost: 27 Estimated # of Rows Returned: 636 1) informix.test: INDEX PATH (1) Index Keys: x (Serial, fragments: ALL) Lower Index Filter: informix.test.x = 19 (2) Index Keys: x (Serial, fragments: ALL) Lower Index Filter: informix.test.x = 10 (3) Index Keys: x (Serial, fragments: ALL) Lower Index Filter: informix.test.x = 1 delete from test where x =19Estimated Cost: 5 Estimated # of Rows Returned: 90 1) informix.test: INDEX PATH (1) Index Keys: x (Serial, fragments: ALL) Lower Index Filter: informix.test.x = 19 delete from test where x =10Estimated Cost: 5 Estimated # of Rows Returned: 96 1) informix.test: INDEX PATH (1) Index Keys: x (Serial, fragments: ALL) Lower Index Filter: informix.test.x = 10 delete from test where x =1Estimated Cost: 44 Estimated # of Rows Returned: 1085 1) informix.test: INDEX PATH (1) Index Keys: x (Serial, fragments: ALL) Lower Index Filter: informix.test.x = 1 Bye. Here Jack Parker <vze2qjg5@verizon.net> wrote:First of all - the estimated cost is not an exact number - you should use it as an order of magnitude check, not a real value. Second of all, you need to tell us what the execution method of each is - the first and second look like a potential scan of a small table, while the second two look like they might be index reads. Third of all, in the first query, the engine is not going to read the table three times, it's going to read it once and check each row for your filter. In the 2-4th queries, you are doing three sets of reads - however they are performed. So if anything, in an ideal world, the estimated costs for each of the 4 queries should be the same. You may not have an index, but perhaps you've done update statistics on this column and the database knows there are no rows with a value of 2 or 3. That would explain the low cost there. j. ----- Original Message ----- From: "PARAMESHWAR...." To: Sent: Monday, June 27, 2005 4:09 AM 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. > > > --------------------------------- Yahoo! Sports Rekindle the Rivalries. Sign up for Fantasy Football

Having seen the exact queries and the explain.out I think I can explain the anomaly. You are changing the data between the query involving IN and the 3 queries using =. And even if you are setting the data back to what it was in the second case you are changing the populations with each delete. I don't suppose you performed an UPDATE STATISTICS statement between each DELETE. Hope this helps Malcolm -----Original Message----- From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] On Behalf Of Chakravarth.... Sent: 27 June 2005 14:16 To: ids@iiug.org Subject: Re: Estimated cost [5263] hi, Here is the explain out delete from test where x in (19,10,1)Estimated Cost: 27 Estimated # of Rows Returned: 636 1) informix.test: INDEX PATH (1) Index Keys: x (Serial, fragments: ALL) Lower Index Filter: informix.test.x = 19 (2) Index Keys: x (Serial, fragments: ALL) Lower Index Filter: informix.test.x = 10 (3) Index Keys: x (Serial, fragments: ALL) Lower Index Filter: informix.test.x = 1 delete from test where x =19Estimated Cost: 5 Estimated # of Rows Returned: 90 1) informix.test: INDEX PATH (1) Index Keys: x (Serial, fragments: ALL) Lower Index Filter: informix.test.x = 19 delete from test where x =10Estimated Cost: 5 Estimated # of Rows Returned: 96 1) informix.test: INDEX PATH (1) Index Keys: x (Serial, fragments: ALL) Lower Index Filter: informix.test.x = 10 delete from test where x =1Estimated Cost: 44 Estimated # of Rows Returned: 1085 1) informix.test: INDEX PATH (1) Index Keys: x (Serial, fragments: ALL) Lower Index Filter: informix.test.x = 1 Bye. Here Jack Parker <vze2qjg5@verizon.net> wrote:First of all - the estimated cost is not an exact number - you should use it as an order of magnitude check, not a real value. Second of all, you need to tell us what the execution method of each is - the first and second look like a potential scan of a small table, while the second two look like they might be index reads. Third of all, in the first query, the engine is not going to read the table three times, it's going to read it once and check each row for your filter. In the 2-4th queries, you are doing three sets of reads - however they are performed. So if anything, in an ideal world, the estimated costs for each of the 4 queries should be the same. You may not have an index, but perhaps you've done update statistics on this column and the database knows there are no rows with a value of 2 or 3. That would explain the low cost there. j. ----- Original Message ----- From: "PARAMESHWAR...." To: Sent: Monday, June 27, 2005 4:09 AM 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. > > > --------------------------------- Yahoo! Sports Rekindle the Rivalries. Sign up for Fantasy Football

Sigh. My machine has gone away, so I am stuck with webmail. This probably won't get through to the list. You can see that it is doing the same work for each query - and using an index to boot. The higher cost looks like it id due to the estimated number of rows returned for each query. However, these estimated costs are so low that they really are not worth worrying about. I would be more concerned if the statements in question actually took longer than you expected. Regards, Jack Parker >From: Chakravarthy Pathri <pcdudyala@yahoo.com> >Date: Mon Jun 27 08:09:21 CDT 2005 >To: Jack Parker <vze2qjg5@verizon.net>, ids@iiug.org >Subject: Re: Estimated cost [5258] >hi,Here is the explain out ?delete from test where x in (19,10,1)Estimated Cost: 27 >Estimated # of Rows Returned: 636? 1) informix.test: INDEX PATH??? (1) Index Keys: x?? (Serial, fragments: ALL) >??????? Lower Index Filter: informix.test.x = 19??? (2) Index Keys: x?? (Serial, fragments: ALL) >??????? Lower Index Filter: informix.test.x = 10??? (3) Index Keys: x?? (Serial, fragments: ALL) >??????? Lower Index Filter: informix.test.x = 1???delete from test where x =19Estimated Cost: 5 >Estimated # of Rows Returned: 90? 1) informix.test: INDEX PATH??? (1) Index Keys: x?? (Serial, fragments: ALL) >??????? Lower Index Filter: informix.test.x = 19 >?delete from test where x =10Estimated Cost: 5 >Estimated # of Rows Returned: 96? 1) informix.test: INDEX PATH??? (1) Index Keys: x?? (Serial, fragments: ALL) >??????? Lower Index Filter: informix.test.x = 10 >delete from test where x =1Estimated Cost: 44>Estimated # of Rows Returned: 1085? 1) informix.test: INDEX PATH??? (1) Index Keys: x?? (Serial, fragments: ALL) >??????? Lower Index Filter: informix.test.x = 1 >?Bye.?????Here > >Jack Parker <vze2qjg5@verizon.net> wrote:First of all - the estimated cost is not an exact number - you should use it >as an order of magnitude check, not a real value. > >Second of all, you need to tell us what the execution method of each is - >the first and second look like a potential scan of a small table, while the >second two look like they might be index reads. > >Third of all, in the first query, the engine is not going to read the table >three times, it's going to read it once and check each row for your filter.<BR>In the 2-4th queries, you are doing three sets of reads - however they are<BR>performed. So if anything, in an ideal world, the estimated costs for each<BR>of the 4 queries should be the same.<BR><BR>You may not have an index, but perhaps you've done update statistics on this >column and the database knows there are no rows with a value of 2 or 3. >That would explain the low cost there. > >j. >----- Original Message ----- >From: "PARAMESHWAR...." <PCDUDYALA@YAHOO.COM> >To: <IDS@IIUG.ORG> >Sent: Monday, June 27, 2005 4:09 AM >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. >> >> >> > > > Yahoo! Sports >Rekindle the Rivalries. Sign up for Fantasy Football