Solved

Slow code problem in SQL

Posted on 2002-07-01
8
256 Views
Last Modified: 2012-05-04
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
Comment
Question by:Rougie
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 1

Accepted Solution

by:
johan_brohn earned 50 total points
ID: 7123283
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
 
LVL 5

Expert Comment

by:CFXPERT
ID: 7123391
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
 
LVL 2

Expert Comment

by:chops123
ID: 7124199
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:Rougie
ID: 7124616
The code updates almost instantly and was very easy to implement.  Although the other 2 comments were good advice.

Thanks
0
 

Author Comment

by:Rougie
ID: 7124704
The code updates almost instantly and was very easy to implement.  Although the other 2 comments were good advice.

Thanks
0
 

Author Comment

by:Rougie
ID: 7124707
johan brohn,

In 'WHERE 1 = 0', what does the 1 and the 0 refer to?
0
 
LVL 1

Expert Comment

by:johan_brohn
ID: 7124973
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
 
LVL 2

Expert Comment

by:chops123
ID: 7125877
Rougie...I would still recommend you use an update statement rather than the procedure you are using.

KC
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

680 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question