Write a hard-pressed SQL query optimization

In the original: Remember the SQL query optimization of a bitter force

Recently in the maintenance of the company project, the need to load a page, a total load of more than 4,000 data, it takes 35 seconds, if the data grew to 40,000, I estimate a few minutes can not be confused. Lying trough, to me is the user's words estimate can not stand, while idle of nothing, want to optimize it, walk you.

First paste the query:

       Selectpub_aidbasicinformation.aidbasicinfoid, Pub_aidbasicinformation.username, Pub_aidbasicinformation.distric       T, Pub_aidbasicinformation.street, pub_aidbasicinformation.community, Pub_aidbasicinformation.discard, Pub_application.createon asAppcreateon, Pub_user.username asDepartmentusername, Pub_consult1.consultid, Pub_consult1.caseid, Clinicaltb.clinical,aidnametb.aid Name, Pub_application.isusetraining, Pub_application.applicationid, Tab.num fromPUB_CONSULT1INNER JOINPub_application onPub_consult1.applicationid=Pub_application.applicationidINNER JOINPub_aidbasicinformation onPub_application.aidbasicinfoid=pub_aidbasicinformation.aidbasicinfoidINNER JOIN(SelectConsultid,dbo.f_getclinical (Consultid) asClinical fromPUB_CONSULT1) CLINICALTB onClinicaltb.consultid=Pub_consult1.consultid Left Join(Select distinctApplicationID,sum(Traniningnumber) asNum fromDbo. Review_aid_usetraining_recordwhereAidreferralid is NULL  Group  byApplicationID) tab ontab. ApplicationID=Pub_consult1.applicationidINNER JOIN(SelectCONSULTID,DBO.F_GETAIDNAMEBYCONSULT1 (Consultid) asAidname fromPUB_CONSULT1) Aidnametb onAidnametb.consultid=Pub_consult1.consultid Left OUTER JOINPub_user onPub_application.reviewuserid=Pub_user.useridWHEREPub_consult1.directory= 0     Order  byPub_application.createondesc

View Code

After execution there is a picture of the truth:

So slow, no way to see how the query plan is:

This is the SQL query inside the execution of three functions when the query plan is generated, one can see that the execution of the cost is relatively large, and are spent on the clustered index scan, put the mouse on the clustered index scan block above, see the following detailed plan:

From these graphs, you can see the query I/O overhead, the operator overhead, the estimated number of rows, and the object and query conditions of the operation, all of which provide favorable evidence for optimizing the query. 1th, the 3 diagram IO overhead is relatively large, the 2nd graph estimates the number of rows is larger, and then according to other information, the first thought should be to build the index, not the words to change the query.

Let's take a look at what optimization information the Database Engine Tuning Advisor can give us, and sometimes it can help us provide useful information, such as creating statistics, indexing, partitioning, etc.

Open SQL Server Profiler to save the query you just executed as a trace (. trc) file, and then open Database Engine Tuning Advisor to do the following

The final generated recommendations are reported as follows:

Here you can click to view some suggestions, partition, create index, and follow the prompts to create the following index:

CREATE nonclustered INDEXIndex1 on [dbo].[pub_aidbasicinformation](    [aidbasicinfoid] ASC)CREATE nonclustered INDEXIndex1 on [dbo].[pub_application](    [ApplicationID] ASC,[Reviewuserid] ASC,[aidbasicinfoid] ASC,[Createon] ASC)CREATE nonclustered INDEXIndex1 on [dbo].[PUB_CONSULT1](    [Directory] ASC,[ApplicationID] ASC) CREATE nonclustered INDEXIdnex1 on [dbo].[Review_aid_usetraining_record](    [Aidreferralid] ASC,[ApplicationID] ASC)

View Code

After the index was created, execute the query again, thought can improve efficiency, did not expect me to go, or to 30 seconds, almost no improvement, optimization engine advisor sometimes will fail, here just to show you have this solution to solve the problem, sometimes is reliable, but this is not reliable. No way, only open the function to look closely, combined with the above query plan detail diagram, delete the previously created index, and then create the following index:

CREATE nonclustered INDEX  on dbo. Report_adapterassessment_aid (    asc, Productdiraid  ASC)CREATE nonclustered INDEX  on dbo. Report_adapterassessment (    ASC)

View Code

Execute Query again

Okay, just 3.5 seconds, almost 10 times times the speed, it seems to be the effect of the ha.

Let's see if there are any changes to the query plan, and the previous diagram shows the following question:

As we can see, the index scan is gone, only index lookups, clustered index lookups, key lookups, and operator overhead, I/O overhead are much lower. Index Scan, clustered index Scan (Clustered index Scan) is almost the same as a table scan, which is basically a row-by-line scan of table records, which is very slow, while index lookup (index seek), clustered index lookup, The key lookup is pretty fast. The purpose of the optimization query is to try to remove those with the XXXX scan, with XXXX lookup.

