asked on

Optimize the query

Hi Experts,

I have large amount of data since Year 2010 which is stored in MYSQL database.

I fetch the data using the below query in tableview (VB.NET and .aspx page).

Dim sql = "Select tinvoice.nInvoiceID, tinvoice.dtInvoice, tinvoice.sInvoiceNo" & _
                      ", ifnull(tpacking.sPackingNo,'Flanges') as Pack, CONCAT(ifnull(mcustomer.sCustomername,''),' - ', ifnull(mcustomer.sCmpycode,'')) as Cust" & _
                      ", tinvoice.sTerms as Term, tinvoice.nInvAmt, tinvoice.bDiscoType, tpackingdet.nWeight, ifnull(mqtyunit.sQtyUnit,'') as Unit" & _
                      ", mprice.sPrice, mcurrencyname.sCurrencyName" & _
                      " FROM(tinvoice)" & _
                      " Left Join tpacking ON (tinvoice.nPackingID = tpacking.nPackingID)" & _
                      " Left Join mcustomer ON (tpacking.nCustomerIDCE = mcustomer.nCustomerID)" & _
                      " Left Join (SELECT tpackingdet.nPackingID, tpackingdet.nQtyUnitID, tpackingdet.nSalesRateID, IFNULL(sum(tpackingdet.nWeight), 0) as nWeight" & _
                                   " From tpackingdet" & _
                                   " Group By tpackingdet.nPackingID)" & _
                                  " tpackingdet on (tpacking.nPackingID = tpackingdet.nPackingID)" & _
                      " INNER JOIN mqtyunit ON (tpackingdet.nQtyUnitID = mqtyunit.nQtyUnitID)" & _
                      " INNER JOIN tsalesdet ON (tpackingdet.nSalesRateID = tsalesdet.nSalesDetID)" & _
                      " INNER JOIN tsales ON (tsalesdet.nSalesID = tsales.nSalesID)" & _
                      " INNER JOIN tenquiry ON (tsales.nEnquiryID = tenquiry.nEnquiryID)" & _
                      " INNER JOIN mprice ON (tenquiry.nPriceID = mprice.nPriceID)" & _
                      " INNER JOIN mcurrencyname ON (tenquiry.nCurrencyNameID = mcurrencyname.nCurrencyNameID)" & _
                      " where 1 " & cond & _
                      " order by tinvoice.sInvoiceNo desc,tinvoice.nInvoiceID desc" + vlimit

Open in new window

While running the query my web application takes longer (approx 12-15 minutes) to populate data in tableview.

How do I optimize it so that it run faster?

Regards,
D Patel

Kent Olsen

Hi DPatel,

What are the the filter conditions (WHERE clause) that will be part of the query? Also, how many invoices do you expect to be returned?

Kent

SOLUTION

Tomas Helgi Johannsson

membership

This solution is only available to members.

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

Start Free Trial

D Patel

ASKER

Hello Experts,

Thanks for your reply.

@KDO :
the condition depends on the user search criteria (whether it is date from, date to, country, invoice number, packing list number, customer name etc...)

also limit is set to 500 records at a time.

@ Tomas Helgi :
Index is already created for the tables with are used in Joins.

Regards,
D Patel

ASKER CERTIFIED SOLUTION