Solved

Slow code problem in SQL

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

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now