从元组中获取结果的快速查询
本文关键字:查询 结果 元组 获取 | 更新日期: 2023-09-27 17:59:13
我有一个表,记录了两个有效的英国邮政编码与谷歌/必应之间的距离。在使用该系统时,会将此表添加到中,以便快速进行下一次距离查询,并且我不必调用web服务来在线检索距离。
以下是表格结构:
OID PostcodeA PostcodeB DistanceMeters DistanceMiles
-------------------- --------- --------- --------------------------------------- ---------------------------------------
1 BR60PS BT248DN 788847 490
2 BR60PS CM201JA 64426 40
3 BR60PS CM82AP 77640 48
4 BR60PS CO123AX 131617 82
5 BR60PS CT146EL 119366 74
6 BR60PS DA110TA 29247 18
7 BR60PS DE216AH 262570 163
8 BR60PS DL81AB 397524 247
9 BR60PS HG27JE 368802 229
10 BR60PS IP121AL 144394 90
11 BR60PS IP141AH 144183 90
12 BR60PS IP209AH 172259 107
现在我有一个Scalar UDF,我通过Linq到SQL使用它,它的定义如下:
ALTER FUNCTION [dbo].[GetDistanceFromCache]
(@PostcodeA VARCHAR (MAX), @PostcodeB VARCHAR (MAX))
RETURNS DECIMAL
AS
BEGIN
DECLARE @FoundDistance AS DECIMAL;
SELECT @FoundDistance = DistanceMiles
FROM AddressInfoRecordedDistance
WHERE (PostcodeA = @PostcodeA
AND PostcodeB = @PostcodeB)
OR (PostcodeB = @PostcodeA
AND PostcodeA = @PostcodeB);
RETURN ISNULL(@FoundDistance, -1);
END
我需要知道是否有更快的SQL语句或c#linq来获得结果?如果我检索800名员工并将其与50个作业进行比较,系统将进入暂停状态,如果我不将DBContext.GetDistanceFromCache()
添加到选择集中,所花费的时间将大大减少。
这是一个拖延的问题:
var query =
from locum in DbContext.Locums
where
locum.IsActive == true &&
locum.IsAdminMarkedComplete == true &&
locum.IsLocumsExciteBan == false &&
locum.IsGPHCBan == false &&
filterID1.Contains(locum.OID) == false &&
filterID2.Contains(locum.OID) == false
select new {
LocumID = locum.OID,
LocumName = locum.FirstName + " " + locum.LastName,
locum.MobileNumber,
locum.Email,
Gender = locum.Gender ? "Male" : "Female",
locum.DateofBirth,
LocumType = locum.LocumType.Name,
**Distance** = DbContext.GetDistanceFromCache(_Postcode, locum.AddressInfo.Postcode),
Address = String.Format("{0} {1} {2} {3}",
locum.AddressInfo.House.Length == 0 ? String.Empty : locum.AddressInfo.House + ", ",
locum.AddressInfo.Street.Length == 0 ? String.Empty : locum.AddressInfo.Street + ", ",
locum.AddressInfo.Area.Length == 0 ? String.Empty : locum.AddressInfo.Area + ", ",
locum.AddressInfo.Postcode ?? String.Empty),
Postcode = locum.AddressInfo.Postcode,
City = locum.AddressInfo.City.Name,
County = locum.AddressInfo.City.County.Name,
locum.SystemUserID
};
我认为您的查询很慢,因为每次LINQ查询执行select new { ... }
时都必须调用DB,这与结果行的次数一样多。
在匹配结果集之前,我会从表AddressInfoRecordedDistance
下载数据
var query = from locum in DbContext.Locums
where ...
select new { ... }; // Don't include GetDistanceFromCache here
var airds = from a in DbContext.AddressInfoRecordedDistance
select a;
foreach (var q in query)
q.Distance = GetDistanceFromCache(q.PostcodeA, q.PostcodeB, airds);
当然,你还需要GetDistanceFromCache
方法:
// The SQL UDF in C# code here (psuedo code, not tested, just "C blunt")
decimal GetDistanceFromCache(string PostcodeA, string PostcodeB, List<...> table)
{
return (from t in table
where
(t.PostcodeA == PostcodeA && t.PostcodeB == PostcodeB) ||
(t.PostcodeB == PostcodeA && t.PostcodeA == PostcodeB)
select t).FirstOrDefault().DistanceMiles;
}
当然,如果您多次运行以上所有操作,则应该缓存变量airds
。
如果您对距离查询的脏读数感到满意,则可以添加类似Nolock的表提示。有关详细信息,请参阅MSDN的表提示。
另一种选择是确保在AddressInRecordedDatabse
表上为PostcodeA
和PostcodeB
以及变量的数据类型&字段匹配,因此不存在隐式数据类型转换。