0

I am looking for a way to speed up the SQL, because I think I am not doing it efficiently, though it is working but very slow when there are more records.

I am using ASP/VBScript, but this is SQL Update issue.

I have a loop FOR, NEXT, capturing the submission form and process to capture the SortNum submitted.

for x = 1 to request.form.count
    ' -- Some where here, I captured the SortNum that associated with RecID
     SortNum = TheCapturedRowNum
     TheRecID = TheCapturedRecID

     sql_UpdateSortNum = "Update Table1 SET SortNum = '"& SortNum &"' where RecID = '"& TheRecID "'"
     conn.execute(sql_UpdateSortNum)
Next

Currently, it is working, but the more record (over 50 records), it will get more slower. I guess it is a bad way to loop through and keep executing the update query. Is there a way to speed up the update, like stored and update one time? please note that SortNum value is NOT the (x) of FOR loop.

Any advice would be appreciated,

Thank you,

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
milacay
  • 171
  • 2
  • 17

1 Answers1

1

1/ Send all rows in "request.form" to a stored procedure ( as a parameter having XML data type or as table value parameter) then use UPDATE FROM statement.

This way will avoid back-and-forth calls to database.

2/ If you don't want to modify too much your code, and if you have a reasonable number of rows to update, you can join more update statements (let's say in batches having about 50 update statements) and send them together to DB:

"Update Table1 SET SortNum =...;Update Table1 SET SortNum =...;Update...;"
Community
  • 1
  • 1
bjnr
  • 3,353
  • 1
  • 18
  • 32
  • Sorry, I am not sure I understand. So, I still need to loop through, but instead of using the Update in my code, I should create a stored procedure that take 2 parameters (RecID, SortNum), then using Update SQL in my store procedure, correct? Basically, I still need to loop through the store procedure and feed the 2 parameters to the update store procedure? Any example, would be apprecidated! – milacay Dec 09 '13 at 22:17
  • @milacay You should loop through your records and create one string in XML format. Then, send that string to a stored procedure that accepts an XML parameter. – Mike Dec 09 '13 at 22:19
  • @milacay: UPDATE FROM allows you to update more rows in a single UPDATE statement. – bjnr Dec 09 '13 at 22:28
  • I haven't done UPDATE FROM before, but I will research about it. By any chance, do you have any reference links for that XML and UPDATE FROM sample? Thanks for helping. – milacay Dec 09 '13 at 22:39
  • @milacay: follow the link in my answer. – bjnr Dec 09 '13 at 22:40
  • @SQLhint.com, I tried the option# you suggested, building a string for multiple UPDATE statements, seperated by (;), but it is not working from page execution, CONN.Execute(SQLString). But when I copied/pasted the SQLString in the SQL Server Manager, and run, it works. Any suggestion? – milacay Dec 09 '13 at 23:17
  • No Error, but it works now. Somehow execute the SQLString (with bunch of update statements) in the Transaction didn't work. I moved it out of the Transaction, and it works. Not sure why – milacay Dec 10 '13 at 16:51