SendStringParametersAsUnicode parameter not working #2144

SimonChou12138 · 2023-06-08T08:36:04Z

SendStringParametersAsUnicode attribute is set to "false" after the JDBC use prepareStatement SQL execution CHAR type query still very slowly

Based on SqlServer2008, the JDBC driver version is 6.2.2.jre8

Through a series of validation, in fact, in a single primary key OR query, actual sendStringParametersAsUnicode attribute is set to "false" is to take effect.

For example, the following SQL precompiled can take effect, The speed is normal.

Note that the ID here is of the char type, and this SQL will be slow before the property is configured

SELECT * FROM FS_InpatientContact WHERE ([ID]=?) OR ([ID]=?) OR ([ID]=?) OR ([ID]=?) OR ([ID]=?)......

However, this property seems to be invalid in another table query, where ITEM_CODE is of type char and REP_NO is of type numeric. Therefore, I speculate that it may have failed in the case of multiple primary keys.

SELECT * FROM AS_REPENTRY WHERE ([ITEM_CODE]=? AND [REP_NO]=?) OR ([ITEM_CODE]=? AND [REP_NO]=?) OR ([ITEM_CODE]=? AND [REP_NO]=?)......

With this in mind I Debug the source code

Through the debug source setObject method, which can be found in fact sendStringParametersAsUnicode properties of false was effective. However, when actually sent to the server, it should still be in Unicode format, so the conversion will be time-consuming, resulting in extremely slow SQL queries.

This problem is more obvious in the larger the data volume of the table

Above is my investigation process, but also hope guys to give some advice

The text was updated successfully, but these errors were encountered:

Jeffery-Wasty · 2023-06-08T17:07:14Z

Hi @SimonChou12138,

First, can you confirm for us the driver version you are using? You write that you are using version 6.2.2, but I don't see this version listed as part of our releases (I see a 6.2.0 and a 6.2.1). Can you confirm for us that you are using the The Microsoft JDBC Driver for SQL Server?

Next, you should do is look into upgrading the version of the driver you are using. Version 6.2.x is no longer supported, as per our support matrix. We recommend upgrading to the latest stable release 12.2.0, or, if possible, the latest preview release 12.3.1.

If you are restricted to version 6.2.x, we'll make our best effort to help you debug this version, but there have been numerous improvements to the driver in the last 6 years, which may help in resolving your situation. Please confirm for us this is the correct driver, and whether you're able to update, and in the meantime we'll look into possible causes for the issue you are facing.

SimonChou12138 · 2023-06-09T01:56:24Z

Hi @Jeffery-Wasty

I'm sorry I forgot to mention my verification of the driver version in issues.

In fact, after tracking the source code without success, I considered whether it was a driver version problem and looked at the fix log in recent years, and did not find similar problems. At the same time, I also tried to upgrade the driver to 12.3.0.jre8-preview version, but unfortunately, the problem still appears.

By the way, I can confirm that I am using the Microsoft JDBC driver for SQL Server. In mvnrepository, I can find 6.2.2. Jre8 version

Jeffery-Wasty · 2023-06-09T21:06:34Z

Hi @SimonChou12138,

I'm a little confused reading through your post, so please correct me if I'm wrong, but the problem you're describing is as follows:

The second query you posted (SELECT * FROM AS_REPENTRY WHERE ([ITEM_CODE]=? AND [REP_NO]=?) OR ([ITEM_CODE]=? AND [REP_NO]=?) OR ([ITEM_CODE]=? AND [REP_NO]=?)......) does not work for sendStringParametersAsUnicode=true. It does work for sendStringParametersAsUnicode=false, but this is not what you want because it requires extra time on the SQL Server side to process everything. This makes it a slow query. Is that correct?

SimonChou12138 · 2023-06-10T12:05:15Z

Hi @Jeffery-Wasty ,

Perhaps my expression is not clear, which has caused you confusion.

Actually my question is, in the same set url parameters sendStringParametersAsUnicode = false. The first SQL query slowness problem is solved, because the parameters are set so that conversion to Unicode is not forced. But in the second SQL the parameters do not play a role.

Jeffery-Wasty · 2023-06-14T16:59:42Z

Hi @SimonChou12138,

Okay I think we understand the issue a bit better now, thank you.

To help us, we need more details regarding the issue:

In the second SQL, sendStringParametersAsUnicode does not have an effect on query speed. Can you measure this? Can you set a timer and give us numbers?
You've given us the queries, can you also provide the tables being used OR the commands used to create them? We want to see the difference between the two tables to see if there is something there that can explain the issue.
For a query, executed in SSMS, there is the ability to view the execution plan (see screenshot. Right click in query window to bring up menu and click "Include Actual Execution Plan". Then run the query).
When executing the 'slow' query, is there any particular step of the query that is slow? You can share the resulting execution plan with us, and we can take a look. I've included an example of the execution plan below.
Finally, you can provide additional logging for us. Instructions on how to do so can be found here: https://learn.microsoft.com/en-us/sql/connect/jdbc/tracing-driver-operation?view=sql-server-ver16. I'm not sure how helpful this will be in this case, but it may reveal something about the issue.

SimonChou12138 · 2023-06-15T10:16:33Z

Hi @Jeffery-Wasty

To solve this problem, I made a replay Demo. The following verification process will hopefully help you locate the problem

Test table generation

SendStringParametersAsUnicode=false Parameter effective table structure

CREATE TABLE [dbo].[Effective_Test_table] (
  [ID] char(50) COLLATE Chinese_PRC_CS_AS  NOT NULL,
  [NAME] varchar(255) COLLATE Chinese_PRC_CS_AS  NULL,
  [AGE] numeric(10)  NULL,
  CONSTRAINT [PK__Effectiv__3214EC2736470DEF] PRIMARY KEY CLUSTERED ([ID])
  WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
  ON [PRIMARY]
)  
ON [PRIMARY]
GO

ALTER TABLE [dbo].[Effective_Test_table] SET (LOCK_ESCALATION = TABLE)

SendStringParametersAsUnicode=false Parameter invalid table structure

CREATE TABLE [dbo].[Invalid_Test_table] (
  [ID] char(50) COLLATE Chinese_PRC_CS_AS  NOT NULL,
  [NAME] varchar(255) COLLATE Chinese_PRC_CS_AS  NULL,
  [AGE] numeric(10)  NOT NULL,
  CONSTRAINT [PK__Invalid___AE7DED4739237A9A] PRIMARY KEY CLUSTERED ([ID], [AGE])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
)  
ON [PRIMARY]
GO

ALTER TABLE [dbo].[Invalid_Test_table] SET (LOCK_ESCALATION = TABLE)

Test Data Generation

Because SendStringParametersAsUnicode=false Only tables with a large amount of data can have obvious performance performance performance. Therefore, we recommend that you generate one million test data.

/**
 * @author SimonChou
 * @Description 生成测试数据
 * @create 2023-06-15 11:44
 */
public class SqlServerGenerateTestDataSink {
    @Data
    @AllArgsConstructor
    public static class TestData{
        private String ID;
        private String NAME;
        private Integer AGE;
    }

