Classic database article! (Required)

Storage Process Writing experience and Optimization Measures

Introduction: complex business logic and database operations are often encountered during database development. In this case, SP is used to encapsulate database operations. If there are many SP projects and there is no certain specification for writing, it will affect the difficulties of system maintenance and the difficulty of understanding the big SP logic in the future, in addition, if the database has a large amount of data or the project has high performance requirements for the SP, you will encounter optimization problems. Otherwise, the speed may be slow. After hands-on experience, an Optimized SP is hundreds of times more efficient than an optimized SP with poor performance.

Content:

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. HighProgramWhen writing an SP program, you should pay attention to the following points:

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. Avoid using distinct, order by, group by, having, join, and cumpute as much as possible, 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) ReasonableAlgorithmUsage:
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 are available: Set statistics Io on, set statistics time on, set showplan on, and so on.

--------------------------------------------------------------------------

--------------------------------------------------------------------------

Full-text index-contains syntax
We usually use contains in the WHERE clause, like this: Select * From table_name where contains (fulltext_column, 'search CONTENTS ').

Let's take an example to learn about it. Suppose there is a table students, where the address is a full text retrieval column.
1. query student addresses in Beijing
Select student_id, student_name
From students
Where contains (address, 'beijing ')
Remark: Beijing is a word that must be enclosed in single quotes.

2. query student addresses in Hebei Province
Select student_id, student_name
From students
Where contains (address, '"Heibei province "')
Remark: Hebei province is a phrase that must be enclosed in double quotation marks.

3. query student addresses in Hebei province or Beijing
Select student_id, student_name
From students
Where contains (address, '"Heibei province" or Beijing ')
Remark: You can specify logical operators (including and, and not, or ).

4. query the addresses with the words "Nanjing Road"
Select student_id, student_name
From students
Where contains (address, 'nanjing near road ')
Remark: The above query will return addresses that contain the words "Nanjing Road", "Nanjing East Road", and "Nanjing West Road.
A near B indicates that A is near B.

5. query the address starting with 'hu '.
Select student_id, student_name
From students
Where contains (address, '"Hu *"')
Remark: The preceding query returns an address containing the words 'hubei' and 'hunanc.
Remember: *, not %.

6. Weighted queries
Select student_id, student_name
From students
Where contains (address, 'isabout (city weight (. 8), county Wright (. 4 ))')
Remark: isabout is the keyword of this query. Weight specifies a value ranging from 0 ~ The number between 1, similar to the coefficient (I understand ). Indicates that different conditions have different focuses.

7. multi-state query of words
Select student_id, student_name
From students
Where contains (address, 'formsof (inflectional, street )')
Remark: The query returns the addresses that contain the words 'street 'and 'streets.
For a verb, different tenses are returned, such as dry, dry, dried, drying, and so on.

---------------------------------------------------------

---------------------------------------------------------

1. sort by strokes of the Last Name:
Select * From tablename order by customername collate chinese_prc_stroke_ci_as

2. database encryption:
Select encrypt ('original password ')
Select pwdencrypt ('original password ')
Select pwdcompare ('original password', 'encrypted password') = 1 -- same; otherwise, different encrypt ('original password ')
Select pwdencrypt ('original password ')
Select pwdcompare ('original password', 'encrypted password') = 1 -- same; otherwise, different

3. Retrieve the fields in the table:
Declare @ list varchar (1000), @ SQL nvarchar (1000)
Select @ list = @ list + ',' + B. name from sysobjects A, syscolumns B where a. ID = B. ID and A. Name = 'table'
Set @ SQL = 'select' + right (@ list, Len (@ list)-1) + 'from table'
Exec (@ SQL)

4. View hard disk partitions:
Exec master .. xp_fixeddrives

