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

Posted on 2008-11-04
Last Modified: 2012-05-05
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 ?
Question by:dvplayltd
    LVL 37

    Expert Comment

    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

    Author Comment

    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.
    LVL 2

    Accepted Solution

    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.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
    Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now