yuvipoy (9/25/2012)
Thanks for your replay,As i have stated early [Quote]Inserting the data externally not from the database itself. [/Quote] my aim is not inserting from the database i need to insert data from non database which may be java or .net c# C++ or anything i need to check how for different connection how the times are taken.Now i'm working with C# so i have posted my example with C# where as i am coding on different platforms also.
The code which you have shared here is a internal one which done at the database level not at the C# level.
I'm not check the loop on database i am checking with C# code with data insertion as my preliminary requirement for now.
Thanks!
If you're inserting from procedural code, and looking for performance, you're going to want to look at several options:
1. You will likely want to look into using a table-valued parameter. You could then create an array in your procedural code (or various other ienumerable types) which can then be streamed in and inserted as a set. I've used this approach successfully on 1000-5000 record sets and it's orders of magnitudes faster than single insert statements.
2. if you're talking in large scale inserts - you may do even better by writing it out to an external file anf having SQL pick up and insert the file in bulk. BCP in with a bulk insert can really get some screaming perf.
3. on the .NET side specifically, you could even look at recordset objects. they all for in-memory inserts which then can be "flushed" into the DB as a set. I haven't played with any such object in java, but would be surprised if they didn't have a similar concept.
The loop used in this way is going to consistently give you the worse perf. You also will have nothing but issues with consistency: with the code as is, you have no good way to get the data out if, say, row 57000 contains garbage and can't be inserted into the DB.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?