Joing to a parameter table

[pdauction]

Hi,



I have the following SQL code in Access

SELECT tb1.interest_key1 AS interest_key, tb1.Rate AS First_Mon_Rate, tb2.Rate AS Val_Rate
FROM (SELECT left(interest_key,7) AS interest_key1, rate FROM SOURCETABLE
WHERE INTEREST_KEY Like "*" & "220180702")  AS tb1 
LEFT JOIN (SELECT left(interest_key,7) AS interest_key1, rate 
FROM SOURCETABLEWHERE INTEREST_KEY Like "*" & "220180702")  AS tb2 
ON tb1.interest_key1 = tb2.interest_key1
ORDER BY tb1.interest_key1;

This works well within a minute or so. However I'd like to make the where criteria (20180702-yyymmdd) come from a 'parameters' table I created, prmTable.

I updated the code as follows;

SELECT tb1.interest_key1 AS interest_key, tb1.Rate AS First_Mon_Rate, tb2.Rate AS Val_Rate
FROM (SELECT left(interest_key,7) AS interest_key1, rate FROM SOURCETABLE, prmTable 
WHERE INTEREST_KEY Like "*" & prmTable.[Mon_Date])  AS tb1 
LEFT JOIN (SELECT left(interest_key,7) AS interest_key1, rate FROM SOURCETABLE, prmTable 
WHERE INTEREST_KEY Like "*" & "2" & prmTable.[Val_Date])  AS tb2 ON tb1.interest_key1 = tb2.interest_key1
ORDER BY tb1.interest_key1;

It now fails to run - I left it going over 20 mins and it crashes out.

