Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SQL Server
Expertise: Beginner
Jun 26, 2000

Opening a Recordset for Adding Records

Question:
Which is the fastest way to open a recordset (ADO) to add new records:
SqlString="select * from authors where id<0"
rs.open SqlString, Connection, adOpenDynamic, 
adLockPessimistic, adcmdText rs.Add .....
or
rs.open "authors", Connection, adOpenDynamic, 
adLockPessimistic, adcmdTable

Answer:
The approach you should take really depends on your architecture, but I would advise against using the second statement, which returns all the records in a table. If you don't need them then there's no point in returning them. Otherwise, it might be best to limit the number of records returned to 100 or 250.

You're on the right track with the first query. An alternate method is to create a stored procedure called GetEmptyTable and pass it the table name. Then you can use the same stored procedure for different tables throughout your code.

GetEmptyTable 'Categories'

CREATE PROCEDURE GetEmptyTable
	@TableName  varchar(255)
AS

SET NOCOUNT ON

DECLARE @strSQL varchar(255)
	IF @TableName IS NOT NULL OR @TableName <> ''
		BEGIN
			SELECT @strSQL = 'SELECT * FROM ' +
@TableName + ' WHERE 1 = 2' EXECUTE (@strSQL) END
I would definitely recommend using stored procedures instead of sending dynamic SQL to the server. There's a number of reasons why it's better but the bottom line is stored procedures are faster.
DevX Pro
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date