Why Use Parameter Object?

[RichS]

I've been doing some searching about why it is beneficial to use the parameter object explicitly when executing Stored Procedures from VB - as opposed to something like:

EXEC SP_MySProc ?, lngMyVal

Is there a benefit to adding parameters this way:

.Parameters.Append .CreateParameter("@MyVal", _
 adInteger, adParamInput, 4, lngMyVal)

Rich

 

[xbafge]

I also am struggling with ADO parametrized queries and was not able to get any satisfactory replies. Further I am not using stored procedures. But a few hypotheses I formed while digging into the topic ( in relation to queries are ):
o Microsoft claims its more efficient ( speedwise ) to populate the Parameters collection by hand than let the provider do it ( using Paramaters.Refresh )
o doing allows the adoption of ones own symbolic name for the parameters ( otherwise you have to hardcode to the provider generated parameter names Param1 etc. )

I came some accross useful articles in MSDn Library Online

 

[zemp]

One reason to use ADO parameters is to prevent SQL Injection Attacks. See faq709-1526.

Take Care,

zemp

"If the grass looks greener... it's probably because there is more manure."

 

[chiph]

The other reason is that your code will run faster, since SQL Server will be able to cache your query for the next time you call it.

Every query that you send to SQL Server (or any SQL-aware database) needs to be parsed to make sure it's valid SQL, and that the objects in the query (tables, columns, etc) exist in the database. By using parameters, the query that SQL Server sees is identical every time you call it, so it's able to find that query in it's procedure cache. Example:
[tt]
SELECT FirstName, LastName
FROM tbl_User
WHERE UserID = 232

SELECT FirstName, LastName
FROM tbl_User
WHERE UserID = 233
[/tt]
This results in both queries being parsed every time (and stored in the procedure cache) because they're not 100% identical. If you had coded it with parameters, the database would see this instead:
[tt]
SELECT FirstName, LastName
FROM tbl_User
WHERE UserID = ?

SELECT FirstName, LastName
FROM tbl_User
WHERE UserID = ?
[/tt]
The first one would have been parsed and stored in the cache before execution. The second one would have run much faster because SQL Server is able to pull it out from cache (skipping the expensive parse step entirely) before running it. Every time you run that query afterwards results in it being pulled from cache, with a huge overall performance increase.

Chip H.


If you want to get the best response to a question, please check out FAQ222-2244 first

 

[RichS]

Thanks for the excellent replies. I think maybe I am confused about something: Does the following not implicitly use the Paramter object?

EXEC SP_MySProc ?, lngMyVal

I have done this way for as long as I can remember:

.Parameters.Append .CreateParameter("@MyVal", _
 adInteger, adParamInput, 4, lngMyVal)

but a coworker claims the first way is just as fast. It seems to me that if I explicitly define the variable type, the object does not have to go through the steps to figure it out.
Thanks very much.
Rich

 

[SemperFiDownUnda]

Parameters.Refresh is not actually needed anymore :/

Since MDAC 2.1 the parameter collection would be populated once you fill in these 3 items

Command.ActiveConnection
Command.CommandType
Command.CommandText

Right after the 3rd one is set look at your parameter collection and see it filled.

Next do your .Append and watch your collection start to get doulbe entries. This is a problem where the documentation from microsoft one way or another has not kept up with the actual tools.

When I taught MOC and they went into the .Append I always taught it but demonstrated that it was no longer needed. I also meantioned that you need to know the old way since if you take test it is the "Correct" way of doing it.

Difference between teaching from the book and teaching from experience.

Uses of the Parameter collection. IF you have output parameters then you must use the Parameter collection to get those values back.

I'm not sure about the speed difference but overall its cleaner in my opinion to use it rather then build up the .CommandText to include the parameter.



Hope I've been helpful,
Wayne Francis

If you want to get the best response to a question, please check out FAQ222-2244 first