Collect and summarize database optimization issues

PersonAreIn useSQLHourOften fall intoIncorrectArea, that is, tooOffNote on the obtainedEndIf they are correct, they are ignored.ImplementationMethodRoomPossible performance differences,ThisPerformance differences between large orComplexDataLibrary RingEnvironment (for exampleConnectionMachine eventsOfficeManagementOLTPOr Decision Support SystemUnifiedDSS) Medium tableNowTeyouIsMingDisplay.

I am workingRealIn practiceFound, BadSQLIt often comes from inappropriate indexes.Design, Not fullConnectionConnection conditions and unavailabilityExcellentOfWhereClause. InPairItLaijinRows with appropriateExcellentAfter the upgrade, the running speed is clear.DisplayTo improve!The following is a summary of these three aspects:IsMore straightGuanLocationDescriptionMingProblem, AllRealIn this exampleSQLRunTimeAverageTested, Not exceedingPassAverage value of 1 secondIs(<1Seconds ).----Test EnvironmentEnvironment:Host:Hp lh ii ----MasterFrequency:330 MHZ ----Memory:128MB----Operating SystemUnified:Operserver5.0.4 ----DataLibrary:Sybase11.0.3 I. unreasonable IndexesDesign----Example: TableRecordYes620000Row,TrySee the following for different indexes:SQLRunning status:---- 1.InDateA non-cluster index is created.Select count (*) from record where date> '20160301' and date <'20160301' and amount> 19991201 (25 seconds)Select date, sum (amount) from record group by date (55 seconds)Select count (*) from record where date> '123' and place in ('bj ', 'sh') (27 seconds)----Analysis:----DateThere are a lot of duplicates onReset ValueIn non-clustered indexes, data is physically stored randomlyPageInInspectionFindHour, RequiredMandatoryOne row tableScanCan be foundThisYifanPerimeterAll rows in.---- 2.InDateOn a Cluster IndexSelect count (*) from record where date> '20160901' and date <'20160901' and amount> 19991201 (14 seconds)Select date, sum (amount) from record group by date (28 seconds)Select count (*) from record where date> '2013' and place in ('bj ', 'sh') (14 seconds)----Analysis:----Under the cluster index, the data is physicallyShunSort in DataPageUp, heavyReset ValueAnd thereforeInspectionFindHour, You can findThisParametersPerimeterAnd onlyThisParametersPerimeterInternalScanData profilingPageTo avoidScanningImprovedQuerySpeed.---- 3.InPlace,Date,AmountOnGroupIndex combinationSelect count (*) from record where date> '20160301' and date <'20160301' and amount> 19991201 (26 seconds)Select date, sum (amount) from record group by date (27 seconds)Select count (*) from record where date> '2013' and place in ('bj, 'sh') (<1 second)----Analysis:----ThisIs not very reasonableGroupIndex, becauseIsBefore itExportColumn isPlace, Article 1 and Article 2SQLNo referencePlaceTherefore, the index is not used. The thirdSQLUsedPlaceAnd all referenced columns are included inGroupThe index overwrite is formed, so it is very fast.---- 4.InDate,Place,AmountOnGroupIndex combinationSelect count (*) from record where date> '20160301' and date <'20160301' and amount> 19991201 (<1 second)Select date, sum (amount) from record group by date (11 seconds)Select count (*) from record where date> '2013' and place in ('bj ', 'sh') (<1 second)----Analysis:----ThisIs a reasonableGroupIndex. It setsDateWorkIsBeforeExportColumn to makeEachItemsSQLBoth the first and third indexes can be used.SQLAnd thus the performance has reached the maximum.Excellent.---- 5. Conclusion :----The index created by default is a non-cluster index,HourIt is not optimal; a reasonable indexDesignTo be created inPairVariousType QueryAnalysis andPrediction.GenerallyDescription:①.Large numbers of duplicatesReset ValueAndJingChangfanPerimeter Query(Between,>, <,>=, <=) AndOrder,GroupSendingGenerated column, available for testConsiderationsCreate a cluster index;②.JingOften the sameHourAccess Multiple columns andEachBoth columns contain duplicates.Reset ValueAvailableConsiderationsCreateGroupIndex combination;③.GroupTry to combine IndexesKey QueryIndex coverage.ExportColumns must be the most usedFrequencyComplex columns. 2. IncompleteConnectionConditions:Example: TableCardYes7896Line, inCard_noThere is a non-clustered index, tableAccountYes191122Line, inAccount_noThere is a non-clustered index on,TryView different tablesConnectionUnder the following conditionsSQLOfExecutiveRow condition:Select sum (a. amount) from account a, card B where a. card_no = B. card_no (20 seconds)Select sum (a. amount) from account a, card B where a. card_no = B. card_no and a. account_no = B. account_no (<1 second)----Analysis:----In the firstConnectionOptimal connection conditionsQueryThe solution isAccountOut-of-officeLayerTable,CardWorkLayerTable, usingCardOn the index, itsI/OThe number of times can be estimated by the following formula:Is:ExternalLayerTableAccountOn22541Page+(OuterLayerTableAccountOf191122Line*InternalLayerTableCardUpperCorrespondingExternalLayerThe first row of the tableQueryFound3Page)= 595907TimesI/OIn the secondConnectionOptimal connection conditionsQueryThe solution isCardOut-of-officeLayerTable,AccountWorkLayerTable, usingAccountOn the index, itsI/OThe number of times can be estimated by the following formula:Is: OuterLayerTableCardOn1944Page+(OuterLayerTableCardOf7896Line*InternalLayerTableAccountUpperCorrespondingExternalLayerTableEachOne rowQueryFound4Page)= 33528TimesI/OYesSee, Only fullConnectionWhen conditions are met, the real best solution will beExecutiveLine.Summary:1.The multi-table operation isActual executionBefore the row,Query OptimizationAccordingConnectionThe receiving condition, which indicates the numberGroupPossibleConnectionConnect to the solution and find the systemUnified overheadThe minimal best solution.ConnectionTest conditionsBelt considerationsTables with Multiple indexes and rows; tables with internal and external IndexesSelectFormula: ExternalLayerNumber of matched rows in the table*InternalLayerTableEachOnceQueryThe number of times of searching is determined, multiplicationProductMinimumIsThe best solution.2.QueryViewExecutiveSolution--UseSet showplanOn, HittingOpenShowplanOption, You can seeConnectionConnectShunSequence and usageTypeIndex information; want to see moreDetailsInformation, requiredSaRoleExecutiveLineDbcc (3604,310,302). Iii. unavailableExcellentOfWhereClause1.Example:SQLConditionLanguageAll columns in a sentence have an appropriate index,ExecutiveThe row speed is very slow:Select * from record wheresubstring (card_no, 5378) = '000000' (13 seconds)Select * from record whereamount/30 <1000 (11 seconds)Select * from record whereconvert (char (10), date, 112) = '123' (10 seconds)Analysis:WhereClausePairAny Column OperationsEndThe results are all inSQLRunHourColumn by ColumnCalculationSo it hasIntoThe row table is not used for search.TheColumn indexes;IfThisSomeEndIfQuery compile timeThen?You canSQLExcellentTranscoderExcellentTo avoid table searchSQLRewrite to the followingThis way:Select * from record where card_no like '000000' (<1 second)Select * from record where amount <1000*30 (<1 second)Select * from record where date = '2014/1/01' (<1 second)You willFoundSQLMingDisplayGet up!2.Example: TableStuffYes200000Row,Id_noNon-Cluster Index,PleaseSee the following:ThisItemsSQL:Select count (*) from stuff where id_no in ('0', '1') (23 seconds)Analysis:---- WhereIn the condition'In'InLogicEquivalent'Or', SoLanguageMethod analyzer willIn ('0', '1 ')TransferInformatizationIsId_no = '0' or id_no = '1'ComeExecutiveLine.MeAreIt is expected thatEachItemsOrSub-StatementDo not checkFind, and thenEndIf they are added,This wayAvailableId_noOn;HoweverActual(AccordingShowplan),It uses"ORPolicy", That is, extractFullFootEachItemsOrClause row, savedTemporaryDataLibraryIn the worksheet, create a unique index to remove duplicates.ReplayLine, and finally fromThisItemsTemporaryTableCalculationComputingEndResult. Therefore,ActualProcess not usedId_noIndex and completeTime alsoTo be acceptedTempdbDataLibraryPerformance impact.RealPracticeCertificateThe more rows, the worse the performance of the worksheet.StuffYes620000LineHour,ExecutiveLineTimeActually achieved220Seconds!AlsoIt is betterOrSub-StatementOpen:Select count (*) from stuff where id_no = '0' select count (*) from stuff where id_no = '1'Get twoEndIf this happens, it is more cost-effective to make an addition. BecauseFor eachThe sentence is indexed,ExecutiveLineTimeOnly3In seconds620000Row bottom,TimeOnly4Seconds.Or, writeSimpleStorageStoredCheng:Create proc count_stuff asdeclare @ a intdeclare @ B intdeclare @ c intdeclare @ d char (10) beginselect @ a = count (*) from stuff where id_no = '0' select @ B = count (*) from stuff where id_no = '1' endselect @ c = @ a + @ bselect @ d = convert (char (10), @ c) print @ dCalculate directlyEndResult,ExecutiveLineTimeSame as face 1SampleFast! ---- Conclusion:---- 
YesSeeSuperiorInstantWhereThe clause uses the index and cannotExcellentInstantSendingTable generatedScanOrAmountExternalOverhead.1.AnyPairAll Column Operations willExportTo tableScanDescription, which includes dataLibraryFunction,CalculationComputing expressions, etc,Query timeTry to move the operation to the right of the equal signEdge.2. in,OrThe clause usually uses a worksheet to invalidate the index.ProductionGenerate a large number of duplicatesReset Value, You can take the testConsiderationsRemove a clauseOpen; SplitOpenClauseShouldContains indexes.3.Be good at using storageStoredProcess, which enablesSQLChangeMore flexible and efficient.From aboveThisSome examples show that,SQLExcellentOfEssenceInEndIf it is correct, useExcellentYou canRecognitionOfLanguageUse indexes to reduce the number of tables.ScanIllustratedI/OTimes to avoid table searchSendingRaw. ItsRealSQLPerformanceExcellentIsComplexOfPassCheng, aboveThisSome are justYingUseLayerOnceTypeBodyNow, In-depth researchAlsoYesRelatedAnd DataLibrary LayerOfCapitalSource configuration, NetworkNetwork LayerTraffic control and operationSystem layerOfTotalBodyDesign. 1. If developers use tables or views of other databases, they must create a View in the current database to perform cross-database operations. It is best not to directly use "databse. dbo. table_name ", because sp_depends cannot display the cross-database table or view used by the SP, it is not convenient to verify.

2. Before submitting the SP, the developer must have used set showplan on to analyze the query plan and perform its own query optimization check.

3. High program running efficiency and application optimization. Pay attention to the following points during SP writing:

A) SQL usage specifications:

