PLZ HELP ME

[CHDH]

i have a query that return data from 5 tables:
encais(>150000 row)
avenant(>170000 row)
client(>60000 row)
agences(20 row)
MODPAI(11 row)
i have Interbase 6.5 server on(windows nt server P2)
delphi 5 prof program executed on P4 winxp prof
when i excuted the query it has no response and the
program stopped.
the query is like this:
select cnumpol,anumave,anumatr,cnumcli,typemode,mmontt,mdatem,
lnomcl
from encais e,
modepai m,
avenant a ,
agences G,
Client C
where A.anumcli=e.cnumcli and
g.sectagen='51' and
e.magece=g;codagen and
e.mmodep=m.mode and
e.cnumpol=a.cnumpol and
e.mavena=A.anumave and
m.typemode in('C','R') and
e.mdatem>='01.01.2004' and e.mdatem<=:'30.04.2007' and a.adatemi>='01.01.2004' and a.adatemi<='30.04.2007'
thank you.

 

[unclejimbob]

I am not sure how the query can run when it has embedded semi colons in the lines "e.magece=g;codagen" and "e.mdatem<=:'30.04.2007'" so I am assuming that you didn't cut and paste it from your code and instead typed it in. I am also assuming that, like me, you have traced it as a problem with the SQL statement itself by running it under IBConsole (or whatever tool you have) rather than having it come from any other source.

I would alter the where clause in your query from:

where A.anumcli=e.cnumcli and
g.sectagen='51' and
e.magece=g.codagen and
e.mmodep=m.mode and
e.cnumpol=a.cnumpol and
e.mavena=A.anumave and
m.typemode in('C','R') and
e.mdatem>='01.01.2004' and e.mdatem<='30.04.2007' and a.adatemi>='01.01.2004' and a.adatemi<='30.04.2007'

to

where A.anumcli=e.cnumcli and
not (g.sectagen<>='51') and
e.magece=g.codagen and
e.mmodep=m.mode and
e.cnumpol=a.cnumpol and
e.mavena=A.anumave and
not (m.typemode not in('C','R')) and
e.mdatem>='01.01.2004' and e.mdatem<='30.04.2007' and a.adatemi>='01.01.2004' and a.adatemi<='30.04.2007'

Let me know if this works. If it does I can explain why. If not then we are in trouble.

good luck

ujb

 

[unclejimbob]

Sorry I made a typo, the altered where clause should read...

where A.anumcli=e.cnumcli and
not (g.sectagen<>'51') and
e.magece=g.codagen and
e.mmodep=m.mode and
e.cnumpol=a.cnumpol and
e.mavena=A.anumave and
not (m.typemode not in('C','R')) and
e.mdatem>='01.01.2004' and e.mdatem<='30.04.2007' and a.adatemi>='01.01.2004' and a.adatemi<='30.04.2007'

 

[unclejimbob]

I'm in the neighbourhood so thought I'd add a note to this post. One reason why some queries run slowly without any visible clue is that sometimes InterBase's query 'optimizer' does not pick the most appropriate plan when dealing with large result sets. In particular if you have a small table and/or a table with low selectivity and you have defined a primary key on it and you join to a large table with a foreign key to the smaller table InterBase will attempt to use the index to the smaller table instead of just carrying out a table scan. You can see this when you look at the query plan, FK indexes to both agences and MODPAI will be used in the query. So, what you do is convince InterBase not to use these indexes and you do this by altering the where clause to remove any potential for the table to join themselves on the indexed column - i.e. use a NOT(x NOT IN (y1,y2)) or a NOT(x<>y)

This probably needs an FAQ