Post reply

Simple Insert Stored Proc and Performance

  • November 14, 2009 at 9:54 pm

    #136218

    Hi,

    I have a simple insert stored proc in sql 2005 (unfortunately I'm at home and can't generate the SQL but trust me the SP is simple). The insert has about 6 parameters that are either varchar (no more than 40), integer, or numeric(18,4 or 18,9). The SP is literally only Create SPName (Parameters) as Begin Insert Into TableName (6 fields), (6 Values) End. Select No Count is referenced. The table has no more than 5000 records and no indexes (staging table). So a very basic Insert SP. The db is on a lab server, and I am doing my development work on it for now, so no one is hitting the db or the table with "requests".

    The SP is called via vb ADO and the front end app can only call the SP 252 times during a single "button push" to insert 252 records. If I don't call (.Execute in the code below) the SP in vb, the looping mechanism is equal to or less than .1 seconds. When I call the SP it takes 8 seconds. I know the vb ADO code is written efficiently, e.g.,

    [Code="plain"]

    Sub LoadData

    Set ConnOb = Server.Createobject("ADODB.Connection")

    ConnOb.Open Session("ConnectionString")

    Set CmdOb = Server.Createobject("ADODB.Command")

    With CmdOb

    .CommandText = "TheStoredProc"

    .CommandType = adCmdStoredProc

    .Parameters.Append 'something'

    .Parameters.Append 'something else'

    .ActiveConnection = ConnOb

    End With

    **Loop**

    With CmbOb

    .Parameters("something") = value1

    .Parameters("something else") = value2

    .Execute, , adExecuteNoRecords **tried without adExecuteNoRecords = No difference in execution time, still 8 seconds

    End With

    ** End Loop **

    **Clean Up (Close Connection)

    End Sub

    [/code]

    I'm quite certain the performance is on the server side. Any suggestions on how I can improve the performance b.c I want to use the template above to add on to the front end app to call the insert about 1000 times for another process, and I don't want the user to have to wait 35 seconds to load 1000 records.

    I can't use SSIS as end users will have to be able to load data and then refresh a screen so the process has to be real time. I am wary of linked servers and loading bulk as I like the integrity check of parameterized stored procs.

    Any help or ideas on how I can speed up the Insert SP would be greatly appreciated.

    Thanks

  • November 15, 2009 at 2:41 am

    #1079439

    Hi ,

    you havent really give us much to go on.

    All you have said is that you call a SP and it performs poorly.

    No matter how 'simple' it is there is always a potential for chaos.

    We really need to see the stored procedure code,the DDL and the query plan (actual not estimated)

    Additionally use a trace (or profiler) to confirm your suspicions that the issue is with the procedure.

    See the links in my Sig below for further details on how you can help us to help you.


    Clear Sky SQL
    My Blog[/url]

  • November 15, 2009 at 2:46 am

    #1079440

    HI

    And always measure actual performance before jumping to any conclusions either way. I'm not at all convinced that stored procs are any faster for simple . exclusively to simple single line insert/update/delete/select statements

    With best Regards

    canard roti - Pluies acides

  • November 16, 2009 at 8:11 am

    #1079691

    Did you try to call the SP in a loop from the SQL Server Management Studio to make sure the performance hit is not SQL related? It looks like you trying to test SP performance using the client app. You have to get much closer to the SQL Server to find out where the problem is.

    ---------------------------------------------
    [font="Verdana"]Nothing is impossible.
    It is just a matter of time and money.[/font]

  • November 16, 2009 at 9:34 pm

    #1080018

    If you open and close connection on each insert than .. 8 sec for 252 inserts would be about right. Try to open connection, do all inserts and then close conenction. I would give it improvement 10:1, 0.8 sec vs. 8s.

    To beat things up even more .. you probably would need to move to .NET and SQL 2008 and use streaming inserts :-).

  • November 17, 2009 at 3:43 am

    #1080125

    An alternative to the simple stored procedure is to use a disconnected recordset and batch update mode:

    From memory!

    -- Client based cursor

    rs.CursorLocation = adUseClient

    -- Get the structure of the target table into an empty recordset

    rs.Open "select {columns} from dbo.TableToInsert WHERE 1=2", m_conn, , adLockBatchOptimistic, adCmdText

    -- Disconnect the recordset

    Set rs.ActiveConnection = Nothing

    -- Add records to the recordset now

    ...Your AddNew code goes here

    -- Reconnect the recordset

    Set rs.ActiveConnection = m_conn

    -- Batch update

    rs.UpdateBatch

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply