Jump to content

Problem with indices?


Guest stoffel

Recommended Posts

Guest stoffel

I am executing this query. The execution time is unacceptable (1min for 2000 records). Can anybody tell me how i can optimize my sql or my indices in the tables?select art.art,art.desc,book1.cust,book1.pricefrom PUB.artjoin PUB.prbk as book1on art.company='company1'and art.company=book1.companyand book1.grp='00000526'and art.art=book1.artand book1.cust=(select max(book2.cust)from PUB.prbk as book2where book1.company=book2.companyand book1.grp=book2.grpand book1.art=book2.artand (book2.cust='BRONFRITUUR' or book2.cust=''))Table art:fields: company,art,descindex: company+artTable prbkfields: company,art,grp,cust,priceindex: company+grp+art+cust

Link to post
Share on other sites
I am executing this query. The execution time is unacceptable (1min for 2000 records). Can anybody tell me how i can optimize my sql or my indices in the tables?...
Most of those conditions are WHERE conditions, not join conditions. Try:
select art.art,art.desc,book1.cust,book1.pricefrom PUB.art join PUB.prbk as book1 on book1.company = art.companywhere art.company='company1'and...(everything else)

It would also help if you gave the tables an integer ID field rather than using the string "company" as the ID. The join would then be on the integer field.

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...