I. Avoid large transaction operations as much as possible. Use the holdlock clause with caution to improve the system concurrency capability.

Ii. Try to avoid repeated accesses to the same or several tables, especially tables with large data volumes. You can consider extracting data to a temporary table based on the conditions and then connecting it.

Iii. avoid using a cursor whenever possible because the cursor is inefficient. If the cursor operation contains more than 10 thousand rows of data, it should be rewritten. If the cursor is used, try to avoid table join operations in the cursor loop.

Iv. note that when writing where statements, the order of statements must be taken into account. The order before and after condition clauses should be determined based on the index order and range size, and the field order should be consistent with the index order as much as possible, the range is from large to small.

V. do not perform functions, arithmetic operations, or other expression operations on the left side of "=" in the where clause. Otherwise, the system may not be able to correctly use the index.

Vi. use exists instead of select count (1) to determine whether a record exists. The count function is used only when all the rows in the statistical table are used, and count (1) is more efficient than count.

Vii. Try to use "> =" instead of "> ".

Viii. Note the replacement between the or clause and the union clause.

Ix. Pay attention to the data types connected between tables to avoid the connection between different types of data.

X. Pay attention to the relationship between parameters and data types in stored procedures.

Xi. Pay attention to the data volume of insert and update operations to prevent conflicts with other applications. If the data volume exceeds 200 data pages (400 Kb), the system will update the lock and the page lock will be upgraded to the table lock.

