Pervasive SQL V8 OLE DB or ODBC string / speed

[BenF]

Hey;

I've been developing our intranet based on pervasive SQL v8. Currently I use vb (ASP) and the Pervasive ODBC Engine driver...

BUT, things are too slow. I am wondering if there exists an OLE DB driver and and OBDC driver? Which one would be fastest?

I have the connection string for the ODBC:
objConn.ConnectionString = "Driver={Pervasive ODBC Engine Interface};dbq=e:\STARCO"
objConn.Open



But I cannot figure out how (if it is possible?) to connect using an OLE DB driver... If pervasive supplies such a thing, and if it would be faster.


Does anyone know the connection string for Pervasive OLE DB (DSN-less preferred)
?
-Ben

 

[mirtheil]

To use the PervasiveOLEDB provider, your connection string is:
objConn.ConnectionString = &quot;Provider=PervasiveOLEDB;Data Source=<database name>;Location=<servername>&quot;
As far as being faster, the connection itself will be a little slower but once connected, the speed should be the same as the ODBC driver.
What kind of queries are you running? Are they slow using the PCC as well as in the ASP application? Can you post a sample query that's slow? Also, how long do the queries take?

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

 

[BenF]

Ok, so I think you are saying that pervasive ODBC driver is in effect, faster than OLE DB. This is good to know.





Here is a sample query:

First I build a query, but the details are too long to post, here is the majority of it:
SQL = &quot;SELECT iciloc.LOCATION as location, iciloc.ITEMNO as itemno, iciloc.QTYSALORDR as QTYSALORDR, iciloc.QTYONHAND as QTYONHAND, iciloc.QTYONORDER as QTYONORDER, right(icloc.ADDRESS4,5) as shippingdays, icloc.&quot;&quot;DESC&quot;&quot; as LocationDescription FROM iciloc, icloc WHERE iciloc.location=icloc.location and iciloc.LOCATION='&quot; & location & &quot;' AND &quot;


Then for each of the item/location matches I perform the following queries (yea, two queries on each result from the previous query is not a good idea but Im pretty option-less)






'***********Begin Code to Get recent transfers of this item to this location***

ictranConn = server.CreateObject(&quot;ADODB.Connection&quot
ictranConn.Open (&quot;L&quot
ictranSQL = &quot;Select ICTRANH.TRANSDATE as dateshipped, ictrand.qty as qty FROM ICTRAND, ICTRANH WHERE ictrand.transseq = ictranh.transseq AND ( ictrand.dayendseq=ictranh.dayendseq AND (ictranh.transdate between &quot; & todaysdateMinusTwenty & &quot; and &quot; &todaysDate & &quot AND ictrand.toloc='&quot; & (currentLocation) & &quot;' and ictrand.fmtitemno ='&quot; & poporlFrmItemNo & &quot;' )&quot;

rsIcTran=ictranConn.Execute(ictranSQL)










'add WHERE LOCATION=current location #currentLocation
thesql = &quot;Select poporl.oqordered as oqordered, poporl.EXPARRIVAL as exparrival, poporh1.PONUMBER as PONO from poporl, poporh1 WHERE poporl.ITEMNO='&quot; & poporlFrmItemNo & &quot;' and poporl.porhseq=poporh1.porhseq and poporl.dtcomplete='&quot; & ZERO & &quot;'AND poporl.LOCATION='&quot; & currentLocation & &quot;'&quot;
rsdetails=dbConn.Execute(thesql)