• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 281
  • Last Modified:

Slow code problem in SQL

I converted an application from an Access database to a SQL database, but the update code takes so much longer in SQL to process (about 30 seconds).

Is this a common problem?  Should I change the update code or am I not aware of something else that may have slowed things down?  I thought SQL would increase performance.

This is the type of update code I am using:



Dim objRS
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open "Table1", objConn, , adLockOptimistic, adCmdTable

     objRS.AddNew
     
     objRS("RandomNumber")=session("Fred")
     objRS("Sup_Email")= SupEmail
     objRS("Subordinate_Email")= SubEmail
     objRS("Reviewing_Officer_Email")= RevEmail
     objRS("HR_Office_Email")= HREmail    
     objRS("LastName")=request.form("LastName")
     objRS("FirstName")=request.form("FirstName")
         
     objRS.Update    
        objRS.Close
     Set objRS = Nothing
     objConn.Close
     Set objConn = Nothing    


Thanks for any suggestions.
 
0
Rougie
Asked:
Rougie
  • 3
  • 2
  • 2
  • +1
1 Solution
 
johan_brohnCommented:
objRS.Open "Table1", objConn, , adLockOptimistic, adCmdTable

Have you tried not to get the entire table?

objRS.Open "SELECT * FROM Table1 WHERE 1 = 0", objConn, , adLockOptimistic, adCmdText
0
 
CFXPERTCommented:
Also you may want to make sure that you put indexes on all the fields you will be querying on in the SQL Server Tables.  A field that does not have an index will return data much more slower than that of a field with an index.
0
 
chops123Commented:
Updating a recordet physically is very intensive.

Use an "UPDATE" query statement to update the necessary fields in the table.

This will take care of the overheads you are now facing.

Your current code is causing huge overheads due to the fact that you are opening a recordset simply to update each field in the table.

Regards
KC
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
RougieAuthor Commented:
The code updates almost instantly and was very easy to implement.  Although the other 2 comments were good advice.

Thanks
0
 
RougieAuthor Commented:
The code updates almost instantly and was very easy to implement.  Although the other 2 comments were good advice.

Thanks
0
 
RougieAuthor Commented:
johan brohn,

In 'WHERE 1 = 0', what does the 1 and the 0 refer to?
0
 
johan_brohnCommented:
1 = 0 will ensure that you have an empty resultset since it
always evaluates to false. You will still have the field
definitions though.
0
 
chops123Commented:
Rougie...I would still recommend you use an update statement rather than the procedure you are using.

KC
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now