OLEDB or ODBC 1

[Mighty]

I have just upgraded to Pervasive 2000 and need to access my data from ASP through the web. I have two servers - one containing the data and one running the webserver. I have a number of SQL statements to access, update and insert data. Most are relatively simple select statements - although there are a number of complex join statements.

What I was wondering is whether I should use OLEDB or ODBC to access the data - and why?

All input would be appreciated. Mise Le Meas,

Mighty

 

[Pervasivite]

I would seriously look at using OleDB because it allows you to do transactional/Btrieve (iRowset), which is blazingly fast along with SQL queries (iCommand) whereas ODBC is strictly SQL which is rarely as fast as transactional, especially for lookup and things like bulk inserts.

There is a paper on the Pervasive site about OleDB and the "best practices" to utilize it under. Many of our developers use ADO with the ODBC bridge and are quite happy, but there are some instances, especially in three tier apps where the OleDB provider is a better choice. You can also contact Pervasive's Developer Support as a pre-Sales call or with the coupon that comes for developers with the purchased SDK (the docs in the SDK are extremely helpful for any development effort and the SDK is only around $100 for developers and comes with a Workgroup engine licensed for 3 users).

Also in order to legally use the Pervasive.SQL 2000/2000i engine through a pooled connection like ASP and a Web site you should contact your Pervasive Sales rep about an Internet/Intranet license. they are about a fifth the cost of the Emterprise database competition and you get both SQL and the blazingly fast transactional access like Btrieve, ActiveX, and OleDB rowset.

Pervasivite

 

[Mighty]

Pervasivite,

I am pretty new to this whole area of connecting to databases through the web, etc. In your response to my initial post, you mention things that I don't really understand. Could you explain or point me to somewhere where I can learn more:

transactional/Btrieve (iRowset)
iCommand
fast transactional access like Btrieve, ActiveX, and OleDB rowset.




Mise Le Meas,

Mighty

 

[Pervasivite]

I did a quick define on the terms you highlighted.

transactional/Btrieve (iRowset) - fast low level interface
iCommand - relational interface for ADO and OleDB/ODBC
fast transactional access like Btrieve, ActiveX, and OleDB rowset - low level access to the data which is file based.

Relational access is the dominant data access method these days, but it is certainly not the only one, nor would I say that it is the best way to go get data for all tasks. It's kind of like having to choose between a screwdriver and a hammer to do household tasks - neither is ideal for every task and each has it's strengths.

Relational was originally concieved to save disk space and reduce redundancy which is not so necessary now in a time of 40GB drives for $100. In Relational and SQL the programmer/user writes an english like query and the database engine translates that into something that can be executed by the actual component that does I/O on the disk. Computers don't understand english so by necessity that looks completely different from english. This means that there is a lot of translation going on that that necessitates overhead. Thus SQL is relatively slow for many things because it parses the query and creates a query plan and optimizes it. SQL/Relational is quite flexible and standard (though not as much as people seem to think) and it great for reporting and ad hoc queries. It also often doesn't take as much technical knowledge of data to begin to use since it is a consistent logical model and the user doesn't have to know about the physical properties of the data and database because they are abstracted.

Other data models such as network, hierarchical, and ISAM/VSAM, may not be as flexible, but they can be lots faster and more powerful becuase they don't have to adhere to the "10 rules" of the relational database model. They often can take more time to code unless wrappers and components are built and used. Basically these models are usually file based so you do things like open a file and get a record, then get another record, etc... These access methods have been used for years on mainframes and minis, in languages like COBOL, and in products like Btrieve (which supports BASIC, C, Fortran, Pascal, COBOL, and lots of 4GL tools) and much of that code has been migrated to PCs. This sort of access is great for "line of business apps" where you have to have instant response and data formats and layouts may not align well with a relational model. I have seen Pervasive.SQL do 4000+ inserts, updates, and deletes a second on quite modest dual CPU PC servers with a good disk array. Pervasive.SQL provides both a full relational engine, the SRDE, and the Btrieve engine which is transactional (also called navigational or ISAM).

The advantage of transactional is that it can do enourmous amounts of work on relatively modest computers with little CPU overhead and it usually maxes out the disk I/O or netowrk before the CPU starts working hard. Relational on the other hand usually benefits from large multi CPU systems and often requires an administrator to keep the data and engine in 'tune' - a DBA (who usually makes a hefty salary). In addition Pervasive.SQL also has a lot of different interfaces for programmers to use in their SDK: ActiveX control accesses BTrieve, PDAC is for Borland Delphi or C++Builder and can use either relational or transactional, while JCL is transactional for Java and JDBC is relational for Java, ADO can do both transactional (iRowset) and relational (ICommand), ODBC is relational only and the native SQL interface in Pervasive.SQL.

The whole point of this is that it's a good idea to use the best tool for the task at hand provided that there is adequate knowledge of how to properly use the tool. You could install a screw with a hammer, but it would be better to learn to use a screwdriver and have a more satisfactory result. Many developers are using SQL for tasks it is not well suited to do and so resort to making it work by throwing hardware at it so it will perform adequately and spending hours, days, and weeks optimizing queries and writing stored procedures.

I would recommend getting a copy of the Pervasive.SQL 2000 SDK and downloading and reading some white papers from the Pervasive site.

Pervasivite

 

[Mighty]

Pervasivite,

Thanks for all the advice and help. I have just purchased the Pervasive.SQL SDK and there seems to be a lot of documentation with it. I will have a look through this and see what I can find.

I just want to go back to my original post. I mentioned that I run a number of SQL queries on my Pervasive DB and wanted to know whether to use OLEDB or ODBC. Your response was:

I would seriously look at using OleDB because it allows you to do transactional/Btrieve (iRowset), which is blazingly fast along with SQL queries (iCommand) whereas ODBC is strictly SQL which is rarely as fast as transactional, especially for lookup and things like bulk inserts.

The SQL queries that I run from my ASP code are all select statements. I don't run any insert or update queries on the Pervasive DB. Some of the queries are quite complex and involve joining several tables. Bearing this in mind, do you still think that OLEDB is the way to go. I have found from using both a third party OLEDB provider on Btrieve 6.15 or the ODBC provider on Pervasive 2000, that speed is a real issue. The response time is quite slow - even on our local network (I haven't tried it through the internet yet). So much so that I have resorted to importing the data to MS Access and reading it from there.

As all my ASP and ADO skills are self-taught, there could be something wrong in my methods that is affecting speed.

Mise Le Meas,

Mighty

 

[Pervasivite]

You are probably quite correct in being cautious about OleDB and you should stick with ODBC until you have thoroughly tested the OleDB. If the queries are complex and used for things like reporting them ODBC is probably going to be your safest bet and also get the best performance in most cases because all the processing would be offloaded to the database server. This would be especially true on a highly loaded Webserver, but probabaly not so critical on one that did light work. If speed is an issue with either ODBC or OleDB (iCommand) then you can look at using the iRowset which has a lot less overhead, though it may require a bit more programming time.

Pervasivite