Many Queries Versus One Large Query with Many Expressions

[ErikaMarie0421]

Greetings!



Okay - so I find myself (quite often) writing queries (which I mainly do in design....I am in the beginning stages of learning how to write in SQL instead) that have a lot of expressions and parameters, which I write inside the query field by using the "Build" function.

This really slows down the query when I run it - the more it gets 'bogged down' with a gagillion expressions. So I was wondering: is it better to have many separate queries with minimal fields and then pull them all into one query which will display all of the info I need to create a report from the query? Or is it better to have one large query which ends up looking like this:

SELECT TBL_SeminarDates_LOOKUP.[Seminar Code], DB2ADMIN_FDO_SEMINAR.AGT_ID, RTrim([DB2ADMIN_AIS_AGENTS_GENERAL_INFORMATION]![AGT_LAST_NAME]) & ', ' & RTrim([DB2ADMIN_AIS_AGENTS_GENERAL_INFORMATION]![AGT_FIRST_NAME]) & ' ' & RTrim([DB2ADMIN_AIS_AGENTS_GENERAL_INFORMATION]![AGT_MIDDLE_NAME]) AS [Agent Name], DB2ADMIN_AIS_AGENTS_GENERAL_INFORMATION.AGT_TYPE_C ODE, DB2ADMIN_AIS_AGENTS_GENERAL_INFORMATION.CURR_WR_CD _AGY_NUM, DB2ADMIN_AIS_AGENCY_GENERAL_INFORMATION.GA_LAST_NA ME, DB2ADMIN_AIS_AGENTS_GENERAL_INFORMATION.CURR_REG_C ODE, DB2ADMIN_AIS_AGENTS_GENERAL_INFORMATION.CAR_EFF_DA TE, QRY_LengthOfContract.[Length of Contract], QRY_AgtContDate_MonthsActive_CurrYr.[Months Active in Curr Yr] AS [Months Active in Curr Year], [QRY_AgtNAFYC_CurrYr]![Curr Yr NAFYC] AS [Curr Year NAFYC], [QRY_AgtNAFYC_CurrYr]![Curr Yr Cases] AS [Curr Year Cases], [QRY_AgtContDate_MonthsActive_PrevYr]![Months Active in Prev Yr] AS [Months Active in Prev Year], [QRY_AgtNAFYC_PrevYr]![Prev Yr NAFYC] AS [Prev Year NAFYC], [QRY_AgtNAFYC_PrevYr]![Prev Yr Cases] AS [Prev Year Cases], Nz(Sum([QRY_AgtContDate_MonthsActive_CurrYr]![Months Active in Curr Yr]+[QRY_AgtContDate_MonthsActive_PrevYr]![Months Active in Prev Yr]),0) AS [Total Months Active (Prev & Curr Yr)], Sum(Nz([Curr Year NAFYC],0)+Nz([Prev Year NAFYC],0)) AS [Total NAFYC], IIf([Total Months Active (Prev & Curr Yr)]=0,0,[Total NAFYC]/[Total Months Active (Prev & Curr Yr)]) AS [Monthly NAFYC Average], IIf([Months Active in Curr Year]=0,0,[Curr Year NAFYC]/[Months Active in Curr Year]) AS [Curr Yr Monthly NAFYC Avg], [TBL_Production_NonProp-ONESCO_2010].[ONESCO GDC (Excluding Non-Prop)] AS [Curr Yr ONESCO Net GDC], [TBL_Production_NonProp-ONESCO_2010].[50% Non-Prop] AS [Curr Yr 50% Non-Prop], [TBL_Production_NonProp-ONESCO_2009].[ONESCO GDC (Excluding Non-Prop)] AS [Prev Yr ONESCO Net GDC], [TBL_Production_NonProp-ONESCO_2009].[50% Non-Prop] AS [Prev Yr 50% Non-Prop], IIf([Months Active in Curr Year]=0,0,[Curr Yr ONESCO Net GDC]/[Months Active in Curr Year]) AS [Curr Yr Monthly ONESCO Avg], IIf([Months Active in Curr Year]=0,0,[Curr Yr 50% Non-Prop]/[Months Active in Curr Year]) AS [Curr Yr Monthly Non-Prop Avg], IIf([Curr Yr Monthly ONESCO Avg]>=[Curr Yr MONTHLY Maximum ONESCO for Type 1],[Curr Yr MONTHLY Maximum ONESCO for Type 1],[Curr Yr Monthly ONESCO Avg]) AS [Curr Yr ONESCO Included (Monthly Avg)], IIf([Prev Yr ONESCO Net GDC]>=[Prev Yr Maximum ONESCO for Type 1],[Prev Yr Maximum ONESCO for Type 1],[Prev Yr ONESCO Net GDC]) AS [Prev Year ONESCO Included], Sum(Nz([Prev Year NAFYC],0)+Nz([Prev Yr 50% Non-Prop],0)+Nz([Prev Year ONESCO Included],0)) AS [Total Prod_Prev Yr], IIf([Total Prod_Prev Yr]>=[TBL_SeminarProductionRequirements_LOOKUP]![TYPE 1A PRODUCTION MINIMUM],[TBL_SeminarProductionRequirements_LOOKUP]![TYPE 1A TUITION],'9999') AS [Tuition Based on Prev Yr 1A], IIf([Total Prod_Prev Yr] Between [TBL_SeminarProductionRequirements_LOOKUP]![TYPE 1B PRODUCTION MINIMUM] And [TBL_SeminarProductionRequirements_LOOKUP]![TYPE 1B PRODUCTION MAXIMUM],[TBL_SeminarProductionRequirements_LOOKUP]![TYPE 1B TUITION],'9999') AS [Tuition Based on Previous Year 1B], IIf([Total Prod_Prev Yr] Between [TBL_SeminarProductionRequirements_LOOKUP]![TYPE 1C PRODUCTION MINIMUM] And [TBL_SeminarProductionRequirements_LOOKUP]![TYPE 1C PRODUCTION MAXIMUM],[TBL_SeminarProductionRequirements_LOOKUP]![TYPE 1C TUITION],'9999') AS [Tuition Based on Previous Year 1C], IIf([Tuition Based on Prev Yr 1A]<[Tuition Based on Previous Year 1B] And [Tuition Based on Previous Year 1C],[Tuition Based on Prev Yr 1A],IIf([Tuition Based on Previous Year 1B]<[Tuition Based on Prev Yr 1A] And [Tuition Based on Previous Year 1C],[Tuition Based on Previous Year 1B],IIf([Tuition Based on Previous Year 1C]<[Tuition Based on Prev Yr 1A] And [Tuition Based on Previous Year 1B],[Tuition Based on Previous Year 1C],'9999'))) AS [Tuition Based on Prev Yr], Sum(Nz([Curr Yr Monthly NAFYC Avg],0)+Nz([Curr Yr Monthly Non-Prop Avg])+Nz([Curr Yr ONESCO Included (Monthly Avg)],0)) AS [Avg Prod_Curr Yr], IIf([Avg Prod_Curr Yr]>=[QRY_SeminarTuition_MonthlyNAFYCRequirements_CurrYr]![TYPE 1A MINIMUM AVG],[TBL_SeminarProductionRequirements_LOOKUP]![TYPE 1A TUITION],'9999') AS [Tuition Based on Curr YrA], IIf([Avg Prod_Curr Yr] Between [QRY_SeminarTuition_MonthlyNAFYCRequirements_CurrYr]![TYPE 1B MINIMUM AVG] And [QRY_SeminarTuition_MonthlyNAFYCRequirements_CurrYr]![TYPE 1B MAXIMUM AVG],[TBL_SeminarProductionRequirements_LOOKUP]![TYPE 1B TUITION],'9999') AS [Tuition Based on Curr YrB], IIf([Avg Prod_Curr Yr] Between [QRY_SeminarTuition_MonthlyNAFYCRequirements_CurrYr]![TYPE 1C MINIMUM AVG] And [QRY_SeminarTuition_MonthlyNAFYCRequirements_CurrYr]![TYPE 1C MAXIMUM AVG],[TBL_SeminarProductionRequirements_LOOKUP]![TYPE 1C TUITION],'9999') AS [Tuition Based on Curr YrC], IIf([Tuition Based on Curr YrA]<[Tuition Based on Curr YrB] And [Tuition Based on Curr YrC],[Tuition Based on Curr YrA],IIf([Tuition Based on Curr YrB]<[Tuition Based on Curr YrA] And [Tuition Based on Curr YrC],[Tuition Based on Curr YrB],IIf([Tuition Based on Curr YrC]<[Tuition Based on Curr YrA] And [Tuition Based on Curr YrB],[Tuition Based on Curr YrC],'9999'))) AS [Tuition Based on Curr Yr], IIf([Tuition Based on Prev Yr]>[Tuition Based on Curr Yr],[Tuition Based on Curr Yr]) & IIf([Tuition Based on Curr Yr]>[Tuition Based on Prev Yr],[Tuition Based on Prev Yr]) & IIf([Tuition Based on Curr Yr]=[Tuition Based on Prev Yr],[Tuition Based on Prev Yr]) AS [Minimal Tuition], IIf([Minimal Tuition]='9999','--',[Minimal Tuition]) AS [FINAL TUITION]
FROM ((((((((((((TBL_SeminarDates_LOOKUP LEFT JOIN DB2ADMIN_FDO_SEMINAR ON TBL_SeminarDates_LOOKUP.[Seminar Name] = DB2ADMIN_FDO_SEMINAR.SEMINAR_NAME) LEFT JOIN DB2ADMIN_AIS_AGENTS_GENERAL_INFORMATION ON DB2ADMIN_FDO_SEMINAR.AGT_ID = DB2ADMIN_AIS_AGENTS_GENERAL_INFORMATION.AGT_ID) LEFT JOIN QRY_AgtNAFYC_CurrYr ON DB2ADMIN_FDO_SEMINAR.AGT_ID = QRY_AgtNAFYC_CurrYr.AGT_ID) LEFT JOIN QRY_AgtNAFYC_PrevYr ON DB2ADMIN_FDO_SEMINAR.AGT_ID = QRY_AgtNAFYC_PrevYr.AGT_ID) LEFT JOIN QRY_AgtContDate_MonthsActive_CurrYr ON DB2ADMIN_FDO_SEMINAR.AGT_ID = QRY_AgtContDate_MonthsActive_CurrYr.AGT_ID) LEFT JOIN QRY_AgtContDate_MonthsActive_PrevYr ON DB2ADMIN_FDO_SEMINAR.AGT_ID = QRY_AgtContDate_MonthsActive_PrevYr.AGT_ID) LEFT JOIN [TBL_Production_NonProp-ONESCO_2010] ON DB2ADMIN_FDO_SEMINAR.AGT_ID = [TBL_Production_NonProp-ONESCO_2010].AGT_ID) LEFT JOIN [TBL_Production_NonProp-ONESCO_2009] ON DB2ADMIN_FDO_SEMINAR.AGT_ID = [TBL_Production_NonProp-ONESCO_2009].AGT_ID) LEFT JOIN DB2ADMIN_AIS_AGENCY_GENERAL_INFORMATION ON DB2ADMIN_AIS_AGENTS_GENERAL_INFORMATION.CURR_WR_CD _AGY_NUM = DB2ADMIN_AIS_AGENCY_GENERAL_INFORMATION.WR_CD_AGY_ NUM) LEFT JOIN QRY_LengthOfContract ON DB2ADMIN_FDO_SEMINAR.AGT_ID = QRY_LengthOfContract.PRIMARY_AGT_ID) LEFT JOIN TBL_SeminarProductionRequirements_LOOKUP ON TBL_SeminarDates_LOOKUP.[Seminar Code] = TBL_SeminarProductionRequirements_LOOKUP.[SEMINAR CODE]) LEFT JOIN QRY_SeminarTuition_MaxONESCOConsideration ON TBL_SeminarDates_LOOKUP.[Seminar Code] = QRY_SeminarTuition_MaxONESCOConsideration.[SEMINAR CODE]) LEFT JOIN QRY_SeminarTuition_MonthlyNAFYCRequirements_CurrYr ON TBL_SeminarDates_LOOKUP.[Seminar Code] = QRY_SeminarTuition_MonthlyNAFYCRequirements_CurrYr .[SEMINAR CODE]
WHERE (((DB2ADMIN_FDO_SEMINAR.SEMINAR_STATUS_CD)="1") AND ((DB2ADMIN_AIS_AGENTS_GENERAL_INFORMATION.CURR_CON T_STAT_CD)="ACTIVE") AND ((DB2ADMIN_AIS_AGENTS_GENERAL_INFORMATION.AGT_TYPE _CODE)=[TBL_SeminarProductionRequirements_LOOKUP]![AGENT TYPE #1])) OR (((DB2ADMIN_FDO_SEMINAR.SEMINAR_STATUS_CD)="1") AND ((DB2ADMIN_AIS_AGENTS_GENERAL_INFORMATION.CURR_CON T_STAT_CD)="ACTIVE") AND ((DB2ADMIN_AIS_AGENTS_GENERAL_INFORMATION.AGT_TYPE _CODE)=[TBL_SeminarProductionRequirements_LOOKUP]![AGENT TYPE #2])) OR (((DB2ADMIN_FDO_SEMINAR.SEMINAR_STATUS_CD)="1") AND ((DB2ADMIN_AIS_AGENTS_GENERAL_INFORMATION.CURR_CON T_STAT_CD)="ACTIVE") AND ((DB2ADMIN_AIS_AGENTS_GENERAL_INFORMATION.AGT_TYPE _CODE)=[TBL_SeminarProductionRequirements_LOOKUP]![AGENT TYPE #3]))
GROUP BY TBL_SeminarDates_LOOKUP.[Seminar Code], DB2ADMIN_FDO_SEMINAR.AGT_ID, RTrim([DB2ADMIN_AIS_AGENTS_GENERAL_INFORMATION]![AGT_LAST_NAME]) & ', ' & RTrim([DB2ADMIN_AIS_AGENTS_GENERAL_INFORMATION]![AGT_FIRST_NAME]) & ' ' & RTrim([DB2ADMIN_AIS_AGENTS_GENERAL_INFORMATION]![AGT_MIDDLE_NAME]), DB2ADMIN_AIS_AGENTS_GENERAL_INFORMATION.AGT_TYPE_C ODE, DB2ADMIN_AIS_AGENTS_GENERAL_INFORMATION.CURR_WR_CD _AGY_NUM, DB2ADMIN_AIS_AGENCY_GENERAL_INFORMATION.GA_LAST_NA ME, DB2ADMIN_AIS_AGENTS_GENERAL_INFORMATION.CURR_REG_C ODE, DB2ADMIN_AIS_AGENTS_GENERAL_INFORMATION.CAR_EFF_DA TE, QRY_LengthOfContract.[Length of Contract], QRY_AgtContDate_MonthsActive_CurrYr.[Months Active in Curr Yr], [QRY_AgtNAFYC_CurrYr]![Curr Yr NAFYC], [QRY_AgtNAFYC_CurrYr]![Curr Yr Cases], [QRY_AgtContDate_MonthsActive_PrevYr]![Months Active in Prev Yr], [QRY_AgtNAFYC_PrevYr]![Prev Yr NAFYC], [QRY_AgtNAFYC_PrevYr]![Prev Yr Cases], [TBL_Production_NonProp-ONESCO_2010].[ONESCO GDC (Excluding Non-Prop)], [TBL_Production_NonProp-ONESCO_2010].[50% Non-Prop], [TBL_Production_NonProp-ONESCO_2009].[ONESCO GDC (Excluding Non-Prop)], [TBL_Production_NonProp-ONESCO_2009].[50% Non-Prop], QRY_SeminarTuition_MaxONESCOConsideration.[Prev Yr Maximum ONESCO for Type 1], QRY_SeminarTuition_MaxONESCOConsideration.[Curr Yr MONTHLY Maximum ONESCO for Type 1], DB2ADMIN_AIS_AGENTS_GENERAL_INFORMATION.AGT_LAST_N AME, TBL_SeminarProductionRequirements_LOOKUP.[TYPE 1A PRODUCTION MINIMUM], TBL_SeminarProductionRequirements_LOOKUP.[TYPE 1A TUITION], TBL_SeminarProductionRequirements_LOOKUP.[TYPE 1B PRODUCTION MINIMUM], TBL_SeminarProductionRequirements_LOOKUP.[TYPE 1B PRODUCTION MAXIMUM], TBL_SeminarProductionRequirements_LOOKUP.[TYPE 1B TUITION], TBL_SeminarProductionRequirements_LOOKUP.[TYPE 1C PRODUCTION MINIMUM], TBL_SeminarProductionRequirements_LOOKUP.[TYPE 1C PRODUCTION MAXIMUM], TBL_SeminarProductionRequirements_LOOKUP.[TYPE 1C TUITION], QRY_SeminarTuition_MonthlyNAFYCRequirements_CurrYr .[TYPE 1A MINIMUM AVG], QRY_SeminarTuition_MonthlyNAFYCRequirements_CurrYr .[TYPE 1B MINIMUM AVG], QRY_SeminarTuition_MonthlyNAFYCRequirements_CurrYr .[TYPE 1B MAXIMUM AVG], QRY_SeminarTuition_MonthlyNAFYCRequirements_CurrYr .[TYPE 1C MINIMUM AVG], QRY_SeminarTuition_MonthlyNAFYCRequirements_CurrYr .[TYPE 1C MAXIMUM AVG]
HAVING (((TBL_SeminarDates_LOOKUP.[Seminar Code])=[Enter Seminar Code] Or (TBL_SeminarDates_LOOKUP.[Seminar Code])=[Enter Seminar Code] Or (TBL_SeminarDates_LOOKUP.[Seminar Code])=[Enter Seminar Code]))
ORDER BY DB2ADMIN_AIS_AGENTS_GENERAL_INFORMATION.AGT_LAST_N AME;

[TheShabz]

Breaking them up is the better idea. Not only for running time but also for troubleshooting. lets say you have all that set up and you hit run and it fails or returns incorrect information. do you really want to sift through that looking for the comma you missed or the extra I you put in "maximum" you put in? I would break that up into chunks, then either have one final query that puts the sub queries together or a macro that runs each in order.

[ErikaMarie0421]

Thanks a ton. As of now, it takes about a minute or so to run the query - do you think breaking it down into more queries will cause it to run quicker or slower?
Thanks!

[TheShabz]

It really all depends on so many other factors that are outside of the db that its really hard to say how much time you'd save. Is the db on a network drive? how fast is your processor? how much ram do you have? For me, the acceptable time for a query to run depends on its use. If I'm creating a form or report from the query, a minute is too long. However, if I'm creating one of my marketing lists off of 8-10 tables of 5-10 million records per, I'm totally fine with letting the process go on for half an hour or more. it really all depends.

Also, I've trained my users to be patient. the phrase "ad hoc" is nonexistent. Even if its something that takes me 5 mins to run/format/return, they arent getting anything same-day if a request is submitted prior to noon. Don't know if this part would apply to you but its relevant.

Take care.