我有一个客户表，然后有一个单独的事务表交叉引用客户表。我想通过一个特定的列作为组合的 SQL 视图呈现给消费者。此事务列称为"上次编辑时间"。
如果我通过查询名字、姓氏等列来运行 SELECT 语句，视图本身很好。我遇到问题的地方是在查询"上次编辑">列时。此列使用前 1 个选择条件。例如，如果我在"1/1/2019 00：00：01"和"6/30/2019 23：59：59"之间查询 LastEdited U。结果需要很长时间，因为有明显的性能损失。
关于如何最好地处理这个问题的任何建议？我尝试通过调用标量函数为此视图创建列，例如：
(选择dbo.fncUser_LastCustomerEditDate(c.客户编号))作为上次编辑
此标量函数基本上返回 TOP 1 记录。我还尝试通过针对外部应用引用它来创建列，例如：
。外部应用(从 dbo.tbl121_DataLog dl 中选择前 1 个 GMTTimestamp，其中 dl.PrimaryKey = c.Id 和 dl.DBActionId In (600001， 600002) 顺序由 dl.GMTTimeStamp DESC) dl
这样我的 SELECT 列表只看：
dl.GMTTimeStamp as LastEdited
这两个选项都无法通过基于此列查询结果视图来快速返回结果。
任何帮助或建议将不胜感激！
以下是我到目前为止尝试过的两种视图结构：
CREATE VIEW [dbo].[viwUser_Customers_Custom2]
AS
SELECT c.Id, c.Type, c.CustomerNo, sit.Code AS PrimarySiteCode, sit.Name AS PrimarySiteName, emp.Code AS PrimarySalespersonCode,
emp.Name AS PrimarySalespersonName, c.Title, c.FirstName, c.MiddleName, c.LastName, c.NickName, c.Greeting, c.PreviousName,
dbo.fncCustContactMethodText(c.PreferredContactMethod) AS PreferredContactMethod, cad.Address1, cad.Address2, cad.Address3, cad.City, cad.State,
cad.PostCode, cad.Country, cpp.CountryPrefix AS PhoneCountryPrefix, cpp.AreaCode AS PhoneAreaCode, cpp.Number AS PhoneNumber,
cpp.Extension AS PhoneExtension, dbo.fncCustFormattedPhoneNo(cpp.CountryPrefix, cpp.AreaCode, cpp.Number, cpp.Extension)
AS PhoneNumberFormatted, cpf.CountryPrefix AS FaxCountryPrefix, cpf.AreaCode AS FaxAreaCode, cpf.Number AS FaxNumber,
cpf.Extension AS FaxExtension, dbo.fncCustFormattedPhoneNo(cpf.CountryPrefix, cpf.AreaCode, cpf.Number, cpf.Extension) AS FaxNumberFormatted,
cpm.CountryPrefix AS MobileCountryPrefix, cpm.AreaCode AS MobileAreaCode, cpm.Number AS MobileNumber, cpm.Extension AS MobileExtension,
dbo.fncCustFormattedPhoneNo(cpm.CountryPrefix, cpm.AreaCode, cpm.Number, cpm.Extension) AS MobileNumberFormatted, cpe.EmailAddress, c.Sex,
c.UDFList1, c.UDFList2, c.UDFList3, c.UDFList4, c.UDFList5, c.UDFList6, c.UDFList7, c.UDFList8, c.UDFList9, c.UDFList10, c.UDFList11, c.UDFList12,
c.FingerSize1 AS LeftLittleFingerSize, c.FingerSize2 AS LeftRingFingerSize, c.FingerSize3 AS LeftMiddleFingerSize,
c.FingerSize4 AS LeftIndexFingerSize, c.FingerSize5 AS LeftThumbSize, c.FingerSize6 AS RightThumbSize, c.FingerSize7 AS RightIndexFingerSize,
c.FingerSize8 AS RightMiddleFingerSize, c.FingerSize9 AS RightRingFingerSize, c.FingerSize10 AS RightLittleFingerSize, c.LeftWristSize,
c.RightWristSize, c.NeckSize, c.SocSecNumber, c.POSComments, c.SecureComments, c.Status, c.Active, c.Day, c.Month, c.Year, dbo.fncGetDOB(c.Day,
c.Month, c.Year) AS BirthDate, dbo.fncGetAge(c.Day, c.Month, c.Year, c.Age, c.AgeReferenceDate, GETDATE()) AS Age, CAST('' AS varchar(1000))
AS MailingName, CAST('' AS varchar(1000)) AS GreetingName, CAST(0 AS money) AS CustomerSalesTotalForDateRange, CAST(0 AS money)
AS CustomerSalesTotal, CAST('' AS varchar(50)) AS PartnerCustomerNo, CAST('' AS varchar(20)) AS PartnerBirthDate, CAST(0 AS money)
AS PartnerSalesTotalForDateRange, CAST(0 AS money) AS PartnerSalesTotal, dbo.fnc604S_GetAnniversary(c.Id) as Anniversary,
Coalesce((Select Top 1 cd.DiscountPercent From tbl605_CustDiscount cd Where cd.CustomerId = c.Id),0) as DiscountPercent,
dl.GMTTimeStamp as LastEdited
FROM dbo.tbl600_Customer AS c INNER JOIN
dbo.tbl103_Sites AS sit ON c.SiteId = sit.Id LEFT OUTER JOIN
dbo.tbl601_CustAddress AS cad ON cad.CustomerId = c.Id AND cad.[Default] = 1 LEFT OUTER JOIN
dbo.tbl602_CustPhone AS cpp ON cpp.CustomerId = c.Id AND cpp.Device = 1 AND cpp.DeviceDefault = 1 LEFT OUTER JOIN
dbo.tbl602_CustPhone AS cpf ON cpf.CustomerId = c.Id AND cpf.Device = 3 AND cpf.DeviceDefault = 1 LEFT OUTER JOIN
dbo.tbl602_CustPhone AS cpm ON cpm.CustomerId = c.Id AND cpm.Device = 2 AND cpm.DeviceDefault = 1 LEFT OUTER JOIN
dbo.tbl602_CustPhone AS cpe ON cpe.CustomerId = c.Id AND cpe.Device = 4 AND cpe.DeviceDefault = 1 LEFT OUTER JOIN
dbo.tbl405_Employees AS emp ON dbo.fnc600S_GetPrimarySalespersonId(c.Id) = emp.Id OUTER APPLY
(SELECT top 1 GMTTimestamp from dbo.tbl121_DataLog dl where dl.PrimaryKey = c.Id and dl.DBActionId In (600001, 600002) order by dl.GMTTimeStamp DESC) dl
GO
CREATE VIEW [dbo].[viwUser_Customers_Custom]
AS
SELECT c.Id, c.Type, c.CustomerNo, sit.Code AS PrimarySiteCode, sit.Name AS PrimarySiteName, emp.Code AS PrimarySalespersonCode,
emp.Name AS PrimarySalespersonName, c.Title, c.FirstName, c.MiddleName, c.LastName, c.NickName, c.Greeting, c.PreviousName,
dbo.fncCustContactMethodText(c.PreferredContactMethod) AS PreferredContactMethod, cad.Address1, cad.Address2, cad.Address3, cad.City, cad.State,
cad.PostCode, cad.Country, cpp.CountryPrefix AS PhoneCountryPrefix, cpp.AreaCode AS PhoneAreaCode, cpp.Number AS PhoneNumber,
cpp.Extension AS PhoneExtension, dbo.fncCustFormattedPhoneNo(cpp.CountryPrefix, cpp.AreaCode, cpp.Number, cpp.Extension)
AS PhoneNumberFormatted, cpf.CountryPrefix AS FaxCountryPrefix, cpf.AreaCode AS FaxAreaCode, cpf.Number AS FaxNumber,
cpf.Extension AS FaxExtension, dbo.fncCustFormattedPhoneNo(cpf.CountryPrefix, cpf.AreaCode, cpf.Number, cpf.Extension) AS FaxNumberFormatted,
cpm.CountryPrefix AS MobileCountryPrefix, cpm.AreaCode AS MobileAreaCode, cpm.Number AS MobileNumber, cpm.Extension AS MobileExtension,
dbo.fncCustFormattedPhoneNo(cpm.CountryPrefix, cpm.AreaCode, cpm.Number, cpm.Extension) AS MobileNumberFormatted, cpe.EmailAddress, c.Sex,
c.UDFList1, c.UDFList2, c.UDFList3, c.UDFList4, c.UDFList5, c.UDFList6, c.UDFList7, c.UDFList8, c.UDFList9, c.UDFList10, c.UDFList11, c.UDFList12,
c.FingerSize1 AS LeftLittleFingerSize, c.FingerSize2 AS LeftRingFingerSize, c.FingerSize3 AS LeftMiddleFingerSize,
c.FingerSize4 AS LeftIndexFingerSize, c.FingerSize5 AS LeftThumbSize, c.FingerSize6 AS RightThumbSize, c.FingerSize7 AS RightIndexFingerSize,
c.FingerSize8 AS RightMiddleFingerSize, c.FingerSize9 AS RightRingFingerSize, c.FingerSize10 AS RightLittleFingerSize, c.LeftWristSize,
c.RightWristSize, c.NeckSize, c.SocSecNumber, c.POSComments, c.SecureComments, c.Status, c.Active, c.Day, c.Month, c.Year, dbo.fncGetDOB(c.Day,
c.Month, c.Year) AS BirthDate, dbo.fncGetAge(c.Day, c.Month, c.Year, c.Age, c.AgeReferenceDate, GETDATE()) AS Age, CAST('' AS varchar(1000))
AS MailingName, CAST('' AS varchar(1000)) AS GreetingName, CAST(0 AS money) AS CustomerSalesTotalForDateRange, CAST(0 AS money)
AS CustomerSalesTotal, CAST('' AS varchar(50)) AS PartnerCustomerNo, CAST('' AS varchar(20)) AS PartnerBirthDate, CAST(0 AS money)
AS PartnerSalesTotalForDateRange, CAST(0 AS money) AS PartnerSalesTotal, dbo.fnc604S_GetAnniversary(c.Id) as Anniversary,
Coalesce((Select Top 1 cd.DiscountPercent From tbl605_CustDiscount cd Where cd.CustomerId = c.Id),0) as DiscountPercent, (select dbo.fncUser_LastCustomerEditDate(c.CustomerNo)) as LastEdited
FROM dbo.tbl600_Customer AS c INNER JOIN
dbo.tbl103_Sites AS sit ON c.SiteId = sit.Id LEFT OUTER JOIN
dbo.tbl601_CustAddress AS cad ON cad.CustomerId = c.Id AND cad.[Default] = 1 LEFT OUTER JOIN
dbo.tbl602_CustPhone AS cpp ON cpp.CustomerId = c.Id AND cpp.Device = 1 AND cpp.DeviceDefault = 1 LEFT OUTER JOIN
dbo.tbl602_CustPhone AS cpf ON cpf.CustomerId = c.Id AND cpf.Device = 3 AND cpf.DeviceDefault = 1 LEFT OUTER JOIN
dbo.tbl602_CustPhone AS cpm ON cpm.CustomerId = c.Id AND cpm.Device = 2 AND cpm.DeviceDefault = 1 LEFT OUTER JOIN
dbo.tbl602_CustPhone AS cpe ON cpe.CustomerId = c.Id AND cpe.Device = 4 AND cpe.DeviceDefault = 1 LEFT OUTER JOIN
dbo.tbl405_Employees AS emp ON dbo.fnc600S_GetPrimarySalespersonId(c.Id) = emp.Id
GO
查询 SQL 视图时滞后为 1-2 分钟。
我找到了一种更简单的方法来实现这一点。只需添加一个 SQL 触发器以将带时间戳的行插入到补充表中。此表将具有适当的结构，以便更快地查找。