| Subject | Problem with a query |
|---|---|
| Author | Jonathan Neve |
| Post date | 2004-06-11T09:36:12Z |
Hi!
I've got a strange behaviour with the following query:
select r.*, e.nomprenom as nomchauff
from recep r
join employes e on e.code = r.chauff
where
(r.date_recep >= '01/01/04' and r.date_recep < ('07/01/04' + 1))
and (r.num_fact is null)
and (r.affretement = 0)
What happens is simply that sometimes it takes up to about 10 seconds,
but often, it's nearly instantaneous. So I can't figure out why it's
sometimes so slow.
The RECEP table contains about 45000 records. Could this be the problem?
I've heard of some people having much bigger tables without any problem.
My application uses the BDE, so I thought it might have something to do
with that, but this morning, I tried it in IBPlanalyser (which uses
IBX), and it did the same thing: the first time I ran the query it took
about 4-5 seconds, and the following times, it hardly took 1 second.
I also thought it might have something to do the query being prepared,
because it seems that when it happens, it's usually on the first run of
the query. If I run the query again, the problem always disappears.
However, I don't think this (preparation of the query) could be the
problem, because, a) Often, the problem doesn't happen at all, even on
the first run, and b)Once I've had the problem once, even if I destroy
the form that contains the query, and open it again, it doesn't happen
again.
It seems as though it basically, "the first time", although I don't
quite know what it's the first time of. I can close my application, and
even all other connections to the database, and yet, it almost never
does it twice in a row. Even if I restart the Interbase server, it
doesn't always do it.
When trying to reproduce it, it almost never happens, but when I'm not
trying, it usually does it the first time... Not too sure how that
works, but it seems to be true! :-)
BTW, I'm using IB 6.0 and FB 1.5. Also, this is my development machine,
so there's only one person using it, it's not going over the network,
and I only do one thing at a time, so I don't see why I should get such
a random response time
Anyway, if anyone have any advice or explanation, it woul de much
appreciated.
Thanks!
Jonathan Neve.
I've got a strange behaviour with the following query:
select r.*, e.nomprenom as nomchauff
from recep r
join employes e on e.code = r.chauff
where
(r.date_recep >= '01/01/04' and r.date_recep < ('07/01/04' + 1))
and (r.num_fact is null)
and (r.affretement = 0)
What happens is simply that sometimes it takes up to about 10 seconds,
but often, it's nearly instantaneous. So I can't figure out why it's
sometimes so slow.
The RECEP table contains about 45000 records. Could this be the problem?
I've heard of some people having much bigger tables without any problem.
My application uses the BDE, so I thought it might have something to do
with that, but this morning, I tried it in IBPlanalyser (which uses
IBX), and it did the same thing: the first time I ran the query it took
about 4-5 seconds, and the following times, it hardly took 1 second.
I also thought it might have something to do the query being prepared,
because it seems that when it happens, it's usually on the first run of
the query. If I run the query again, the problem always disappears.
However, I don't think this (preparation of the query) could be the
problem, because, a) Often, the problem doesn't happen at all, even on
the first run, and b)Once I've had the problem once, even if I destroy
the form that contains the query, and open it again, it doesn't happen
again.
It seems as though it basically, "the first time", although I don't
quite know what it's the first time of. I can close my application, and
even all other connections to the database, and yet, it almost never
does it twice in a row. Even if I restart the Interbase server, it
doesn't always do it.
When trying to reproduce it, it almost never happens, but when I'm not
trying, it usually does it the first time... Not too sure how that
works, but it seems to be true! :-)
BTW, I'm using IB 6.0 and FB 1.5. Also, this is my development machine,
so there's only one person using it, it's not going over the network,
and I only do one thing at a time, so I don't see why I should get such
a random response time
Anyway, if anyone have any advice or explanation, it woul de much
appreciated.
Thanks!
Jonathan Neve.