Adding Criteria Slows Query (1 Viewer)

RogerCooper · Mar 27, 2018

I have the following query on external tables

SELECT SYSADM_CUSTOMER_ORDER.ID AS [SO#], SYSADM_CUSTOMER_ORDER.CUSTOMER_PO_REF, SYSADM_CUST_ORDER_LINE.PART_ID, SYSADM_CUST_ORDER_LINE.UNIT_PRICE, SYSADM_CUST_ORDER_LINE.TRADE_DISC_PERCENT, CCur([unit_price]*(100-[Trade_Disc_Percent])/100) AS [Net Price], GCommerce.[Unit Price], Abs(CCur([unit_price]*(100-[Trade_Disc_Percent])/100)-[Unit Price]) AS Discrepancy
FROM (GCommerce INNER JOIN SYSADM_CUSTOMER_ORDER ON GCommerce.PurchaseOrderNumber = SYSADM_CUSTOMER_ORDER.CUSTOMER_PO_REF) INNER JOIN SYSADM_CUST_ORDER_LINE ON (SYSADM_CUSTOMER_ORDER.ID = SYSADM_CUST_ORDER_LINE.CUST_ORDER_ID) AND (GCommerce.[Line Number] = SYSADM_CUST_ORDER_LINE.LINE_NO)
ORDER BY SYSADM_CUSTOMER_ORDER.ID;

SYSADM_CUSTOMER_ORDER & SYSADM_CUST_ORDER_LINE are Oracle Tables, GCommerce is a text table. The query takes a few seconds to run.

If add a criterion to the query:

SELECT SYSADM_CUSTOMER_ORDER.ID AS [SO#], SYSADM_CUSTOMER_ORDER.CUSTOMER_PO_REF, SYSADM_CUST_ORDER_LINE.PART_ID, SYSADM_CUST_ORDER_LINE.UNIT_PRICE, SYSADM_CUST_ORDER_LINE.TRADE_DISC_PERCENT, CCur([unit_price]*(100-[Trade_Disc_Percent])/100) AS [Net Price], GCommerce.[Unit Price], Abs(CCur([unit_price]*(100-[Trade_Disc_Percent])/100)-[Unit Price]) AS Discrepancy
FROM (GCommerce INNER JOIN SYSADM_CUSTOMER_ORDER ON GCommerce.PurchaseOrderNumber = SYSADM_CUSTOMER_ORDER.CUSTOMER_PO_REF) INNER JOIN SYSADM_CUST_ORDER_LINE ON (SYSADM_CUSTOMER_ORDER.ID = SYSADM_CUST_ORDER_LINE.CUST_ORDER_ID) AND (GCommerce.[Line Number] = SYSADM_CUST_ORDER_LINE.LINE_NO)
WHERE (((Abs(CCur([unit_price]*(100-[Trade_Disc_Percent])/100)-[Unit Price]))>0.01))
ORDER BY SYSADM_CUSTOMER_ORDER.ID;

The query slows and eventually fails from using too much memory. I know that I can populate a table as a workaround. However, why is this failing? Is there a way to prevent this?

arnelgp · Mar 27, 2018

Maybe you should qualify your field in the criteria with its table name,eg

Tablename.unit_price

isladogs · Mar 27, 2018

Code:

Abs(CCur([unit_price]*(100-[Trade_Disc_Percent])/100)-[Unit Price])

The above expression seems unnecessarily complex to me

As an example I tried a unit price of 2.5 and a trade discount % = 20

Substituting I get:
=(2.5*(100-20)/100)-2.5
= (2.5*0.8) - 2.5
= 2-2.5
=-0.5

Applying Abs & CCur gives an answer = 0.50

An easier way of getting that result is:

Code:

CCur([Unit_Price]*[Trade_Disc_Percent]/100)

Try using that in both the Discrepancy field and the WHERE clause

RogerCooper · Mar 27, 2018

ridders said:
Code:

Abs(CCur([unit_price]*(100-[Trade_Disc_Percent])/100)-[Unit Price])

The above expression seems unnecessarily complex to me

As an example I tried a unit price of 2.5 and a trade discount % = 20

Substituting I get:
=(2.5*(100-20)/100)-2.5
= (2.5*0.8) - 2.5
= 2-2.5
=-0.5

Applying Abs & CCur gives an answer = 0.50

An easier way of getting that result is:

Code:

CCur([Unit_Price]*[Trade_Disc_Percent]/100)

Try using that in both the Discrepancy field and the WHERE clause

That is not how a discount% works. The discount are taken off the list price so, a price of $2.50 with a discount of 20% is $2.00.

In any case, the computation is fast. Only when I try to select based upon the discrepancy is there a problem. This also occurs if I don't have a discrepancy field and just use criteria in the Net Price field.

isladogs · Mar 27, 2018

RogerCooper said:
That is not how a discount% works. The discount are taken off the list price so, a price of $2.50 with a discount of 20% is $2.00.

In any case, the computation is fast. Only when I try to select based upon the discrepancy is there a problem. This also occurs if I don't have a discrepancy field and just use criteria in the Net Price field.

I'm fully aware how a discount % works.
The price of $2.00 is your Net Price field.
I was working through your discrepancy field calculation where the answer would be $0.50 and pointing out that it was unnecessarily complex. Calculating that field by an over complex formula AND sorting by that same formula are contributing to the slowdown in your query...which is what your post asked about. Simplify the formula used and there will be a speed increase

pbaldy · Mar 27, 2018

Are the external tables in Access or some other platform, like SQL Server? CCur() is an Access function. Using it in the criteria when the back end is non-Access would force the back end to send the whole table across the wire so Access can evaluate it. That would obviously slow it down.

Pat Hartman · Mar 27, 2018

on external tables

What does that mean? Are the tables Jet/ACE, SQL Server, Excel, something else? Some combination?

Sometimes you can coerce Access into optimizing a query by breaking it up yourself. If Colin's suggestion to simplify the calculation doesn't solve the problem. Try just selecting the table the calculation applies to and creating a query that does the calculation and selects only the relevant records. Save as a querydef. Then create a second query that joins the first query to the second table.

You got sloppy in your SQL so I can't actually tell if all the fields in the calculation come from the same table. If they don't, then you can't optimize the join.

RogerCooper · Mar 28, 2018

pbaldy said:
Are the external tables in Access or some other platform, like SQL Server? CCur() is an Access function. Using it in the criteria when the back end is non-Access would force the back end to send the whole table across the wire so Access can evaluate it. That would obviously slow it down.

The external tables are in Oracle and CSV. I can't change the tables themselves. Let me see what I can do to eliminate the CCur(). Thanks for the help.

Mark_ · Mar 28, 2018

You can do this as two queries.
First is an optimized one that returns JUST the record set you want from the back end.
Second is a query against the first to format / calculate / order your data.

This means that the back end can look through millions of records to return the 167 records you really want. Your second query uses those 167 to do what ever you really need done.

RogerCooper · Mar 28, 2018

Mark_ said:
You can do this as two queries.
First is an optimized one that returns JUST the record set you want from the back end.
Second is a query against the first to format / calculate / order your data.

This means that the back end can look through millions of records to return the 167 records you really want. Your second query uses those 167 to do what ever you really need done.

When I used 2 queries there was no improvement in performance.

Pat Hartman · Mar 28, 2018

Just because Access allows you to join heterogeneous tables doesn't mean that it is a good idea. To do this join, Access downloads the entire Oracle table into memory and then tries to perform the join to the csv file locally.

It would be far more efficient to load the csv file into a temp table in the Oracle database and then Oracle can do the join.

Did you consider the suggestion I made? Can you apply the criteria to a single table? If so, that is the simplest solution to optimize the join.

RogerCooper · Mar 28, 2018

Pat Hartman said:
Just because Access allows you to join heterogeneous tables doesn't mean that it is a good idea. To do this join, Access downloads the entire Oracle table into memory and then tries to perform the join to the csv file locally.

It would be far more efficient to load the csv file into a temp table in the Oracle database and then Oracle can do the join.

Did you consider the suggestion I made? Can you apply the criteria to a single table? If so, that is the simplest solution to optimize the join.

I could also load the records from Oracle to an Access table. I was just looking for a simpler solution.

isladogs · Mar 28, 2018

And have you tried my simplified formula for discrepancy field and the WHERE clause?

RogerCooper · Mar 28, 2018

ridders said:
And have you tried my simplified formula for discrepancy field and the WHERE clause?

Even without CCUR, any criteria slows the query. I will need to load into a separate table.

Pat Hartman · Mar 28, 2018

Could you PLEASE answer the question I asked regarding the criteria.

Does the calculation use columns from the SAME table? If so, split the query and apply the criteria to ONE table. Then create another query that joins the first query to the second table.

isladogs · Mar 28, 2018

I also don't think you answered my question
When I first suggested simplifying the expression you dismissed my response suggesting (wrongly) I didn't understand the calculation.
Its not clear whether you've actually tried it since.

Before I sign out completely, I suggest you read this article by Allen Browne about poor performance in queries
http://allenbrowne.com/QueryPerfIssue.html

Mark_ · Mar 28, 2018

RogerCooper said:
When I used 2 queries there was no improvement in performance.

So your first query is your quick query, and your second is effectively

Code:

Select * From Query1 Where [Discrepancy] > 0.01

Or did you do something else?

RogerCooper · Mar 29, 2018

Pat Hartman said:
Could you PLEASE answer the question I asked regarding the criteria.

Does the calculation use columns from the SAME table? If so, split the query and apply the criteria to ONE table. Then create another query that joins the first query to the second table.

No. I am comparing 2 tables the SYSADM_CUST_ORDER_LINE table with the GCommerce table. The SYSADM_CUST_ORDER_LINE table is an Oracle Table while the GCommerce table is CSV. (I am testing that the prices on the orders as entered our system agree with the price on the transmitted order). I can't change these external tables. Multiple queries does not solve the issue, even though restricting the orders to the last 30 days reduces the number of records substantially, putting criteria that involves the CSV table seems to force a comparison of every record in the Oracle tables. What is odd is that only the use of criteria causes a problem. The query shows the difference in value with any problem.

I will simply need to populate an Access table with the past 30 days orders and then compare. It is an awkward solution, but it will work.

Pat Hartman · Mar 29, 2018

It isn't odd at all. since the calculation contains fields from both tables, nothing can be done on the server and all the data from the tables must be loaded into memory on the local PC. The only thing that will improve performance is putting the .csv file in a table on the server. Index on the join fields. Use TRUNCATE to delete the existing rows before you load the new rows.

Adding Criteria Slows Query (1 Viewer)

Registered User.

..forever waiting... waiting for jellybean!

MVP / VIP

Registered User.

MVP / VIP

Wino Moderator

Super Moderator

Registered User.

Longboard on the internet

Registered User.

Super Moderator

Registered User.

MVP / VIP

Registered User.

Super Moderator

MVP / VIP

Longboard on the internet

Registered User.

Super Moderator

Similar threads

Users who are viewing this thread