Slow query / not finishing at all

[pella88]

Hello everyone,

I am still a beginner in Access, however, I have no other solution for my problem, so I must use Access (to be honest, best solution would be SQL, however clients which will be using my solution don't have SQL servers or SSMS, and excel is of course out of the question due to high volume of the data).

So, the idea is to create a tool, which will create a report for an overview of balances based on segments and similar. I have total of 8 data tables and 3 static (mapping) tables.

Tables are set up with indexes (fields which are used as connection in queries) and primary keys (where available). Tables have up to 150 fields (not all of them are filled, but I did limit the size of the fields according to technical specification I have for these tables).

I created a form with buttons (VBA codes) which enable user to import data tables each month (previous month gets deleted). And they work perfectly.

After import is finished, I created following queries:
1. Table1_Query and Table2_Query - for these two tables I get an extract of certain fields which are to be joined with rest of the tables and give certain conditions
2. Table'x'_Query - for 4 data table I have a query which selects fields which I need for reporting, including data from first two queries. All queries have same number/name fields so it is possible to get a union later on (due to updates of certain fields I need later on, explanation below).
3. Union_Query - I join all data into a single query
4. Create_Table - I use create table query in order to convert Union_Query in order to do an update which I already mentioned (afaik it Is not possible to do update of query fields).
5. Update_Table - There is total of 4 updates. Updates are in fact conversion of amounts in different currencies according to static table FX_rates, and they are being converted into a EUR.
6. Final_Overview - Out of updated_table, I create a final overview query which gives me report I need.

So far, this has been working properly, but a bit slow. However, now I have added a connection to Table2_query (which wasn't there before), and also a connection to another data table (directly to it, not a query one). Currently, I cannot do any of the Table'x'_Queries, since it takes like 15mins, and then just stays at 99%.

Here is an example of one of the queries SQL code:

SELECT 
           GI_RECORD.SATZART, 
           KD_QUERY.KUNDNR_SHORT, 
           GI_RECORD.KONTO, 
           GI_RECORD.PRODID, 
           GI_RECORD.KOREID, 
           ' ' AS SWIFTWCDBETRNOMI, 
           0 AS NOMINALE, 
           GI_RECORD.WCDBETRRAHMEXT, 
           Sum(GI_RECORD.BETRRAHMEXT) AS BETRRAHMEXT, 
           GI_RECORD.WCDZINSA, 
           Sum(GI_RECORD.BETRZINSA) AS BETRZINSA, 
           SAL_RECORD.SWIFTWCD, 
           Sum(SAL_RECORD.AKTSALD) AS AKTSALD, 
           Max(SAL_RECORD.UEZZAEHLER) AS UEZZAEHLER, 
           RAT_QUERY.NPL_FLAG, 
           Sum(EWB_RECORD.BETREWB_SP+EWB_RECORD.BETREWB_PP) AS TOTAL_PROVISIONS, 
           STATIC_KOREID_MAPPING.CATEGORY_HIGH_LEVEL, 
           STATIC_CLIENT_SEGMENTATION.categorization

FROM (RAT_QUERY 

INNER JOIN ((((GI_RECORD 

LEFT JOIN STATIC_KOREID_MAPPING ON GI_RECORD.KOREID = STATIC_KOREID_MAPPING.L05_PROD_KEY_PLAIN) 

LEFT JOIN SAL_RECORD ON (GI_RECORD.SATZART = SAL_RECORD.KTYP) AND (GI_RECORD.KONTO = SAL_RECORD.KONTO)) 

INNER JOIN KD_QUERY ON GI_RECORD.KUNDNR = KD_QUERY.KUNDNR) 

INNER JOIN STATIC_CLIENT_SEGMENTATION ON KD_QUERY.KUNDART = STATIC_CLIENT_SEGMENTATION.code) ON RAT_QUERY.RATREF = KD_QUERY.KUNDNR_SHORT) 

LEFT JOIN EWB_RECORD ON (GI_RECORD.SATZART = EWB_RECORD.EWBPRODTYP) AND (GI_RECORD.KONTO = EWB_RECORD.KONTO)

GROUP BY GI_RECORD.SATZART, KD_QUERY.KUNDNR_SHORT, GI_RECORD.KONTO, GI_RECORD.PRODID, GI_RECORD.KOREID, GI_RECORD.WCDBETRRAHMEXT, GI_RECORD.WCDZINSA, SAL_RECORD.SWIFTWCD, RAT_QUERY.NPL_FLAG, STATIC_KOREID_MAPPING.CATEGORY_HIGH_LEVEL, STATIC_CLIENT_SEGMENTATION.categorization

HAVING (((GI_RECORD.PRODID) Like "A*") AND ((STATIC_KOREID_MAPPING.CATEGORY_HIGH_LEVEL) Like "Loans to customers"));

I just noticed two mistakes, but don't think it will help that much:
1. GI should be main from, and others should be joined to it (not sure why it goes from RAT first)
2. TOTAL_PROVISIONS should have an IIF.

GI does have 200k records, KD has 100k records, STATIC tables have 100-150 records, RAT has 40k records, SAL has 300-400k records. So as you can see, it's fairly large amount of data, but the query never finishes.

Let me know if you need any other info, and also, If you have any suggestion, please do, since it is my first time to do something like this in access.

Br,
pella88

 

[xenou]

Hi break your query up into several parts so you can analyze which parts are taking the longest. Sometimes Access works best with fewer nested queries and not so many left joins all mixed together. With Access remember to compact and repair your database regularly which also can help a lot in some cases.

 

[pella88]

If I break all of these into multiple parts (which is feasible for me to do, and to be honest pretty easy), would it work if I run Union_Query, or even Create_Table query?

I mean, in a sense, all of these queries are in fact temporary results. If I run Create table query, it is dependant on Union_Query, which is dependant on 4 different main queries, and if I split main queries to several queries, then there is even more dependency. If I run Create table query, will it work? Will it do all of these in one step and create the table? My idea is to create a button which will run create table query, and then one button to do the updates, and one button to create final overview (or generally one button to do all three).

Users will not be able to see all of these queries, as all of these will be run from a form with import buttons, clear tables buttons and similar.

 

[xenou]

Yes, it looks like parts of your query can be run independently - firstly to verify their performance, secondly, to be used as queries of queries, or, if you prefer, to create temporary tables for subsequent steps in a procedure to run all of them in order.

 

[pella88]

Hello again,

I just tried doing only a join between GI and SAL, and the query doesn't run almost at all. Here is the code (note that GI has 85k and SAL has 200k entries):

SELECT 
GI_RECORD.SATZART, 
GI_RECORD.KONTO, 
GI_RECORD.PRODID, 
GI_RECORD.KOREID, 
' ' AS SWIFTWCDBETRNOMI, 
0 AS NOMINALE, 
GI_RECORD.WCDBETRRAHMEXT, 
Sum(GI_RECORD.BETRRAHMEXT) AS BETRRAHMEXT, 
GI_RECORD.WCDZINSA, 
Sum(GI_RECORD.BETRZINSA) AS BETRZINSA, 
SAL_RECORD.SWIFTWCD, 
Sum(SAL_RECORD.AKTSALD) AS AKTSALD, 
Max(SAL_RECORD.UEZZAEHLER) AS UEZZAEHLER, 
STATIC_KOREID_MAPPING.CATEGORY_HIGH_LEVEL

FROM (GI_RECORD 

LEFT JOIN STATIC_KOREID_MAPPING ON GI_RECORD.KOREID = STATIC_KOREID_MAPPING.L05_PROD_KEY_PLAIN) 

LEFT JOIN SAL_RECORD ON (GI_RECORD.KONTO = SAL_RECORD.KONTO) AND (GI_RECORD.SATZART = SAL_RECORD.KTYP)

GROUP BY GI_RECORD.SATZART, GI_RECORD.KONTO, GI_RECORD.PRODID, GI_RECORD.KOREID, GI_RECORD.WCDBETRRAHMEXT, GI_RECORD.WCDZINSA, SAL_RECORD.SWIFTWCD, STATIC_KOREID_MAPPING.CATEGORY_HIGH_LEVEL

HAVING (((GI_RECORD.PRODID) Like "A*") AND ((STATIC_KOREID_MAPPING.CATEGORY_HIGH_LEVEL) Like "Loans to customers"));

 

[pella88]

Hello again,

I just tried doing only a join between GI and SAL, and the query doesn't run almost at all. Here is the code (note that GI has 85k and SAL has 200k entries):

SELECT 
GI_RECORD.SATZART, 
GI_RECORD.KONTO, 
GI_RECORD.PRODID, 
GI_RECORD.KOREID, 
' ' AS SWIFTWCDBETRNOMI, 
0 AS NOMINALE, 
GI_RECORD.WCDBETRRAHMEXT, 
Sum(GI_RECORD.BETRRAHMEXT) AS BETRRAHMEXT, 
GI_RECORD.WCDZINSA, 
Sum(GI_RECORD.BETRZINSA) AS BETRZINSA, 
SAL_RECORD.SWIFTWCD, 
Sum(SAL_RECORD.AKTSALD) AS AKTSALD, 
Max(SAL_RECORD.UEZZAEHLER) AS UEZZAEHLER, 
STATIC_KOREID_MAPPING.CATEGORY_HIGH_LEVEL

FROM (GI_RECORD 

LEFT JOIN STATIC_KOREID_MAPPING ON GI_RECORD.KOREID = STATIC_KOREID_MAPPING.L05_PROD_KEY_PLAIN) 

LEFT JOIN SAL_RECORD ON (GI_RECORD.KONTO = SAL_RECORD.KONTO) AND (GI_RECORD.SATZART = SAL_RECORD.KTYP)

GROUP BY GI_RECORD.SATZART, GI_RECORD.KONTO, GI_RECORD.PRODID, GI_RECORD.KOREID, GI_RECORD.WCDBETRRAHMEXT, GI_RECORD.WCDZINSA, SAL_RECORD.SWIFTWCD, STATIC_KOREID_MAPPING.CATEGORY_HIGH_LEVEL

HAVING (((GI_RECORD.PRODID) Like "A*") AND ((STATIC_KOREID_MAPPING.CATEGORY_HIGH_LEVEL) Like "Loans to customers"));

Is it possible that indexes and primary keys are messing things up? I put indexes on all fields used for joining, as well as primary keys to fields which are unique (KONTO for example). However, when I did this, problems started. Is it possible that this is causing the issues? If so, is my understanding of indexes wrong, since I read somewhere that declaring indexes speeds up the queries and joining process.

 

[xenou]

primary keys to fields which are unique (KONTO for example)

If Konto is unique AND it is in your select list and GROUP BY clause, then you should not use SUM or GROUP BY at all. You will end up grouping on every single record - groups of one.

Also remove the left joins unless they are absolutely necessary.

As far as indexes go, they sometimes help, sometimes don't. You have to experiment. A primary key field should be in every table and it is indexed automatically.

Your example has three joins (GI_Record, Static_KoreID_Mapping, and SAL_Record) so maybe it's still not reduced to the smallest parts yet. I would for sure want to run the query without the sum or max functions to see how much data there is, and also how much time the grouping functions are adding to the query.

 

[pella88]

I removed the indexes and it worked... Then I added one by one join and fields select from these tables, and it worked... however, after the last join, even though it worked first time, it stopped working again... I have one question (for my knowledge) and a comment/question about a possible solution.

1. How do Access queries work? So each time I added a join and run the query, it ran extremely fast. My conclusion is that the old results is somehow saved in the cache or similar, and then next query takes much faster since first part has already been done...

2. My friend gave me an idea, and that is not to import all fields for all of these tables into access (like GI_RECORD has more than 100 fields, and I am using only 5-6) and this might speed up the process... Do you think it makes sense to use this approach? And if so, how can I import certain columns from Excel to Access based on the field names... First time I imported, I used Access feature: Do not import this field, but for the future, I want user to click a button, select a complete GI_RECORD and Access will take only fields that exists in Access... One of the idea, which could make sense is to import complete table into Access, then append data from full table to partial table, and then delete data from temporary table... Other solution (which I am not sure if it is possible), is to do a import like Excel does with Import from TxT, where you can specify which type the column is and if it should be imported for each field, and since the structure of the file is always the same, I would say not to import any column except the ones I need (status 9 was in Excel I think)... I found that it is possible to define the range, but my range is not continuous (its 5-6 fields across excel file with 100+ columns), and not sure if this solution can be used...

Hopefully, I am clear with my intentions

EDIT:
Just remember there is "Save import steps" options. Is it possible to use VBA to run these import steps and then it will only import fields I already selected in first import? Should make sense... I will try it also, and in case I manage, will let you know...

 

[pella88]

Ok, I think that Save Import steps is maybe best way to go, but is it possible to use dialog box to give file which should be used in DoCmd.RunSavedImportExport procedure? I already have a dialog box macro, however, cannot find anywhere how to use it for saved import procedure...

 

[xenou]

are you using an Access Macro or writing VBA code?