I am an application developer using Microsoft Visual C++ and the ODBC API
to try to write to a Sybase System 11 database. We are trying to isert many
small records into a table as quickly as possible. Right now we are using
SQLPrepare and calling SQLExecute once for each record, and our performance
is about 20 seconds for about 8000 records.
Is there anything from a coding stand-point to make it faster? Is there
some way to configure the ODBC Driver to tell it that we will only be doing
inserts so that the SQLExecute method runs faster? Is there something better
than ODBC to use? (I already tried MFC recordsets, and they were much
I've already tried setting the number of records and binding arrays to the
parameters (so I only had to call SQLExecute once), but that was actually
Am I using the right product?
Or should I just focus on tuning the database setup?
This is a big issue for us.
Thank you very much for you help,
From: "Eric Stuckey" <firstname.lastname@example.org>
Subject: How do I insert many records quickly using ODBC?
Date: Tue, 14 Mar 2000 15:11:39 -0500
Organization: Molecularware Inc.
X-Newsreader: Microsoft Outlook Express 5.00.2919.6600
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6600
NNTP-Posting-Host: w022.z216112046.bos-ma.dsl.cnc.net 18.104.22.168
Xref: forums-1-dub sybase.public.sqlserver.nt:2415
Article PK: 1089869
Date: Wed, 15 Mar 2000 19:50:49 +1300
From: "Mark A. Parsons" <iron_horse@compu_serve.com>
Organization: Pegasys Ltd
X-Mailer: Mozilla 4.61 [en] (Win98; U)
Subject: Re: How do I insert many records quickly using ODBC?
Content-Type: text/plain; charset=us-ascii
NNTP-Posting-Host: p313.ipa1-n8-16.iconz.net.nz 22.214.171.124
Xref: forums-1-dub sybase.public.sqlserver.nt:2411
Article PK: 1089865
Well, I'm not a C/C++ type (I'm a DBA for goodness sakes!!), so fwiw ...
1) Have you looked at using 'bcp'? This is an O/S utility provided by
Sybase for doing bulk loading of data. You also have access to the bcp
API's through the db-lib and ct-lib libraries (or so I've heard it
mentioned many times; not sure if these are in ODBC, or a
2) If you have to do the 'bunches-of-inserts' routine ... are you using
a transaction wrapper (e.g., begin tran, commit tran)? If not, try
wrapping groups of inserts into a single transaction (e.g., begin tran,
insert#1, insert#2, ... insert#n, commit tran); this has the affect of
deferring the transaction log writes until you do the 'commit tran',
thus cutting down on your disk I/O's ... can same mucho time.
Experiment with the size of 'n' to see what works best for you (make
sure you don't fill up your transaction log and/or block other users