optimize performance for recurring query

[dvault101]

I have spreadsheet reports I generate for some clients on a weekly basis. Each report shows the activity for the previous week. I've built VBA that can run the query and send the report, but I'm hitting a major performance problem with regards to the query itself. If I manually enter the week number into the query, I get my results within a minute or two. But A) I want to make the process completely automated and B) the week changes 52 times a year.

If I set the week number I want into a local table (as the only record) and do an inner join to the activity table (which is on a network server and holds millions of records), it takes hours to run and i'm pretty sure it rechecks the local table as it parses through each record in the activity table. How can I set my query to look up the correct week each time it is run, but without having to look it up for each record?

I'm on access 2007. Thanks.

 

[spikepl]

Provide some solid info to look at: SQL of query, table structure, relations, indexes.

 

[dvault101]

I'm posting two versions of my query. Table names were compacted for privacy. The first is the one that works quickly but has to be set manually each time.

[/FONT][/SIZE][/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman][SIZE=3][FONT=Calibri] [/FONT][/SIZE][/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman][SIZE=3][FONT=Calibri]SELECT M_RPT_STAGE_NAT_CLIENT.NAT_CLIENT_ID, M_RPT_STAGE_NAT_CLIENT.NAME, M_CLIENT.CLIENT_ID, M_CLIENT.NAME, M_S.DLR_ID, M_M_TRX.S_ID, M_S.NAME, M_M_TRX.TRX_ID, M_M_TRX.AUTH_NO, M_M_TRX.AUTH_METH, M_M_TRX.TRX_DATE, M_M_TRX.WEEK_NO, M_M_TRX.TRX_TYPE, M_M_TRX.PROD_ID, M_RPT_STAGE_PROD.DESCR, M_M_TRX.QTY, M_R.B_CODE, M_R.CITY, M_R.STATE, M_M_TRX.R_PRICE, M_M_TRX.FRT_RATE, M_M_TRX.DLR_ADJ, M_M_TRX.RETAIL_PRICE, M_M_TRX.EXT_RETAIL_AMT, M_M_TRX.CLIENT_TAX_RATE, M_M_TRX.CLIENT_PRICE, M_M_TRX.CLIENT_TOT_AMT, IIf([CARD_TYPE_ID]=7,"Y","N") AS FW, M_M_TRX.NW_R_ID INTO exporttable[/FONT][/SIZE][/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman][SIZE=3][FONT=Calibri]FROM M_R RIGHT JOIN ((((M_M_TRX INNER JOIN M_S ON M_M_TRX.S_ID = M_S.S_ID) INNER JOIN M_CLIENT ON M_S.CLIENT_ID = M_CLIENT.CLIENT_ID) INNER JOIN M_RPT_STAGE_NAT_CLIENT ON M_CLIENT.NAT_CLIENT_ID = M_RPT_STAGE_NAT_CLIENT.NAT_CLIENT_ID) INNER JOIN M_RPT_STAGE_PROD ON M_M_TRX.PROD_ID = M_RPT_STAGE_PROD.PROD_ID) ON M_R.R_ID = M_M_TRX.NW_R_ID[/FONT][/SIZE][/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman][SIZE=3][FONT=Calibri]GROUP BY M_RPT_STAGE_NAT_CLIENT.NAT_CLIENT_ID, M_RPT_STAGE_NAT_CLIENT.NAME, M_CLIENT.CLIENT_ID, M_CLIENT.NAME, M_S.DLR_ID, M_M_TRX.S_ID, M_S.NAME, M_M_TRX.TRX_ID, M_M_TRX.AUTH_NO, M_M_TRX.AUTH_METH, M_M_TRX.TRX_DATE, M_M_TRX.WEEK_NO, M_M_TRX.TRX_TYPE, M_M_TRX.PROD_ID, M_RPT_STAGE_PROD.DESCR, M_M_TRX.QTY, M_R.B_CODE, M_R.CITY, M_R.STATE, M_M_TRX.R_PRICE, M_M_TRX.FRT_RATE, M_M_TRX.DLR_ADJ, M_M_TRX.RETAIL_PRICE, M_M_TRX.EXT_RETAIL_AMT, M_M_TRX.CLIENT_TAX_RATE, M_M_TRX.CLIENT_PRICE, M_M_TRX.CLIENT_TOT_AMT, IIf([CARD_TYPE_ID]=7,"Y","N"), M_M_TRX.NW_R_ID, M_M_TRX.CARD_TYPE_ID[/FONT][/SIZE][/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman][SIZE=3][FONT=Calibri]HAVING (((M_RPT_STAGE_NAT_CLIENT.NAT_CLIENT_ID)=1644) AND ((M_M_TRX.WEEK_NO)=[week]))[/FONT][/SIZE][/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman][SIZE=3][FONT=Calibri]ORDER BY M_CLIENT.CLIENT_ID;[/FONT][/SIZE][/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman][SIZE=3][FONT=Calibri]