    @SneakyThrows
    public static void main(String[] args) {
        StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment();
        env.setParallelism(128);

        DataStreamSource<TestData> source = env.addSource(new SourceFunction<TestData>() {
            private volatile boolean running = true;
            private final int maxRecords = 1000000;
            private int generatedRecords = 0;

            @Override
            public void run(SourceContext<TestData> sourceContext) throws Exception {
                Random random = new Random();

                while (running && generatedRecords < maxRecords) {
                    String randomId = IdUtil.objectId();
                    String randomName = IdUtil.nanoId();
                    Integer randomAge = random.nextInt(100);

                    sourceContext.collect(new TestData(randomId, randomName, randomAge));

                    generatedRecords++;
                }
            }

            @Override
            public void cancel() {
                running = false;
            }
        });

        SinkFunction<TestData> jdbcSink = JdbcSink.sink(
            "INSERT INTO [dbo].[Invalid_Test_table] ([ID], [NAME], [AGE]) VALUES (?,?,?);",
            (JdbcStatementBuilder<TestData>) (preparedStatement, value) -> {
                preparedStatement.setObject(1,value.getID());
                preparedStatement.setObject(2,value.getNAME());
                preparedStatement.setObject(3,value.getAGE());
            },
            JdbcExecutionOptions.builder()
            .withMaxRetries(3)
            .withBatchSize(100000)
            .build(),
            new JdbcConnectionOptions.JdbcConnectionOptionsBuilder()
            .withDriverName("com.microsoft.sqlserver.jdbc.SQLServerDriver")
            .withUrl("jdbc:sqlserver://XXX.XXX.XXX.XXX:XXXX;databaseName=TEST")
            .withUsername("sa")
            .withPassword("XXX")
            .withConnectionCheckTimeoutSeconds(60)
            .build()
        );
        source.addSink(jdbcSink);

        env.execute();
    }
}

Verification test Demo

Demo Code

/**
 * @author SimonChou
 * @Description 验证测试Demo
 * @create 2023-06-15 14:38
 */
@Slf4j
public class SqlSeverJdbcTest {
    @SneakyThrows
    public static void main(String[] args) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        // 获取连接
        try(Connection connection = DriverManager.getConnection("jdbc:sqlserver://XXX.XXX.XXX.XXX:XXXX;databaseName=TEST;sendStringParametersAsUnicode=false", "sa", "XXX")){
            // Sql
            String effectiveTestTablePkSql = "SELECT ID FROM [dbo].[Effective_Test_table]";
            String invalidTestTablePKSql = "SELECT ID,AGE FROM [dbo].[Invalid_Test_table]";

            // 构建查询
            preparedStatement = connection.prepareStatement(invalidTestTablePKSql);
            resultSet = preparedStatement.executeQuery();

            List<Map<String,Object>> pkDataList = new ArrayList<>();
            int iPutIndex = 0;

            while (resultSet.next()){
                Map<String,Object> pkDataMaps = new HashMap<>();
                ResultSetMetaData metaData = resultSet.getMetaData();
                int columnCount = metaData.getColumnCount();

                for (int i = 1; i <= columnCount; i++) {
                    // 存放主键的K,V键值对
                    pkDataMaps.put(metaData.getColumnLabel(i),resultSet.getObject(i));
                }

                pkDataList.add(pkDataMaps);
                ++iPutIndex;

                // 每N行写入一次
                if (iPutIndex >= 1000) {
                    List<Object> paramValueList = new ArrayList<>();

                    PreparedStatement preparedStatement2 = null;
                    Statement statement = null;
                    ResultSet resultSetForPreparedStatement = null;
                    ResultSet resultSetForStatement = null;

                    // 利用ID主键查询全量数据
                    try(Connection connection2 = DriverManager.getConnection("jdbc:sqlserver://XXX.XXX.XXX.XXX:XXXX;databaseName=TEST;sendStringParametersAsUnicode=false", "sa", "XXX")){
                        // Sql
                        String effectiveTestTableFullSql = "SELECT * FROM [dbo].[Effective_Test_table]";
                        String invalidTestTableFullSql = "SELECT * FROM [dbo].[Invalid_Test_table]";

                        // 用于拼装preparedStatement SQL
                        StringJoiner or = new StringJoiner(" OR ");
                        // 用于拼装statement SQL
                        StringJoiner or2 = new StringJoiner(" OR ");
                        pkDataList.forEach(c -> {
                            StringJoiner and = new StringJoiner(" AND ", "(", ")");
                            StringJoiner and2 = new StringJoiner(" AND ", "(", ")");
                            for (Map.Entry<String, Object> k : c.entrySet()) {
                                and.add(k.getKey() + "=?");
                                and2.add(k.getKey() + "='" + k.getValue()+"'");
                                paramValueList.add(k.getValue());
                            }
                            or.add(and.toString());
                            or2.add(and2.toString());
                        });

                        /*
                          prepareStatement测试
                         */
                        String preparedStatementTestSql = invalidTestTableFullSql + " WHERE " + or;
                        System.out.println("preparedStatement test Sql:"+preparedStatementTestSql);

                        preparedStatement2 = connection2.prepareStatement(preparedStatementTestSql);
                        for (int i = 1; i <= paramValueList.size(); i++) {
                            preparedStatement2.setObject(i,paramValueList.get(i-1));
                        }

                        // 计算执行时间
                        StopWatch jdbcScanTimeCost = StopWatch.createStarted();
                        System.out.println("开始执行preparedStatement test Sql查询");
                        resultSetForPreparedStatement = preparedStatement2.executeQuery();

                        int countRow1 = 0;
                        while (resultSetForPreparedStatement.next()){
                            countRow1++;
                        }
                        System.out.println("preparedStatement test Sql 查询" +countRow1+"条记录执行耗时:"+jdbcScanTimeCost.getTime()+"ms");


                        /*
                          Statement测试
                         */
                        int countRow2 = 0;
                        String statementTestSql = invalidTestTableFullSql + " WHERE " + or2;
                        System.out.println("statement test Sql:"+statementTestSql);

                        StopWatch jdbcScanTimeCost2 = StopWatch.createStarted();
                        System.out.println("开始执行statement test Sql查询");
                        statement = connection2.createStatement();
                        resultSetForStatement = statement.executeQuery(statementTestSql);
                        while (resultSetForStatement.next()){
                            countRow2++;
                        }
                        System.out.println("statement test Sql 查询" +countRow2+"条记录执行耗时:"+jdbcScanTimeCost2.getTime()+"ms");
                    }finally {
                        resultSetForStatement.close();
                        resultSetForPreparedStatement.close();
                        statement.close();
                        preparedStatement2.close();
                    }

                    pkDataList.clear();
                    iPutIndex = 0;
                }
            }
        }finally {
            resultSet.close();
            preparedStatement.close();
        }
    }
}

Next, we will verify the output of the test code and the execution time, and press SendStringParametersAsUnicode=false before and After parameter addition and PreparedStatement and Statement the execution situation is analyzed by combining the two Test tables of the parameter effective table and the non-effective table.

JdbcUrl not added `SendStringParametersAsUnicode=false` execution before parameters

Effective_Test_table

Invalid_Test_table

Summary

Through the implementation of 3.1.1 and 3.1.2, we can analyze that no sendStringParametersAsUnicode=false before the parameter PreparedStatement in this case, the SQL query speed of both tables is very slow, while in Statement the execution is very fast.

