● There are many reasons for the query speed. Common aspects: 1, no indexing or no index (this is the most common problem in query, is the defect of programming) 2, I / O throughput, forming bottleneck effect. 3. No Create Calculation Columns cause the query to not optimize. 4, less memory 5, slow network speed 6, the amount of data queries is too large (can be used multiple queries, other methods to reduce the amount of data) 7, lock or dead lock (this is also the most common problem in query slow, is the program Designed defects) 8, sp_lock, sp_who, activity user view, because reading and writing competitive resources. 9. Return unnecessary rows and column 10, query statements are not good, no optimization ● You can optimize queries by following: 1, put data, logs, indexes on different I / O devices, increase read speed It can be placed on RAID0 before, and SQL2000 is not supported. The larger the amount of data (size), the more important I / O. 2, longitudinal, horizontal division table, reduce the size of the table (sp_spaceuse) 3, upgrade hardware 4, establish index, optimize index, optimize access, based on query conditions, The amount of data on the result set is restricted. Note that the fill factor should be appropriate (preferably the default value 0). The index should be as small as possible, using a small number of bytes, the index is created, do not build a single index of the field of limited values, such as gender field 5, improve the network speed; 6, expand the server's memory, Windows 2000 and SQL Server 2000 support 4-8G memory. Configuring virtual memory: The virtual memory size should be configured based on concurrently running services on your computer. When running Microsoft SQL Server? 2000, you can consider setting the virtual memory size to 1.5 times the physical memory installed in your computer. If the full-text search function is installed, it is intended to run the Microsoft Search Service to perform full-text index and query, consider: Configuring the virtual memory size to at least 3 times the physical memory installed in the computer. Configure the SQL Server Max Server Memory server configuration option to 1.5 times the physical memory (half of the virtual memory size). 7, increase the number of servers CPU; however, it must be understood that the parallel processing serial processing needs resources such as memory. Using parallel or stroke is the MSSQL automatic evaluation selection. A single task breaks down into multiple tasks, you can run on the processor. For example, the sorting, connection, scanning, and group BY sentences are executed simultaneously, and SQL Server determines the optimal parallel level according to the system's load, complex query that consumes a large number of CPUs is most suitable for considering. But update operations Update, INSERT, DELETE cannot be handled in parallel. 8. If you use the Like to query, simple use of index is not available, but the full-text index, the space is expected. When Like 'a%' uses index Like '% a' does not use the claim to query the Like '% a%' query, the query consumption and the total length of the field value are proportional to the total length, so it cannot be used with a CHAR type, but VARCHAR. Built a full text index for the value of the field. 9, DB Server and Application Server separation; OLTP and OLAP separation 10, distributed partition views can be used to implement database server consortium. The consortium is a set of separately managed servers, but they cooperate with each other to share the processing load of the system. This mechanism for forming a database server consortium through partition data can enlarge a set of servers to support the processing needs of large multi-layer Web sites. For more information, see Design the Joint Database Server.
(Refer to SQL Help File 'Partition View') a, before implementing partition view, you must first partition table B. After creating a member table, a distributed partition view is defined on each member server, and each view has the same The name. Thus, the query of the reference partition view name can be run on any member server. The system operation is like a copy of the original table on each member server, but in fact there is only one member table and a distributed partition view on each server. The location of the data is transparent to the application. 11. Reconstruction Index DBCC ReIndex, DBCC INDEXDEFRAG, shrink data, and log DBCC Shrinkdb, DBCC ShrinkFile. Set the automatic shrinkage log. Do not set the database automatic growth for large databases, which reduces the performance of the server. There is a lot of attention on T-SQL writing, and the common points are listed below: First, the process of DBMS processing the query plan is like this: 1, the words of the query statement, the syntax check 2, submit the statement to DBMS query Optimizer 3, optimizer to make algebraic optimization and access path optimization 4, generate query plan 5 by the precompilation module, then submit it to the system processing execution 6, and finally return the execution result to the user, look at SQL Server's data stored structure: A page size is 8K (8060) bytes, 8 pages are a panel, and store it according to B. 12, COMMIT and ROLLBACK Difference Rollback: Roll back all things. Commit: Submit the current thing. No need to write things in dynamic SQL, if you want to write, you should write a function or stored a function or stored procedure to write the dynamic SQL. 13. Inquiring the number of rows returned by WHERE words in the SELECT statement, avoiding table scanning, if unnecessary data is returned, waste the server's I / O resources, and weigh the network's burden reduction performance. If the table is very large, lock the table during the table scan, and other joint access tables are prohibited, and the consequences are serious. 14. The annotation of SQL declares that there is no impact on the implementation 15, and does not use the cursor as much as possible, it takes up a lot of resources. If Row-By-Row is required, try to use non-cursor techniques, such as: cycling on client, use temporary tables, table variables, subquery, with CASE statements, and more. The cursor can be classified according to the extraction options it support: Enter the line must be extracted in the order from the first line to the last line. Fetch next is the only allowed extraction operation and the default mode. Scroll can be randomly extracted anyone anywhere in the cursor. The technology of the cursor becomes powerful under SQL2000, and his purpose is to support cycles. There are four concurrent options read_only: Not allowed by the cursor positioning update (UPDATE), and there is no lock in the line of the composition result set. Optimistic with Values: Optimistic concurrency control is a standard part of the theory of transaction control. Optimistic concurrency control is used in this case, that is, in the interval of opening the cursor and update line, only a small chance allows the second user to update a row. When a cursor is opened by this option, there is no lock control, which will help maximize its processing capabilities. If the user tries to modify a certain line, the current value of this line is compared to the value obtained when the last extracted this line. If any value changes, the server will know that other people have updated this row and will return an error. If the value is the same, the server performs modification. Choose this concurrent option optimistic with row versioning: This optimistic concurrent control option is based on row version. Using row version control, the table must have some version identifier, the server can use it to determine if the row changes after reading the cursor.
In SQL Server, this performance is provided by the TimeStamp data type, which is a binary number that represents the relative order of changes in the database. Each database has a global current timestamp value: @@ dBTS. Each time you change the line with the TimeStAMP column, SQL Server stores the current @@ dbts value in the timestamp column, then adds the value of @@ dbts. If a table has a TIMESTAMP column, the timestamp will be recorded. The server can compare the current timestamp values of a row and the timestamp values stored at the last extracted to determine if the line is updated. The server does not have to compare all the values of all columns, just compare the timestamp columns. If the application requires the optimism that does not have a TimeStAMP column, the vernier is based on the value-based optimistic concurrency control. Scroll Locks This option implements pessimistic concurrency control. In pessimistic concurrency control, the application will try to lock the database line when reading the row of the database. When you use a server cursor, you will place an update lock when you read the cursor. If the cursor is opened in the transaction, the transaction lock will remain until the transaction is submitted or rollback; the cursor lock will be removed when the next line is extracted. If you open the cursor outside the transaction, the lock is discarded when the next row is extracted. Therefore, whenever the user needs full pessimistic concurrent control, the cursor should be opened in the transaction. The update lock will prevent any other tasks from getting the update lock or row lock to prevent other tasks from updating the row. However, the update lock does not prevent shared locks, so it does not block other task read rows unless the second task is also required to be read with an update lock. The scrolling lock is based on the lock prompt specified in the SELECT statement defined in the cursor, which can generate a scroll lock. The scroll lock is obtained on each line when extracted, and keeps the next extraction or the cursor shutdown, the first generator is accurate. The server gets scrolling locks for rows in the new extract next time, and releases the scroll lock for the last extraction. The scroll lock is independent of the transaction lock and can be kept until a submission or rollback operation. If the option is turned off, the COMMIT statement does not close any open cursors, and the scroll lock is reserved to maintain the isolation of the extracted data. The type of scrolling lock depends on the lock prompt in the cursor concurrent option and the cursor SELECT statement. Lock prompt read-only optimistic value optimistic row version control lock No prompt unlock unlock unlock Update NOLOCK unlock unlock unlock unlock HOLDLOCK shared shared update UPDLOCK error Update update TabLockx error Unlock unlock Update Other unlocking Lock Unlock Update * Specifying the NOLOCK prompt will make the table that specifies the prompt is read only in the cursor. 16. Use PROFILER to track queries, get the time required to query, find out the problem of SQL; optimize index 17 with index optimizer, Note the difference between UNION and UNION ALL. Union all 18, pay attention to use Distinct, do not use it without necessary, it will slow down the query as union. Duplicate records are no problem in the query. Do not return unwanted rows, column 20, column 20, use sp_configure 'query Governor Cost Limit' or set query_governor_cost_limit to limit the resource consumed by query consumption. When the resource exceeds the resource consumed by the query, the server automatically cancels the query, kills it before the query.
Set LockTime Sets the lock time 21, use the SELECT TOP 100/10 Percent to limit the number of rows returned by the user or set rowcount to limit the line 22 of the operation, before SQL2000, generally do not use the following words: "is null", "< > ","! = ","!> ","! <"," Not "," not in "," not like ", and" like '% 500' ", because they don't go The index is entirely a table scan. Do not add functions in the list name in the WHERE sentence, such as Convert, Substring, etc. If you must use a function, create a calculation column to create a claim to replace it. You can also be Wordstring: where substring (firstname, 1, 1) = ' M 'Change to WHERE FirstName Like' M% '(Index Scan), must separate the function and column name. And the index cannot be built too much and too large. Not in will scan the table multiple times, use exists, not exists, in, left output, especially the left connection, and the exists is faster, the slowest is the NOT operation. If the value of the column contains space, it used to The index does not work, and now the 2000 optimizer can handle it. The same is IS NULL, "Not", "Not", "Not in" can optimize her, and "<>" or the like cannot be optimized, and the index is not used. 23. Use Query Analyzer to see if the SQL statement query plan and evaluation analysis is Optimized SQL. The general 20% code occupies 80% of resources, our optimization is these slow places. 24. If I use IN or or alternative, I found that the query didn't take the index. Use the display declaration specified index: select * from personmember (index = ket_title) where processid in ('male,' female ') 25, will need to query results Pre-calculated is placed in the table, and then select when query. This is the most important means before SQL7.0. For example, hospital hospitalization calculations. 26, min () and max () can be used to use the appropriate index. 27, the database has a principle that the closer code is, the closer the data, so give the default, in turn, Rules, Triggers, Constraint (constraints such as 外健 CheckuniQue ..., the maximum length of the data type is constrained), This is not only the maintenance work, the program is high, but the speed is high. 28. If you want to insert a large binary value to the image column, use the stored procedure, don't use embedd insert to insert (I don't know if Java is). Since the application first converts the binary value into a string (size is twice the size), the server converts him into a binary value after being characterized. The stored procedure does not have these actions: method: CREATE Procedure P_INSERT AS INSERT INTO TABLE Fimage) VALUES (@Image), calling this stored process in the front desk to pass into binary parameters, so that the speed is significantly improved. 29, Between is faster than in the time, Between finds the range faster. The difference can be seen with the query optimizer.
SELECT * from Chinese, ') Select * from Chinese' and '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' ''.. Since IN will compare multiple times, sometimes it will be slow. 30. It is necessary to create an index for global or local temporary tables, sometimes it is possible to improve speed, but not necessarily, because index also consumes a lot of resources. His creation is the same as an actual table. 31. Do not build things that have no effect, such as generating reports, waste resources. Use it only when you use things. 32. Use the words of the other to break down into multiple queries and connect multiple queries through the Union. Their speed is only related to whether the index is used. If the query needs to be used to use the union index, the efficiency executed with Union ALL is higher. The words of multiple OR do not use the index, and the form is rewritten in the form of Union to try again. A key issue is used to use an index. 33. Try to use a small view, it is low. The view operation is slower than the direct pair of tables, and you can use Stored Procedure to replace her. It is especially necessary to use views nested, nested views increase the difficulty of finding the original information. We look at the essence of the view: It is the optimized SQL that has been stored on the server has produced query planning. When retrieving data for a single table, do not read the views of multiple tables, directly from the table retrieving or only including the view of this table, otherwise add unnecessary overhead, the query is disturbed. In order to speed up the view, MSSQL increase The function of the view index. 34. Do not use Distinct and Order By when there is not necessary, these actions can be changed to the client. They add additional overhead. This is the same as Union and Union ALL. SELECT top 20 ad.companyname, comid, position, ad.referenceid, worklocation, convert (varchar (10), ad.postDate, 120) as postDate1, workyear, degreedescription FROM jobcn_query.dbo.COMPANYAD_query ad where referenceID in ( 'JCNAD00329667' , 'JCNAD132168', 'JCNAD00337748', 'JCNAD00338345', 'JCNAD00333138', 'JCNAD00303570', 'JCNAD00303569', 'JCNAD00303568', 'JCNAD00306698', 'JCNAD00231935', 'JCNAD00231933', 'JCNAD00254567', 'JCNAD00254585', ' JCNAD00254608 ',' JCNAD00254607 ',' JCNAD00258524 ',' JCNAD00332133 ',' JCNAD00268618 ',' JCNAD00279196 ',' JCNAD00268613 ') order by postdate desc 35, after the iN list denomination, the most frequent values appear on the most In front, the least placed at the end, and the number of judgments is reduced. 36. When using Select Into, it locks the system table (sysObjects, sysindexes, etc.) to block other connections.
When you create a temporary table, use the display blessing statement, not Select Into. Drop Table T_LXH Begin TRAN SELECT * INTO T_LXH from ChineseResume WHERE Name = 'XYZ' - Commit in another connection Select * from sysobjects can see SELECT INTO lock Housing system tables, CREATE TABLE also locks system tables (whether it is a temporary table or system table). So don't use it within things! ! ! If this is a common table, or a temporary table variable is used. 37. Generally, you can eliminate excess rows before group by a Having sentence, so try not to use them to do the job. Their execution sequence should be the best: SELECT's WHERE 字 Select all suitable lines, group by uses a statistical row, the Having sentence is used to eliminate excess packets. Thus, the Group By has a small Having, the query is fast. For large data lines, packets and Having is very resource. If the purpose of Group By does not include calculations, just group, then Distinct faster 38, update multiple record scores multiple updates Multiple updates Every time, it is said that the batch is good 39, with less use of temporary table, try to use the results set Table type variables instead of it, Table type variables are better than temporary 40, under SQL2000, the calculation field is indexable, and the conditions that need to be satisfied are as follows: a, the expression of the calculated field is determined, can not be used TEXT, NTEXT, Image Data Type C, must be formulated as the following option ANSI_NULLS = ON, ANSI_PADDINGS = ON, .... 41, try to put data on the server on the server, reduce the overhead of the network, such as using a stored procedure. The stored procedure is compiled, optimized, and is organized into an execution plan, and the SQL statement stored in the database is a collection of streaming languages, which is of course fast. Dynamic SQL repeatedly executed, the temporary stored procedure can be used, which is placed in the TEMPDB. Previously because SQL Server did not support complex mathematical calculations, this work had to be placed on other layers to increase the overhead of the network. SQL2000 supports UDFS, now supports complex mathematical calculations, and the return value of the function is not too big, so that the overhead is very large. User-defined Function Implied Like Cursor The consumption consumption, if the result is stored in the stored procedure 42, do not use the same function in one sentence, waste resources, put the result in the variable, then call faster 43 The efficiency of select count (*) is taught, and try to change his writing, and EXISTS is fast. At the same time, please pay attention to the difference: select count (field of null) from table and select count (field of not null) from Table's return value is different! ! ! 44. When the server's memory is more, the number of formulated threads = maximum connection 5, this can play the greatest efficiency; otherwise, the maximum number of connected numbers is enabled by the maximum number of thread pools to solve, if still quantity = max connection Number 5, serious damage to the performance of the server. 45. Access your table in accordance with a certain order. If you lock in the table a, lock Store on the table B, then lock them in this order during all stored procedures. If you (inadvertent), you can lock the table B in a stored process, lock the table a, which may cause a deadlock.
If the lock order is not well designed, the dead lock is difficult to find 46, monitor the corresponding hardware load Memory: Page Faults / sec counter if this value is occasionally high, indicating that there is a thread competition memory. If the continuation is high, the memory may be a bottleneck. Process: 1,% DPC Time refers to the percentage of the processor to receive and provide services during the modal interval. (DPC is running the interval with the standard interval priority). Since DPC is performed in privileged mode, the percentage of DPC time is part of the privilege time. These times are calculated separately and is not part of the total number of intervals. This total shows the average time of the percentage of instance time. 2,% Processor Time Counter If the parameter value continues to exceed 95%, the bottleneck is the CPU. You can consider adding a processor or for a faster processor. 3,% Privileged Time refers to a percentage of non-idle processor time for privileged modes. (Privilege mode is a processing mode for operating system components and manipulating hardware drivers. It allows you to access hardware and all memory. Another mode is a user mode, which is an application, ambient division system and A limited processing mode for integral system design. The operating system converts the application thread into a privileged mode to access the operating system service). The% of the privilege time includes time for interruption and DPC. The high privilege time ratio may be caused by a large number of space generated by the failure device. This counter will be displayed as part of the sample time when it is busy. 4,% User Time indicates a database operation that consumes the CPU, such as sorting, executing aggregate functions et al. If this value is very high, consider adding an index, try to use a simple table connection, horizontal splitting large form, etc. to reduce this value. Physical Disk: Curretn Disk Queue Length Counter This value should not exceed 1.5 to 2 times the number of disks. To improve performance, you can increase the disk. SQLServer: The higher the value of the Cache Hit Ratio counter. If you continue less than 80%, you should consider adding memory. Note that the parameter value is started from SQL Server, and the number of times is accumulated, so the value will not reflect the current value of the system after a period of operation. 47. Analysis SELECT EMP_NAME FORM EMPLOYEE WHERE SALE> 3000 If Salry is a float type, the optimizer is optimized to Convert (Float, 3000), because 3000 is an integer, we should use 3000.0 when programming Don't wait until running, let DBMS conveys. The same character and conversion of integer data.
48, associated with the query sequence with the write select a.personMemberID, * from chineseresume a, personmember b where personMemberID = b.referenceid and a.personMemberID = 'JCNPRH39681' (A = B, B = 'number') select a.personMemberID , * from chineseresume a, personmember b where a.personMemberID = b.referenceid and a.personMemberID = 'JCNPRH39681' and b.referenceid = 'JCNPRH39681' (A = B, B = 'number', A = 'number') select A.PersonMemberID, * from ChineseeSrest A, Personmember B Where b.referenceid = 'jcnprh39681' and a.personmemberid = 'jcnprh39681' (b = 'number', a = 'number') 49, (1) IF does not enter the person in charge Code Then code1 = 0 code2 = 9999 else code1 = code2 = responsible person code END IF execution SQL statement is: SELECT person in charge of FROM P2000 WHERE header code> =: code1 and person in charge code <=: code2 (2) IF No input Personal code THEN SELECT person in charge from P2000 else code = person in charge of the code SELECT person in charge of the code from P2000 WHERE header code =: code end if the first method only one SQL statement, the second method used two SQL Scriptures. When the person in charge is not entered, the second method obviously performs high efficiency than the first method, because it does not limit the conditions; when the person in charge is input, the second method is still higher than the first method, not only It is a minus condition that is also the fastest query operation due to equal operation. We write procedures don't be afraid of trouble 50. About JobCN now query the new method of page (as follows), use performance optimizer to analyze performance bottlenecks, if the I / O or network speed, the following methods are optimized and effective, if Or in memory, better in the current method. Please distinguish the following method, indicating that the smaller the index is, the better.
begin DECLARE @local_variable table (FID int identity (1,1), ReferenceID varchar (20)) insert into @local_variable (ReferenceID) select top 100000 ReferenceID from chineseresume order by ReferenceID select * from @local_variable where Fid> 40 and fid <= 60 end and begin DECLARE @local_variable table (FID int identity (1,1), referenceID varchar (20)) insert into @local_variable (referenceID) select top 100000 referenceID from chineseresume order by updatedate select * from @local_variable where Fid> 40 and fid <= 60 end different begin create table #temp (FID int identity (1,1), referenceID varchar (20)) insert into #temp (referenceID) select top 100000 referenceID from chineseresume order by updatedate select * from #temp where FID> 40 and FID <= 60 Drop Table #Temp End Author Blog:
http://blog.9cbs.net/xmllover/
