我希望有人可以看看下面的我的查询。我在运行的网站上遇到了一些CPU问题,该网站在高峰时段降低了该站点的速度。
当用户注册系统时,该系统从具有180万行的邮政编码表中选择升级和北方值,我在此表上设置了一个索引,并相信它正在运行尽可能,但是我也有另一个查询当我在执行计划中查看时,表现出很高的成本(在某些部分中为28%)。
以下查询选择帐户详细信息,用户平均星级评级,还使用一个函数来计算登录的用户与所有结果之间的里程,以显示最近的第一个。
。您可以看到我把它们放在一起的方式,我的SQL还可以,但是有限的,因此,因为这是一个长期运行的问题。
SELECT tblAccounts.accountID ,
tblAccounts.city ,
tblAccounts.emailAddress ,
tblAccounts.paypalEmailAddress ,
tblAccounts.skypeUsername ,
tblAccounts.EA ,
tblAccounts.postcode ,
[onlineBootyUser].[fn_radialDistance](tblAccounts.EA, tblAccounts.NO,
@EA, @NO) AS miles ,
tblOrders.tableclothVisible ,
( SELECT AVG(starRating) AS averageRating
FROM onlinebootyUser.tblStarRating
WHERE ( accountID = onlinebootyUser.tblOrders.accountID )
) AS starRating
FROM tblOrders
INNER JOIN tblAccounts ON tblOrders.accountID = tblAccounts.accountID
WHERE ( tblAccounts.valid = 1 )
AND ( ( tblOrders.orderStatus = 'Completed' )
OR ( tblOrders.orderStatus = 'Pending' )
)
AND tblOrders.bootsaleDate = @bootsaleDate
AND tblOrders.itemsAllowed > 0
AND ( SELECT COUNT(*)
FROM tblItems
WHERE tblItems.accountID = tblAccounts.accountID
AND tblItems.bootsaleDate = @bootSaleDate
) > 0
ORDER BY miles ASC
函数fn_radialDistance
如下:
declare @d float;
set @d = power(square(@east1-@east2)+square(@north1-@north2),0.5E)/1609.0E
return @d
感谢您的查看!
这是您的查询格式,因此我可以阅读:
SELECT a.accountID, a.city, a.emailAddress, a.paypalEmailAddress, a.skypeUsername, a.EA,
a.postcode,
[onlineBootyUser].[fn_radialDistance](tblAccounts.EA, a.NO, @EA, @NO) AS miles,
o.tableclothVisible,
(SELECT AVG(starRating) AS averageRating
FROM onlinebootyUser.tblStarRating sr
WHERE sr.accountID = o.accountID
) AS starRating
FROM tblOrders o INNER JOIN
tblAccounts a
ON o.accountID = a.accountID
WHERE (a.valid = 1) AND (o.orderStatus in ('Completed', 'Pending')) AND
o.bootsaleDate = @bootsaleDate AND o.itemsAllowed > 0 AND
(SELECT count(*)
FROM tblItems i
WHERE i.accountID = a.accountID AND i.bootsaleDate = @bootSaleDate
) > 0
ORDER BY miles ASC
出现的问题是您的索引。以下索引应有所帮助:
tblItems(accountID, bootsaleDate);
tblStarRating(accountID, starRating);
您可以通过用exists
替换where
中的子查询:
exists (select 1
from tblItems i
where i.accountID = a.accountID and i.bootsaleDate = @bootSaleDate
)
这可能是一个较小的改进,除非匹配的数量很高。
除此之外,您还将做出执行计划。功能调用很可能会伤害性能。