Add JdbcUrl sendStringParametersAsUnicode=false the execution after the parameter

Effective_Test_table

Invalid_Test_table

Summary

Through the implementation of 3.2.1 and 3.2.2, we can analyze and add SendStringParametersAsUnicode=false after the parameter PreparedStatement in this case, the execution speed of the Effective_Test_table table can be the same as that of Statement the execution speed is basically the same, so it can be concluded that in fact SendStringParametersAsUnicode=false the parameter takes effect in the Effective_Test_table table. If you look at the Invalid_Test_table table, you can see that after you add parameters PreparedStatement in the case of fast, Statement in the case of slow, so we can draw a conclusion SendStringParametersAsUnicode=false the parameter does not take effect.

Q&A

About execution plan

About what you mentioned in the issues for queries executed in SSMS, you can view the execution plan. Unfortunately, this problem is used under the Jdbc driver. PreparedStatement SQL reproduced , when converted to normal Statement SQL in queries executed in SSMS in fact, there are no slow problems that all hit indexes, which can be well reflected in the verification test Demo.

About logging

I put sendStringParametersAsUnicode = false when the Invalid_Test_table table is invalid, the logs are printed when the table is executed. For more information, see

But I think maybe the log may not be too helpful, as mentioned earlier I have debug the source code, so the actual observable log I have seen, of course, I may not observe too carefully.

