×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Query Optimisation

Query Optimisation

Query Optimisation

(OP)
Folks,

I am really only starting now to try to access Pervasive tables directly from the web. When I tried this before, performance was so bad that I had to abandon the idea. However, I am back on the track of trying to do this.

The query that I am trying to run at the moment joins three tables - Order Master, Part Master and Job Progress. Sometimes this query runs pretty quickly (few seconds) but other times it can timeout. I don't know if it is my query or something to do with the database.

The main indexes for the tables are:

Order Master: ORDNUM_10 + LINNUM_10 + DELNUM_10
Part Master: PRTNUM_01
Job Progress: ORDNUM_14 + OPRSEQ_14

Bearing that in mind, my query is as follows:

SELECT "Order Master".ORDNUM_10, "Order Master".PRTNUM_10, "Part Master".PMDES1_01, "Job Progress".QTYREM_14 FROM ("Order Master" INNER JOIN "Part Master" ON "Order Master".PRTNUM_10 = "Part Master".PRTNUM_01) INNER JOIN "Job Progress" ON "Order Master".ORDER_10 = "Job Progress".ORDNUM_14 WHERE "Job Progress".OPRSEQ_14 = '0099' AND "Job Progress".QTYREM_14 > 0 AND "Order Master".STATUS_10 NOT IN ('4', '5') AND "Order Master".LINNUM_10 = '00' AND "Order Master".DELNUM_10 = '00' ORDER BY "Order Master".ORDNUM_10

Is that query OK or should I look at a more efficient way of writing it?

Mighty

RE: Query Optimisation

(OP)
I also have another query which runs on an access database. This database has three link tables which link to a Pervasive database. When I run this my website completely hangs up and I have to recycle the application pool in IIS to get it back up. the query is as follows:

SELECT [Load Details].[Lot Number], [Load Details].[Pallet Number], [Load Details].[Coffin Number], [Load Details].[Coffin Qty], [Load Details].[Initials], [Load Details].[Date Stored], [Part Master].[PRTNUM_01], [Part Master].[PMDES1_01], [Job Progress].[QTYREM_14] FROM (([Load Details] INNER JOIN [Order Master] ON [Load Details].[Lot Number]=[Order Master].[ORDNUM_10]) INNER JOIN [Part Master] ON [Order Master].[PRTNUM_10]=[Part Master].[PRTNUM_01]) INNER JOIN [Job Progress] ON [Order Master].[ORDER_10]=[Job Progress].[ORDNUM_14] WHERE ((([Load Details].[ID])=8) And (([Job Progress].[OPRSEQ_14])='0099') And (([Order Master].[LINNUM_10])='00') And (([Order Master].[DELNUM_10])='00'))ORDER BY [Load Details].[Pallet Number], [Load Details].[Coffin Number], [Load Details].[Lot Number]

Any ideas?

Mighty

RE: Query Optimisation

A few questions:
1. Use the Query Plan Viewer to determine if the engine is optimizing on indexes.  
2. How long does the query take in the PCC?
3. What version of PSQL are you using?

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
http://www.mirtheil.com

RE: Query Optimisation

(OP)
Hi mirtheil,

I'm back again. Where do I get and how do I run Query Plan Viewer? Do I need to run it on the server or can I run it from the client. using Pervasive SQL 2000i.

Have you any opinions on the idea of linking tables from an access database and joining such tables with an access table?

Mighty

RE: Query Optimisation

(OP)
Just ran it there from the website again and it took 45 seconds. Ran it from PCC and took 2-3 seconds. Refreshed the website page after that and it took about 2-3 seconds also. Seems sporadic.

Am I doing something wrong?

By the way, that was the query using just pervasive tables - not from access db.

Mighty

RE: Query Optimisation

(OP)
Just tried a consistency check on the database. When I opened a command prompt and ran checkDB.exe, I got to pick the database and then got the following error:

Runtime Error
Program e:\pvsw\BIN\checkDB.exe
abnormal program termination

So I then tried to run it from PCC. Got to select the DB, specify consistency check, add all the tables and then:

Error while running wizard
Detailed Description
The server threw an exception
(0x80010105)

I tried to run a consistency check on another Pervasive Db that runs on the same server and it also failed. Both database are in use - one supports ERP application and the other supports a financial application. Would it matter if the tables were in use.

Could my Pervasive Installation be dodgy.

Mighty

RE: Query Optimisation

The speed (45s vs 2-3s) tells me that the data is being read from disk on the first run and then from cache on the second run.  
PSQL 2000 didn't do well with remote DB Checks. Also, if the DDFs are particularly bad, errors like this can occur.
Who is the vendor of the application that's creating this data?  Because you can access the data, I doubt your PSQL install is suspect.  
Here's a link to a PDF Pervasive sent me at one point:
http://www.mirtheil.com/queryplanviewer.pdf
It should help.  

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
http://www.mirtheil.com

RE: Query Optimisation

(OP)
Do I have to run Query Plan Viewer on the server or can I run it from the client.

I ran the consistency check from the Server on two different databases and they both failed. Two applications are Kewill ERP (MAX) and Great Plains Dynamics.

Mighty

RE: Query Optimisation

You have to set it at the Engine level.  I haven't worked with either of those programs specifically so I can't tell you if they have problems.  Does DEMODATA fail in the Consistency check too?

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
http://www.mirtheil.com

RE: Query Optimisation

(OP)
Yes DEMODATE also fails consistency check. Don't really know what I am looking at when I look at the query plan view. Any chance I could send it to you for your opinion?

Mighty

RE: Query Optimisation

POst it on a web site somewhere so I and others can look at it.
We really don't want to post email addresses here.  

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
http://www.mirtheil.com

RE: Query Optimisation

(OP)
Mirtheil,

The plan can be viewed at http://216.153.93.158/qpf.jpg

Any suggestions as to why consistency check would be failing on all DB's on my server - including DEMODATA

Mighty

RE: Query Optimisation

You're using a key on Part Master, Job Progress but not on Order Master. Is there an index on ORDNUM_10?   That's about the only thing I can suggest.
I'm not sure what to suggest on the consistency check. Make sure you are logged in as an Administrator.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
http://www.mirtheil.com

RE: Query Optimisation

(OP)
AS regards the consistency check, I am logged in as an administrator.

On the Order Master table, the first index is ORDNUM_10 + LINNUM_10 + DELNUM_10. I specify the values of LINNUM and DELNUM in the query. But I am trying to get ORDNUM_10 from the query so I can't specify the values for that.

Mighty

RE: Query Optimisation

(OP)
The ORNUM_10 field is a 6 character string which actually contains a numerical value. This value begins with either a 1, 2, 5 or 7. I only want records beginning with a 5. Would it speed up or slow down the query if I added "WHERE LEFT(ORDNUM_10, 1) = '5'"

Mighty

RE: Query Optimisation

The LEFT(ORDNUM_10,1) could slow it down.  I'm not exactly sure.  It might be worth a try.  Usually scalar functions will slow things down.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
http://www.mirtheil.com

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close