Pervasive for Web Backend

[Mighty]

As far as I remember, I broached this question here before but probably a couple of years ago. We use Pervasive SQL2000i and the database for our ERP and financial systems. It seems to function fine with just the few odd glitches.

However, we also have a customer portal website where customers can enter orders, check stock, check financial accounts, etc. When I first set up this website over 2 years ago, I found that speed of access to the Pervasive tables was really slow and it could take over 5 minutes in some cases to load pages with complex queries. Obviously this is not acceptable so at the time I set up an MS Access database and run scripts nightly to pull data from Pervasive and store in the Access tables. The obvious drawback of this is that the data displayed on the website is not real time.

2 years on and we are going to try to look at acessing the data directly again. The setup is as follows:

We have a webserver which is quite old but which we hope to upgrade in the near future.
The Pervasive tables are on a seperate server which is newer.
I have a few pages on our Intranet which still accesses the Pervasive table as speed is not such an issue. All of my queries just use a Pervasive Client DSN. I don't know too much about accessing Pervasive tables from the web and there is most likely a quicker way to access the tables.

All my website code is in ASP and I just run the queries from my ASP code. IS this OK or would it be quicker to write components in VB or something similar to access the tables.

Any help or advice anyone can give me on this would be greatly appreciated.

Thanks in advance.

Mighty

 

[mirtheil]

Nothing you've suggested so far addresses the most likely cause of slow response and that's query optimization. These "complex queries" are probably doing a table scan or creating a temporary sort file. For optimization, you would want to use the Query Plan Viewer and run the queries outside the ASP/IIS environment to verify they really are the cause of the performance bottleneck.

WHile you could wrtie components to access the tables, unless you use the Btrieve API or Btrieve ActiveX controls, you're not going to get much of a speed increase and if you do use the Btrieve API/ActiveX controls, you have to contend with network permissions for the files in question.

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

 

[Mighty]

mirtheil,

Thanks for the response. I have never seen, never mind used, this query plan viewer. How do I go about using this to test out my queries.

What is the fastest way to access the data. Is it acceptable to access the tables using the Pervasive Client DSN. Is it quicker to go straight to the DDF files. Is it important to include fields that are indexed in queries.

I had no experience of databases before this job and I could probably do with some training but any pointers you can give me would be appreciated.

Mighty

 

[Mighty]

I found a paper on the Pervasive Website on "Best Practices for Relational Access in Pervasive.SQL". It instructs you how to use the Query Plan Viewer. I have added the two new scripts (QryPlan and QryPlanOutput) for the relevant DSN. I wrote a simple VBscript proram to run a query on the database and I also tried to run a query on the database using the Control Center. Neither option generated a QPF file. What am I doing wrong?

Mighty

 

[mirtheil]

What did you set the QryPlan and QryPlanOutput to? Also, what happens if you restart the machine? If the ODBC components were in memory when you started, the changes wouldn't take effect until they were reloaded.
BTW, what's the SQL statement that's slow? Post it here, maybe we can offer suggestions.



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

 

[Mighty]

I set the QryPlan to 1 and the QryPlanOutput to c:\qryplan\qryplan.qpf. I set this up on my own client PC and did reboot after setting it up.

As regards the slow SQL statement. I run absolutely loads of queries and all of them are much quicker running from an Access table that from Pervasive. If I was to pick one of the worst ones, it would be something like the one below. Please bear in mind that this is the Access version of the query and the syntax may not be exactly correct for Pervasive.

SELECT [Lot Tracking Hist].PRTNUM_72, [Lot Tracking Hist].LOTNUM_72 AS LOTNUM, SUM([Lot Tracking Hist].TNXQTY_72) AS QTY, [Lot Tracking Hist].ORDNUM_72, [SO Detail].CURQTY_28, [SO Detail].CURDUE_28, [Part Master].PMDES1_01, [Part Sales].SLSUOM_29 FROM (([Lot Tracking Hist] INNER JOIN [SO Detail] ON (Left([Lot Tracking Hist].ORDNUM_72, 6) = [SO Detail].ORDNUM_28 AND MID([Lot Tracking Hist].ORDNUM_72, 7, 2) = [SO Detail].LINNUM_28 AND RIGHT([Lot Tracking Hist].ORDNUM_72, 2) = [SO Detail].DELNUM_28)) INNER JOIN [Part Master] ON [Lot Tracking Hist].PRTNUM_72 = [Part Master].PRTNUM_01) LEFT OUTER JOIN [Part Sales] ON [Lot Tracking Hist].PRTNUM_72 = [Part Sales].PRTNUM_29 WHERE [Lot Tracking Hist].CUSTID_72 = '" & Session("Custid") & "' AND [Lot Tracking Hist].TNXDTE_72=#" & shipDate & "# AND LEFT([Lot Tracking Hist].ORDNUM_72, 6)='" & orderNum & "' GROUP BY [Lot Tracking Hist].PRTNUM_72, [Lot Tracking Hist].LOTNUM_72, [Lot Tracking Hist].ORDNUM_72, [SO Detail].CURQTY_28, [SO Detail].CURDUE_28, [Part Master].PMDES1_01, [Part Sales].SLSUOM_29 ORDER BY [Lot Tracking Hist].ORDNUM_72

Thanks for this.

Mighty

 

[mirtheil]

Is the Pervasive server running WIndows or NetWare? Have you tried setting the Query PLan information at the Server level?
Any time you start doing JOINs in Pervasive, you've got to be careful to make sure that the indexes are used. I wouldn't be surprised if it's doing a temporary sort file.
Also, have your tried Pervasive.SQL V8? THere have been a number of enhancements to the SQL side of things in V8.


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

 

[BenF]

Strange.

We were doing this for about a year and it was lighting quick.

We are still doing it, but with ASP.NET and Pervasive V8 now instead of 2000i with aSP.

Id be happy to give you some tips if you want.

ben

 

[Mighty]

Ben,

Thanks for the offer of help. I have actually moved away from it for the moment but it is definitely something that we are planning to look at. If you could give me some tips, I would appreciate it.

Cheers.

Mighty

 

[Mighty]

Hi Ben,

I'm back again. I just got a new webserver - after 2 years of bugging our financial people. In your last post, you mentioned that you might be able to give me some tips in relation to accessing Pervasive data from the web.

I might look at changing my website now when I transfer it to the new server. Any tips you can give me would be greatly appreciated.

Regards,

Nick.

Mighty

 

[BenF]

Yea, just use the Engine DNS to create a connection....There is tons of stuff on Pervasives site (development) .

I went with an ODBC connection, 'Pervasive ODBC Engine Interface'. But I host my pervasive data on the same server as the web server. If you host pervasive data on a different server than the web server, you MUST MUST MUST use Pervasive ODBC CLient Interface. WHy? Because you cannot have two engines accessing the same database. So you cant use the Pervasive Engine interface on the web server to access the Pervasive data on the DATA server.

You can also go dsn-less connection on the web server. if the pervasive data is hosted there, that would be easy. I have heard this is faster but am not certain.