Solved

Issues with an Access query

Posted on 2011-09-02
8
364 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 58
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 58
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
 
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 58
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

734 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