locked
Slow running query RRS feed

  • Question

  • Below query running very slow...

    select COUNT(1)  from [bts_mst]  
    inner JOIN [BTS_Retailermapping_mst]  on [bts_mst].BTSID=[BTS_Retailermapping_mst].SAM_ID
    inner JOIN KPI_Transaction_Inbound  on Retailer_MDN =BTS_Retailermapping_mst.RETAILER_EVD_NO 
    where bts_mst.Distributor_Id= '974165'  and bts_mst.BTSID='S_KL_PRNM_3' AND (KPI_Transaction_Inbound.ACTT_MTD>0.00)
    AND KPI_Transaction_Inbound.ID=(select max(ID) from KPI_Transaction_Inbound 
    where Retailer_MDN =BTS_Retailermapping_mst.RETAILER_EVD_NO)

    Record count Below table 

    bts_mst---->99059

    BTS_Retailermapping_mst---->687422

    KPI_Transaction_Inbound------>7759078

    Please help as early as possible...

    Thursday, April 7, 2016 1:21 PM

Answers

  • No help is possible without the full table DDL (including indexes), the version and edition of SQL Server and the Actual Query Plan that is "very slow".

    David


    David http://blogs.msdn.com/b/dbrowne/

    Thursday, April 7, 2016 4:07 PM
  • As David said.

    However, this

    KPI_Transaction_Inbound.ID=(select max(ID) from KPI_Transaction_Inbound 
    where Retailer_MDN =BTS_Retailermapping_mst.RETAILER_EVD_NO)

    is almost certainly your problem.  This is running for every single row.

    Thursday, April 7, 2016 5:17 PM
    Answerer

All replies

  • No help is possible without the full table DDL (including indexes), the version and edition of SQL Server and the Actual Query Plan that is "very slow".

    David


    David http://blogs.msdn.com/b/dbrowne/

    Thursday, April 7, 2016 4:07 PM
  • As David said.

    However, this

    KPI_Transaction_Inbound.ID=(select max(ID) from KPI_Transaction_Inbound 
    where Retailer_MDN =BTS_Retailermapping_mst.RETAILER_EVD_NO)

    is almost certainly your problem.  This is running for every single row.

    Thursday, April 7, 2016 5:17 PM
    Answerer