B) Specification for indexing:

I. You should consider creating indexes in combination with applications. We recommend that you create a large OLTP table with no more than six indexes.

Ii. Try to use the index field as the query condition, especially the clustered index. If necessary, you can use index index_name to forcibly specify the index.

Iii. Avoid performing table scan when querying large tables. If necessary, create an index.

Iv. when using an index field as a condition, if the index is a joint index, you must use the first field in the index as the condition to ensure that the system uses the index, otherwise, the index will not be used.

V. Pay attention to index maintenance, rebuild indexes periodically, and recompile the stored procedure.

C) use of tempdb:

I. Try to avoid using distinct, order by, group by, having, join, and *** pute, because these statements will increase the burden on tempdb.

Ii. Avoid frequent creation and deletion of temporary tables and reduce the consumption of system table resources.

Iii. when creating a temporary table, if a large amount of data is inserted at one time, you can use select into instead of create table to avoid logs and increase the speed. If the data volume is small, in order to ease the system table resources, we recommend that you first create table and then insert.

Iv. if the temporary table has a large amount of data and requires an index, you should place the process of creating a temporary table and creating an index in a single sub-storage process, in this way, the system can use the index of the temporary table.

V. if a temporary table is used, you must explicitly delete all temporary tables at the end of the stored procedure. First truncate the table and then drop the table, so that the system table can be locked for a long time.

Vi. Use caution when connecting large temporary tables to other large tables to query and modify them, reducing the burden on the system table, because this operation will use the tempdb system table multiple times in one statement.

D) Reasonable algorithm usage: Based on the SQL optimization technology mentioned above and the SQL Optimization content in the ASE Tuning manual, combined with practical applications, a variety of algorithms are used for comparison to obtain the method with the least resource consumption and the highest efficiency. Specific ASE optimization commands available: set statistics io on, set statistics time on, set showplan on, etc.