Why is sp_executesql only used on the first SQL invocation?

[sql-sith]

Driver version or jar name

6.1.6-preview and later

SQL Server version

SQL Server Enterprise:

• 2014 CU12
• 2017 CU8

Client operating system

Windows 10 Version 1803, but really ... any

Java/JVM version

java version "1.8.0_171"
Java(TM) SE Runtime Environment (build 1.8.0_171-b11)
Java HotSpot(TM) 64-Bit Server VM (build 25.171-b11, mixed mode)

Problem description

I just have a question, not a problem. My question is about the changes explained at https://github.com/Microsoft/mssql-jdbc/wiki/PreparedStatement-metadata-caching. I'm curious why there is no option to run sp_executesql for every invocation of a proc instead of just the first one. I can understand why some devs might want prep/exec/prepexec, but we don't need our devs to control their procedure caching and would be very happy to have MSSQL do it via sp_executesql. Is there a reason this is not done?

Expected behavior and actual behavior

The driver executes every statement with sp_executesql.

Repro code

The issue is discussed at the github link provided above.

[cheenamalhotra]

Hi @sql-sith ,

The main discussion for this feature can be referenced in issue #166.

Initially there was a proposition to replace sp_prepexec/sp_unprepare with sp_executesql only, but looks like it was later concluded to use sp_executesql only the first time with subsequent calls to prepare/exec/unprepare. From the discussion I can say it was done to get enhanced performance by minimizing SQL Server load and implementing handle caching in the driver.

I'd like to invite @brettwooldridge @davidfrankson @gordthompson and @TobiasSQL for shedding more light on this, as they'll be able to better explain, being involved in the initial design.

[sql-sith]

Thank you, @cheenamalhotra . I read that issue quite a while ago and there is a lot more discussion there now. It's a pretty long read, but very informative! I had forgotten the discussion about not sending long query text to the server repeatedly and that did not occur to me. Based on reading issue #166, I think I understand this now and can relay to my team, so I'll close this ticket.