Subquerys - execution time too long

[TimGoff]

Hello

I'm running a query parameters with dates, which runs from 2 further subqueries.

If I set the parameter time to a couple of days, it executes with a minute or so. If i put it to 10 days it can take upto an hour.

Is there any system parameters I can change here to speed things up - is it possible with the more rows that are being returned - it requires more memory ?

Thanks
Tim

 

[Golom]

Post your SQL ... but note that queries based on other queries often do not enjoy the benefits of indexing as much as queries based on base tables. Also correlated sub-queries are notoriously slow.

 

[TimGoff]

Hello Golom,

I've actaully inserted all the SQL into the one query, rather than referencing the query's as seperate objects. It does appear to take along time to run still though...

It's a bit of a monster I'm afraid...

SELECT BSS_Finance_Migration.Channel_Name, BSS_Finance_Migration.TX_Time, BSS_Finance_Migration.TX_Date, BSS_Finance_Migration.Series_Title, BSS_Finance_Migration.Programme_Title, BSS_Finance_Migration.House_Number, BSS_Finance_Migration.ZZ_House_Number, BSS_Finance_Migration.Unique_String, BSS_Finance_Migration.Run_Value, (IIf(IsNull([OnAir_Finance_Migration.amort_value])=True,[BSS_Finance_Migration.Run_Value],[BSS_Finance_Migration.Run_Value]-[OnAir_Finance_Migration.Amort_Value])) AS Difference

FROM (SELECT tbl_Bcst_T_chl.ulcode, tblBSS_Finance_Migration.Amort_Type, tbl_Bcst_T_chl.Channel_Name, tbl_Bcst_T_srs.Series_Title, tbl_Bcst_T_prg_finance_Rec.Programme_Title, IIf([amort_type]=1,[tblBSS_Finance_Migration].[Channel_ID] & Left([tx_time],5) & Format([tx_date],"dd/mm/yy") & [tbl_bcst_t_prg_finance_Rec].[house_number],[tblBSS_Finance_Migration].[Channel_ID] & Format([tx_date],"dd/mm/yy") & [tbl_bcst_t_prg_finance_Rec].[house_number]) AS Unique_String, tblBSS_Finance_Migration.TX_Time, tblBSS_Finance_Migration.TX_Date, tbl_Bcst_T_prg_finance_Rec.House_Number, tblBSS_Finance_Migration.House_Number AS ZZ_House_Number, tblBSS_Finance_Migration.Run_Value
FROM ((tblBSS_Finance_Migration LEFT JOIN tbl_Bcst_T_prg_finance_Rec ON tblBSS_Finance_Migration.House_Number = tbl_Bcst_T_prg_finance_Rec.Original_House_Number) LEFT JOIN tbl_Bcst_T_srs ON tbl_Bcst_T_prg_finance_Rec.Series_ID = tbl_Bcst_T_srs.Series_ID) INNER JOIN tbl_Bcst_T_chl ON tblBSS_Finance_Migration.Channel_id = tbl_Bcst_T_chl.ulcode) AS BSS_Finance_Migration


LEFT JOIN (SELECT tbl_Bcst_T_chl.ulcode, tbl_Bcst_T_chl.Channel_Name, tbl_bcst_t_chl.ulcode & [TX_Time] & Format([TX_Date],"dd/mm/yy") & Left([House_Number],8) AS Unique_String, tblOnAir_Finance_Migration.TX_Time, tblOnAir_Finance_Migration.Tx_Date, tblOnAir_Finance_Migration.Amort_Value
FROM tblOnAir_Finance_Migration INNER JOIN tbl_Bcst_T_chl ON tblOnAir_Finance_Migration.Channel_ID = tbl_Bcst_T_chl.ulcode) AS OnAir_Finance_Migration

ON BSS_Finance_Migration.Unique_String = OnAir_Finance_Migration.Unique_String

WHERE BSS_Finance_Migration.ulcode >=fSetSQLParameters("Finance_Channels_Start") And BSS_Finance_Migration.ulcode<=fSetSQLParameters("Finance_Channels_End") AND BSS_Finance_Migration.TX_Date >=fSetSQLParameters("Start_Date_Normal") And BSS_Finance_Migration.TX_Date <=fSetSQLParameters("End_Date_Normal")


ORDER BY BSS_Finance_Migration.TX_Date, BSS_Finance_Migration.TX_Time

 

[Golom]

Just a few comments:

Your WHERE clause seems to apply only to parameters in the first of the sub-queries. Perhaps it would work more quickly if you placed the WHERE inside that first select.

WHERE tbl_Bcst_T_chl.ulcode BETWEEN 
          fSetSQLParameters("Finance_Channels_Start") And 
          fSetSQLParameters("Finance_Channels_End") 

      AND tblBSS_Finance_Migration.TX_Date BETWEEN 
          fSetSQLParameters("Start_Date_Normal") And 
          fSetSQLParameters("End_Date_Normal")

I don't know what the UDF fSetSQLParameters is doing. It should be examined to see if it is doing a lot of processing that may impact performance.

The standard question we always ask when performance is a concern is "Are all the fields referenced in JOIN conditions or WHERE clauses indexed?"

You may try breaking this down by running the sub-queries separately to get timings and/or run them as MAKE TABLE queries and then try this one referencing the tables rather than the queries.

 

[TimGoff]

Golom,

Thanks for you posts & help.

What I did in the end was create a field in each of the source tables, and used update queries to populate with what i was using as the key in the previous queries (unique_string). I then indexed both fields - and the query run time is down to seconds.

You mentioned corralated queries - would you mind explaining a little bit more what they are please?

Thanks
tim

 

[Golom]

Yours are not correlated queries. A correlated subquery is one in which a field or fields from the outer query are referenced in the subquery. The following simple example is a correlated sub query.

Select SomeDate, Amount,

(Select SUM(Amount) From tbl S Where S.SomeDate <= T.SomeDate) As Total

From tbl As T

Order By SomeDate

Notice that T.SomeDate is referenced inside the subquery that computes a running total for the Amount field. That will cause the subquery to be run for every record of the outer query. That gets a bit expensive if you have tens or hundreds of thousands of records to process.

 

[TimGoff]

Thanks for you help Golom
Tim