2

Folks,

I'm using the best-practice of prepared sql statements to execute many Inserts/Updates that vary by the same parameters. I have two choices in my design: 1. all of the work gets done in a single transaction. 2. break-up the work into a number of transactions (not one per statement, but something that suits the concurrency of my environment). If I opt for #2, will SQL take advantage of the cached compiled query/execution plan across transactions? Or, because the query was made within a transaction, the life of the cache will be limited to the transaction?

Community
  • 1
  • 1
Suraj
  • 35,905
  • 47
  • 139
  • 250

2 Answers2

6

Plans are unrelated to transactions. Or connections for that matter

That is, a plan can be shared by many txns and/or users and/or connections. And at different times if the plan is valid and still in cache

gbn
  • 422,506
  • 82
  • 585
  • 676
5

The query cache is independent of transactions, so your queries will get cached regardless which option you choose.

TToni
  • 9,145
  • 1
  • 28
  • 42