捕获到日志消息: Property:serverName Value:XXX.XXX.XXX.XXX
捕获到日志消息: Property:portNumber Value:1433
捕获到日志消息: Property:databaseName Value:TEST
捕获到日志消息: Property:sendStringParametersAsUnicode Value:false
捕获到日志消息: ConnectionID:1 created by (SQLServerDriver:1)
捕获到日志消息: ConnectionID:1 This attempt server name: XXX.XXX.XXX.XXX port: 1433 InstanceName: null useParallel: false
捕获到日志消息: ConnectionID:1 This attempt endtime: 1686823293398
捕获到日志消息: ConnectionID:1 This attempt No: 0
捕获到日志消息: ConnectionID:1 Connecting with server: XXX.XXX.XXX.XXX port: 1433 Timeout slice: 1874 Timeout Full: 15
捕获到日志消息: ConnectionID:1 ClientConnectionId: 68b89d7d-edff-4069-885c-320f9db212fb Server returned major version:10
捕获到日志消息: SQLServerPreparedStatement:1 created by (ConnectionID:1 ClientConnectionId: 68b89d7d-edff-4069-885c-320f9db212fb)
捕获到日志消息: SQLServerPreparedStatement:1: calling sp_executesql: SQL:SELECT ID,AGE FROM [dbo].[Invalid_Test_table]
捕获到日志消息: SQLServerResultSet:1 created by (SQLServerPreparedStatement:1)
捕获到日志消息:  SQLServerResultSetMetaData:1 created by (SQLServerResultSet:1)
捕获到日志消息: Property:serverName Value:XXX.XXX.XXX.XXX
捕获到日志消息: Property:portNumber Value:1433
捕获到日志消息: Property:databaseName Value:TEST
捕获到日志消息: Property:sendStringParametersAsUnicode Value:false
捕获到日志消息: ConnectionID:2 created by (SQLServerDriver:1)
捕获到日志消息: ConnectionID:2 This attempt server name: XXX.XXX.XXX.XXX port: 1433 InstanceName: null useParallel: false
捕获到日志消息: ConnectionID:2 This attempt endtime: 1686823294891
捕获到日志消息: ConnectionID:2 This attempt No: 0
捕获到日志消息: ConnectionID:2 Connecting with server: XXX.XXX.XXX.XXX port: 1433 Timeout slice: 1874 Timeout Full: 15
捕获到日志消息: ConnectionID:2 ClientConnectionId: 66cd8ad6-cc5b-4a5a-85d2-0492adc4a14e Server returned major version:10
preparedStatement test Sql:SELECT * FROM [dbo].[Invalid_Test_table] WHERE (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?)
捕获到日志消息: SQLServerPreparedStatement:2 created by (ConnectionID:2 ClientConnectionId: 66cd8ad6-cc5b-4a5a-85d2-0492adc4a14e)
开始执行preparedStatement test Sql查询
捕获到日志消息: SQLServerPreparedStatement:2: calling sp_executesql: SQL:SELECT * FROM [dbo].[Invalid_Test_table] WHERE (ID=@P0 AND AGE=@P1) OR (ID=@P2 AND AGE=@P3) OR (ID=@P4 AND AGE=@P5) OR (ID=@P6 AND AGE=@P7) OR (ID=@P8 AND AGE=@P9) OR (ID=@P10 AND AGE=@P11) OR (ID=@P12 AND AGE=@P13) OR (ID=@P14 AND AGE=@P15) OR (ID=@P16 AND AGE=@P17) OR (ID=@P18 AND AGE=@P19) OR (ID=@P20 AND AGE=@P21) OR (ID=@P22 AND AGE=@P23) OR (ID=@P24 AND AGE=@P25) OR (ID=@P26 AND AGE=@P27) OR (ID=@P28 AND AGE=@P29) OR (ID=@P30 AND AGE=@P31) OR (ID=@P32 AND AGE=@P33) OR (ID=@P34 AND AGE=@P35) OR (ID=@P36 AND AGE=@P37) OR (ID=@P38 AND AGE=@P39) OR (ID=@P40 AND AGE=@P41) OR (ID=@P42 AND AGE=@P43) OR (ID=@P44 AND AGE=@P45) OR (ID=@P46 AND AGE=@P47) OR (ID=@P48 AND AGE=@P49) OR (ID=@P50 AND AGE=@P51) OR (ID=@P52 AND AGE=@P53) OR (ID=@P54 AND AGE=@P55) OR (ID=@P56 AND AGE=@P57) OR (ID=@P58 AND AGE=@P59) OR (ID=@P60 AND AGE=@P61) OR (ID=@P62 AND AGE=@P63) OR (ID=@P64 AND AGE=@P65) OR (ID=@P66 AND AGE=@P67) OR (ID=@P68 AND AGE=@P69) OR (ID=@P70 AND AGE=@P71) OR (ID=@P72 AND AGE=@P73) OR (ID=@P74 AND AGE=@P75) OR (ID=@P76 AND AGE=@P77) OR (ID=@P78 AND AGE=@P79) OR (ID=@P80 AND AGE=@P81) OR (ID=@P82 AND AGE=@P83) OR (ID=@P84 AND AGE=@P85) OR (ID=@P86 AND AGE=@P87) OR (ID=@P88 AND AGE=@P89) OR (ID=@P90 AND AGE=@P91) OR (ID=@P92 AND AGE=@P93) OR (ID=@P94 AND AGE=@P95) OR (ID=@P96 AND AGE=@P97) OR (ID=@P98 AND AGE=@P99) OR (ID=@P100 AND AGE=@P101) OR (ID=@P102 AND AGE=@P103) OR (ID=@P104 AND AGE=@P105) OR (ID=@P106 AND AGE=@P107) OR (ID=@P108 AND AGE=@P109) OR (ID=@P110 AND AGE=@P111) OR (ID=@P112 AND AGE=@P113) OR (ID=@P114 AND AGE=@P115) OR (ID=@P116 AND AGE=@P117) OR (ID=@P118 AND AGE=@P119) OR (ID=@P120 AND AGE=@P121) OR (ID=@P122 AND AGE=@P123) OR (ID=@P124 AND AGE=@P125) OR (ID=@P126 AND AGE=@P127) OR (ID=@P128 AND AGE=@P129) OR (ID=@P130 AND AGE=@P131) OR (ID=@P132 AND AGE=@P133) OR (ID=@P134 AND AGE=@P135) OR (ID=@P136 AND AGE=@P137) OR (ID=@P138 AND AGE=@P139) OR (ID=@P140 AND AGE=@P141) OR (ID=@P142 AND AGE=@P143) OR (ID=@P144 AND AGE=@P145) OR (ID=@P146 AND AGE=@P147) OR (ID=@P148 AND AGE=@P149) OR (ID=@P150 AND AGE=@P151) OR (ID=@P152 AND AGE=@P153) OR (ID=@P154 AND AGE=@P155) OR (ID=@P156 AND AGE=@P157) OR (ID=@P158 AND AGE=@P159) OR (ID=@P160 AND AGE=@P161) OR (ID=@P162 AND AGE=@P163) OR (ID=@P164 AND AGE=@P165) OR (ID=@P166 AND AGE=@P167) OR (ID=@P168 AND AGE=@P169) OR (ID=@P170 AND AGE=@P171) OR (ID=@P172 AND AGE=@P173) OR (ID=@P174 AND AGE=@P175) OR (ID=@P176 AND AGE=@P177) OR (ID=@P178 AND AGE=@P179) OR (ID=@P180 AND AGE=@P181) OR (ID=@P182 AND AGE=@P183) OR (ID=@P184 AND AGE=@P185) OR (ID=@P186 AND AGE=@P187) OR (ID=@P188 AND AGE=@P189) OR (ID=@P190 AND AGE=@P191) OR (ID=@P192 AND AGE=@P193) OR (ID=@P194 AND AGE=@P195) OR (ID=@P196 AND AGE=@P197) OR (ID=@P198 AND AGE=@P199)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
捕获到日志消息: SQLServerResultSet:2 created by (SQLServerPreparedStatement:2)
preparedStatement test Sql 查询100条记录执行耗时:34326ms
statement test Sql:SELECT * FROM [dbo].[Invalid_Test_table] WHERE (ID='648aac21401733061b7f3aa8' AND AGE='39') OR (ID='648aac21401733061b7f3aa9' AND AGE='47') OR (ID='648aac21401733061b7f3aaa' AND AGE='27') OR (ID='648aac21401733061b7f3aab' AND AGE='47') OR (ID='648aac21401733061b7f3aac' AND AGE='26') OR (ID='648aac21401733061b7f3aad' AND AGE='78') OR (ID='648aac21401733061b7f3aae' AND AGE='90') OR (ID='648aac21401733061b7f3aaf' AND AGE='17') OR (ID='648aac21401733061b7f3ab0' AND AGE='86') OR (ID='648aac21401733061b7f3ab1' AND AGE='18') OR (ID='648aac21401733061b7f3ab2' AND AGE='92') OR (ID='648aac21401733061b7f3ab3' AND AGE='47') OR (ID='648aac21401733061b7f3ab4' AND AGE='90') OR (ID='648aac21401733061b7f3ab5' AND AGE='73') OR (ID='648aac21401733061b7f3ab6' AND AGE='63') OR (ID='648aac21401733061b7f3ab7' AND AGE='59') OR (ID='648aac21401733061b7f3ab8' AND AGE='51') OR (ID='648aac21401733061b7f3ab9' AND AGE='42') OR (ID='648aac21401733061b7f3aba' AND AGE='36') OR (ID='648aac21401733061b7f3abb' AND AGE='72') OR (ID='648aac21401733061b7f3abc' AND AGE='69') OR (ID='648aac21401733061b7f3abd' AND AGE='14') OR (ID='648aac21401733061b7f3abe' AND AGE='29') OR (ID='648aac21401733061b7f3abf' AND AGE='71') OR (ID='648aac21401733061b7f3ac0' AND AGE='65') OR (ID='648aac21401733061b7f3ac1' AND AGE='65') OR (ID='648aac21401733061b7f3ac2' AND AGE='0') OR (ID='648aac21401733061b7f3ac3' AND AGE='51') OR (ID='648aac21401733061b7f3ac4' AND AGE='50') OR (ID='648aac21401733061b7f3ac5' AND AGE='55') OR (ID='648aac21401733061b7f3ac6' AND AGE='34') OR (ID='648aac21401733061b7f3ac7' AND AGE='53') OR (ID='648aac21401733061b7f3ac8' AND AGE='38') OR (ID='648aac21401733061b7f3ac9' AND AGE='37') OR (ID='648aac21401733061b7f3aca' AND AGE='94') OR (ID='648aac21401733061b7f3acb' AND AGE='19') OR (ID='648aac21401733061b7f3acc' AND AGE='21') OR (ID='648aac21401733061b7f3acd' AND AGE='79') OR (ID='648aac21401733061b7f3ace' AND AGE='57') OR (ID='648aac21401733061b7f3acf' AND AGE='75') OR (ID='648aac21401733061b7f3ad0' AND AGE='78') OR (ID='648aac21401733061b7f3ad1' AND AGE='91') OR (ID='648aac21401733061b7f3ad2' AND AGE='21') OR (ID='648aac21401733061b7f3ad3' AND AGE='40') OR (ID='648aac21401733061b7f3ad4' AND AGE='18') OR (ID='648aac21401733061b7f3ad5' AND AGE='35') OR (ID='648aac21401733061b7f3ad6' AND AGE='26') OR (ID='648aac21401733061b7f3ad7' AND AGE='17') OR (ID='648aac21401733061b7f3ad8' AND AGE='66') OR (ID='648aac21401733061b7f3ad9' AND AGE='15') OR (ID='648aac21401733061b7f3ada' AND AGE='89') OR (ID='648aac21401733061b7f3adb' AND AGE='29') OR (ID='648aac21401733061b7f3adc' AND AGE='37') OR (ID='648aac21401733061b7f3add' AND AGE='42') OR (ID='648aac21401733061b7f3ade' AND AGE='53') OR (ID='648aac21401733061b7f3adf' AND AGE='16') OR (ID='648aac21401733061b7f3ae0' AND AGE='58') OR (ID='648aac21401733061b7f3ae1' AND AGE='73') OR (ID='648aac21401733061b7f3ae2' AND AGE='84') OR (ID='648aac21401733061b7f3ae3' AND AGE='88') OR (ID='648aac21401733061b7f3ae4' AND AGE='96') OR (ID='648aac21401733061b7f3ae5' AND AGE='43') OR (ID='648aac21401733061b7f3ae6' AND AGE='75') OR (ID='648aac21401733061b7f3ae7' AND AGE='4') OR (ID='648aac21401733061b7f3ae8' AND AGE='95') OR (ID='648aac21401733061b7f3ae9' AND AGE='0') OR (ID='648aac21401733061b7f3aea' AND AGE='86') OR (ID='648aac21401733061b7f3aeb' AND AGE='62') OR (ID='648aac21401733061b7f3aec' AND AGE='76') OR (ID='648aac21401733061b7f3aed' AND AGE='16') OR (ID='648aac21401733061b7f3aee' AND AGE='18') OR (ID='648aac21401733061b7f3aef' AND AGE='19') OR (ID='648aac21401733061b7f3af0' AND AGE='73') OR (ID='648aac21401733061b7f3af1' AND AGE='90') OR (ID='648aac21401733061b7f3af2' AND AGE='37') OR (ID='648aac21401733061b7f3af3' AND AGE='90') OR (ID='648aac21401733061b7f3af4' AND AGE='91') OR (ID='648aac21401733061b7f3af5' AND AGE='2') OR (ID='648aac21401733061b7f3af6' AND AGE='40') OR (ID='648aac21401733061b7f3af7' AND AGE='18') OR (ID='648aac21401733061b7f3af8' AND AGE='97') OR (ID='648aac21401733061b7f3af9' AND AGE='44') OR (ID='648aac21401733061b7f3afa' AND AGE='81') OR (ID='648aac21401733061b7f3afb' AND AGE='4') OR (ID='648aac21401733061b7f3afc' AND AGE='54') OR (ID='648aac21401733061b7f3afd' AND AGE='33') OR (ID='648aac21401733061b7f3afe' AND AGE='68') OR (ID='648aac21401733061b7f3aff' AND AGE='21') OR (ID='648aac21401733061b7f3b00' AND AGE='16') OR (ID='648aac21401733061b7f3b01' AND AGE='45') OR (ID='648aac21401733061b7f3b02' AND AGE='1') OR (ID='648aac21401733061b7f3b03' AND AGE='8') OR (ID='648aac21401733061b7f3b04' AND AGE='23') OR (ID='648aac21401733061b7f3b05' AND AGE='15') OR (ID='648aac21401733061b7f3b06' AND AGE='11') OR (ID='648aac21401733061b7f3b07' AND AGE='52') OR (ID='648aac21401733061b7f3b08' AND AGE='6') OR (ID='648aac21401733061b7f3b09' AND AGE='20') OR (ID='648aac21401733061b7f3b0a' AND AGE='11') OR (ID='648aac21401733061b7f3b0b' AND AGE='93')
开始执行statement test Sql查询
捕获到日志消息: SQLServerStatement:3 created by (ConnectionID:2 ClientConnectionId: 66cd8ad6-cc5b-4a5a-85d2-0492adc4a14e)
捕获到日志消息: SQLServerStatement:3 Executing (not server cursor) SELECT * FROM [dbo].[Invalid_Test_table] WHERE (ID='648aac21401733061b7f3aa8' AND AGE='39') OR (ID='648aac21401733061b7f3aa9' AND AGE='47') OR (ID='648aac21401733061b7f3aaa' AND AGE='27') OR (ID='648aac21401733061b7f3aab' AND AGE='47') OR (ID='648aac21401733061b7f3aac' AND AGE='26') OR (ID='648aac21401733061b7f3aad' AND AGE='78') OR (ID='648aac21401733061b7f3aae' AND AGE='90') OR (ID='648aac21401733061b7f3aaf' AND AGE='17') OR (ID='648aac21401733061b7f3ab0' AND AGE='86') OR (ID='648aac21401733061b7f3ab1' AND AGE='18') OR (ID='648aac21401733061b7f3ab2' AND AGE='92') OR (ID='648aac21401733061b7f3ab3' AND AGE='47') OR (ID='648aac21401733061b7f3ab4' AND AGE='90') OR (ID='648aac21401733061b7f3ab5' AND AGE='73') OR (ID='648aac21401733061b7f3ab6' AND AGE='63') OR (ID='648aac21401733061b7f3ab7' AND AGE='59') OR (ID='648aac21401733061b7f3ab8' AND AGE='51') OR (ID='648aac21401733061b7f3ab9' AND AGE='42') OR (ID='648aac21401733061b7f3aba' AND AGE='36') OR (ID='648aac21401733061b7f3abb' AND AGE='72') OR (ID='648aac21401733061b7f3abc' AND AGE='69') OR (ID='648aac21401733061b7f3abd' AND AGE='14') OR (ID='648aac21401733061b7f3abe' AND AGE='29') OR (ID='648aac21401733061b7f3abf' AND AGE='71') OR (ID='648aac21401733061b7f3ac0' AND AGE='65') OR (ID='648aac21401733061b7f3ac1' AND AGE='65') OR (ID='648aac21401733061b7f3ac2' AND AGE='0') OR (ID='648aac21401733061b7f3ac3' AND AGE='51') OR (ID='648aac21401733061b7f3ac4' AND AGE='50') OR (ID='648aac21401733061b7f3ac5' AND AGE='55') OR (ID='648aac21401733061b7f3ac6' AND AGE='34') OR (ID='648aac21401733061b7f3ac7' AND AGE='53') OR (ID='648aac21401733061b7f3ac8' AND AGE='38') OR (ID='648aac21401733061b7f3ac9' AND AGE='37') OR (ID='648aac21401733061b7f3aca' AND AGE='94') OR (ID='648aac21401733061b7f3acb' AND AGE='19') OR (ID='648aac21401733061b7f3acc' AND AGE='21') OR (ID='648aac21401733061b7f3acd' AND AGE='79') OR (ID='648aac21401733061b7f3ace' AND AGE='57') OR (ID='648aac21401733061b7f3acf' AND AGE='75') OR (ID='648aac21401733061b7f3ad0' AND AGE='78') OR (ID='648aac21401733061b7f3ad1' AND AGE='91') OR (ID='648aac21401733061b7f3ad2' AND AGE='21') OR (ID='648aac21401733061b7f3ad3' AND AGE='40') OR (ID='648aac21401733061b7f3ad4' AND AGE='18') OR (ID='648aac21401733061b7f3ad5' AND AGE='35') OR (ID='648aac21401733061b7f3ad6' AND AGE='26') OR (ID='648aac21401733061b7f3ad7' AND AGE='17') OR (ID='648aac21401733061b7f3ad8' AND AGE='66') OR (ID='648aac21401733061b7f3ad9' AND AGE='15') OR (ID='648aac21401733061b7f3ada' AND AGE='89') OR (ID='648aac21401733061b7f3adb' AND AGE='29') OR (ID='648aac21401733061b7f3adc' AND AGE='37') OR (ID='648aac21401733061b7f3add' AND AGE='42') OR (ID='648aac21401733061b7f3ade' AND AGE='53') OR (ID='648aac21401733061b7f3adf' AND AGE='16') OR (ID='648aac21401733061b7f3ae0' AND AGE='58') OR (ID='648aac21401733061b7f3ae1' AND AGE='73') OR (ID='648aac21401733061b7f3ae2' AND AGE='84') OR (ID='648aac21401733061b7f3ae3' AND AGE='88') OR (ID='648aac21401733061b7f3ae4' AND AGE='96') OR (ID='648aac21401733061b7f3ae5' AND AGE='43') OR (ID='648aac21401733061b7f3ae6' AND AGE='75') OR (ID='648aac21401733061b7f3ae7' AND AGE='4') OR (ID='648aac21401733061b7f3ae8' AND AGE='95') OR (ID='648aac21401733061b7f3ae9' AND AGE='0') OR (ID='648aac21401733061b7f3aea' AND AGE='86') OR (ID='648aac21401733061b7f3aeb' AND AGE='62') OR (ID='648aac21401733061b7f3aec' AND AGE='76') OR (ID='648aac21401733061b7f3aed' AND AGE='16') OR (ID='648aac21401733061b7f3aee' AND AGE='18') OR (ID='648aac21401733061b7f3aef' AND AGE='19') OR (ID='648aac21401733061b7f3af0' AND AGE='73') OR (ID='648aac21401733061b7f3af1' AND AGE='90') OR (ID='648aac21401733061b7f3af2' AND AGE='37') OR (ID='648aac21401733061b7f3af3' AND AGE='90') OR (ID='648aac21401733061b7f3af4' AND AGE='91') OR (ID='648aac21401733061b7f3af5' AND AGE='2') OR (ID='648aac21401733061b7f3af6' AND AGE='40') OR (ID='648aac21401733061b7f3af7' AND AGE='18') OR (ID='648aac21401733061b7f3af8' AND AGE='97') OR (ID='648aac21401733061b7f3af9' AND AGE='44') OR (ID='648aac21401733061b7f3afa' AND AGE='81') OR (ID='648aac21401733061b7f3afb' AND AGE='4') OR (ID='648aac21401733061b7f3afc' AND AGE='54') OR (ID='648aac21401733061b7f3afd' AND AGE='33') OR (ID='648aac21401733061b7f3afe' AND AGE='68') OR (ID='648aac21401733061b7f3aff' AND AGE='21') OR (ID='648aac21401733061b7f3b00' AND AGE='16') OR (ID='648aac21401733061b7f3b01' AND AGE='45') OR (ID='648aac21401733061b7f3b02' AND AGE='1') OR (ID='648aac21401733061b7f3b03' AND AGE='8') OR (ID='648aac21401733061b7f3b04' AND AGE='23') OR (ID='648aac21401733061b7f3b05' AND AGE='15') OR (ID='648aac21401733061b7f3b06' AND AGE='11') OR (ID='648aac21401733061b7f3b07' AND AGE='52') OR (ID='648aac21401733061b7f3b08' AND AGE='6') OR (ID='648aac21401733061b7f3b09' AND AGE='20') OR (ID='648aac21401733061b7f3b0a' AND AGE='11') OR (ID='648aac21401733061b7f3b0b' AND AGE='93')
捕获到日志消息: SQLServerResultSet:3 created by (SQLServerStatement:3)
statement test Sql 查询100条记录执行耗时:5ms
捕获到日志消息: Property:serverName Value:XXX.XXX.XXX.XXX
捕获到日志消息: Property:portNumber Value:1433
捕获到日志消息: Property:databaseName Value:TEST
捕获到日志消息: Property:sendStringParametersAsUnicode Value:false
捕获到日志消息: ConnectionID:3 created by (SQLServerDriver:1)
捕获到日志消息: ConnectionID:3 This attempt server name: XXX.XXX.XXX.XXX port: 1433 InstanceName: null useParallel: false
捕获到日志消息: ConnectionID:3 This attempt endtime: 1686823329258
捕获到日志消息: ConnectionID:3 This attempt No: 0
捕获到日志消息: ConnectionID:3 Connecting with server: XXX.XXX.XXX.XXX port: 1433 Timeout slice: 1875 Timeout Full: 15
捕获到日志消息: ConnectionID:3 ClientConnectionId: 3170aa8f-cd81-4fe6-89fd-43867f3270a0 Server returned major version:10
preparedStatement test Sql:SELECT * FROM [dbo].[Invalid_Test_table] WHERE (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?) OR (ID=? AND AGE=?)
捕获到日志消息: SQLServerPreparedStatement:4 created by (ConnectionID:3 ClientConnectionId: 3170aa8f-cd81-4fe6-89fd-43867f3270a0)
开始执行preparedStatement test Sql查询
捕获到日志消息: SQLServerPreparedStatement:4: calling sp_executesql: SQL:SELECT * FROM [dbo].[Invalid_Test_table] WHERE (ID=@P0 AND AGE=@P1) OR (ID=@P2 AND AGE=@P3) OR (ID=@P4 AND AGE=@P5) OR (ID=@P6 AND AGE=@P7) OR (ID=@P8 AND AGE=@P9) OR (ID=@P10 AND AGE=@P11) OR (ID=@P12 AND AGE=@P13) OR (ID=@P14 AND AGE=@P15) OR (ID=@P16 AND AGE=@P17) OR (ID=@P18 AND AGE=@P19) OR (ID=@P20 AND AGE=@P21) OR (ID=@P22 AND AGE=@P23) OR (ID=@P24 AND AGE=@P25) OR (ID=@P26 AND AGE=@P27) OR (ID=@P28 AND AGE=@P29) OR (ID=@P30 AND AGE=@P31) OR (ID=@P32 AND AGE=@P33) OR (ID=@P34 AND AGE=@P35) OR (ID=@P36 AND AGE=@P37) OR (ID=@P38 AND AGE=@P39) OR (ID=@P40 AND AGE=@P41) OR (ID=@P42 AND AGE=@P43) OR (ID=@P44 AND AGE=@P45) OR (ID=@P46 AND AGE=@P47) OR (ID=@P48 AND AGE=@P49) OR (ID=@P50 AND AGE=@P51) OR (ID=@P52 AND AGE=@P53) OR (ID=@P54 AND AGE=@P55) OR (ID=@P56 AND AGE=@P57) OR (ID=@P58 AND AGE=@P59) OR (ID=@P60 AND AGE=@P61) OR (ID=@P62 AND AGE=@P63) OR (ID=@P64 AND AGE=@P65) OR (ID=@P66 AND AGE=@P67) OR (ID=@P68 AND AGE=@P69) OR (ID=@P70 AND AGE=@P71) OR (ID=@P72 AND AGE=@P73) OR (ID=@P74 AND AGE=@P75) OR (ID=@P76 AND AGE=@P77) OR (ID=@P78 AND AGE=@P79) OR (ID=@P80 AND AGE=@P81) OR (ID=@P82 AND AGE=@P83) OR (ID=@P84 AND AGE=@P85) OR (ID=@P86 AND AGE=@P87) OR (ID=@P88 AND AGE=@P89) OR (ID=@P90 AND AGE=@P91) OR (ID=@P92 AND AGE=@P93) OR (ID=@P94 AND AGE=@P95) OR (ID=@P96 AND AGE=@P97) OR (ID=@P98 AND AGE=@P99) OR (ID=@P100 AND AGE=@P101) OR (ID=@P102 AND AGE=@P103) OR (ID=@P104 AND AGE=@P105) OR (ID=@P106 AND AGE=@P107) OR (ID=@P108 AND AGE=@P109) OR (ID=@P110 AND AGE=@P111) OR (ID=@P112 AND AGE=@P113) OR (ID=@P114 AND AGE=@P115) OR (ID=@P116 AND AGE=@P117) OR (ID=@P118 AND AGE=@P119) OR (ID=@P120 AND AGE=@P121) OR (ID=@P122 AND AGE=@P123) OR (ID=@P124 AND AGE=@P125) OR (ID=@P126 AND AGE=@P127) OR (ID=@P128 AND AGE=@P129) OR (ID=@P130 AND AGE=@P131) OR (ID=@P132 AND AGE=@P133) OR (ID=@P134 AND AGE=@P135) OR (ID=@P136 AND AGE=@P137) OR (ID=@P138 AND AGE=@P139) OR (ID=@P140 AND AGE=@P141) OR (ID=@P142 AND AGE=@P143) OR (ID=@P144 AND AGE=@P145) OR (ID=@P146 AND AGE=@P147) OR (ID=@P148 AND AGE=@P149) OR (ID=@P150 AND AGE=@P151) OR (ID=@P152 AND AGE=@P153) OR (ID=@P154 AND AGE=@P155) OR (ID=@P156 AND AGE=@P157) OR (ID=@P158 AND AGE=@P159) OR (ID=@P160 AND AGE=@P161) OR (ID=@P162 AND AGE=@P163) OR (ID=@P164 AND AGE=@P165) OR (ID=@P166 AND AGE=@P167) OR (ID=@P168 AND AGE=@P169) OR (ID=@P170 AND AGE=@P171) OR (ID=@P172 AND AGE=@P173) OR (ID=@P174 AND AGE=@P175) OR (ID=@P176 AND AGE=@P177) OR (ID=@P178 AND AGE=@P179) OR (ID=@P180 AND AGE=@P181) OR (ID=@P182 AND AGE=@P183) OR (ID=@P184 AND AGE=@P185) OR (ID=@P186 AND AGE=@P187) OR (ID=@P188 AND AGE=@P189) OR (ID=@P190 AND AGE=@P191) OR (ID=@P192 AND AGE=@P193) OR (ID=@P194 AND AGE=@P195) OR (ID=@P196 AND AGE=@P197) OR (ID=@P198 AND AGE=@P199)

