Please help optimize a SQL

Category: MS-SQL Server -> Applications Author: linyi0101 Date: 2001-07-08 20:19:22
 
linyi0101
2001-07-08 20:19:22
DP_DistributionEntries amount of data 300W +, I wrote this relatively inefficient SQL query time-consuming 25 seconds, experts optimize what
select
cid ,
(select District from DP_Cities where id = (select ParentId from DP_Cities where ID = clientCityId)) as 'provinces',
(select District from DP_Cities where ID = clientCityId) as 'ground-level city',
; ClientMobile as 'phone number',
ClientName as 'name',
ClientAddress as 'residence',
(select top 1 JoinTime from DP_DistributionEntries where Mobile = ClientMobile order by JoinTime asc) as 'the 1st date of purchase',
(select top 1 JoinTime from DP_DistributionEntries where Mobile = ClientMobile order by JoinTime desc) as 'the last 1 purchase date',
(select SUM (Distribution ) from DP_DistributionEntries where Mobile = ClientMobile) as 'total number of units purchased',
(select SUM (a.Distribution * b. Price) from DP_DistributionEntries a, DP_PriceEntries b where a.PDId = b.PDId and a.Mobile = ClientMobile and b.Mobile = ClientMobile) as 'total purchase amount'
from
DP_ClientInfo
jiaxiaochun
2001-07-08 20:36:22
1. according to the amount of data indexed (link fields and filter fields)
2. changed so much subquery table joins are better
hanou168
2001-07-08 20:52:13
unless there is no other way, to minimize the use of words in the select subquery .
kaoshishui
2001-07-08 20:58:42
1. first through the following statement creates a temporary table table expressions or subquery so
SELECT ClientMobile, Min (JoinTime) as' the first a second purchase date ', Max (JoinTime)' Finally a second purchase date ', SUM (Distribution) as' total number of units purchased'
FROM DP_DistributionEntries
GROUP BY ClientMobile
2. use the above sub-query associated DP_ClientInfo results
"provinces" and "prefecture-level city" can be directly used INNER JOIN to optimize
BLime
2001-07-08 21:04:17
subquery to join operations .
lyy890413
2001-07-08 21:23:42
According to the amount of data indexed (link fields and filter fields)
changed so much subquery table joins are better