Question about efficiency

  • Thread starter Thread starter Simon Harvey
  • Start date Start date
S

Simon Harvey

Hi,

I'm, wondering about what the best way to approach the following problem is:

I have a an application that has a list of users whose data needs updated in
a database (SQL Server).
Now, the only way I currerntly know how to do this update is to either
repeatedly execute an SQL string or repeatedly call a storedprocedure,
changing the data as I go through the list of users.
These two options dont seem to be very efficient so I was wondering if there
was an easier/more effective way to tackle this problem?

Is there a way you can just execute a big batch without having to constantly
keep executing the sp or statement over and over again?

Thanks to anyone who can offer advice on this

Kind regards

Simon
 
If your sp is good enough to handle multiple customers in single call of sp
then, you can do that.

You may try to make temporary table on connection object and fill list of
customers there in your development tool and use that temporary table in
your sp.
you can execute now your sp with executenonquery method of command object.
that command object should have connection object where you created your
temporary table with list of customers.

Hope this helps.

Rajesh Patel
 
Simon,

Try putting your commands into a transaction. I can insert approx. 1,400
rows a second ( MSS 2k).

Cheers,
Rob Panosh
 
Hi,

By putting my commands into a transaction do you just mean that I say
something like

(Pseudo Code)
Begin Transaction
Do Loads of Update statements one after another
End Transaction

Why would doing the work in a transaction make the code more efficient?

Thanks for your help!

Simon
 
If I was forced to do it, I would perform a bulk import of the necessary
data into a temporary table, then run a stored procedure opening a cursor
on that temporary table and performing the updates.
This may make the updates faster, but probably not easier/more effective --
for instance how are you (easily) going to deal with an update error when
the sp cursor is halfway through the temporary table?
 
Simon,

'Open your connection.
sqlConnection.Open()

'Create the transaction object.
sqlTransaction = sqlConnection.BeginTransaction

Dim sqlCommand AS System.Data.SqlClient.SqlCommand

'Loop thru your commands. When you create your command use the connection
that transaction object was created.
For x = 0I To (Me.transactionQueue.Count - 1I)

sqlCommand = me.transactionQuery.Item(x)

With sqlCommand
.Connection = sqlConnection
.Transaction = sqlTransaction
.Text = "INSERT INT .... "
.ExecuteNonQuery()
End With

Next

'Now either commit or rollback your transaction.
sqlTransaction.Commit()

Hope this helps.

Rob
 
Back
Top