5. Compare whether tables A and B are equal:
If (select checksum_agg (binary_checksum (*) from)
=
(Select checksum_sum( binary_checksum (*) from B)
Print 'Equality'
Else
Print 'unequal'

6. Kill all event Inspector processes:
Declare hcforeach cursor global for select 'Kill '+ rtrim (spid) from Master. DBO. sysprocesses
Where program_name in ('SQL filer', n' SQL event profiler ')
Exec sp_msforeach_worker '? '

7. Record Search:
Starting with N records
Select Top N * from table
-------------------------------
N to M records (primary index ID required)
Select top M-N * from table where ID in (select top m id from Table) order by ID DESC
----------------------------------
N to the end record
Select Top N * from Table order by ID DESC

8. How to modify the Database Name:
Sp_renamedb 'old _ name', 'new _ name'

9: Get all user tables in the current database
Select name from sysobjects where xtype = 'U' and status> = 0

10: Get all fields of a table
Select name from syscolumns where id = object_id ('table name ')

11. View views, stored procedures, and functions related to a table
Select a. * From sysobjects A, syscomments B where a. ID = B. ID and B. Text like '% table name %'

12: view all stored procedures in the current database
Select name as stored procedure name from sysobjects where xtype = 'P'

13: Query all databases created by the user
Select * from Master .. sysdatabases d Where sid not in (select Sid from Master .. syslogins where name = 'sa ')
Or
Select dbid, name as db_name from Master .. sysdatabases where Sid <> 0x01

14: query the fields and Data Types of a table
Select column_name, data_type from information_schema.columns
Where table_name = 'table name'

[N]. [title]:
Select * From tablename order by customername

[N]. [title]:
Select * From tablename order by customername

Bytes --------------------------------------------------------------------------------------

Bytes --------------------------------------------------------------------------------------

SQLOptimization is a complex task. The following basic principles are recorded when I read a book. They are clear and have no nonsense:

1.Use of indexes:

(1).When the inserted data is the number of records in the data table10%First, you need to delete the index of the table to improve the efficiency of data insertion. After the data is inserted, the index is created.

(2).Avoid using functions or computing in index columns.WhereIf the index is a part of the function, the optimizer will not use the index and use full table scan. For example:

Inefficiency:Select * from Dept where Sal * 12> 2500;

Efficient:Select * from Dept where SAL> 2500/12;

(3 ).Avoid using index ColumnsNotAnd"! =", The index can only tell what exists in the table, but cannot tell what does not exist in the table, when the database encountersNotAnd"! =", The full table scan will stop using the index.

(4 ).Index Column> =Replace>

 Inefficiency:Select * from EMP where deptno> 3

 Efficient:Select * from EMP where deptno> = 4

The difference between the two is that the formerDBMSWill Jump directly to the firstDeptnoEqual4And the latter will first locateDeptnoEqual3And scan forward to the first record.DeptnoGreater3.

(5 ).You have to enable the index for a column using a function. function-based indexes are a good solution.

2.Usage of cursors:

When you delete, update, and insert data in a massive data table, the efficiency of using a cursor is the slowest, but it is essential to use a cursor, therefore, it is very important to use the cursor correctly:

(1 ).Use a timestamp in the source table for data extraction. In this way, daily maintenance of dimension table data only applies to data with the latest update date, greatly reducing the number of data records to be maintained.

(2 ).InInsertAndUpdateA condition is added to a dimension table to filter existing records in the dimension table. For example:

Insert into dim_customer select * From ods_customer where ods_customer.code not exists (dim_customer.code)

Ods_customerIs the data source table.Dim_customerIt is a dimension table.

(3 ).Use an explicit cursor because the implicit cursor performs two operations, the first record retrieval and the second check.Too rule rowsThisException,The explicit cursor does not perform the second operation.

3.When data is extracted and uploadedSQLOptimization:

(1). WhereJoin order in the clause:

OracleBottom-up sequence analysisWhereClause, according to this principle, the join between tables must be written in otherWhereConditions that can filter out a large number of records must be written inWhereThe end of the clause. For example:

Inefficiency:Select * from EMP e where SAL> 5000 and job = 'manager' and 25 <(select count (*) from EMP where Mgr = E. empno );

Efficient:Select * from EMP e where 25 <(select count (*) from EMP where Mgr = E. empno) and Sal> 5000 and job = 'manager ';

(2 ).When deleting a full table, useTruncateSubstitutionDelete,Note thatTruncateIt can only be used to delete a full table becauseTruncateYesDDLInsteadDML.

(3 ).Try to use moreCommit

As long as it is possibleDelete, insert, updateUse as many operations as possibleCommit,In this way, the system performance will beCommitThe released resources are greatly improved.

(4 ).UseExistsSubstitutionInTo improve the query efficiency.

(5 ).UseNot existsSubstitutionNot in

(6 ).OptimizationGroup

ImproveGroupStatement efficiency, you can record unnecessary records inGroupPreviously filtered out. For example:

Inefficiency:Select job, AVG (SAL) from EMP group by job having job = 'President 'or job = 'manager ';

Efficient:Select job, AVG (SAL) from EMP having job = 'President 'or job = 'manager' group by job;

(7 ).Conditional useUnion-allSubstitutionUnion: This sorting is unnecessary and the efficiency will be improved.3To5Times.

(8 ).Separate tables and Indexes

Always create your table and index in different tablespaces.OracleInternal System Objects are stored inSystemIn the tablespace. At the same time, ensure that the data table space and index tablespace are placed on disks controlled by Different Hard Disk control cards.

Trackback: http://tb.blog.csdn.net/TrackBack.aspx? Postid = 596347