Requirements: Identify recently changed customer information (sorted by last change time, from Systemlog table Logdatetime field)
Description
Customer: Client Information Form
Systemlog: System Log table, record all table information increment, delete, change
The development of their own industry software, not only their own companies are in use, other companies are also in use, the company software maintenance and provide upgrade services
Since the previous design Customer information table did not change the Time field, the system is now running in n different databases
Causes the database structure to not be arbitrarily changed (although it can be upgraded, but too expensive)
You cannot use views and stored procedures to use only SQL statements
I wrote the SQL and the effect, although can achieve effect, but Sqlsrever execution plan and IO situation is very bad
(Personal understanding Systemlog query again)
--Customer information sorted by last changed time:
With _temp as (Selectcustomerid,companyname, (SELECT TOP 1 logdatetime from Systemlog WHERE logtype= ' change ' and logtable= ' Cust Omer ' and Rid=customerid order by ID DESC] as Ed, Row_number () Over (ORDER by (SELECT TOP 1 logdatetime from Systemlog WH ERE logtype= ' change ' and logtable= ' Customer ' and Rid=customerid ORDER by ID DESC] desc) as Rownumberfrom customerwhere Custom ERSTATUS=18) SELECT * from _temp WHERE RowNumber between 0 and ten ORDER by Ed DESC
System log for querying customer information
SELECT TOP TenS.rid asCustomerId, CompanyName, S.logdatetime fromSystemlog S Left JOINCustomer C onS.rid=CustomerIdWHERELogType='Change' andLogtable='Customer' andC.customerstatus= -ORDER byS.idDESC
Set STATISTICS IO on;
(10 rows affected)
Table ' Systemlog '. Scan count 2, logical read 1697 times, physical read 0 times, read 7 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.
Table ' worktable '. Scan Count 155, logical read 50,916 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.
Table ' Customer '. Scan count 1, logical read 19 times, physical read 0 times, read 1 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.
(1 rows affected)
(10 rows affected)
Table ' Customer '. Scan count 0, logical read 157 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.
Table ' Systemlog '. Scan count 1, logical read 73 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.
(1 rows affected)
May I ask what optimization method you have? Thank you very much
How does SQL Server use a CTE to sort by sub-query?