This is the version I have problems with. There are no indexes. The accrualstore table only ever has one record, and that is the week the report should run off of. It is automatically updated each time the database is opened.

[/FONT][/SIZE][/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman][SIZE=3][FONT=Calibri]SELECT M_RPT_STAGE_NAT_CLIENT.NAT_CLIENT_ID, M_RPT_STAGE_NAT_CLIENT.NAME, M_CLIENT.CLIENT_ID, M_CLIENT.NAME, M_S.DLR_ID, M_M_TRX.S_ID, M_S.NAME, M_M_TRX.TRX_ID, M_M_TRX.AUTH_NO, M_M_TRX.AUTH_METH, M_M_TRX.TRX_DATE, M_M_TRX.WEEK_NO, M_M_TRX.TRX_TYPE, M_M_TRX.PROD_ID, M_RPT_STAGE_PROD.DESCR, M_M_TRX.QTY, M_R.B_CODE, M_R.CITY, M_R.STATE, M_M_TRX.R_PRICE, M_M_TRX.FRT_RATE, M_M_TRX.DLR_ADJ, M_M_TRX.RETAIL_PRICE, M_M_TRX.EXT_RETAIL_AMT, M_M_TRX.CLIENT_TAX_RATE, M_M_TRX.CLIENT_PRICE, M_M_TRX.CLIENT_TOT_AMT, IIf([CARD_TYPE_ID]=7,"Y","N") AS FW, M_M_TRX.NW_R_ID INTO exporttable[/FONT][/SIZE][/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman][SIZE=3][FONT=Calibri]FROM accrualstore INNER JOIN (M_R RIGHT JOIN ((((M_M_TRX INNER JOIN M_S ON M_M_TRX.S_ID = M_S.S_ID) INNER JOIN M_CLIENT ON M_S.CLIENT_ID = M_CLIENT.CLIENT_ID) INNER JOIN M_RPT_STAGE_NAT_CLIENT ON M_CLIENT.NAT_CLIENT_ID = M_RPT_STAGE_NAT_CLIENT.NAT_CLIENT_ID) INNER JOIN M_RPT_STAGE_PROD ON M_M_TRX.PROD_ID = M_RPT_STAGE_PROD.PROD_ID) ON M_R.R_ID = M_M_TRX.NW_R_ID) ON accrualstore.MaxOfWEEK = M_M_TRX.WEEK_NO[/FONT][/SIZE][/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman][SIZE=3][FONT=Calibri]GROUP BY M_RPT_STAGE_NAT_CLIENT.NAT_CLIENT_ID, M_RPT_STAGE_NAT_CLIENT.NAME, M_CLIENT.CLIENT_ID, M_CLIENT.NAME, M_S.DLR_ID, M_M_TRX.S_ID, M_S.NAME, M_M_TRX.TRX_ID, M_M_TRX.AUTH_NO, M_M_TRX.AUTH_METH, M_M_TRX.TRX_DATE, M_M_TRX.WEEK_NO, M_M_TRX.TRX_TYPE, M_M_TRX.PROD_ID, M_RPT_STAGE_PROD.DESCR, M_M_TRX.QTY, M_R.B_CODE, M_R.CITY, M_R.STATE, M_M_TRX.R_PRICE, M_M_TRX.FRT_RATE, M_M_TRX.DLR_ADJ, M_M_TRX.RETAIL_PRICE, M_M_TRX.EXT_RETAIL_AMT, M_M_TRX.CLIENT_TAX_RATE, M_M_TRX.CLIENT_PRICE, M_M_TRX.CLIENT_TOT_AMT, IIf([CARD_TYPE_ID]=7,"Y","N"), M_M_TRX.NW_R_ID, M_M_TRX.CARD_TYPE_ID[/FONT][/SIZE][/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman][SIZE=3][FONT=Calibri]HAVING (((M_RPT_STAGE_NAT_CLIENT.NAT_CLIENT_ID)=1644))[/FONT][/SIZE][/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman][SIZE=3][FONT=Calibri]ORDER BY M_CLIENT.CLIENT_ID;[/FONT][/SIZE][/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman][SIZE=3][FONT=Calibri]

 

