execution of select

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

execution of select

Hannes Ricklefs
Hi,

I am using the prepare bind step ... approach for getting my data out of the
database.

I am wondering at what stage the actual SQL gets executed. Is that the first
call to step? or does the sql get executed for each step call?

I am thinking that the average call to step should be the same no matter how
big the database is?!

Thanks in advance,
Hannes
Reply | Threaded
Open this post in threaded view
|

Re: execution of select

Igor Tandetnik
Hannes Ricklefs
<[hidden email]> wrote:
> I am wondering at what stage the actual SQL gets executed. Is that
> the first call to step? or does the sql get executed for each step
> call?

I'm not sure what you mean by "executed". Prepare generates an execution
plan. With each call to step, this execution plan is carried out until
one row is available.

> I am thinking that the average call to step should be the same no
> matter how big the database is?!

I guess this depends on how you define the set you are averaging over.
Even within the same database, time taken by a call to step may vary
widely. Consider:

select * from table;

You get the records from table in no particular order. Every step call
will take approximately the same time, independent of the size of the
table.

select * from table
order by someField;

Assuming there is no index on someField, this query requires retrieving
all records from table into memory and sorting them there before the
first row can be produced. As a result, the first step can take a long
time, proportional to NlogN where N is the number of records, whereas
subsequent steps are fast.

Igor Tandetnik