Solved

Slow code problem in SQL

Posted on 2002-07-01
8
245 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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL date incremented 11 31
SQL Server Generate Scripts Fails 5 36
sql server computed columns 11 31
How to construct an if else statement from existing code 6 21
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

803 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