[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

How to increase bulk insert/select speed?

Posted on 1999-11-15
11
Medium Priority
?
782 Views
Last Modified: 2012-08-14
Today, I am writing a vc++ console application to insert 10000 records ,
totally 1x10E7 bytes, to mssql 7.0 server.  Surprisely, the speed is
about 29Kbytes per second.  
However, by ftp or copying the same
number of bytes to the server, i
got 2900Kbytes per second.  A 100
times different!!!

I have chose "select into/bulk copy" in the database property. But no help.

Can I increase the speed of bulk insert
or bulk select from the server?

Or my program which using COM is slow?

Thanks.
Rgds.
Alan
alanpong@hkstar.com
--
my source code look like this :

void main()
{
_ConnectionPtr m_pConnection;
_CommandPtr m_pCommand;

::CoInitialise(NULL);

m_pConnection.CreateInstance(__uuid(Connection));
m_pCommand.CreateInstance(__uuid(Command));

m_pConnection->Open.....
m_pCommand->CommandText = "insert mytable values ('xxxxxx......') "

start=clock();
for (i=0;i++;i<10000)
m_pCommand->Execute....
finish=clock();

printf("duration is %2.1f",finish - start);

}
0
Comment
Question by:alanpong
  • 6
  • 3
  • 2
11 Comments
 
LVL 1

Author Comment

by:alanpong
ID: 2208490
Edited text of question.
0
 
LVL 1

Author Comment

by:alanpong
ID: 2208495
Edited text of question.
0
 
LVL 7

Expert Comment

by:simonsabin
ID: 2208502
Right the data to a text file and then use BCP, or you can use the db library directly and use BCP that way.
0
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
LVL 1

Author Comment

by:alanpong
ID: 2208530
remark:
i use the sql oledb provider, not the
odbc driver.
0
 
LVL 1

Author Comment

by:alanpong
ID: 2208546
simonsabin,
do you mean in order to gain faster
performance, just bypass the ADO
layer and go directly to db library?
(I like ADO which makes coding more easier. :>)
Anyway, i will try bcp first
because bcp is written by dblib(?)
If bcp work fast, then dblib should
work fast too....
tell you the result later....
0
 
LVL 7

Accepted Solution

by:
simonsabin earned 90 total points
ID: 2208666
The other thing is the nature of the logging using BCP. it is greatly reduced.
0
 
LVL 1

Author Comment

by:alanpong
ID: 2210625
Wow.  Bcp reports about 2044 kbytes
per second!  Speed increaes a lot.
0
 

Expert Comment

by:guyrafa
ID: 3313956
Please help me
I’m facing the same problem.
I used ado recordset with batchupdate and execute method of an ado connection with a block of inserts or SP (that perform the insert), to insert blocks of 10000 records and every insert took more then half a second.

When using BCP from a file it took me about 0.2 seconds for 10,000 this is steal to slow for me.

Can you please tell me what did you use at the end?
Did you find away to use dblib? If so please send me a sample code ????

Thanks
Guy
0
 
LVL 1

Author Comment

by:alanpong
ID: 3314872
finally, i did what simonsabin told.
I didn't try dblib.

if u think BCP is still slow,
theorectially, to get faster transfer rate, we should do lower lever thing.
So, the workaround would be : programming a client with winsock, transferring the text data to sql server via TCPIP, bypassing the db library layer.

In the sql server, programming a server to import the text server.

hmm....
But, if sql 7.0's "import and export wizard" is a COM component, we can make use of it without inventing any new thing.  (i didn't try whether it is faster or slow.)

0
 
LVL 7

Expert Comment

by:simonsabin
ID: 3315950
Th fastest method of transfer is to use the BULK INSERT command in TSQL (slightly faster than BCP)
To speed it up ensure
No indexes on table
No Constraints on table
Data file is on different disk to that of the database data file.
Log file is on different disk as well
Use RAID
Database is already big enough to take the data i.e. it does not have to grow to accommodate the data.
Increase the Max Async IO configuration of the server, if using RAID.
Get faster disk
If using RAID get more disks to use in the RAID array.
If not use more disks and spread database across a file on each disk.
Get more processors
Get faster processors
Have everything on the server i.e eliminate network bottleneck


0
 

Expert Comment

by:guyrafa
ID: 3316471
Thanks  simonsabin
Most of that is already set will the set the rest of the options and check the bcp again.

Do you know of way to get similar results of BCP import without using a file?

Thanks alanpong
Im going to look into the DTS Objects
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

612 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question