×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

PLZ HELP ME

PLZ HELP ME

PLZ HELP ME

(OP)
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.
     
      

RE: PLZ HELP ME

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 smile

ujb

RE: PLZ HELP ME

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'

RE: PLZ HELP ME

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 smile

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close