command Prepared method

[baran121]

hello
i heard that if i use Prepared method for sql connection it will be better.
i want to learn what is command Prepared method, and how can i use that in vb6.
thank you.

 

[TysonLPrice]

I was curious and Googled it. This was the first thing that caught my eye but is by no means meant to be as definitive...From:

http://msdn.microsoft.com/en-us/library/aa260835(VS.60).aspx

The (so-called) Prepared property

In theory, the Prepared property was designed to reduce work on the server by pre-compiling ad hoc queries so subsequent executions would use a temporary stored procedure instead of repeating the compile phase each time the query is executed. However, this is not the case with ADO's implementation—keep reading.

Since ODBC was invented some years ago, SQL Server has gotten much smarter—it now knows how to leverage existing (in cache) compiled query plans. That is, once you execute a query from ADO (or by any means), SQL Server constructs a query plan, saves it in the procedure cache, and executes it. When the query is done, SQL Server marks the query plan as "discardable" but leaves it in memory as long as it can. When another identical (or close-enough) query comes in, which is very likely in systems running multiple clients, SQL Server simply re-uses the cached plan. This saves a significant amount of time and greatly improves scalability. It makes SQL Server actually run faster as more users are added, assuming they're doing about the same things with the same set of queries.

ADO and its ODBC and OLE DB data providers know about this strategy, and in most cases they execute sp_executesql to take advantage of this feature. However, this puts the Prepared property in a quandary. It insists on creating temporary stored procedures, but the data providers insist on using sp_executesql. The result? Chaos. I describe what happens a little later when executing Command objects is discussed.

My recommendation for the Prepared property: forget it—at least for SQL Server. For other providers, set up a trace that shows exactly what's going on—what the server is being asked to do.

 

[strongm]

Minimal example:

[blue]Set objCommand.ActiveConnection = objConnection
objCommand.Prepared = True
...
objCommand.Execute[/blue]

However, you are likely to discover that it doesn't actually give you the performance boost you hope.

 

[dilettante]

I suspect that the caveats about prepared queries stem from its blind misuse. It begins to remind me of discussions about Dim ... As New or when to Set ... = Nothing where people try to demand a simple-minded universal rule.


There is a cost to prepare a query, so unless it is going to be followed up by repeated use it becomes extra overhead. The numbers cited for SQL Server say "4 uses" or "3 to 5 uses" before returns are gained from preparing a given query.

Jet 4.0 OLDB Provider had a flaw (no indication it has ever been fixed) that kept it from re-using a prepared query (unlike saved QueryDefs or Procedures). This makes them even slower. The Jet ODBC Driver doesn't have this bug.

MySQL valued this capability enough to add prepared queries as late as 2005-2006.


So statements that prepared queries should be ignored are basically crap. As with most techniques you have to know where they're appropriate, and sometimes this will require the use of profiling tools specific to the DBMS at hand.

Contrary to the 3rd party article on MSDN linked and quoted above, Executing Prepared Statements is an actual (authoritative) Microsoft article which states:

Prepared execution is commonly used by applications to execute the same parameterized SQL statement repeatedly. Prepared execution is faster than direct execution for statements executed more than three or four times because the statement is compiled only once, while statements executed directly are compiled each time they are executed. Prepared execution can also provide a reduction in network traffic because the driver can send an execution plan identifier and the parameter values, rather than an entire SQL statement, to the data source each time the statement is executed. The Prepared property of the Command object allows you to specify whether to prepare a statement.

An ADO application can use prepared execution to reduce the parsing and compiling overhead associated with repeatedly executing an SQL statement that is executed numerous times. The application builds a character string containing an SQL statement and then uses the Prepared property to have the provider save a prepared (or compiled) version of the query specified in the CommandText property before the first execution of a Command object. This can slow the first call of the Execute method, but after the command is compiled, the provider uses the compiled version of the command for any subsequent executions, which results in improved performance.

If the Prepared property is set to False, the provider executes the Command object directly without creating a compiled version.

The Prepared property can be used when executing a statement with multiple parameter sets. An application can execute a parameterized statement more than once by supplying a different parameter set at each execution instead of reconstructing the statement whenever the parameter set is different.

This article has a VB example, though sadly it's sort of clunky and also shows an example of when not to use a prepared query (it executes it merely once).

So "your mileage may vary" might be the best answer, but for a prepared query to have much value your application should be performing bulk operations.