Solved

Issues with an Access query

Posted on 2011-09-02
8
324 Views
Last Modified: 2012-05-12
I have an Access database I'm linking to tables in a Pervasive SQL databae

I have the following query and when I run it against a small set of records (say 100) it works fine. But when I run the query on a database with 22,000 records I get the following results

The Access query starts to run with Access (Not Responding). After about 30 seconds Access returns the data and everything looks fine. In about another 5 seconds Access is Not Responding again. And after that it goes back and forth from Responding to Not Responding every 30 seconds.

All this time I'm not clicking on anything, all I do is let it sit there. Can anyone help me with this?

Here is the query:
   
SELECT INVENTORY.CODE AS SKU, UNIT_OF_MEASURE.CODE AS UOM_CODE, UNIT_OF_MEASURE.UOM_DESCRIPTION AS UOM_DESCRIPTION, IIf([UNIT_OF_MEASURE.CODE]<>[INVENTORY.BVSTKUOM],IIf([PRICING]![FACTOR_PRICES]=0,[PRICING]![BVRTLPRICE01],IIf([UNIT_OF_MEASURE]![DIRECT_FACTOR]=0,Round([PRICING_1]![BVRTLPRICE01]/[PRICING]![PRICE_FACTOR],2),Round([PRICING]![PRICE_FACTOR]*[PRICING_1]![BVRTLPRICE01],2))),[PRICING]![BVRTLPRICE01]) AS PRICE, IIf([UNIT_OF_MEASURE.CODE]<>[INVENTORY.BVSTKUOM],IIf([PRICING]![FACTOR_PRICES]=0,[PRICING]![BVRTLPRICE01],IIf([UNIT_OF_MEASURE]![DIRECT_FACTOR]=0,Round([PRICING_1]![BVRTLPRICE01]/[PRICING]![PRICE_FACTOR],2),Round([PRICING]![PRICE_FACTOR]*[PRICING_1]![BVRTLPRICE01],2))),[PRICING]![BVRTLPRICE01]) AS PRICE_LIST_1, IIf([UNIT_OF_MEASURE.CODE]<>[INVENTORY.BVSTKUOM],IIf([PRICING]![FACTOR_PRICES]=0,[PRICING]![BVRTLPRICE02],IIf([UNIT_OF_MEASURE]![DIRECT_FACTOR]=0,Round([PRICING_1]![BVRTLPRICE02]/[PRICING]![PRICE_FACTOR],2),Round([PRICING]![PRICE_FACTOR]*[PRICING_1]![BVRTLPRICE01],2))),[PRICING]![BVRTLPRICE02]) AS PRICE_LIST_2, IIf([UNIT_OF_MEASURE.CODE]<>[INVENTORY.BVSTKUOM],IIf([PRICING]![FACTOR_PRICES]=0,[PRICING]![BVRTLPRICE03],IIf([UNIT_OF_MEASURE]![DIRECT_FACTOR]=0,Round([PRICING_1]![BVRTLPRICE03]/[PRICING]![PRICE_FACTOR],2),Round([PRICING]![PRICE_FACTOR]*[PRICING_1]![BVRTLPRICE03],2))),[PRICING]![BVRTLPRICE01]) AS PRICE_LIST_3, IIf([UNIT_OF_MEASURE.CODE]<>[INVENTORY.BVSTKUOM],IIf([PRICING]![FACTOR_PRICES]=0,[PRICING]![BVRTLPRICE04],IIf([UNIT_OF_MEASURE]![DIRECT_FACTOR]=0,Round([PRICING_1]![BVRTLPRICE04]/[PRICING]![PRICE_FACTOR],2),Round([PRICING]![PRICE_FACTOR]*[PRICING_1]![BVRTLPRICE01],2))),[PRICING]![BVRTLPRICE04]) AS PRICE_LIST_4, PRICING.BVBREAKQTY01 AS QTY_BREAK_1, PRICING.BVBREAKQTY02 AS QTY_BREAK_2, PRICING.BVBREAKQTY03 AS QTY_BREAK_3, PRICING.BVBREAKQTY04 AS QTY_BREAK_4, PRICING.BVBREAKPRICE01 AS QTY_BREAK_PRICE_1, PRICING.BVBREAKPRICE02 AS QTY_BREAK_PRICE_2, PRICING.BVBREAKPRICE03 AS QTY_BREAK_PRICE_3, PRICING.BVBREAKPRICE04 AS QTY_BREAK_PRICE_4, PRICING.PMO_RETAIL AS SPECIAL_PRICE, PRICING.PMO_START_DTE AS SPEC_PRICE_START_DATE, PRICING.PMO_END_DTE AS SPEC_PRICE_END_DATE
FROM (((INVENTORY LEFT JOIN UNIT_OF_MEASURE ON (INVENTORY.CODE = UNIT_OF_MEASURE.PART_NO) AND (INVENTORY.WHSE = UNIT_OF_MEASURE.WHSE_NO)) LEFT JOIN PRICING ON (UNIT_OF_MEASURE.PRICESOURCECONST = PRICING.BVSPECPRICESOURCEID) AND (UNIT_OF_MEASURE.CODE = PRICING.BVSPECPRICEUOM) AND (UNIT_OF_MEASURE.PART_NO = PRICING.BVSPECPRICEPARTNO) AND (UNIT_OF_MEASURE.WHSE_NO = PRICING.BVSPECPRICEWHSE)) LEFT JOIN INVE_WB_MAIN ON (INVENTORY.CODE = INVE_WB_MAIN.I_PART_NO) AND (INVENTORY.WHSE = INVE_WB_MAIN.I_WHSE)) LEFT JOIN PRICING AS PRICING_1 ON (INVENTORY.BVSTKUOM = PRICING_1.BVSPECPRICEUOM) AND (INVENTORY.CODE = PRICING_1.BVSPECPRICEPARTNO) AND (INVENTORY.WHSE = PRICING_1.BVSPECPRICEWHSE)
WHERE (((INVENTORY.WHSE)="00") AND ((INVE_WB_MAIN.M_ITEM)=True) AND ((PRICING_1.BVSPECPRICESOURCEID)="I"));

