473,883 Members | 1,787 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

SqlCommand slow on INSERT


I have a c# program that loops through a table on a DB2 database.

On each iteration it assigns data to values in the SqlParameter
collection. The command text is an INSERT statement to a Sql Server
database, run with an .ExecuteQuery I enclosed the loop in a
SqlTransaction and commit it at the end.

I timed the program and it inserts about 70 records a second...which I
think is sort of slow...so I set up some Debug.WriteLine s to show where
the time was being spent.

The DataReader loop to the DB2 table is instantaneous. Almost 0s spent
getting each record. Same with assigning values.

The slow step is the actual execution of the SqlCommand. However, I
also ran a SQL Trace and monitored the execution of the statement on the
server. It took 0s to execute. The SqlCommand itself is adding an
extra 0.01s to 0.03s which can add up over the course of hundreds of
thousands of records.

So the only overhead is running .ExecuteQuery on the SqlCommand
object(!) Is there anyway to reduce or minimize this overhead, or a
setting that can affect performance.

I mean if my external source and target are running at 0s - my code
shouldn't be adding overhead to run a command!
Feb 24 '06
20 6110
John, I'm not talking about using INSERTs from your client. I'm talking
about using an intelligent INSERT statement within the context of a stored
procedure that runs on the server. It inserts rows from a "temporary" table
on the server (which was filled with BulkCopy) to a permanent table. This
permits the server-side business rules, triggers, RI and other logic to run.

--
_______________ _______________ ______
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
_______________ _______________ ____

"John Bailo" <ja*****@texeme .com> wrote in message
news:XY******** *************** *******@speakea sy.net...

Hmmm...well, there's no reason I can't do that as an intermediate way in
my code.

Do I have to create the temporary table on the same command that I run my
INSERTS on?

Or can I just create two SqlCommand's and run them on the same
SqlConnection? Will the table remain in memory if I do that?

And then I'll need to do the transfer from the temp table to the table
after that.

William (Bill) Vaughn wrote:
Basically, yes. We generally transfer to "temporary" tables in the
database (not to tempdb) and perform intelligent INSERT/UPDATE statements
from there. It's far faster.

Feb 28 '06 #21

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

Similar topics

2
2344
by: Dinesh | last post by:
Hi, I have one stored procedure in SQL server in which i have written one insert statement. Now in my cs file i pass the parameters require to execute that stored procedure and finaly by mistaken I used command.ExecuteScalar instead of command.ExecuteNonQuery. Surprisingly i am able to insert the data in table. Can anyone please tell me how i am able to insert data using ExecuteScalar of SQLCommand class. Help is really appriciated.
2
1384
by: Tracey | last post by:
Sorry for the repeated post. I tried to update a record in database using SqlCommand.ExecuteNonQuery( ) method (I failed using SqlDataAdapter). I traced the above statement and found that it returned 1 rows afftected. Then I checked my database, the data was not updated at all Can someone give me a clue ?
6
8590
by: B B | last post by:
Okay, here is what's happening: I have a reasonably fast laptop (1.4 GHz Mobile M, so comparable to 2.5GHz P4) doing .net development. Running Windows XP pro, SP2 IIS is installed and running fine All SQL Servers I am referring to share a small (10 computers or so) LAN with a 100MB Switch. No other computers on the LAN exhibit this problem.
15
18899
by: Khurram | last post by:
I have a problem while inserting time value in the datetime Field. I want to Insert only time value in this format (08:15:39) into the SQL Date time Field. I tried to many ways, I can extract the value in timeonly format by using this command Format(now,"HH:mm:ss") But when I insert it into the Sql Server database, it embadded date value with it. the output looks like that "01/01/1900 08:59:00" in that case time is
8
4343
by: shenanwei | last post by:
I have 2 same windows machine, same instance configure and Database , all run DB2 UDB V8.1.5 Test 1 : create table OUT_1 (LINE VARCHAR(350), LINENUMBER INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE)); insert into out_1 (line) values ('C000000002XYTNF102020201855000000075000519600040547000003256510 0000000000000000000000000SIM CAR ADJ JOHN, SMITHJA CPRM SIM CARMBCORL XYTNF1020282726
5
2588
by: TheSteph | last post by:
How can I manually generate a SQL statement (SQLCommand) containing binary data ? I'd like to write all the text of the SQL statement for that operation... Example : I Have Binary data (that represent an image) Byte TmpByte; TmpByte = ....BINARY DATA OF AN IMAGE....
1
5853
by: Noppers | last post by:
I am trying to insert data into 2 tables, Order and Order_Item, in a transaction. Everything works fine if I only have 1 row in my objCartDT dataset. If I have only one row, the 2 tables are updated as expected. But if I have more than one row in the objCartDT, something fails, and I can't figure out what (but I suspect it has to do with the params in conjunction with "cmdNewOrder_Items.ExecuteNonQuery();" and the looping I'm trying to do. Maybe...
0
2234
by: Judge Garth | last post by:
With classic ADO you could attach a ReturnValue parameter to a command containing inline parameters. I didn't add any OTHER parameter objects to do so, The entire command was in the CommandText. The example is to insert a single new row to table Notes which has an Identity field named Notes_ID. There is no problem inserting the row, but I want the new Note_ID value returned with the command. Ex: In SQL Server (2000 or 2005) ...
2
3242
by: shauncl | last post by:
Hello Everyone, I'm currently writing a simple Windows Service (C#) that pings an ip address and inserts the pingreply result in a SQL table. I'm able to send a ping request and get the reply status but I have yet to figure out how to get the sql insert to work. The funny thing is, I'm able to ping and insert through a console application but not a Windows Service. I think it might have something to do with my Account information but my...
0
9944
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
11153
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10757
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10420
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9583
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7975
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7134
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5804
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4620
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

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.