Problems caused by SQL SERVER Sendstringparametersasunicode

Friday encountered the development of the request to assist in the data booking program in the single and other tables to retrieve data when retrieving the condition Mantissa is 9 of the data particularly slow. The first time to think of whether there are several issues:

1, the database the most relevant data;

2, the data table has been partitioned, and the corresponding data fall in the partition performance problems;

3. There are many conversations to retrieve the batch records;

But it soon ruled out several of these possibilities, as follow-up investigations were found as follows:

1, query the corresponding data, the mantissa 0-9 of the data almost evenly distributed;

2, directly using SQL Server Query Analyzer query speed quickly;

All of us basically think that there is no problem with the database, but the following survey feels that people are lost. The corresponding user is established on other servers on the same network segment. Table, the data to test, found that the program to take data quickly, and production database is the difference between the amount of data, as well as the table structure (in order to test lazy all fields defined as varchar, then the exception to the implementation of the program is forced to change some of the fields to date). Although the testing process is not rigorous, it seems to be able to tilt the balance of the problem to the database side.

Just like this. The day of the weekend test groping quickly passed, the home of wireless network 6 dollars an hour is really not small, weeks one by one long ago came to the company, return to the embrace of limited broadband, but depressed production base we only query a few fields of authority, I can not move. So contact the upstream data units, calculated by looking at the implementation of the log and other means to further narrow the problem, but to the other side to work with the contact list. Think about it or take half a day to clear our side of the possibility of all the problems before we open this contact list.

How to further narrow the gap, the next look at whether the network has a problem, coincidentally, the database server IP end is 9, and exactly is the end of the 9 data, in order to eliminate the problem, conducted a series of explorations:

1, the replacement test database server, found that the data speed is normal;

2, in the test program client segment to increase the host mapping, but the data speed is still;

3, in the same network Segment server deployment test program.

All of the above three tests indicate that the probability of network anomaly is getting smaller, at least the network interconnection between IDC and Sub Center intranet is normal.

Calm down. Why is the database query slow, only three levels of reasons, the database itself performance, network problems, client issues, and today's most problematic is the client program exception, since the production database can not do effective debugging, then we have to remove our problems as far as possible.

So please development department Hou Wenjie The relevant code of the timestamp further refinement, text Jerry in a few minutes to provide the code, immediately upload test found in PS = conn.preparestatement and ps.setstring efficiency are no problem, and rs = Ps.executequery () is the culprit of sin.

Again to examine the data, a sudden flash of thought, since the SQL Server client query is normal, will not be the program assembly of SQL problems. So immediately asked Wenjie to give me a test arbitrary SQL statements interface, through this interface, I can arbitrary test cout (*), like and other statements of performance (tragedy is a long time did not touch the code, my development environment has not been able to run). I got this interface one hours later, and then I found the exciting result:

A complete SQL statement I assembled myself was executed quickly after the program was loaded, while the other, the same SQL statement assembled through JDBC SetString, was surprisingly slow.