Jeffery-Wasty · 2023-06-23T16:08:36Z

Thank you for the extra information and the repro code.

I still can't say for sure what the problem is, but my best guess is this. Using CHAR and the collation specified,Chinese_PRC_CS_AS, sets the list of possible stored values to a particular range. With computations involving only the id as the primary key (which maintains the same collation) there is no conflict here. With the introduction of a secondary primary key in age, which is not subject to the same collation, those same computations now involve values not specified in the Chinese_PRC_CS_AS range. This means a forced conversion to Unicode to properly execute the query, regardless of the connection property sendStringParametersAsUnicode, and thus the difference in speed you are seeing.

To have consistency between these tables, you should be using NCHAR and NVARCHAR as opposed to CHAR and VARCHAR when it comes to non-Ascii character sets. If you're able to try these tests again with NCHAR and NVARCHAR, please let us know the results. I'm still not able to replicate this on my end, but I expect more consistent results after the change.

SimonChou12138 · 2023-06-25T14:11:38Z

Hi @Jeffery-Wasty

More verification

I think your theory is quite reasonable, so I did more verification, but it seems that the results are still not satisfactory.

Encoding problem

As for the problem of Chinese_PRC_CS_AS set coding you mentioned, I have created a new database of default character set, and created a new table of Invalid_Test_table on the library, and created test data of the same volume test to control variables. Admittedly, tweaking the sorting rules is a good idea, it makes the execution a lot better, but it's still slow