Open in new window

0
Comment
Question by:Gerhardpet
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 57
ID: 36474001
<<All this time I'm not clicking on anything, all I do is let it sit there. Can anyone help me with this?>>

  Not with what you said per say; that's normal behavior.  The query hasn't finished executing, but is going back and fetching more records as it's trying to fully populate the result set.  

  You main issue is one of performance.

  There is nothing outragous in your query, but you have a considerable number of IIF()'s, which are never great for performance.  If you really need that logic in the query, then you have two options:

1. Write a pass-through query and run the query on the server side.
2. Just pull the raw data into a temp table, then execute a second query on the temp table with IIF()'s in it.

  When your working with ODBC datasources, it's always best to simply write "straight" queries and pull raw data.

  If you use any JET specific expressions (ie. VBA Calls) or join to local tables, JET will handle all the processing and go back and forth with the server many times in order to process the data.

Jim.
0
 
LVL 28

Expert Comment

by:Bill Bach
ID: 36474006
I don't have an immediate solution, but here's the next step in troubleshooting:

Try running the same query to the text view from the Pervasive Control Center -- this should execute and return the entire data set to the computer in the same way as MSAccess.  If the problem is not exhibited there, then the problem is with MSAccess itself.  

It is a fairly complicated query, so it is also possible that Access is getting only part of the data in the first time period, then it thinks some more before getting more data.  Only a guess...
0
 
LVL 18

Expert Comment

by:mirtheil
ID: 36474011
It sounds like Access is reading a batch of records, then stops, then reads more records, then stops, then reads more records.  

How long does the query take when running through the PCC into Text mode (if you run into Grid mode, only some of the records are returned initially).  How about through ODBC Test?  

How many records are in the result set?
What happens if you limit the query to 10 records? 100 records?  


0
 
LVL 57
ID: 36474013
<<  If you use any JET specific expressions (ie. VBA Calls) or join to local tables, JET will handle all the processing and go back and forth with the server many times in order to process the data.>>

  BTW, you can turn on ODBC tracing in ODBC Manager to see what actually is getting sent to the server to execute a query in Access.

Jim.
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 1

Author Comment

by:Gerhardpet
ID: 36474361
Jim,
You say " BTW, you can turn on ODBC tracing in ODBC Manager to see what actually is getting sent to the server to execute a query in Access"

How do I turn it on? Can you explain how I would do that? Also I have never used a Pass-Through query in Access. How does that work?

mirtheil,
with 100 records the query works find. In the live Pervasive database I have over 22,000 records

If I run the query in PCC in text mode I get the following error
[Pervasive][ODBC Engine Interface]Syntax Error: SELECT INVENTORY.CODE AS SKU, UNIT_OF_MEASURE.CODE AS UOM_CODE, UNIT_OF_MEASURE.UOM_DESCRIPTION AS UOM_DESCRIPTION, IIf(<< ??? >>[UNIT_OF_MEASURE.CODE]<>[INVENTORY.BVSTKUOM],IIf([PRICING]![FACTOR_PRICES]=0,

Open in new window

0
 
LVL 28

Accepted Solution

by:
Bill Bach earned 500 total points
ID: 36474520
Because of the stuff in square brackets in your SQL query, you are definitely using JET to process the query.  Thus, JDettman's answer is likely the most correct.  There is no way to execute this code directly in Pervasive, because you need the Access data at the same time.  This also eliminates the ability to do a pass-through query, for the same reason.

A potential solution would be to import the data from Pervasive to Access FIRST, then process the entire thing in JET.  Or, perhaps you can start with a PSQL-only query, then merge THAT query with the JET query.  Neither is a really good solution, but this is what you have to do when you are joining disparate databases like this.

To enable ODBC Tracing, go to the ODBC Administrator and go to the Tracing tab.  Set your trace file and enable tracing there, then run your query.  When done, stop the tracing, and you'll be able to see every little stinkin' piece of ODBC calls.  Note that this will slow the query down a LOT and generate a big log file, but you will know exactly what it is doing.
0
 
LVL 57
ID: 36474696
<<How do I turn it on? Can you explain how I would do that?>>
 
  Bill's already answered that.  What you looking for is JET should only send one SQL statement to the back end.  If it sends more then that, then you are using/doing something in the query that forces the JET to handle all the processing.

  A good example of that is a delete query; say there are hundreds of records that will be deleted, but because of the way you express the delete, JET can end up trying to decide what needs to be deleted and then will issue one SQL execution per delete rather then a single SQL statement.

<< Also I have never used a Pass-Through query in Access. How does that work?>>

  It's a special type of query in Access that basically says "Take what's here and don't touch it, just send it to the server".

  What does that mean exactly?  Well first and foremost, you query must be able to execute totally server side, so it must be written in the SQL dialect of the sever.  So you cannot use any JET specific expressions.  

  You saw that with your test "If I run the query in PCC in text mode I get the following error".  That's because of the IIF()'s, which is not valid syntax for Pervasive and it does not understand.

  Second thing is you call it a bit differently.  How you do that exactly depends on if your using DAO or ADO.

Jim.
0
 
LVL 1

Author Comment

by:Gerhardpet
ID: 36482068
I have decided to use the Make Table query to first make local tables and then run the query.

I asked a new question here about it

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27291188.html
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now