Extremely Long Time To Process Query

[JimReiley]

Why would the following query take a couple hours to run? Using regular table processing would be less than 1 minute. Table A has approx 10k recs, B approx 50k recs.

SELECT adnumber, entryyear,EntryMonthDay FROM Tablea A
where a.entryyear = '2005' and not exists
(select * from Tableb B
where substring(b.Number,1,8) = a.number and b.type=15)
order by a.number

Thanks.

 

[mirtheil]

What's the query plan show is happening?
How long does the sub query take?

If you add an "exists", the engine probably can't optimize.
WHat happens if you change it to:
SELECT adnumber, entryyear,EntryMonthDay FROM Tablea A
where a.entryyear = '2005' and not exists
(select * from Tableb B
where b.type=15 and substring(b.Number,1,8) = a.number)
order by a.number
or
SELECT adnumber, entryyear,EntryMonthDay FROM Tablea A
where a.entryyear = '2005' and not exists
(select * from Tableb B
where a.number = substring(b.Number,1,8) and b.type=15)
order by a.number
or
SELECT adnumber, entryyear,EntryMonthDay FROM Tablea A
where a.entryyear = '2005' and not exists
(select * from Tableb B
where b.type=15 and a.number = substring(b.Number,1,8))
order by a.number


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
Custom VB and Btrieve development.

http://www.mirtheil.com

 

[JimReiley]

This query is being run in the PCC and What is 'the query plan'?, and I don't have a clue as to how to see what the subquery is doing. All the permutations you asked about take approx the same amount of time.

THanks.

 

[mirtheil]

Here's a link to a document Pervasive sent to me:

http://www.mirtheil.com/files/queryplanviewer.pdf.

As far as the subquery, how long does the query:
select * from Tableb B
where substring(b.Number,1,8) = a.number and b.type=15)
take?
One more thing, if you run the query twice is the second run fast or does it take the same time?

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
Custom VB and Btrieve development.

http://www.mirtheil.com

 

[JimReiley]

I downloaded the pdf, havn't looked at it yet. The sub-query, as best I can tell, is instantaneous.

 

[JimReiley]

I downloaded the pdf, havn't looked at it yet. The sub-query, as best I can tell, is instantaneous, every time a new record in a is processed. Hard to tell.

 

[JimReiley]

I setup the query plan viewer. Doesn't seem to work. Per the instructions, I added 2 keys to the odbc dsn I use, QryPlan, set to 1, and QryPlanOutput. I have run several small programs, using the PCC and the connectivity test from pervasive, plus a small test program I have. No output is ever generated. The directory I have pointed to is c:\delphisource\query\xxx.qpf.

 

[mirtheil]

What version of Pervasive are you using?

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
Custom VB and Btrieve development.

http://www.mirtheil.com

 

[JimReiley]

8.6 server engine and 8.6 workgroup engine on the workstation.

Jim

 

[mirtheil]

And where's the data located? Also, did you create the entries at the server or the client?


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
Custom VB and Btrieve development.

http://www.mirtheil.com

 

[JimReiley]

The server us a Netware server, not Window, does that make any difference. The entries are made on the dsn that access's the server.
Jim

 

[mirtheil]

You need to set the Query Plan settings at the Engine DSN (on the server), not the client. You'll probably just need to add the values to the ODBC.INI on the server in your Engine DSN entry.


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
Custom VB and Btrieve development.

http://www.mirtheil.com

 

[JimReiley]

Ok, the QryPlan works. The results don't mean much to me. What should I be looking for as far as a potential bottle neck?

 

[mirtheil]

First, is it using indexes? The PDF sshows what to look for in the bottom nodes to determine if it's using indexes? Also, what else do you see?


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
Custom VB and Btrieve development.

http://www.mirtheil.com

 

[JimReiley]

In the main query - the last block

FreshJobs [A]:

Actual rows: 10584,
Estimated rows: 529

Range Info:
No active Index Code.

In the sub query on B

InvHistoryDetail :

Actual rows: 129009,
Estimated rows: 5160


Doesn't look like its using an index on the subquery table. And there is one defined for the field its querying on.

 

[mirtheil]

DO the tables pass a database consistency check?

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
Custom VB and Btrieve development.

http://www.mirtheil.com

 

[JimReiley]

I doubt it. I find that test to be incredibly worthless.

 

[JimReiley]

I changed the ddf definition for table b so I wouldn't have to use a substring of a field that is indexed. The query now runs almost instantly. Thanks for all you help. It's appreciated.

Jim