i was using PreparedStatement to load some data out of sql database, which has been working fine for quite some time. Till recently, for one day's data, the query is not able to finish (hang on reading the response from database)

       "stackTrace": [         {           "methodName": "socketRead0",           "fileName": "SocketInputStream.java",           "lineNumber": -2,           "className": "java.net.SocketInputStream",           "nativeMethod": true         },         {           "methodName": "socketRead",           "fileName": "SocketInputStream.java",           "lineNumber": 116,           "className": "java.net.SocketInputStream",           "nativeMethod": false         },         {           "methodName": "read",           "fileName": "SocketInputStream.java",           "lineNumber": 171,           "className": "java.net.SocketInputStream",           "nativeMethod": false         },         {           "methodName": "read",           "fileName": "SocketInputStream.java",           "lineNumber": 141,           "className": "java.net.SocketInputStream",           "nativeMethod": false         },         {           "methodName": "read",           "fileName": "IOBuffer.java",           "lineNumber": 2058,           "className": "com.microsoft.sqlserver.jdbc.TDSChannel",           "nativeMethod": false         },         {           "methodName": "readPacket",           "fileName": "IOBuffer.java",           "lineNumber": 6617,           "className": "com.microsoft.sqlserver.jdbc.TDSReader",           "nativeMethod": false         },         {           "methodName": "nextPacket",           "fileName": "IOBuffer.java",           "lineNumber": 6567,           "className": "com.microsoft.sqlserver.jdbc.TDSReader",           "nativeMethod": false         },         {           "methodName": "ensurePayload",           "fileName": "IOBuffer.java",           "lineNumber": 6540,           "className": "com.microsoft.sqlserver.jdbc.TDSReader",           "nativeMethod": false         },         {           "methodName": "skip",           "fileName": "IOBuffer.java",           "lineNumber": 7200,           "className": "com.microsoft.sqlserver.jdbc.TDSReader",           "nativeMethod": false         },         {           "methodName": "skipValue",           "fileName": "dtv.java",           "lineNumber": 3362,           "className": "com.microsoft.sqlserver.jdbc.ServerDTVImpl",           "nativeMethod": false         },         {           "methodName": "skipValue",           "fileName": "dtv.java",           "lineNumber": 162,           "className": "com.microsoft.sqlserver.jdbc.DTV",           "nativeMethod": false         },         {           "methodName": "skipValue",           "fileName": "Column.java",           "lineNumber": 152,           "className": "com.microsoft.sqlserver.jdbc.Column",           "nativeMethod": false         },         {           "methodName": "skipColumns",           "fileName": "SQLServerResultSet.java",           "lineNumber": 216,           "className": "com.microsoft.sqlserver.jdbc.SQLServerResultSet",           "nativeMethod": false         },         {           "methodName": "loadColumn",           "fileName": "SQLServerResultSet.java",           "lineNumber": 770,           "className": "com.microsoft.sqlserver.jdbc.SQLServerResultSet",           "nativeMethod": false         },         {           "methodName": "getterGetColumn",           "fileName": "SQLServerResultSet.java",           "lineNumber": 2036,           "className": "com.microsoft.sqlserver.jdbc.SQLServerResultSet",           "nativeMethod": false         },         {           "methodName": "getValue",           "fileName": "SQLServerResultSet.java",           "lineNumber": 2054,           "className": "com.microsoft.sqlserver.jdbc.SQLServerResultSet",           "nativeMethod": false         },         {           "methodName": "getValue",           "fileName": "SQLServerResultSet.java",           "lineNumber": 2040,           "className": "com.microsoft.sqlserver.jdbc.SQLServerResultSet",           "nativeMethod": false         },         {           "methodName": "getString",           "fileName": "SQLServerResultSet.java",           "lineNumber": 2525,           "className": "com.microsoft.sqlserver.jdbc.SQLServerResultSet",           "nativeMethod": false         },

the same query however works well with plain run in the sql tool, or if run using plain java statement.

and until I have added a dummy where clause ``... and 1=1``, then suddenly the preparedstatement is able to return the result timely again.

in the beginning, i thought it was the dummy clause which made a difference strangely. turns out, this was a problem with sql server parameter sniffing.

the dummy where clause worked only because sql server now see it as a different query, hence not using the previous cached execution plan.

this can be reproduced by adding `

option (recompile)

to the query. this will trigger sql server to drop the previous cached execution plan, as such, even the original query, without the dummy where clause is now back to performing again.

SELECT * FROM sys.database_scoped_configurations;

https://sqlperformance.com/2013/08/t-sql-queries/parameter-sniffing-embedding-and-the-recompile-options

https://www.databasejournal.com/features/mssql/turning-off-parameter-sniffing-for-a-sql-server-database-by-default.html