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?
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
The query cache is independent of transactions, so your queries will get cached regardless which option you choose.