ISTech Support Forum

ISTech Support Forum
http://www.istechforum.com/YaBB.pl
Pervasive SQL >> >> Slow response from INVTXN
http://www.istechforum.com/YaBB.pl?num=1206362129

Message started by GasGiant on 03/24/08 at 05:35:29

Title: Slow response from INVTXN
Post by GasGiant on 03/24/08 at 05:35:29

We do a lot of our data viewing with browser-based programs and have excellent response times with almost everything... except looking up Work Order info from the main transaction table, INVTXN. Looking up the lines from a WO transaction can take a very long time (12 to 45 seconds, sometimes longer), when looking up serial number transactions takes a few tenths of a second.

I have tried consolidating transactions, which dropped my number of records from ~400k down to ~290k, but there was no change in the time it takes to do WO look ups. Would I be right in assuming that, since WOPRE is not an indexed field and serial number is... that that has something to do with it? Which brings me to my real question: would a view that is indexed on WOPRE and WOSUF speed things up? I have a basic understanding of views and have requested them before, but I have not created one. Is it easy to do with PSQL?

But would that speed things up? Or would it be faster if I retrieved all of the rows into an array and then used PHP to select the rows I want? That seems like it should be slower. Maybe it would be faster if I suck the view into a MySQL table over night and then run the query from there instead? Sounds like a lot of experimenting. I thought I'd check with you folks and see if anyone knows offhand what would speed things up. Thanks.

Title: Re: Slow response from INVTXN
Post by GasGiant on 04/07/08 at 05:20:29

More info....

Monday morning and it took over two minutes to look up the transactions for one WO. Once I let it complete that function, now the look ups take about 9 seconds. Hmmm. Pervasive is caching the INVTXN, methinks. Or at least some part or form of it. Makes me think that a view might actually work. The cached data must be something like a view, indexed on the WO number. I will test my idea and report back.

Meanwhile, if anyone else has a suggestion, I'm open.

Title: Re: Slow response from INVTXN
Post by GasGiant on 04/09/08 at 10:10:56

Would it be possible to add wo_pre as an index in INVTXN and would that help query speed? Queries based on other indexed fields seem to be zippy, while looking for a WO # is terribly slow.

Title: Re: Slow response from INVTXN
Post by dameng on 04/14/08 at 14:20:05

colin,
i think you are already using the ODBC to access the INVTXN file, ergo, you can add your own Indexes. it won't affect EVO/DBA dictionary, but it will need to run through the file, so best to set it without anyone using the file.

i've done that on my DDF's for my customers, it works fine.

also, yes, the newer versions of pervasive get better at cache previous queries, hence subsequent queries are still in the buffer.

also,
depends on the query constructed, views and stored procedures both are great way to pull in data especially for reporting, but if memory serves me, pervasive doesn't allow defining an index within a view, it takes on the property of the table as listed in the system ddf's. but you can link and subquery, etc.

hth,