In what situations is the prepare method of a SqlCommand object useful?

Question

Does any body know that in what situations the prepare method of an ADO.NET SqlCommand Object is useful?

Answer 1

• If the underlying database supports it and
• If you're going to invoke the same command (with different parameter values) a number of times. This way you can save a some time because the database can reuse the same query plan.

Normally modern databases cache commands and query plans but by having your queries or commands preprepared you can still save some time.

Answer 2

We are talking about a SqlCommand which implies Microsoft SQL Server. As far as I know SQL Server has always supported it.

It's not clear however if it does anything useful. I've yet to find anyone who really knows. A lot of people claim it does nothing useful and there is no measurable performance difference. I'd like to see evidence one way or another.

On another database (Firebird using an FbCommand object) I know it can cause problems if you are not careful. If you prepare a statement but then never use it a transaction gets held open forever. We had to remove all prepares from our code for Firebird. We had been in the habit of preparing anything that might get executed multiple times.