RE: expected result rows in select
I'm not so sure that "select count(*) . . ." actually counts everything --
running it on a fairly large table here (2.5M rows) returns almost
instantly, whereas
select count(*) from table where 1=1
gives the same answer after a much longer time.
> -----Original Message-----
> From: jmseo [SMTP:jmseo2000@netsgo.com]
> Sent: Thursday, October 21, 1999 10:06 PM
> To: informix-list@iiug.org
> Subject: expected result rows in select
>
> Hi,
>
> In INFORMIX/ESQL-C, I need to know expected result rows in select
> statement.
> Usually, application knows the result rows after finishing to fetch
> results.
> But, my application needs to know before fetching.
>
> In maual, sqlca.sqlerrd[0] field is said that
> ' After a successfule PREPARE statement for a SELECT, UPDATE, ....., this
> field contains
> the estimated number of rows affected.'
> I tested it but failed.
>
> Would you tell how I can get the number of rows before fetch the result?
> I think to execute "select count(*) ..." is the worst choice....because of
> performance.
>
> Reply to me please...
>
> Regards,
> Jungmin Seo
>
In article <7uqidu$c1h$1@news.xmission.com>, Demus, Alan
<demusa@hastings-ent.com> writes
>
>I'm not so sure that "select count(*) . . ." actually counts everything --
>running it on a fairly large table here (2.5M rows) returns almost
>instantly, whereas
>
If stats are up to date it will use them. select count(*) is a few
seconds straight after updating stats on all tables even if the data
for the table has been pushed out of buffers since update stats read
the whole database.
>select count(*) from table where 1=1>
>gives the same answer after a much longer time.
>
>> -----Original Message-----
>> From: jmseo [SMTP:jmseo2000@netsgo.com]
>> Sent: Thursday, October 21, 1999 10:06 PM
>> To: informix-list@iiug.org
>> Subject: expected result rows in select
>>
>> Hi,
>>
>> In INFORMIX/ESQL-C, I need to know expected result rows in select
>> statement.
>> Usually, application knows the result rows after finishing to fetch
>> results.
>> But, my application needs to know before fetching.
>>
>> In maual, sqlca.sqlerrd[0] field is said that
>> ' After a successfule PREPARE statement for a SELECT, UPDATE, ....., this
>> field contains
>> the estimated number of rows affected.'
>> I tested it but failed.
>>
>> Would you tell how I can get the number of rows before fetch the result?
>> I think to execute "select count(*) ..." is the worst choice....because of
>> performance.
>>
>> Reply to me please...
>>
>> Regards,
>> Jungmin Seo
>>
--
David Williams