November 14, 2009 at 9:54 pm
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
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.
November 15, 2009 at 2:46 am
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
November 16, 2009 at 8:11 am
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
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
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