Is that enough for you? But back to think about it, more than 4,000 data to 3.5 seconds, or a little slow, should be able to hurry up, so decided to change the query. Look at the query, the only one that can be optimized is the three function.

In order to see the function execution effect first delete the index, see the query in the function F_GETAIDNAMEBYCONSULT1 to do, intercept the query in the subquery related to the function:

Select  from (Select as aidnamefrominnerjoin  pub_consult1on Aidnametb.consultid=

The results obtained:

I did not expect this point of data to 46 seconds, it seems that this function is really the culprit.

The specific code of the function is not posted out, and the function is still under the set of another function, the function itself is slow to execute, not to mention the function lining query also contains functions. In fact, according to a few related tables to query several fields, and the value of a field to merge into the same row, so there is no need to use a function or stored procedures, with a subquery and SQL FOR XML path is OK, the function is changed to the following query:

 withCte1 as(    SelectA.adapterassessmentid, Case  whenB.aidname is NULL  ThenA. AidnameElseB.aidnameEndAidname fromReport_adapterassessment_aid asA Left JoinPub_productdir asB onA.productdiraid=b.productdiraid), Cte2 as(    --Grouping and merging Aidname field values based on Adapterassessmentid    SelectAdapterassessmentid, (SelectAidname+','  fromcte1whereAdapterassessmentid=TB. Adapterassessmentid forXML Path ("')) asAidname fromCte1 asTBGroup  byadapterassessmentid), Cte3 as(    SelectConsultid, Left(Aidname,LEN(Aidname)-1) asAidname from    (       SelectPub_consult1.consultid,cte2. Aidname fromPub_consult1,report_adapterassessment,cte2wherePub_consult1.consultid=Report_adapterassessment.consultid andReport_adapterassessment.adapterassessmentid=Cte2. Adapterassessmentid andReport_adapterassessment.assesstuijian is NULL    )  asTb

View Code

The result of this query will be less than 1 seconds without an index. Then write the main query:

Select distinctpub_aidbasicinformation.aidbasicinfoid, Pub_aidbasicinformation.username, Pub_aidbasicinformation.distri       CT, Pub_aidbasicinformation.street, pub_aidbasicinformation.community, Pub_aidbasicinformation.discard, Pub_application.createon asAppcreateon, Pub_user.username asDepartmentusername, Pub_consult1.consultid, Pub_consult1.caseid, clinicaltb.clinical, Cte3.A Idname, Pub_application.isusetraining, Pub_application.applicationid, Tab.num fromPUB_CONSULT1INNER JOINPub_application onPub_consult1.applicationid=Pub_application.applicationidINNER JOINPub_aidbasicinformation onPub_application.aidbasicinfoid=pub_aidbasicinformation.aidbasicinfoidINNER  JOIN(SelectConsultid,dbo.f_getclinical (Consultid) asClinical fromPUB_CONSULT1) CLINICALTB onClinicaltb.consultid=Pub_consult1.consultid Left Join(Select distinctApplicationID,sum(Traniningnumber) asNum fromdbo. Review_aid_usetraining_recordwhereAidreferralid is NULL            Group  byapplicationid) Tab ontab. ApplicationID=Pub_consult1.applicationid Left JOINCte3 onCte3. Consultid=Pub_consult1.consultid Left OUTER JOINPub_user onPub_application.reviewuserid=Pub_user.useridwherePub_consult1.directory= 0Order  byPub_application.createondesc

View Code

This is basically done, it takes 8 seconds without an index, or 27 seconds faster than a function without an index.

Put the index in, just 1.6 seconds, than to build the index function without subqueries and SQL for XML path 1.9 seconds faster

Query there is a place to use the function, estimated that the optimization can also improve the efficiency of execution, because the time is limited and the length of a little longer, here is not much to say.

Finally make a summary, query optimization is the following methods:

1: Increase the index or rebuild the index. It is common to index fields in foreign keys, connection fields, sort fields, filter queries, or through Database Engine Tuning Advisor information. Sometimes when you create an index, you find that the query is executed in the same way as an index scan or a clustered index scan, rather than looking at the index, it is likely that your query field and the Where Condition field are not all included in the indexed field, and the solution to this problem is to build more indexes. or include the appropriate fields when you create the index, and have the indexed fields overwrite your query fields and where criteria fields.

2: Adjust the query statement, the premise to understand the other people's query, to understand the business logic.

3: Table partitioning, big data volume can be considered.

4: Improve server hardware configuration.

Write a hard-pressed SQL query optimization