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. 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.
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.
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.
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.
"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.
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.
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Jacek Generowicz |
last post by:
I have a multiple disptacher which, conceptually, looks something like
this:
class Multimethod:
def __init__(self):
self.methods = {}
|
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...
|
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...
|
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...
|
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,...
|
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...
|
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
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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)...
|
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...
|
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....
|
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
|
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...
|
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...
| |