2

I am trying to join 5 tables in which i want to get different currency mentioned on different tables against same contract id.
It is giving me results when i join any three tables but when I add one more table in query the server gets unresponsive until I have to kill the process.

Please help me where I am doing a mistake.

SELECT c.department_id,
  c.contract_id,
  c.seller_id,
  c.buyer_id,
  c.contract_ratecurrency AS contractcurrency,
  b.currency_id           AS billcurrency,
  s.saleinv_currency      AS saleinvcurrency,
  cm.currency_id          AS commissioncurrency,
  sl.currency_id          AS cmlogcurrency,
  c.contract_iscancel
FROM tbl_contracts C
JOIN tbl_contract_bill b ON c.contract_id=b.contract_id
JOIN tbl_contract_saleinvoice s ON c.contract_id =s.contract_id
JOIN tbl_commission_payment cm ON c.department_id = cm.department_id
JOIN tbl_saleinvoice_commission_log sl ON c.department_id = sl.department_id
WHERE (c.contract_ratecurrency <> s.saleinv_currency
       OR c.contract_ratecurrency     <> b.currency_id
       OR s.saleinv_currency          <> b.currency_id
       OR cm.currency_id              <> sl.currency_id
       OR c.contract_ratecurrency     <> cm.currency_id
       OR s.saleinv_currency          <> cm.currency_id
       OR b.currency_id               <> cm.currency_id)
AND (c.contract_iscancel        =0)

requried result should be

ccontractid,csellerid,cbuyerid,ccurrency,bcurrency,scurrency,cmcurrency,slcurrency
101,25,50,1,1,2,3,1
102,28,16,2,3,1,3,2
sidra ahsan
  • 21
  • 3
  • Can you explain the purpose of this query? – P.Salmon Jan 23 '20 at 10:22
  • actually i want to get currency difference in all 5 tables – sidra ahsan Jan 23 '20 at 10:36
  • Please read this https://meta.stackoverflow.com/questions/271055/tips-for-asking-a-good-structured-query-language-sql-question/271056#271056 Pay particular attention to the section on query performance. Then, please give us a bit more information by [edit]ing your question. – O. Jones Jan 23 '20 at 10:42
  • What condition are you trying to add? Information about the table sizes and types would also help. – Gordon Linoff Jan 23 '20 at 11:43
  • Please provide `EXPLAIN SELECT ..` – Rick James Jan 23 '20 at 22:35
  • thanks every one for your support. total number of rows in all 5 tables are 36k. but i am getting result after using index is 1290256 rows. what mistake i am doing in this query. kindly help me how to sort out this. – sidra ahsan Jan 28 '20 at 07:55

1 Answers1

0

It looks like you are having performance issues. To optimize your database structure you have multiple options:

  1. Adding indexes on your keys.

    Let's take a look to your join statement:

    JOIN tbl_saleinvoice_commission_log sl ON c.department_id = sl.department_id

    Adding a clustered index on department_id on tbl_saleinvoice_commission_log table will help you a lot in performance wise. For more information you can check this link.

  2. Partitioning is another way to increase performance, but you need to check your database structure to see whether it works for you or not. For more information you can check this link.

Also I believe your tables are one to many, so you might need to check how many rows you are trying to retrieve. If your database server is not capable of processing big number of rows you might need to improve your hardware or CPU usage limits of your database daemon.

osumatu
  • 410
  • 1
  • 8
  • 25