Solved

Slow code problem in SQL

Posted on 2002-07-01
8
261 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

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
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

739 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