PSQL is terribly slow. MSSQL - 10 sec, PSQL - 5 min!!!

[SurgeX]

Hi All,

We're looking at porting our app to PSQL.
We got a table with 40,000 records in it and a simple query like "SELECT * FROM TABLE" runs 10 sec on an MSSQL server and 5 min (!!!) on a PSQL. Why?!! Is it just the way the PSQL performs?!!

Thank you.

 

[mirtheil]

How are you issuing the Select statement? What are you using? Are you using ODBC or OLEDB? info@mirtheil.com
Custom VB and Btrieve development.
Certified Pervasive Developer
Certified Pervasive Technician

 

[SurgeX]

First we just ran the query from within P.SQL Control Center, in the SQL Data Manager (BTW, it's P.SQL 2000i SP4) and then we thought OK, fine, maybe it's just the way this "Query Analyzer" works.
Then we wrote (I say 'we' cuz it was a programmer and me, I'm kinda managing this eval project ) a simple VB app using Pervasive ODBC driver. The same sheee-it.
Are you suggesting to try OLEDB?
Would you please provide with some simple VB test code? I then could try it myself over the weekend...
Thanks, Mirtheil!

 

[mirtheil]

You mentioned that you wrote a simple program in VB,what does that code look like? PervasiveOLEDB is good in some situations but I stay away from it(and ODBC) myself. For the fastest performance, the Btrieve API cannot be beat. This is some code I have:

Dim sTemp As String
Dim columname As ADODB.Fields
Dim thisfield As String
Dim fieldvalue As ADODB.Fields
Dim lCount As Long
Dim sSQL As String
dim Conn As ADODB.Connection
dim RS As ADODB.Recordset

Conn.Open "DSN=DEMODATA"
Set RS = New ADODB.Recordset
sSQL = "Select * from Class"
Set RS = Conn.Execute(sSQL)
For lCount = 0 To RS.Fields.Count - 1
    sTemp = sTemp & Chr$(34) & RS.Fields.Item(lCount).Name & Chr$(34) & ","
Next lCount
sTemp = Left$(sTemp, Len(sTemp) - 1)
list1.additem sTemp

Do Until RS.EOF
    sTemp = ""
    For lCount = 0 To RS.Fields.Count - 1
        thisfield = RS.Fields.Item(lCount).Value
        If IsNull(thisfield) Then
            thisfield = ""
        End If
        sTemp = sTemp & Chr$(34) & thisfield & Chr$(34) & ","
    Next lCount
    sTemp = Left$(sTemp, Len(sTemp) - 1)
    list1.additem sTemp
    RS.MoveNext
Loop

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

 

[SurgeX]

Yes, that's better, thank you.
At least I'm getting comparable results now. The problem was that the programmer was using the Jet interface or something...
But anyways. Simple queries are OK, but ones with multiple joins keep P.SQL busy for hours and seem never complete. I suspect it has something to do with indexes, will take a look.
But in general, according to your experience, does it make any sense trying make P.SQL work with complex queries? Because I'm getting impression that P.SQL is just not capable of doing that kind of job...

 

[mirtheil]

The first thing I would suggest in terms of complex queries is make sure that you're not generating temp files. You would also want to make sure that the Pervasive Engine can optimize on indexes in all tables in the joins.
In my experience, it is possible to get SQL server and Pervasive.SQL server to get comparable results, however, if performance critical, I would avoid ODBC and OLEDB altogether and use the Btrieve API. ODBC is good for reporting but even then, sometimes it's faster to read the data using Btrieve. Pervasive ODBC and OLEDB and the Btrieve API use the same underlying engine (the MKDE) but, OLEDB and ODBC go through a bunch of extra layers (and I'm not talking just in the engine). Here's a simple layout of how the engine works:
ODBC app using ADO. OLEDB is almost identical.
App->MS ADO->MS ODBC->PVSW ODBC->SRDE->MKDE
Btrieve API app:
App->Btrieve API->MKDE
As you can see, accessing Btrieve is way faster.
Hope this helps.
info@mirtheil.com
Custom VB and Btrieve development.
Certified Pervasive Developer
Certified Pervasive Technician

 

[SurgeX]

Thanks Mirtheil, you helped a lot.
We'll continue our tests and then decide whether we go with P.SQL or not...

 

[mcentore]

Do yourself a favor and stay away from Pervasive. I'm forced to use it because another product used Pvs2000i as it's backend and it's been nothing but headaches. I know the Btrieve API is fast, but isn't the point of a database to use SQL?!?! I don't want to learn a new API to get at my data. There's a reason people select the unhappy face!!

 

[mirtheil]

The point of a database is to store and retrieve data. The way the data is stored and retrieved doesn't matter. SQL has it's place but in a lot of cases, SQL is not the answer to every question. For example, if you've got a Point of Sale application, you don't need or want the overhead of SQL on each terminal. You need the SQL for the reporting at the managers machine.
I've used Btrieve6.15, P.SQL 7.0 and P.SQL 2000 and haven't had any problems with the engine. Sure there's been some networking problems that have stumped me but the engine just works and works good.
Let's get a little specific, what kind of headaches have you had with Pervasive. Not the app, not the network, just Pervasive..
info@mirtheil.com
Custom VB and Btrieve development.
Certified Pervasive Developer
Certified Pervasive Technician

 

[cubedwellingslave]

Has anyone made any progress on this?

I just landed in a P.SQL 2000i project and really need to get ODBC running something even close to MSSQL...

thread318-608331

 

[SurgeX]

No, not really.
We've decided not to port our app to PSQL.