473,322 Members | 1,188 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Optimizing SQL INSERTs from inside C#

I'm writing a program that, among other things, has to create and populate 7
tables on SQL Server. It reads in TAB delimited files, generates INSERT
statements and executes them. So far I've been able to optimize the program
by using the built-in streamreader and string functions (Split, Replace,
etc.) in C#, SqlConnection and SqlCommand for connectivity, and I've tried
to optimize the SQL insert statements (there are over 100,000 of them) by
sending them 500 at a time to cut down on the communication overhead
associated with sending SQL commands over a network. I tried going up to
1,000 at a time, but the prep time on the local computer starts eating away
at my time savings. Is there anything else I can do to optimize and speed
up my SQL inserts? (Stored Procedures are not an option).

Thanks,
Michael C.
Nov 16 '05 #1
6 2441
Michael,

Stored procedures are not an option, but you can prepare the statement
on the server in order to create a temporary stored procedure. This should
speed up your operations a little bit.

Hope this helps.

--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Michael C" <mi*******@optonline.net> wrote in message
news:4m********************@news4.srv.hcvlny.cv.ne t...
I'm writing a program that, among other things, has to create and populate 7 tables on SQL Server. It reads in TAB delimited files, generates INSERT
statements and executes them. So far I've been able to optimize the program by using the built-in streamreader and string functions (Split, Replace,
etc.) in C#, SqlConnection and SqlCommand for connectivity, and I've tried
to optimize the SQL insert statements (there are over 100,000 of them) by
sending them 500 at a time to cut down on the communication overhead
associated with sending SQL commands over a network. I tried going up to
1,000 at a time, but the prep time on the local computer starts eating away at my time savings. Is there anything else I can do to optimize and speed
up my SQL inserts? (Stored Procedures are not an option).

Thanks,
Michael C.

Nov 16 '05 #2
BCP is *the* fastest way of getting large amounts of data into SQL server.
If there are indexes on the table, drop them all first and then recreate
them again once the data's safely in.

"Michael C" <mi*******@optonline.net> wrote in message
news:4m********************@news4.srv.hcvlny.cv.ne t...
I'm writing a program that, among other things, has to create and populate 7 tables on SQL Server. It reads in TAB delimited files, generates INSERT
statements and executes them. So far I've been able to optimize the program by using the built-in streamreader and string functions (Split, Replace,
etc.) in C#, SqlConnection and SqlCommand for connectivity, and I've tried
to optimize the SQL insert statements (there are over 100,000 of them) by
sending them 500 at a time to cut down on the communication overhead
associated with sending SQL commands over a network. I tried going up to
1,000 at a time, but the prep time on the local computer starts eating away at my time savings. Is there anything else I can do to optimize and speed
up my SQL inserts? (Stored Procedures are not an option).

Thanks,
Michael C.

Nov 16 '05 #3
I thought about BCP, but I'm not too familiar with it. Can I run BCP to
populate a SQL Server from a client machine? And can I run BCP from within
C#?

Thanks,
Michael C.

"Beeeeeves" <1234512345789123456789> wrote in message
news:e5**************@TK2MSFTNGP12.phx.gbl...
BCP is *the* fastest way of getting large amounts of data into SQL server.
If there are indexes on the table, drop them all first and then recreate
them again once the data's safely in.

"Michael C" <mi*******@optonline.net> wrote in message
news:4m********************@news4.srv.hcvlny.cv.ne t...
I'm writing a program that, among other things, has to create and populate
7
tables on SQL Server. It reads in TAB delimited files, generates INSERT
statements and executes them. So far I've been able to optimize the

program
by using the built-in streamreader and string functions (Split, Replace,
etc.) in C#, SqlConnection and SqlCommand for connectivity, and I've

tried to optimize the SQL insert statements (there are over 100,000 of them) by sending them 500 at a time to cut down on the communication overhead
associated with sending SQL commands over a network. I tried going up to 1,000 at a time, but the prep time on the local computer starts eating

away
at my time savings. Is there anything else I can do to optimize and speed up my SQL inserts? (Stored Procedures are not an option).

Thanks,
Michael C.


Nov 16 '05 #4
The only drawback is you have to have SQL server client tools installed

