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, adcmdTextrs.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)ASSET NOCOUNT ONDECLARE @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.
