1 query optimization process

There is a ticket for the expansion process based on the CRM system. In the database, tc_worksheettc_worksheet_1_1 tc_worksheet_attr tc_worksheet_mattr tc_worksheet_xattr. The ticket data is stored in the preceding tables at the same time.

 

Initial Implementation Scheme: directly use SQL for table Association. It takes about 20 minutes to query the tickets that we need.

 

Optimization solution: a stored procedure + trigger is used to summarize the data in a redundant temporary table, create a trigger in the original ticket table, and call the stored procedure when you open a ticket in the crm system, summarize the data to the redundant temporary table.

Due to these disadvantages, the SQL collection speed in the stored procedure is relatively slow, which affects the creation speed of tickets in the CRM system.

 

Optimization Scheme: because it affects the speed of ticket operation in the crm system, we want to achieve data redundancy synchronization in asynchronous mode and find the implementation scheme of Job in SQLServer. After this solution is implemented, it is found that there is no way to synchronize data to the redundant intermediate table during the insert operation, find the cause, and accidentally find a problem. You have created a ticket in the CRM system, the trigger was executed 13 times before and after the trigger. It is suggested that the operation in the crm system is to insert the primary key in tc_worksheet, and then modify the values of some fields in the data, trigger is executed multiple times.

 

Optimization Scheme: create another intermediate table B in the original table of the ticket and the redundant table of the ticket, after the trigger is executed in the user's ticket table, insert the Operation Ticket id to the intermediate table B (first, determine whether the corresponding ticket field exists in the intermediate table B, if this ticket id is found in Table B in the middle, no operation is performed. Otherwise, the ticket id is inserted.) Enable a thread in the Web program, check whether new data exists in intermediate table B at intervals. If new data exists, execute the stored procedure to summarize the ticket data to the redundant intermediate table. If no data exists, sleep and wait for the next query.

We have not found a better solution yet. You are welcome to criticize and correct it.