What is faster- INSERT INTO or stored procedure that insert info ?

Hello experts!

I have to insert one row in table with many records (and 2-4 indexes). I use this in real time system that do many other thinks and this insert MUST be do with minimal time.

What is faster  to execute one INSERT INTO with run time string that is pass to SQL server 2005    or    to write stored procedure that have parameters and it actually execute this INSERT INTO.
As I know stored procedure are precompiled in advance so they are faster. Is this is true when stored procedure insert record ? Can I see some speed comparison between stored procedure and INSERT INTO ?
dvplayltdAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

momi_sabagCommented:
regular statements are also precompiled, so as long as the query text is exactly the same (and you use prepared statement in order to pass parameters) , the query won't be recompiled
i don't know if anyone can provide a solid benchmark that compares the two
i suggest you just try both options and see for yourself
run the insert statement in a loop for 100k times
call the stored procedure in a loop for 100k times
see which one takes longer on the average case
0
dvplayltdAuthor Commented:
I pass regular statements using ADO 2.7 "connection.execite" and I am sure that INSERT INTO is not precompiled ever sql string is same.

I know that stored procedures are precompiled - so SELECT caluse are very benefit from it, because execite plan is  ready. Is INSERT INTO also has benefit ? In other words - when you have INSERT INTO does SQL server create execite plan ? If INSERT INTO also have execite plan - stored procedure will be some times faster.
0
kerwinsiyCommented:
there would not be much difference in performance. but it would be a lot faster if you concatenated all the query in the run time to execute a 1 time request to the server. performance issue will be raised if you created a loop that will connect to the database every time you want to insert. it is advisable to create a concatenated insert statement in a string after concatening all the insert statement then execute it to the db.

by the way if your going to concatenate indefinite length of string or long string you should use string builder instead of string.

another solution is using data adapter batch update.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.