DatabaseInfo

table structure

CREATE TABLE [dbo].[Invalid_Test_table] (
  [ID] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS  NOT NULL,
  [NAME] varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS  NULL,
  [AGE] numeric(10)  NOT NULL,
  CONSTRAINT [PK__Invalid___AE7DED4707020F21] PRIMARY KEY CLUSTERED ([ID], [AGE])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
)  
ON [PRIMARY]
GO

ALTER TABLE [dbo].[Invalid_Test_table] SET (LOCK_ESCALATION = TABLE)

implementation effect

Data type problem

About what you mentioned should use NCHAR and NVARCHAR instead of CHAR and VARCHAR. Actually, I don't have permission to change the data type. But I still tested the difference in speed and whether it could make a difference. Unfortunately, there has been some improvement, but not much.

table structure

CREATE TABLE [dbo].[Invalid_Test_table] (
  [ID] nchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS  NOT NULL,
  [NAME] varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS  NULL,
  [AGE] numeric(10)  NOT NULL,
  CONSTRAINT [PK__Invalid___AE7DED4709DE7BCC] PRIMARY KEY CLUSTERED ([ID], [AGE])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
)  
ON [PRIMARY]
GO

ALTER TABLE [dbo].[Invalid_Test_table] SET (LOCK_ESCALATION = TABLE)

