Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

How do I insert many records quickly using ODBC?

2 posts in Windows NT Last posting was on 2000-03-15 06:50:49.0Z
Eric Stuckey Posted on 2000-03-14 20:11:39.0Z
Reply-To: "Eric Stuckey" <stuckey@molecularware.com>
From: "Eric Stuckey" <stuckey@molecularware.com>
Subject: How do I insert many records quickly using ODBC?
Date: Tue, 14 Mar 2000 15:11:39 -0500
Lines: 27
Organization: Molecularware Inc.
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2919.6600
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6600
Message-ID: <KmS$aIfj$GA.201@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.nt
NNTP-Posting-Host: w022.z216112046.bos-ma.dsl.cnc.net 216.112.46.22
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2415
Article PK: 1089869

Hi,
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
slower.)

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
slower.

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,
Eric Stuckey


"Mark A. Parsons" <iron_horse Posted on 2000-03-15 06:50:49.0Z
Message-ID: <38CF32C9.7C07441F@compu_serve.com>
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)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: How do I insert many records quickly using ODBC?
References: <KmS$aIfj$GA.201@forums.sybase.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 29
NNTP-Posting-Host: p313.ipa1-n8-16.iconz.net.nz 210.48.25.57
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2411
Article PK: 1089865


> Am I using the right product?
>
> Or should I just focus on tuning the database setup?
>
> This is a big issue for us.

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
Sybase-flavor'd ODBC?).

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
unnecessarily).

--
Mark Parsons
Pegasys Ltd