Anyone gotten the ODBC driver to come even close to MSSQL?

[cubedwellingslave]

Ok has anyone yet figured out the magic key to getting ODBC performance to come even close to MSSQL (we're using P.SQL 2000i SP3 on a Quad Xeon 2.4 Ghz w/ 2GB RAM)

Using the Btreive API would be a waste since the DB is going to be upgraded to MSSQL later this year, but the tools I'm working on can't wait, so I pretty much need to get ODBC working so I don't have to write this app twice.

We're experiencing the "10 seconds on SQL, 5 minutes on P.SQL" issue that seems pretty common.

TIA!

-sdf

PS: if anyone from Pervasive reads these boards:

1) Why the heck, when using the OLEDB driver, do stored procedures execute on the client? This makes writing stored procs that tie separate DB's together for consolidated reporting impossible.

2) SMP Support? I don't like seeing 3 procs sitting idle when CPU0 is pegged at 100% and queries are taking 5 mins to run...

 

[mirtheil]

A few questions first:
1. What does your query look like?
2. Do your DDFs pass a consistency check? If not, then that could be causing most of your performance problems.
3. Have you tried SP4?
4. What's your app built using (VB/VC/ADO/ODBC API/etc)?

To answer at least one of your questions. The OLEDB provider with 2000i has it's own parser. Everything is handled at the client. WIth V8, the OLEDB uses the parser at the engine. That being said, Stored Procedures are not pre-compiled with P.SQL so there will probably not be any performance gain using them.

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

 

[cubedwellingslave]

Thanks for the reply.

I have a couple queries:

"SELECT * FROM UPR30300"

This table has about 80000 records and it takes about 5 minutes to return them all.

The one in my app is an ugly thing with Sums, Compound If's, 4 Inner Joins and a Group by...

2) Where would I find the DDF consistency checker? I read that bad DDF's will ruin performance.

3) I'm checking that SP4 has been approved by the app vendor. I'm not the DBA for the pervasive or the admin of the master app, I'm just in charge of creating some data import/export tools for a sister company.

4) My app is a VB6 SP5 compiled .EXE

Thanks.

-sdf

 

[mirtheil]

What interface are you using in your VB program? ADO/DAO/RDO?
How long does it take to issue the query using something like PCC or ODBCTEst?
DDF Consistency Checker is in "Tasks" then "Check Database" when you right click on the database name in PCC.

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

 

[cubedwellingslave]

1) ADO
2) Reported times *are* from the PCC
3) Thanks. I'll run it right now.

-sdf

 

[maccrystal]

You might want to try DataDirect ODBC divers for PSQL

http://www.datadirect-technologies.com/

These generally perform better than PSQL since they bypass the SQL layer. Download, give it a spin. The other thing to look at is where is the Working Directory is pointed. Usually at C:\..... You can try and direct it to a different drive with less activity or fragmentation.

http://www.appstratinc.com