[plog]

I see a few things--mostly that query seems more complex than it needs to be.

1. Your JOINS are unparseable by a human--INNER JOIN...RIGHT JOIN...INNER JOIN...INNER JOIN. I really have no idea what the main table of this query is.

2. Why is it an aggregate query (uses GROUP BY)? Every field in the SELECT is in the GROUP BY. That's a trick used by those who don't understand their data to seemingly omit duplicates. That probably could be avoided with a sub-query on one of the tables causing the duplicates.

3. Is [week] calculable? Could a function exist that you pass it today's date and it returns the correct [week] value?

 

[dvault101]

Thanks for the feedback. Yeah, I know the query is convoluted, but it is pulling data from multiple tables. m_m_trx would be considered the "main" table because that's where the activity on each client's account is recorded. The data is deliberately *not* summarized because the client imports it into their system to reconcile.

as for "is [week] calculable?", yes, but that is where my problem is. I have a macro in my autoexec that takes today's date, compares it to the list of week numbers, and reports the correct week into the accrualstore table. That table is joined to m_m_trx and pulls the records for the correct week. Unfortunately that, structure, is what makes the automated query take hours to run. I am looking for a better way to make that piece work.

 

[plog]

The data may not be deliberately summarized, but it is technically. Most likely you need a sub-query somewhere to GROUP one set of data, then you use that sub-query instead of the whole table. That's adding unnecessary clock cycles.

Looking up data from a table isn't really calculable. I mean can you do it all logically? Can you explain how I could determine what the correct [week] value is, by only applying logic to today's date?

 

[dvault101]

We operate on a Monday-Sunday week, so in autoexec i have the following:

set warnings no
run sql (as transaction) UPDATE ACCRUAL SET ACCRUAL.ACCRUED = "X"
WHERE ((Now()>=[ACCRUAL].[FINISH]));
run sql (as transaction) SELECT Max(ACCRUAL.WEEK) AS MaxOfWEEK INTO accrualstore
FROM ACCRUAL
HAVING (([accrual].[accrued]="X"));
set warnings yes

Now, after autoexec runs, accrualstore has one column, maxofweek (as decimal) with a value of 201518 (which ran from 4/27 to 5/3). While the database is open that value is inner joined to the m_m_trx table in the weekno column. When i close the database, i have a function call that drops the accrualstore table.

It sounds like what you're suggesting would be to grab all the records for the client, put them into a local table, and then run the report off that? Intriguing.... I'll check that out....

 

[dvault101]

Re: optimize performance for recurring query - solved

I figured out where my problem was. As was pointed out, the query was using more clock cycles than necessary. I created my own problem by linking a local table with the week number to a network table with the data I wanted. Rather than one transmission to the server and one response coming back, it was constantly sending and receiving data as it ran. Using VBA, I rewrote the query to select the week number from a record set.