What you must know about performance optimization?

What you must know about performance optimization?

Recently, some customers reported that the system imported EXECL for data processing time-out. I was the first to respond. It was impossible. I obviously did performance optimization. How can I still time out, how many pieces of data is required! So I asked the customer for EXECL and found that there were more than 7500 pieces of data. After backing up the customer database and debugging the code, I found the performance was poor. It is something that is common in general, but it is easy to ignore. Here we only talk about two points, using String or StringBuilder, check whether the data is correct by using SQL to retrieve data one by one in the loop, or one-time retrieval in the Code for verification! Next we will use the actual data in combination with the chart to give an accurate answer.

Reading directory

• Comparison of performance differences between String and StringBuilder
• Is the number of cyclically retrieved or one-time?
• Sample download and summary

Returns to the top, and compares the performance differences between String and StringBuilder.

The difference between String and StringBuilder is not mentioned here. in learning and work, we often hear that concatenating strings requires the use of StringBuilder objects to be fast, but you may only know this knowledge, have you paid attention to this point in actual development? I also learned this knowledge when the customer follows the feedback and uses the actual results. This will also be remembered in future development! The actual data below may explain some problems.

After calling this function, the number of cycles is 15,200,500,150, 20000, and. You can download the last DEMO experiment. The String is slow enough. To ensure data accuracy, the data of each magnitude is taken ten times and the average value is obtained.

/// <Summary> /// compare the speed of String concatenation with StringBuilder. /// each magnitude test, take the average time of 10 times /// </summary> /// <param name = "Total"> Number of cycles </param> public static void StringSpeedComparer (int Total) {List <string> list = new List <string> (); for (int I = 0; I <Total; I ++) {list. add (Guid. newGuid (). toString ();} int iTest = 10; // total execution time MS double TotalMilliseconds = 0; // String concatenation string strGUID = String. empty; while (iTest> 0) {DateTime dtBegin = DateTime. now; foreach (string temp in list) {strGUID = strGUID + temp + ";";} DateTime dtEnd = DateTime. now; TotalMilliseconds + = (dtEnd-dtBegin ). totalMilliseconds; iTest --;} Console. writeLine ("String concatenation {0} String elapsed time {1} ms", Total, TotalMilliseconds/10); // StringBuilder concatenates StringBuilder sb = new StringBuilder (); iTest = 10; totalMilliseconds = 0; while (iTest> 0) {DateTime dtBegin = DateTime. now; foreach (string temp in list) {sb. appendFormat ("{0};", temp);} DateTime dtEnd = DateTime. now; TotalMilliseconds + = (dtEnd-dtBegin ). totalMilliseconds; iTest --;} Console. writeLine ("StringBuilder splicing {0} strings takes {1} ms", Total, TotalMilliseconds/10 );}

The execution result is as follows:

Graph:

From the perspective of direct viewing, String concatenation is geometric, while StringBuilder is linear, and the increasing trend is slow. Which splicing method is used when the number of loops is large? I believe everyone knows it! When the number is 7500, it can save a whole 4 s of time. Is the performance much improved?

Return to the top to get the number of loops or one-time get the number?

Background: EXECL contains 7500 rows of student information data. to import the data to the student table (p_Student), ensure that the student ID (StudentNo) is unique, A prompt is required when the import is not unique. This requires reading the student information in EXECL in the background code and then verifying whether the student code exists in the database. Of course, the student ID entered in EXECL must also be unique. Next we will simulate this process and compare the performance in two ways. ,

First, create a student information table and insert 7500 pieces of data. The following is an SQL script. The student ID is inserted here with newid. This is not the case. It is unique here, but it is unordered. Try to simulate the real situation as much as possible.

