SQL Server speed issues

[Sean]

I'm working on upgrading a VB3 app that reads a text file line by line,
and executes inserts or updates to an Access database. Because of
speed issues, I recently moved from using OleDb objects and an Access
database to a SQL Server database, and using SQLCommands and
SQLParameters to insert and update data. The problem is, this is not
any faster than using OleDb with an Access database (which is what I
was using). I switched because all my research said it would be
faster, is there anything I need to do to see the benefits of using SQL
Server and SQLClient objects?

I'm not using stored procedures, and the database is SQL Server, not
MSDE.

 

[David Browne]

I'm working on upgrading a VB3 app that reads a text file line by line,
and executes inserts or updates to an Access database. Because of
speed issues, I recently moved from using OleDb objects and an Access
database to a SQL Server database, and using SQLCommands and
SQLParameters to insert and update data. The problem is, this is not
any faster than using OleDb with an Access database (which is what I
was using). I switched because all my research said it would be
faster, is there anything I need to do to see the benefits of using SQL
Server and SQLClient objects?

First off, on a single local computer SQL Server will not necessarilly be
faster than Access.

Second, executing single inserts is not the fastest way to get data into SQL
Server. Look at BCP, or the BULK INSERT statement for that.

Third, when performing many single-row operations in SQL Server you should
always batch multiple statements together into a transaction. Look at the
SqlTransaction class, and enlist your SqlCommand in the transaction.
Without a transaction SQL Server must write each row to disk (in the
transaction log) as you insert them.

David

 

[Sean]

Thanks for the response. I have another question about using
transactions. As the code is now, it gets a record from the text file,
and executes an update command. If the update command doesn't affect
any rows (the record doesn't exist), then an insert command is
executed. Would this need to change for transactions to work? Here's
an example of the code, using a transaction:

Dim cnConn as SqlConnect("connectionstring")
cnConn.Open()
Dim trnTransaction as SqlTransaction = cnConn.BeginTransaction()
Dim cmdCommand as SqlCommand("",cnConn)

cmdCommand.Transaction = trnTransaction
cmdCommand.CommandText = "UPDATE table SET field1=foo, field2=bar WHERE
field3=id"

If cmdCommand.ExecuteNonQuery = 0 Then
cmdCommand.CommandText = "INSERT INTO table (field1, field2, field3)
VALUES (foo, bar, id)"
cmdCommand.ExecuteNonQuery
End If

do a bunch of these command executes....

trnTransaction.Commit

Would I need to change the code to use a Select command to find out if
the record exists, then use an insert or update accordingly?

 

[William \(Bill\) Vaughn]

First, I would code this as a SP.
Next, if not a SP you can code the TSQL to do both operations in a single
batch.
Select to see if the row exists. If true, Update it. If False Insert it.


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
www.sqlreportingservices.net
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Sean]

What would be the format for the SQL statement that checks to see if
the row exists, and update or insert? I'm not sure how to put that all
into one batch.

 

[William \(Bill\) Vaughn]

It probably best to keep the thread of the conversation intact so we can all
understand your question in the proper context.
This TSQL tests for the existence of a row based on the WHERE clause. If
it's found, it UPDATES the row, otherwise it adds it. Ideally, this should
be in a stored procedure where you pass in the parameters to the WHERE
clause and the values to set.

IF EXISTS(SELECT au_id FROM authors WHERE AU_ID = 16)
BEGIN
--the row exists, update it
UPDATE Authors SET Year_Born = 1945 WHERE AU_ID = 16
END
ELSE
BEGIN
INSERT Authors( [Name], Year_Born) VALUES('Fred Flintstone', 100)
END
See Books Online for more details on the IF and IF EXISTS syntax.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
www.sqlreportingservices.net
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Sean]

Thanks for all of the replies. David, your suggestion about using the
SQLTransaction class worked wonders. I used a SqlTransaction object
that was referenced by my SqlCommand objects. Once I reached a certain
number of commands, I called the Commit method of the SqlTransaction
object, then started all over again. This increased speed by 50% in
some cases, cutting my times in half. Thanks again.