implementation effect

More guesses

What is the execution speed if both joint primary keys are of type char or varchar

table structure

CREATE TABLE [dbo].[Invalid_Test_table] (
  [ID] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS  NOT NULL,
  [NAME] varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS  NOT NULL,
  [AGE] numeric(10)  NOT NULL,
  CONSTRAINT [PK__Invalid___3F88F3870DAF0CB0] PRIMARY KEY CLUSTERED ([ID], [NAME])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
)  
ON [PRIMARY]
GO

ALTER TABLE [dbo].[Invalid_Test_table] SET (LOCK_ESCALATION = TABLE)

implementation effect

In the end

It seems that after more verification, collation does affect the query speed of prepareStatement, but there should be other reasons for this.

Jeffery-Wasty · 2023-06-29T20:10:19Z

Hi @SimonChou12138,

I've tried once again to replicate your results, and still can't. My suggestion at this time is to remove anything from your setup besides the driver and SQL Server and attempt that way. I'll continue to look into this on my end.

Jeffery-Wasty · 2023-07-13T15:56:50Z

Hi @SimonChou12138,

Please try testing once more with a more recent version of SQL Server (2019 would be best) along with a recent version of the driver (12.3.1 would be best). My inability to replicate this might be because of the SQL Server version, and I am unable to use 2008.

Jeffery-Wasty · 2023-07-20T15:21:16Z

Hi @SimonChou12138,

Without further update, we'll be closing this issue. Please let us know if you're able to test with a later version of SQL Server.

SimonChou12138 · 2023-07-25T10:14:50Z

Hi @Jeffery-Wasty

I'm sorry I've been so busy lately, but further update is coming

Environment construction

In order to ensure exceptions caused by database version or other configuration reasons, I will use docker to quickly build the environment here

Pull image

sudo docker pull mcr.microsoft.com/mssql/server:2019-latest

Starter container

docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=Aa.123456" -p 1433:1433 --name sqlserver2019 -d mcr.microsoft.com/mssql/server:2019-latest

Other

Library and table building and test data building are the same as in the previous issue, with the default character set SQL_Latin1_General_CP1_CI_AS

Verify

Driver version

<dependency>
 <groupId>com.microsoft.sqlserver</groupId>
 <artifactId>mssql-jdbc</artifactId>
 <version>12.3.0.jre8-preview</version>
</dependency>

Test case

Use cases use the test cases mentioned earlier in issue, and the variables currently controlled are consistent with the rest of the test environment

Summary

