Access optimization of a big data table-query optimization in the linked drop-down box

Problem description
There is a data table (product label table, each product has a unique Sn, about 1 million per month). There are two linkage drop-down boxes on the query interface, [Specification] ____, [batch ]______
After you select a specification (currently 200 specifications), list the batches of the production plan issued by this specification.

Original Method
Specification list
Select specification from label table group by specification

Obtain batches Based on Specifications
Select batch from label table where specification = 'certain specifity' group by batch

To perform a full table scan, when the data reaches 0.5 million or more, the speed is obviously slow, and when the data reaches 2 million, it takes about 1 minute for any of the above queries.

 

Solution

Solution 1: Create an index
Create an index by combining "specifications, batches" (clustered indexes are created separately in batches ).
Problem: because a record corresponds to an index entry, this solution requires a lot of extra space. In addition, there are already three indexes in the table, and too many indexes will lead to a reduction in update and insertion performance, in addition, the deadlock risk will be increased. As a module used on the pipeline, there are requirements for performance.

Solution 2: Create a specification batch table to record the relationship between specifications and batches.
Because the specification is 200 and relatively stable, there will be 100 batches in 100, and the 20 or 30 Batches will only be allocated to batches, so the combination of the two will be around-in a month.

After using the specification batch to correspond to the table, we further determine how to maintain the data of the corresponding table,
Method 1,Each time a production task breaks down and generates tags, the corresponding table of the specification batch is updated (that is, the corresponding operation is performed when the tag table record is added, deleted, and modified ). Consider that task decompositionCodeIt's enough to struggle, so I don't want to modify it a lot.ProgramCode.

Method 2Create a job in the database and perform regular incremental updates.
Production tasks are adjusted (deleted or changed), but the chances of changes are not high, in addition, most of the changes are modified within 1-2 hours after the task is released during the working hours (the execution status or completed the next day)
Therefore, the job is scheduled to run at 12 o'clock. After the job is executed, the type and batch of new records in the tag table are not included in the corresponding table, therefore, the query results in the drop-down box are from two parts. The type batch correspondence before the job execution point comes from the corresponding table, and the type batch correspondence after the job execution point directly queries the label table, because the maximum number of records in the current statistics is recorded at each job execution point, the following query statement is used to query the tag table:
Select batch from label table where specification = 'a certain specificity 'and ID> XXXX, because the index is created on the ID, and every day, the number of records is, therefore, the query execution time is basically stable.

The completed code is similar to the following:

View code

 Alter   Proc   [  DBO ] . [  Get batch by specifications  ]  @ Specification   Nvarchar ( 20  )  As   Create   Table  # T_ct_bno_map_tmp (batch  Nvarchar ( 20  ))  Declare  @ Maxlblid   Bigint   Declare   @ SQL   Nvarchar ( 2000  )  Set   @ Maxlblid  =  0   Select   @ Maxlblid  =   Max (Lblid) From Specification batch table  Set   @ SQL  =  '  Select batch from (select batch from label table where ID> =  '  +  Cast ( @ Maxlblid   As   Nvarchar ( 50 )) +  '  And specification = '''  +  @ Specification  +  '''  Group by batch Union select batch from specification batch corresponding table where specification =  '''  +   @ Specification  +  '''  ) As T group by batch  '  Insert   Into  # T_ct_bno_map_tmp Exec ( @ SQL  )  Select   *   From  # T_ct_bno_map_tmp  Drop   Table # T_ct_bno_map_tmp

Note:
The preceding dynamic SQL statement is used to execute the statement containing "id> = '+ Cast (@ maxlblid as nvarchar (50) +" to obtain the type batch correspondence in the tag table, if dynamic SQL is not used, directly use
Id> @ maxlblid, because the MSSQL query optimizer In the stored procedure does not know what maxlblid may be, and ignores the index created on the ID to perform a full table scan.
The execution process of the two is as follows:
(Static SQL statement)

(Dynamic SQL statement)

Job task code:

View code

 Declare  @ Maxlblid   Bigint  Declare   @ SQL   Nvarchar ( 2000  )  Set   @ Maxlblid  =  0  Select   @ Maxlblid  =   Max (Lblid) From DBO. Specification batch table  Set   @ SQL  =  '  Insert into specification batch table (specification, batno, lblid) Select specification, batno, lb0000from (select specification, batno, max (ID) lblid from label table where ID> =  '  +  Cast ( @ Maxlblid   As   Nvarchar ( 50 )) + '  Group by specification, batno limit t select specification, batno, lblid from kb_lable_ct_bno_map) as t  '  Exec ( @ SQL )