dvplayltd
asked on
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 ?
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 ?
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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