Web Access Painfully slow

[Mighty]

Hi guys,

We use Pervasive 2000i as the database behind our production and financial systems. I need to be able to access this data to display information on our Intranet and Extranet. However, speed of access is really a problem. So much so, that I run a series of scripts (about 20) overnight which copies data from our pervasive tables to an MS access database and I use the Access Db for the two websites.

However, this means that our customers are not getting real time data and we are looking to change this. I am really looking for pointers on what I can do to improve the speed of access. At the moment, i am just using an ODBC connection to the Pervasive database.

I have never used any database other than MS Access and my methods may not be great. Would it be quicker to access the DDF files directly? Does the way that the table is indexed affect the speed and how should I structure queries to account for this.

By the way, I am using ASP to display the data on the web.
Any help or advice that anyone can give me would be great.

Thanks in advance.

Mighty

 

[mirtheil]

Using ASP to access a Pervasive database, you can use ODBC or OLEDB. If the data is on the Web server, then OLEDB might be better for you. If the data is remote (with 2000i), I would suggest ODBC.
If you're seeing slow performance, post your queries that are taking a long time.


info@mirtheil.com
Custom VB and Btrieve development.
Certified Pervasive Developer
Certified Pervasive Technician

 

[Mighty]

Thanks for the response. In my case, the data is not on the web server. I have set up a Client Pervasive DSN on the webserver pointing to a server DSN on the server where the data resides. Is that correct??

One query that runs particularly slow is the following:

SELECT "Transaction History".ORDNUM_15, "Transaction History".PRTNUM_15, "Transaction History".TNXQTY_15, "SO Master".CURR_27, "SO Detail".FORCUR_28, "Part Master".PMDES1_01 FROM (("Transaction History" INNER JOIN "Part Master" ON "Transaction History".PRTNUM_15 = "Part Master".PRTNUM_01) INNER JOIN "SO Detail" ON "Transaction History".ORDNUM_15 = CONCAT(CONCAT("SO Detail".ORDNUM_28, "SO Detail".LINNUM_28), "SO Detail".DELNUM_28)) INNER JOIN "SO Master" ON "SO Detail".ORDNUM_28 = "SO Master".ORDNUM_27 WHERE "Transaction History".TNXDTE_15='2003-05-27' "Transaction History".TNXCDE_15='S' AND "SO Detail".CUSTID_28 = 'ABC' ORDER BY "Transaction History".ORDNUM_15

I should point out that the Transaction History table is quite large - one of the largest in the database. But the query would regularly time out even after setting the timeout on the query to more than a couple of minutes. I also have queries that I used to try but gave up because that were taking over 5 minutes.

Any help you could give would be appreciated.

Mighty

 

[mirtheil]

How long does that query take using the Pervasive COntrol Center? That query is most likely generating a temporary table because of the two INNER JOINs. It could also be doing a Table Scan, searching every single record. Does the database pass a consistency test?
If you reduce the complexity of the query, does it still return slowly?

info@mirtheil.com
Custom VB and Btrieve development.
Certified Pervasive Developer
Certified Pervasive Technician