CODEWITHSUNDEEP
c#.netado.netenterprise-librarydata-access-layer
manning18
manning18

Reputation: 997

How does parameter caching improve performance in ADO.NET?

Looking to deepen my knowledge of ADO.NET - while reading through the Enterprise Library code I noticed they employ a technique called "parameter caching"

Can someone explain to me how this improves performance and what scenarios this helps?

Is the performance gain purely from the cost of construction of DbParameter objects or is there something else happening on the SQL Server side in terms of the query plan?

I would think that the first time you assemble the DbCommand object and wire-it-up with corresponding DbParameter objects, the SQL Server would at this point have cached an execution plan and subsequent calls to the same procedure with the same parameter templates would re-use the existing cached execution plan on the server?

Upvotes: 2

Views: 1169

Answers (1)

Alex Peck
Alex Peck

Reputation: 4711

I think it is purely to avoid database round trips incurred when calling SqlCommandBuilder.DeriveParameters:

If the parameter information is known in advance, it is more efficient to populate the parameters collection by setting the information explicitly.

Upvotes: 1

Related Questions