In my parameters table I have a field I called ID with a value of 1. I re-wrote my SQL to add a field with 1 in to a sub query and then join to the parameter table at the next step - this however also took ages (presumably because it first brings in all the prices.

Is there a smarter way of adding in a parameters table?

Frustratingly I also have a more basic query in the db that seems to work (runs in about 1:30);

SELECT Left(SOURCETABLE.interest_key,7) AS interest_key, SOURCETABLE.Rate AS First_Mon_Rate
FROM SOURCETABLE, prmTable
WHERE INTEREST_KEY Like "*" & "2" & prmTable.[Mon_Date]
ORDER BY interest_key;

I could split my original code above into two like this but it feels like it should be possible in one go.

Note the SOURCETABLE is a linked table to an Oracle database which is quite large.

Grateful as ever for any help.

Many Thanks
Paul

[CJ_London]

just wondering why your code takes so long to run.

You may not be using indexing which will have a significant impact if there is a lot of data

In any event, use of an initial * will negate the use of an index anyway - and with a specific value such as "220180702" does not require the use of Like *, but should be =

If you have dates, they are stored as decimal numbers so you are either storing a date value, a long value (220180702) or text - of these text will be the slowest even with indexing because they take up the most space

I don't understand this - 'where criteria (20180702-yyymmdd)' which does not compare with "220180702".

Suggest explain your sourcetable structure - field names, field types, types of indexing (primary key, no dups, allow dups) - and what you are trying to achieve. Properly structured and even with a million rows, query should not take more than a few seconds to run.

[pdauction]

Thanks Ajax and apologies I didn't get some of the relevant detail in.

The SOURCETABLE has a key field of interest_key (with a data type of Short Text). This has values such as "CEU0203 219930608" with the last 8 characters representing the date of the rates. This is why I have had to resort to using like - I also needed the 9th character from the end to be a 2 which I chose to hard code in the like rather than add to the prmTable (I have tried the non hard-coded way and it isn't any quicker). In my prmTable my Mon_Date and Val_Date fields I have set as numbers (also tried text just in case). The key field in prmTable is ID.

Thanks

[CJ_London]

seems to me that someone has not thought through the impact of using a composite key - from your description there would appear to be 3 or 4 'unrelated' elements combined into one field (often used in excel). You haven't said what other fields are in the table but can these elements not be separated into their own fields and then indexed?

At the moment you have the most inefficient basis for extracting data possible - text, inability to use an index and large data volumes, so I suspect there is nothing you can do to improve the performance.

In this instance, I don't think use of a parameters table will make any difference.

I'm trying to understand what the query is supposed to do since in your first example both subqueries bring through the same data - can't tell for your subsequent examples. At the moment I'm thinking something like this may be faster

SELECT left(interest_key,7) AS interest_key AS interest_key, tb1.Rate AS First_Mon_Rate, tb2.Rate AS Val_Rate
FROM (SELECT interest_key, rate FROM SOURCETABLE
WHERE INTEREST_KEY Like "*" & "220180702")  AS tb1 
LEFT JOIN (SELECT interest_key, rate FROM SOURCETABLE
WHERE INTEREST_KEY Like "*" & "220180702")  AS tb2 
ON left(tb1.interest_key,7) = left(tb2.interest_key,7)
ORDER BY tb1.interest_key1;

note the join between tb1 and tb2 cannot be represented in the query window, only in sql

Suggest you provide some example data and the outcome you would expect from that data

[NoellaG]

Hi, if you have enough permissions on the Oracle database you can try to limit the data by creating a materialized view with a date field that contains the last 8 characters to represent the date of the rates.
Querying this view could be a lot faster than querying the original data table.

[pdauction]

Hi,

Just to note that sadly I don't have any permissions on the Oracle database to change anything.

So my query without trying to link to a parameter table is now essentially (takes up to a minute to run);

SELECT left(tb1.interest_key,7) AS interest_key, tb1.Rate AS First_Mon_Rate, tb2.Rate AS Val_Rate
FROM (SELECT interest_key, rate FROM SOURCEDATA
WHERE INTEREST_KEY Like "*" & "220180702")  AS tb1 
LEFT JOIN (SELECT interest_key, rate FROM SOURCEDATA
WHERE INTEREST_KEY Like "*" & "220180702")  AS tb2 
ON left(tb1.interest_key,7) = left(tb2.interest_key,7)
ORDER BY tb1.interest_key;

The reason why I go back to the same table twice is that the criteria '220180702' which is a 2 followed by the date as yyyymmdd can be different when run at certain times. The first date is to represent the first working day of a month and then the second date the first Monday of a month - sometimes the dates are the same but not always.

I wanted to have a parameter type table to use to input the dates in and then be able to run the queries (I'm running them from Excel) rather than having to change them within each query first.

My SOURCEDATA is setup as (with example data after “--“);
[Key] INTEREST_KEY (Short Text) -- PDU0201 219930608
RATE (Number) -- 1.288
D_DATE (Date/Time) - - 09/06/1993
TYPE (Short Text) -- BLANK
TRANS_ID_SQL (Number) - - 95120923789254
[Key] CDC_DB_ID (Number) - - 1

I have tried using Right instead of Like but that doesn’t seem to work any better. I’m guessing if this query can be made more efficient then joining a parameter table might then be a lot easier…? Just to note again that when adding a parameter table to the above it runs for a very long time (left it over 6 mins this time over 20 in the past)

[CJ_London]

I have tried using Right instead of Like but that doesn’t seem to work any better

it won't because neither way makes use of indexing. Having said that, you have a date field - can this be used instead? From your example data it is a day out, so perhaps the criteria can be adjusted?

Edit: also forgot about that first char - is that CDC_DB_ID? Again different from your example data, but perhaps not?

as said before, a parameter table will not make a difference to performance - even if it can be located in Oracle, it will definitely be slower if it is located in access.

[pdauction]

Thanks Ajax,

I assessed the table and other than for a handful D_DATE-1 was the same as the right of INTEREST_KEY. I revised the query working on this date just simply using D_DATE=#02/07/2018# with no other criteria and it ran in about a minute (tried to add in a reference to a date in a parameter table and that made it run for ages - left it going over 3 mins anyway). This was without adding in the extra "2" which is actually part of the INTEREST_KEY.

[CJ_London]

only other thing I suggest is use a passthrough query - you can use your form to capture the two dates then use vba to create the query. It won't be any faster but perhaps more manageable