Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Query Optimisation

Status
Not open for further replies.
Mighty

Mighty

Programmer
Feb 22, 2001
1,682
US
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
 
Sort by date Sort by votes
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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:
It should help.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

EasyBoy2
  • Locked
  • Question
Slow pervasive query
Replies
0
Views
87
EasyBoy2
EasyBoy2
ShockValue
  • Locked
  • Question
subquery in a query using a case
Replies
2
Views
117
ShockValue
ShockValue
MzKitty
  • Locked
  • Question
Problems with a "Delete From" query.
Replies
1
Views
112
TNGPicard
TNGPicard
eldnahmurof
  • Locked
  • Question
Run PSQL query from command line or script 1
Replies
2
Views
164
eldnahmurof
eldnahmurof
johnny45
  • Locked
  • Question
SLOW QUERY 1
Replies
3
Views
71
mirtheil
mirtheil

Part and Inventory Search

Sponsor

Back
Top