/* --------------------------- Data dictionary generation tool (V2.1) -------------------------------- */goif not exists (SELECT 1 FROM sysobjects WHERE id = OBJECT_ID ('[p_Student]') BEGIN/* ================================================= =========================================* // * Table: p_Student * // * ========================================== ==================== */create table [dbo]. [p_Student] ([StudentGUID] uniqueidentifier, [Name] varchar (40), [Major] varchar (100), [Sex] varchar (8 ), [StudentNo] varchar (100), primary key (StudentGUID) declare @ CurrentUser sysnameselect @ CurrentUser = user_name () execute sp_addextendedproperty 'Ms _ description', 'Student Information table ', 'user', @ CurrentUser, 'table', 'P _ Student 'execute sp_addextendedproperty 'Ms _ description', 'Student Information guid', 'user', @ CurrentUser, 'table', 'P _ Student ', 'column', 'studentgu' execute sp_addextendedproperty 'Ms _ description', 'name', 'user', @ CurrentUser, 'table ', 'P _ Student ', 'column', 'name' execute sp_addextendedproperty 'Ms _ description', 'professional', 'user', @ CurrentUser, 'table ', 'P _ Student ', 'column', 'major' execute sp_addextendedproperty 'Ms _ description', 'Gender ', 'user', @ CurrentUser, 'table ', 'P _ Student ', 'column', 'sex' execute sp_addextendedproperty 'Ms _ description', 'student number', 'user', @ CurrentUser, 'table ', 'P _ Student ', 'column', 'studentno' ENDGO -- insert 7500 simulated data records DECLARE @ Count as intselect @ Count = COUNT (1) FROM p_StudentIF @ Count = 0 begin declare @ I as int set @ I = 7500 WHILE @ I> 0 BEGIN INSERT INTO dbo. p_Student (StudentGUID, Name, Major, Sex, StudentNo) VALUES (NEWID (), -- StudentGUID-uniqueidentifier @ I, -- Name-varchar (40) 'Software Project ', -- Major-varchar (100) 'male', -- Sex-varchar (8) NEWID () -- StudentNo-varchar (100) SET @ I = @ I-1 endgo

After the basic information is ready, enter the background code

/// <Summary> /// Statistical Cycle checksum one-time checksum performance difference // </summary> public static void Check (int Total) {// The student ID List <string> listStudetNo = new List <string> (); for (int I = 0; I <Total; I ++) {listStudetNo. add (Guid. newGuid (). toString ();} using (SqlConnection con = new SqlConnection (SqlCon) {con. open (); string strSQL = "select count (1) FROM dbo. p_Student WHERE StudentNo = '{0}' "; SqlCommand cmd = con. createCo Mmand (); // cyclic verification double TotalMilliseconds = 0; for (int I = 0; I <10; I ++) {foreach (string studentNo in listStudetNo) {DateTime dtBegin = DateTime. now; cmd. commandText = String. format (strSQL, studentNo); int count = (int) cmd. executeScalar (); if (count> 0) {Console. the number of WriteLine ("{0} already exists. Please enter it again! ", StudentNo); return;} DateTime dtEnd = DateTime. now; TotalMilliseconds + = (dtEnd-dtBegin ). totalMilliseconds;} Console. writeLine ("cyclic verification {0} student IDs consumed {1} ms", Total, TotalMilliseconds/10); // One-time verification TotalMilliseconds = 0; strSQL = "select top 1 StudentNo FROM dbo. p_Student WHERE StudentNo IN ('{0}') "; for (int I = 0; I <10; I ++) {DateTime dtBegin = DateTime. now; StringBuilder sb = new StringBuild Er (); foreach (string studentNo in listStudetNo) {sb. appendFormat ("{0};", studentNo);} cmd. commandText = String. format (strSQL, sb. toString (). substring (0, sb. toString (). length-1 ). replace (";", "','"); string no = (string) cmd. executeScalar (); if (! String. IsNullOrEmpty (no) {Console. WriteLine ("{0} number already exists. Please input it again! ", No); return;} DateTime dtEnd = DateTime. now; TotalMilliseconds + = (dtEnd-dtBegin ). totalMilliseconds;} Console. writeLine ("one-time verification {0} student IDs consumed {1} ms", Total, TotalMilliseconds/10 );}}

From the perspective of direct viewing, the cyclic checksum and one-time checksum increase linearly, and the one-time checksum speed is about twice faster than the cycle.

Go back to the top to download and summarize the sample

Sample SQL and DEMO code

In fact, the performance optimization is not just that. I need to summarize it in my daily work. I am amazed at this performance optimization. It takes about 20 s to execute an SQL statement before it is optimized, after an index is added to the table, the speed is changed to 0 s, and the final performance problem is solved successfully.

Performance Optimization ideas:

1: Use StringBuilder to splice a large number of strings

2: Do not repeatedly query SQL statements in a large number of cycles, consider whether it can be replaced by a one-time query, or query data in the Code for logical judgment.

3: SQL Execution is slow. You can use an execution plan to check whether the table lacks indexes.

Now, this article is about to end. If you think it is good for you, remember to like it!

　　

Related reading: Methods for deleting database log files by attaching database methods without log files Data dictionary generation tools series of articles

If you believe that reading this blog has some benefits, click 【Recommendation] Button.
If you want to discover my new blog more easily, click 【Follow me].

If you want to give me more encouragement, please

Because my enthusiasm for writing is inseparable from your support.

Thank you for reading this article. If you are interested in the content of my blog, continue to follow up on my blog.