on the machine. But, you should be able to (if you've not got per proc.
licenses, install the free MSDE as a hack
Ahh, that's a deal-breaker. I'll be running this program on machines that
may or may not have MSDE, SQL Server or SQL Client tools installed; and I
may or may not have the clearance to install them on these machines. The
only guarantee I have about the configuration is that there's a MS SQL
Server available (either locally or on the network), they're running Win2K
or XP, and .NET Framework 1.1 will be installed.

Thanks,
Michael C.
Nov 16 '05 #5

"Michael C" <mi*******@optonline.net> wrote in message
news:4m********************@news4.srv.hcvlny.cv.ne t...
I'm writing a program that, among other things, has to create and populate 7 tables on SQL Server. It reads in TAB delimited files, generates INSERT
statements and executes them. So far I've been able to optimize the program by using the built-in streamreader and string functions (Split, Replace,
etc.) in C#, SqlConnection and SqlCommand for connectivity, and I've tried
to optimize the SQL insert statements (there are over 100,000 of them) by
sending them 500 at a time to cut down on the communication overhead
When you say you send them 500 at a time, what do you mean by that and how
do you achieve it? We have an in-house object relational system, and while
reads are optimised (it's smart enough to combine deep tree reads into
joined selects), the best we've managed for inserts is to prepare the
statement if we're going to perform more than one.

Cheers,
Stu
associated with sending SQL commands over a network. I tried going up to
1,000 at a time, but the prep time on the local computer starts eating away at my time savings. Is there anything else I can do to optimize and speed
up my SQL inserts? (Stored Procedures are not an option).

Thanks,
Michael C.

Nov 16 '05 #6
Ah, shame. Using an SqlConnection will be the fastest then, that's if you
can't redistribute the DTS dlls, which I'm not sure of.

"Michael C" <mi*******@optonline.net> wrote in message
news:Gz*********************@news4.srv.hcvlny.cv.n et...
>The only drawback is you have to have SQL server client tools
installed on the machine. But, you should be able to (if you've not got per proc.
licenses, install the free MSDE as a hack
Ahh, that's a deal-breaker. I'll be running this program on machines that
may or may not have MSDE, SQL Server or SQL Client tools installed; and I
may or may not have the clearance to install them on these machines. The
only guarantee I have about the configuration is that there's a MS SQL
Server available (either locally or on the network), they're running Win2K
or XP, and .NET Framework 1.1 will be installed.

Thanks,
Michael C.

Nov 16 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

15
by: Jacek Generowicz | last post by:
I have a multiple disptacher which, conceptually, looks something like this: class Multimethod: def __init__(self): self.methods = {}
5
by: ArShAm | last post by:
Hi there Please help me to optimize this code for speed I added /O2 to compiler settings I added /Oe to compiler settings for accepting register type request , but it seems that is not allowed...
2
by: sameer | last post by:
Hi All, We had developed a .Net application which has the following environment Uses the No touch deployment concept Uses framework 1.1 on client and server machine, Visual studio 2003, Sql...
2
by: Brian | last post by:
In particular, this question goes out to the Microsoft C++ development team. Back in June, Ronald Laeremans posted the following message. Has the optimizing compiler been included with the...
4
by: Flashman | last post by:
A little confusing with setting up optimizing options with 2003 .NET. Under the Optimization Tab. if you set to /O1 or /O2 is the program ignoring the settings for Inline Function expansion,...
2
by: Jack | last post by:
I have a chunk of code that loads a few dozen function pointers into global variables. I'm concerned with unused memory consumption. What if the client only needs to use one or two functions? Then...
12
by: Mark E. Fenner | last post by:
Hello all, I have a code where my inner loop looks like: allNew = for params in cases: newObj = copy(initialObject) newObj.modify(params) allNew.append(newObj) return allNew
0
by: Yosua | last post by:
This response seems ego driven "Billy Verreynne" <vslabs@onwe.co.zawrote in message news:1a75df45.0403161049.201206fb@posting.google.com...
8
by: rahul | last post by:
How is the memory allocated for structures? I need to optimize the memory usage and bit fields are not doing the trick. Any details about the memory allocation for the structures would be a...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.