Performance problem

[Jeff McKay]

I have a C++ application that I am porting - switching from using SQL Server
as the
back end to using a local Access 2007 database. The API is ODBC. I was able
to do
with without too much trouble, except for a serious performance problem.
This
application bascially dumps a whole lot of external data into the database,
creating
records in a single parent table, with that table having multiple records in
two
child tables. This app works just fine with SQL Server. With Access
however, after
putting in about 60 entries or so, it just slows to a crawl. I am seeing a
delay of
2 or 3 seconds on every SQLExec call that does an INSERT. Any ideas? Maybe
a problem with primary key values? The two child tables are using
autonumber variables as the key, and the key for the parent record is a
character string (the same) with an
incrementing number appended.

 

[Albert D. Kallal]

Repeated executing of "inserts" in a loop is going to slow to a crawl in
many cases.

I would suggest that you use dao (or ado) in this case.

However, are you actually used a .mdb file format (jet 4), or an actually
2007 file format (ace data engine).

I would consider using a reocrdset:

Dim dbe As DAO.DBEngine
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strMdbFile As String
Dim tdefs As DAO.TableDefs
Dim i As Integer
strMdbFile = "C:\Documents and Settings\Albert\My
Documents\Access\ScriptExample\MultiSelect.mdb"

Set dbe = New DAO.DBEngine
Set db = dbe.OpenDatabase(strMdbFile)

Set rst = db.OpenRecordset("customers")

For i = 1 To 10
rst.AddNew
rst!FirstName = "first " & i
rst!Lastname = "last " & i
rst.Update
Next i

rst.close

My guess is that the above loop to insert data will run about 100, or even
200 times faster then even what you had with sql server. It should run VERY
fast indeed...

if you have ado, that likey a cleaner object model to use, but the code is
much the same as above...

Note that "jet" ships with windows xp, and vista, so you don't actualy have
to install access to read/write mdb files on a windows box.

 

[Jeff McKay]

As I might have mentioned, I am porting an application the currently uses
SQL Server as the back end, and if I can't do it using C++ and ODBC so
that I can re-use the source code, I'm just going to abandon the project.
I was hoping there was some kind of tweaking I could do to the database
design or configuration of Access. But I am getting the unpleasant feeling
that Access is not the right kind of platform for this kind of thing.

PS to answer your question, I have been testing with Access 2007 and
an .accdb file. Do you think switching to a .mdb file would have an effect?

 

[Albert D. Kallal]

PS to answer your question, I have been testing with Access 2007 and
an .accdb file. Do you think switching to a .mdb file would have an
effect?

It's certainly might, the one big advantage of using a mdb file is that jet
4.0 ships with windows, and windows vista right out of the box. You would
never have to installed MS access to use that data.

If I were a betting man, I would suggest that you use ADO with your c++ code
to create a record set and run a loop and see how much faster it runs. The
beauty of this approach would be that you would be able to run your code on
Windows XP, or vista without installing additional software libraries
(you'll likely have to include a ado reference in the project that you
create, but other than that you would not be beholden to using MS access for
your application.

Jet is very high performance, and my experiences can be as much as 200%
faster than the equivalent SQL server code (when dealing with a local file
on your computer).

So my pathway or suggestion would be to try and get this running as a record
set code with ADO, and oledb provider. One quick little test should give you
an idea of how much faster this will run....my bets are is that they should
run many orders of magnitude faster than what you had with SQL server....

 

[Jeff McKay]

I think I have this fixed now. I was doing an unnecessary SQLAllocHandle
and SQLFree inside a tight loop which when removed got things going again.
I do think it shows a bug
in the ODBC driver for Access, since I don't get the problem with SQL
Server, and
there wasn't really anything wrong with the original code.