asked on

GoldMine SQL Query (Stuck)

Can someone help me fine tune this query, It is taking too long so I must be doing something wrong.

select
      contact1.company,
      contact1.contact,
      contsupp.address2 as EM_Contact,
      contsupp.contsupref,
      contsupp.zip
      from contsupp join contact1
      on contact1.accountno=contsupp.accountno and
      contsupp.contact = 'E-mail Address'
      where
      contact1.accountno in
      (select * from
      contact1,
      conthist,
      contact2,
      contsupp
      where contact1.accountno=conthist.accountno and
      contact1.accountno = contact2.accountno and
      conthist.ref like
      '%Seminar Approaching%' and
      contact2.umarket = 'New York' or contact2.umarket = 'New Jersey')

jdlambert1

Try this:

SELECT c1.company, c1.contact, cs.address2 as EM_Contact, cs.contsupref, cs.zip
FROM contsupp cs
JOIN contact1 c1 ON c1.accountno = cs.accountno
JOIN contact2 c2 ON c1.accountno = c2.accountno
JOIN conthist ch ON c1.accountno=ch.accountno
WHERE cs.contact = 'E-mail Address'
AND ch.ref like '%Seminar Approaching%'
AND c2.umarket IN ('New York','New Jersey')

Indexes on contsupp(contact) and on contact2(umarket) will help performance, as well as on all accountno columns, if these don't already have indexes.

ratzephyr

ASKER

jdlambert1

Your query is pulling everything four times

ASKER CERTIFIED SOLUTION

jdlambert1

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial