record set efficiency - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

record set efficiency

Author

Topic

SimonMcGurk
Starting Member

8 Posts

Posted - 2006-11-29 : 09:20:10

Hi

the company I work for is very concerned at present with making all new code as efficient as possible - one query which has come up is what the most efficient way to use record sets is - should they be closed after every time they are used - for example which of the below is more efficient

set rs=dbConn.Execute("Select UserName from Users")

If rs.Fields("UserName")="XXX" Then
set rs2=dbConn.Execute("Insert into UserDiary (WhenAdded, UserName), VALUES (<now>, <rs.Fields("UserName")>)
End if

OR

set rs=dbConn.Execute("Select UserName from Users")
userName=rs.Fields("UserName")
rs.close

If UserName="XXX" Then
set rs=dbConn.Execute("Insert into UserDiary (WhenAdded, UserName), VALUES (<now>, <UserName>)
end if

In other words, is the impact of the closing and repoening of the record set higher than leaving this open and using a second conection for the insert?

Sorry if this seems really basic - but any information gratefully received!

Thanks
Simon

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2006-11-29 : 09:25:10

It would be more efficient to do both steps in a single stored procedure instead of sending sql server 2 different commands sequentially.

Kristen
Test

22859 Posts

Posted - 2006-11-29 : 09:31:33

IME establishing a connection is 10x as expensive as passing a query down an existing connection.

I haven't tested it for a while, and connection pooling may mean its no longer the case - but connection pooling was brought in to solve the awful performance with

Make Connection
Use Connection
Drop Connection
RE-Make Connection
...

so I would suggest hanging on to the connection - but not whilst the user has gone out to lunch! so at least for the duration of a single task / block of work.

But then with a global connection in your application you have to be careful NOT to reuse a connection that has not fully retrieved all its data -e.g. if you are looping round a resultset and want to do something else.

But that apart: as DustinMichaels said : Move the logic for a complete task from Application in to SProc which probably be dramatically faster (maybe 100x faster) than dynamic SQL.

As ever: testing will help reveal what works for you, in your environment.

Kristen

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-11-29 : 13:02:09

By the way, you don't need (and shouldn't use) a recordset for INSERT, UPDATE, or DELETE operations. Unless the command returns data, don't use a recordset at all, use a Command object or just the Connection object.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2006-11-30 : 06:08:42

You'd also be better off putting the SQL code into Stored Procedures...both from a security and performance point of view. Remember too to qualify all tables with dbo.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-30 : 06:12:11

Make use of adExecuteNoRecords option value when DELETE, UPDATE and INSERT in your SP. Command parameters are still returned!
But the ADO engine does not have to create an empty returning resultset.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources