Solved

Issues with an Access query

Posted on 2011-09-02
8
334 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
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 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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

932 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

13 Experts available now in Live!

Get 1:1 Help Now