[Reprint] ERP Development-Data query optimization method

System operating Environment:
MSSQL 2008
With the rapid development of the company's business, a variety of business data such as rocket-like high-speed growth, one after another tens of thousands of rows of data tables, often large tables between the association, consuming a large number of disk IO system, and will affect the normal operation of the real-time business, so we must improve the way such queries, to ensure that the system
How is the association query optimized between large tables and large tables?
1 new indexed view, will need to show the associated data to establish a view, so that the query physical existence on the system,
CREATE VIEW TableView
With SCHEMABINDING
As select A.a1,a.a2,b.b1,b.b2 from a LEFT join B on a.ID =b.id
CREATE UNIQUE CLUSTERED INDEX ix_ tableview_id
On TableView (ID);
2 in the data table design, the use of redundant design, the associated table fields into a table, avoid large table association query.
3 in a database report query:
3.1 In the business design, as far as possible to achieve sub-period query, to avoid returning n years of data [because return n years, it is best to find DBA export]
3.2 In a stored procedure, first create a few temporary tables into the temporary table with data intercepted from the large table by query criteria
3.3 Using these temporary small tables to make the associated query, and to obtain the required data, you can avoid large table association queries.
4 Do the system cache design by the way of the program? This method is used with caution [as this can only do approximate statistics---BI].

Postscript:
In the case of Slow program, the first must check the program's optimization space, as far as possible from the software to solve the problem, to avoid problems on the replacement of hardware, or seek help, remember, avoid!

Transfer from http://www.maomao365.com/?p=469

[Reprint] ERP Development-Data query optimization method