Very slow query. Need help fixing.

[pholt33]

I have a query that is supposed to return some basic data for one or more accounts and calculate the change between the time periods. It should run in just a few seconds, particularly if limited to only one account, but it often runs for 10+ minutes which makes it useless.

Please help me get this working correctly!

PARAMETERS  acc_Account Text ( 255 );
SELECT curr.mvs_port, curr.acc_acct, curr.converted_Date, curr.description AS mvs_lo_type, curr.ac_description AS mvs_lo, curr.mvs_mkt AS mvs_mkt, curr.mvs_inc_total AS mvs_inc_total, curr.[Total MktVal] AS [Total MktVal], curr.mvs_uvs AS mvs_uvs, curr.uvs_wo_fees AS uvs_wo_fees, ((curr.mvs_uvs/Prev.mvs_uvs)-1)*100 AS [Gross Return], ((curr.uvs_wo_fees/Prev.uvs_wo_fees)-1)*100 AS [Net Return]

FROM (SELECT dbo_tblMarketValues.mvs_port, dbo_rimsacc_master.acc_acct, dbo_tblDateTable.converted_Date, dbo_tblMarketValuesExcluding.mvs_counter, dbo_tblMarketValues.mvs_lo_type, [dbo_tblLoTypes].description, dbo_tblMarketValues.mvs_lo, [dbo_tblLoCodes].ac_description, mvs_mkt, mvs_inc_total, (mvs_mkt+mvs_inc_total) AS [Total MktVal], mvs_uvs, dbo_tblMarketValuesExcluding.uvs_wo_fees
 
FROM dbo_tblMarketValues
, dbo_tblMarketValuesExcluding
, dbo_tblFormatFile
, dbo_rimsacc_master
, dbo_tblDateTable
, dbo_tblLoTypes
, dbo_tblLoCodes
 
WHERE dbo_tblMarketValues.mvs_port=dbo_tblMarketValuesExcluding.mvs_port
  And dbo_tblMarketValues.mvs_lo=[dbo_tblLoCodes].lo_code 
 And dbo_tblMarketValues.mvs_lo_type=[dbo_tblLoTypes].mvs_lo_type  
And [dbo_tblLoCodes].lo_type=dbo_tblMarketValues.mvs_lo_type 
 And dbo_tblDateTable.counter=dbo_tblMarketValuesExcluding.mvs_counter 
 And dbo_tblDateTable.converted_Date=dbo_tblMarketValues.converted_Date 
And dbo_tblFormatFile.mvs_port=dbo_tblMarketValues.mvs_port 
 And dbo_tblFormatFile.rimsacc=dbo_rimsacc_master.acc_cust
and dbo_rimsacc_master.acc_acct = acc_Account)  AS curr, 

(SELECT dbo_tblMarketValues.mvs_port, dbo_rimsacc_master.acc_acct, dbo_tblDateTable.converted_Date, dbo_tblMarketValuesExcluding.mvs_counter, dbo_tblMarketValues.mvs_lo_type, [dbo_tblLoTypes].description, dbo_tblMarketValues.mvs_lo, [dbo_tblLoCodes].ac_description, mvs_mkt, mvs_inc_total, (mvs_mkt+mvs_inc_total) AS [Total MktVal], mvs_uvs, dbo_tblMarketValuesExcluding.uvs_wo_fees
 
FROM dbo_tblMarketValues
, dbo_tblMarketValuesExcluding
, dbo_tblFormatFile
, dbo_rimsacc_master
, dbo_tblDateTable
, dbo_tblLoTypes
, dbo_tblLoCodes

 WHERE dbo_tblMarketValues.mvs_port=dbo_tblMarketValuesExcluding.mvs_port
  And dbo_tblMarketValues.mvs_lo=[dbo_tblLoCodes].lo_code 
 And dbo_tblMarketValues.mvs_lo_type=[dbo_tblLoTypes].mvs_lo_type  
And [dbo_tblLoCodes].lo_type=dbo_tblMarketValues.mvs_lo_type 
 And dbo_tblDateTable.counter=dbo_tblMarketValuesExcluding.mvs_counter 
 And dbo_tblDateTable.converted_Date=dbo_tblMarketValues.converted_Date 
And dbo_tblFormatFile.mvs_port=dbo_tblMarketValues.mvs_port 
 And dbo_tblFormatFile.rimsacc=dbo_rimsacc_master.acc_cust
and dbo_rimsacc_master.acc_acct = acc_Account)  AS Prev

WHERE (Prev.converted_Date=DateSerial(Year(curr.converted_Date),Month(curr.converted_Date)-1,0))   And (Prev.mvs_lo_type)=curr.mvs_lo_type And ((Prev.mvs_lo)=curr.mvs_lo)

ORDER BY curr.converted_Date;

 

[Joe4]

That looks like quite the monstrosity of a query! Lots of joins going on. Without being familiar with your database structure and data, it may be tough to really analyze it.

However, here are a few tips which may help:
- Make sure every table has a Primary Key
- Make sure all the fields involved in your Joins are indexed

 

[pholt33]

I ended up splitting the code into 2 subqueries and then a final query and now it works! It isnt super fast when running for only 1 account but it is manageable at ~15 seconds.

 

[Joe4]

Even so, I would check for those two things I mentioned, which may help you speed it up even more!

 

[pholt33]

I did change the the index status on as many linking fields as I could. Some I connect through an ODBC and was not able to change those.
Thanks for the suggestions!