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

 

[pella88]

Using VBA code...

But here is an update...

I managed to create a fast way of importing tables, creating queries and everything.. My current approach is:
1. press a button to run DoCmd.TransferSpreadSheet vba code to import data table
2. append only needed data to working tables and delete temporary imported table
3. update all the necessary fields (value fields)
4. union all tables
5. create final temp_table
6. create final overview

This process lasts 10-15mins, which is comparing to previous method, extremely fast...

One problem is the Access limit for file size... During import of one of the tables (full look of the table for one of the users), I get error that there is not enough resources... While this can be solved by user deleting unnecessary data before the import, is there a possibility to do transfer spreadsheet with a condition? Like, import only data which in the field named "Conditional_Field" have value like "A*".

Users can do that in Excel before import, but I am trying to lower the work they need to do...

 

[pella88]

Just a short correction... Error is: System resources exceeded....

 

[xenou]

One option (if the import size is very large but still less than 2 Gb) is to have an otherwise empty Access DB for your import destination. This is a typical workaround to have more data than the 2 Gb limit. Put your temp table there and link to that DB. Probably simplest.

You can query Excel worksheets but I think that would require either linking to the worksheet as a table (I don't like this and it also locks up the worksheet so I would only want to actual connect to it during the import process). You can also use ADO or DAO to query a worksheet as an external data source. Again, the only caveat is that since Excel is not a DB it can be messy if the worksheet has any confusing data in it (such as fields with both numbers and text stored in the same column, or any blank rows in the data). In the case of using ADO or DAO, you'd have to write that query with VBA.