By controlling the variables brought by the database environment this time, after the character set encoding is also changed to the default SQL_Latin1_General_CP1_CI_AS, the test results are found to be consistent with the previous verification.
It is still found that in a joint primary key, when a primary key is of the char and varchar data types, a preparedStatement is executed and a statement is executed, and there is a huge difference in query speed.
In fact, I am curious why this problem cannot be repeated in your environment, and I hope we can control our variables through docker image this time

Jeffery-Wasty · 2023-07-27T17:10:51Z

Hi @SimonChou12138,

Thank you for the reply.

I'm retesting everything once more as it doesn't make sense to me why I can't reproduce the issue you're experiencing. One more bit of information that might help: what platform are you running on? Windows? Linux?

SimonChou12138 · 2023-07-29T16:39:16Z

Hi @Jeffery-Wasty

In fact, in the context of the initial problem, the database was running on windows server and the program connecting to the SQL was running on Linux.
The thing about docker last time is that I wanted to keep our environment the same with a common image instance, so you can try to start the image instance with the container and run my duplicate code earlier to test it.
Because I repeated the same problem in the container image environment

Jeffery-Wasty · 2023-08-02T16:41:27Z

Hi @SimonChou12138,

I can now reproduce the issue, thank you. Will update you on any potential causes I find.

Jeffery-Wasty · 2023-09-26T20:20:58Z

I forgot to update this issue. I was mistaken, I'm still not able to accurately reproduce your issue. I was getting similar time differences, but it was for both sendStringParamsAsUnicode false and true. I'm not sure what else I can try at the moment so this issue is being put in the backlog for the time being, until maybe we can get another set of eyes on it.

CheneyYin · 2023-12-29T02:29:38Z

Hi @Jeffery-Wasty

I'm sorry I've been so busy lately, but further update is coming

Environment construction

In order to ensure exceptions caused by database version or other configuration reasons, I will use docker to quickly build the environment here

Pull image
sudo docker pull mcr.microsoft.com/mssql/server:2019-latest
Starter container
docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=Aa.123456" -p 1433:1433 --name sqlserver2019 -d mcr.microsoft.com/mssql/server:2019-latest
Other

Library and table building and test data building are the same as in the previous issue, with the default character set SQL_Latin1_General_CP1_CI_AS

Verify

Driver version
<dependency>
 <groupId>com.microsoft.sqlserver</groupId>
 <artifactId>mssql-jdbc</artifactId>
 <version>12.3.0.jre8-preview</version>
</dependency>
Test case

Use cases use the test cases mentioned earlier in issue, and the variables currently controlled are consistent with the rest of the test environment

Summary

By controlling the variables brought by the database environment this time, after the character set encoding is also changed to the default SQL_Latin1_General_CP1_CI_AS, the test results are found to be consistent with the previous verification.
It is still found that in a joint primary key, when a primary key is of the char and varchar data types, a preparedStatement is executed and a statement is executed, and there is a huge difference in query speed.
In fact, I am curious why this problem cannot be repeated in your environment, and I hope we can control our variables through docker image this time

@SimonChou12138 @Jeffery-Wasty
Is it possible that this phenomenon is caused by the database memory cache?
You might as well execute the statement first and then the pre-statement. If the statement takes much longer than the pre-statement, then the phenomenon is probably caused by the cache.

Jeffery-Wasty · 2024-01-22T20:52:06Z

Hi @CheneyYin,

Thanks for your reply. This issue is backlogged for the moment. We'll look into your suggestion in greater detail when this gets picked up again.

lilgreenbird added this to Under Investigation in MSSQL JDBC via automation Jun 27, 2023

Jeffery-Wasty moved this from Under Investigation to Waiting for Customer in MSSQL JDBC Jul 11, 2023

Jeffery-Wasty moved this from Waiting for Customer to Backlog in MSSQL JDBC Sep 26, 2023

Jeffery-Wasty added the Performance-related The desired fix involves increasing the performance of a process. label Feb 1, 2024

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

SendStringParametersAsUnicode parameter not working #2144

SendStringParametersAsUnicode parameter not working #2144

SimonChou12138 commented Jun 8, 2023

Jeffery-Wasty commented Jun 8, 2023

SimonChou12138 commented Jun 9, 2023 •

edited

Jeffery-Wasty commented Jun 9, 2023 •

edited

SimonChou12138 commented Jun 10, 2023

Jeffery-Wasty commented Jun 14, 2023

SimonChou12138 commented Jun 15, 2023 •

edited

Jeffery-Wasty commented Jun 23, 2023 •

edited

SimonChou12138 commented Jun 25, 2023

Jeffery-Wasty commented Jun 29, 2023

Jeffery-Wasty commented Jul 13, 2023

Jeffery-Wasty commented Jul 20, 2023

SimonChou12138 commented Jul 25, 2023

Jeffery-Wasty commented Jul 27, 2023 •

edited

SimonChou12138 commented Jul 29, 2023

Jeffery-Wasty commented Aug 2, 2023

Jeffery-Wasty commented Sep 26, 2023

CheneyYin commented Dec 29, 2023

Environment construction

Pull image

Starter container

Other

Verify

Driver version

Test case

Summary

Jeffery-Wasty commented Jan 22, 2024

SendStringParametersAsUnicode parameter not working #2144

SendStringParametersAsUnicode parameter not working #2144

Comments

SimonChou12138 commented Jun 8, 2023

Jeffery-Wasty commented Jun 8, 2023

SimonChou12138 commented Jun 9, 2023 • edited

Jeffery-Wasty commented Jun 9, 2023 • edited

SimonChou12138 commented Jun 10, 2023

Jeffery-Wasty commented Jun 14, 2023

SimonChou12138 commented Jun 15, 2023 • edited

Test table generation

Test Data Generation

Verification test Demo

Demo Code

JdbcUrl not added SendStringParametersAsUnicode=false execution before parameters

Effective_Test_table

Invalid_Test_table

Summary

Add JdbcUrl sendStringParametersAsUnicode=false the execution after the parameter

Effective_Test_table

Invalid_Test_table

Summary

Q&A

About execution plan

About logging

Jeffery-Wasty commented Jun 23, 2023 • edited

SimonChou12138 commented Jun 25, 2023

More verification

Encoding problem

Data type problem

More guesses

In the end

Jeffery-Wasty commented Jun 29, 2023

Jeffery-Wasty commented Jul 13, 2023

Jeffery-Wasty commented Jul 20, 2023

SimonChou12138 commented Jul 25, 2023

Environment construction

Pull image

Starter container

Other

Verify

Driver version

Test case

Summary

Jeffery-Wasty commented Jul 27, 2023 • edited

SimonChou12138 commented Jul 29, 2023

Jeffery-Wasty commented Aug 2, 2023

Jeffery-Wasty commented Sep 26, 2023

CheneyYin commented Dec 29, 2023

Environment construction

Pull image

Starter container

Other

Verify

Driver version

Test case

Summary

Jeffery-Wasty commented Jan 22, 2024

SimonChou12138 commented Jun 9, 2023 •

edited

Jeffery-Wasty commented Jun 9, 2023 •

edited

SimonChou12138 commented Jun 15, 2023 •

edited

JdbcUrl not added `SendStringParametersAsUnicode=false` execution before parameters

Jeffery-Wasty commented Jun 23, 2023 •

edited

Jeffery-Wasty commented Jul 27, 2023 •

edited