Delay in Accessing SQL Table for Addnew

[TorrediPisa]

Good Morning to everyone.

I simply want to create a new record in a SQL Server DB Table (protocol ADO).
I have written, for example, this code:
....
rsADO.Open "TABLE", cnADO, adOpenStatic, adLockOptimistic
rsADO.AddNew
....

Well I note that if the Table contains a great number of records, a certain amount of Time (7 secs) is elapsed from the Open sentence and the AddNew sentence.

But if I replace "TABLE" with "SELECT * FROM TABLE WHERE ID = 0" (Where ID is a Column of The Table and 0 is a already recorded value of it) then there is no appreciable elapsed time between the two sentences.

Do you know whether this could be a valid way to speed up the table opening or maybe there are more professional solutions?

Thank you very much for Yr Time.
Regards
TdP

 

[gmmastros]

Your original method is probably a good candidate for 'worst practices'. Essentially, you are loading the entire table in to the recordset passing data from server to client, so that you can insert a record.

All you really need is the table structure so that when you add a record, ADO knows the field lengths, data types, etc... So, you could do...

Select * From table Where 1=0

This will always return 0 records, but more importantly, you will get a recordset that has every field in the table, with it's associated data types and field lengths.

This is much better than pulling every record. Unfortunately, this is still not best practices (in my opinion). Even better would be to add a stored procedure in the database that you would use for inserting a record. This allows you better security, faster execution, and more maintainable code. If you would like to pursue the stored procedure method for inserting records, let me know and I will assist you further.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[TorrediPisa]

Thank You for yr suggestion.
I will see yr S/P method for inserting records with much interest if this is not a big work for you.
Thank you for yr Help.
Regards
TdP

 

[gmmastros]

OK.

Before beginning, I would like to know if the table has an identity column. Sometimes, when inserting data in to a table that has an identity column, it is important to know what the value for the identity column was (that was inserted).

Also, sometimes it is nice to have a generic 'save' stored procedure. As you know, the syntax for updating a record is very different than the syntax for inserting a record. It is possible to have a stored procedure that will 'save' a record. In the stored procedure, you would check for the existence of a record. If it exists, then you update the data. If it doesn't exist, then you insert the data.

So...

Do you have an identity column (automatically incrementing integer field)?
Do you need to 'save' the data, or will a simple update do?



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[TorrediPisa]

I am sorry for my delayed reply, but I had troubles with my PC.
Well the answer is yes in both cases.

Thank you again for yr kind attention.
Best Regards

TdP

 

[gmmastros]

For the examples shown, assume a table named people, with a PersonId (primary key), PersonName, EyeColor, and ShoeSize fields.

To insert a record where PersonId Is NOT an identity field.

Create Procedure InsertData
	@PersonId Integer,
	@PersonName VarChar(50),
	@EyeColor VarChar(20),
	@ShoeSize Decimal(3,1)
As
SET NOCOUNT ON

Insert Into People(PersonId, PersonName, EyeColor, ShoeSize)
Values (@PersonId, @PersonName, @EyeColor, @ShoeSize)

GO

-- Example Usage
InsertDate 1, 'George', 'Brown', 9.5

Now, suppose the PersonId is an identity field. You want to create a stored procedure that returns the identity that was inserted.

Create Procedure InsertData
	@PersonName VarChar(50),
	@EyeColor VarChar(20),
	@ShoeSize Decimal(3,1)
As
SET NOCOUNT ON

Insert Into People(PersonName, EyeColor, ShoeSize)
Values (@PersonName, @EyeColor, @ShoeSize)

Select Scope_Identity() As PersonId

-- example usage
InsertData 'George', 'Brown', 9.5

If you want a generic save stored procedure...

Create Procedure SaveData
	@PersonId Integer
	@PersonName VarChar(50),
	@EyeColor VarChar(20),
	@ShoeSize Decimal(3,1)
As
SET NOCOUNT ON

If Exists(Select * From People Where PersonId = @PersonId)
  Begin
	-- The record already exists
    Update People
    Set    PersonName = @PersonName,
           EyeColor = @EyeColor,
           ShoeSize = @ShoeSize
    Where  PersonName = @PersonName
  End
Else
  Begin
    Insert Into People(PersonId, PersonName, EyeColor, ShoeSize)
    Values (@PersonId, @PersonName, @EyeColor, @ShoeSize)
  End

-- Example usage
SaveData NULL, 'George', 'Brown', 9.5
SaveData 1, 'George', 'Brown', 9.5

Of course, there are a variety of ways to do this, and these are just a couple of samples. The first and 3rd examples don't return any data back to the client, so you would probably want to use an ADO Connection Object's execute method. Since the second method returns the PersonId, you would need to use an ADO recordset's Open method. This will return 1 record with 1 field, which represents the newly inserted PersonId.

I hope this helps. If you have any follow up questions, let me know.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom