sql server programming: Insert or Update Decision - Performance Issues -- sql server programming

Insert or Update Decision - Performance Issues

Brian Kudera
12/12/2006 8:42:00 PM

sql server programming:

I am reading in a text file line by line and need to perform some
calculations on the data before loading it into a SQL table. I was
originally loading the data into SQL using ADODB via vbscript. I would open a
recordset, check to see if it was AtEndOfStream, and if so, then add a new
row, otherwise update the existing row. This seemed to be taking an
unexpectedly long time, so I tried to compile the code in VB.NET creating a
DLL hoping that the machine compiled language would speed things up.

However, it is still taking up to 4 seconds per row to update. With 180,000
rows to process in my text file, I think I need to seek some help!!

My code snippet that I call through this DLL is found below. My questions
are:
1. Is doing this via VB.NET compiled DLL okay, or is there a better method?
I did not see a performance change when switching from ADODB to the compiled
code.

2. Am I missing a command that will decide for me rather I need to do an
insert or update, and then perform the action? It seems like having to check
to see if the keys already exist to determine rather you need to do an
update/insert statement is a bit redundant.

3. How can I speed things up? It seems like there are so many options, maybe
options that I haven't discovered and options that you may know do not work,
or work better.

If I missed the BOL on this, I apologize. However I do look forward to your
response!

Public Sub SetSalesHistory(ByVal ItemId As String, ByVal SalesWeek
As Integer, ByVal SalesYear As Integer, ByVal Units As Double, ByVal Dollars
As Double, ByVal Cost As Double)

Dim sSQL As String
Dim intResults As Integer

sSQL = ""
sSQL += "SELECT COUNT(*) "
sSQL += "FROM "
sSQL += " Sales "
sSQL += "WHERE "
sSQL += " ItemId = '" & ItemId & "' AND "
sSQL += " SalesWeek = '" & SalesWeek & "' AND "
sSQL += " SalesYear = '" & SalesYear & "'"

Dim cmdSQLCount As New SqlCommand(sSQL, connIngen)
connIngen.Open()
intResults = cmdSQLCount.ExecuteScalar()
connIngen.Close()

If intResults > 0 Then
sSQL = ""
sSQL += "UPDATE Sales "
sSQL += "SET "
sSQL += " SalesMovementUnits = '" & Units & "', "
sSQL += " SalesMovementDollars = '" & Dollars & "', "
sSQL += " SalesMovementCost = '" & Cost & "' "
sSQL += "WHERE "
sSQL += " ItemId = '" & ItemId & "' AND "
sSQL += " SalesWeek = '" & SalesWeek & "' AND "
sSQL += " SalesYear = '" & SalesYear & "'"
Else
sSQL = ""
sSQL += "INSERT INTO Sales "
sSQL += "( "
sSQL += " ItemId, "
sSQL += " SalesWeek, "
sSQL += " SalesYear, "
sSQL += " SalesMovementUnits, "
sSQL += " SalesMovementDollars, "
sSQL += " SalesMovementCost "
sSQL += ") VALUES "
sSQL += "("
sSQL += "'" & ItemId & "',"
sSQL += "'" & SalesWeek & "',"
sSQL += "'" & SalesYear & "',"
sSQL += "'" & Units & "',"
sSQL += "'" & Dollars & "',"
sSQL += "'" & Cost & "'"
sSQL += ")"
End If

Dim cmdSQLUpdate As New SqlCommand(sSQL, connIngen)
connIngen.Open()
intResults = cmdSQLUpdate.ExecuteNonQuery()
connIngen.Close()

Re: Insert or Update Decision - Performance Issues

Dan Guzman
12/12/2006 11:05:08 PM

I suggest you execute the update/insert in a single batch and parameteritize
the SQL. See the SQL example below. However, even with your original code,
4 seconds is still a long time for the queries. Make sure you have an index
on ItemId, SalesWeek and SalesYear.

UPDATE Sales
SET
SalesMovementUnits = @Units,
SalesMovementDollars = @Dollars,
SalesMovementCost = @Cost
WHERE
ItemId = @ItemId AND
SalesWeek = @SalesWeek AND
SalesYear = @SalesYear

IF @@ROWCOUNT = 0
BEGIN
INSERT INTO Sales
(
ItemId,
SalesWeek,
SalesYear,
SalesMovementUnits,
SalesMovementDollars,
SalesMovementCost
VALUES
(
@ItemId,
@SalesWeek,
@SalesYear,
@Units,
@Dollars
@Cost
)
END

--
Hope this helps.

Dan Guzman
SQL Server MVP

[quoted text, click to view]

Re: Insert or Update Decision - Performance Issues

Mike C#
12/13/2006 12:44:51 AM

[quoted text, click to view]

Like Dan pointed out, you will get a nice performance increase from just
parameterizing your queries. Your biggest drain though is opening, closing,
re-opening, re-closing, etc., your SQL connection each time. That's a lot
of overhead! Try opening one SQL connection and keep it open until you're
done instead of opening and closing the connection twice on each call to the
function. And while you're at it, why not put the insert-or-update logic in
a stored procedure so you don't have to send all that extra text to the
server to be parsed with each call?

Re: Insert or Update Decision - Performance Issues

Alex Kuznetsov
12/14/2006 1:23:02 PM

[quoted text, click to view]

Brian,

If you can insert/update in sets of rows, not one row at a time, do so
- it performs much faster.
You can look up "Mimicking MERGE Statement in SQL Server 2005" in my
blog.

-----------------------
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.