| Author |
 Topic  |
|
|
masond
Posting Yak Master
241 Posts |
 Posted - 10/15/2012 : 11:48:49
|
Hey guys
This is my query, its currently taking 10mins to excute
Do you have any tips/tricks to speed up the time ?
Declare @date smalldatetime
set @Date = (select dateadd(MM,-2,max(hst_date_processed))
from dbo.Fact_Financial_History)
Declare @dateend smalldatetime
set @dateend = DATEADD(MM,+3,@date) - 1
select @dateend
--- Total Number of merchants --
SELECT
'Total number of Merchants', COUNT (distinct Dim_Outlet.ParentID)
FROM stg_FDMS_Card_Entitlements INNER JOIN
Dim_Outlet ON stg_FDMS_Card_Entitlements.FDMSAccountNo = Dim_Outlet.FDMSAccountNo
WHERE (stg_FDMS_Card_Entitlements.[Product Code] in ('75','78'))
and Dim_Outlet.Account_Status = '16'
union all
------Merchants added this quarter--
SELECT
'Merchants added this quarter', COUNT (distinct Dim_Outlet.ParentID)
FROM stg_FDMS_Card_Entitlements INNER JOIN
Dim_Outlet ON stg_FDMS_Card_Entitlements.FDMSAccountNo = Dim_Outlet.FDMSAccountNo
WHERE (stg_FDMS_Card_Entitlements.[Product Code] in ('75','78'))
and Dim_Outlet.Account_Status = '16'
and Open_Date between @date and @dateend
union all
------Merchants lost this quarter--
SELECT
'Merchants lost this quarter', COUNT (distinct Dim_Outlet.ParentID)
FROM stg_FDMS_Card_Entitlements
INNER JOIN Dim_Outlet ON stg_FDMS_Card_Entitlements.FDMSAccountNo = Dim_Outlet.FDMSAccountNo
WHERE (stg_FDMS_Card_Entitlements.[Product Code] in ('75','78'))
and Dim_Outlet.Account_Status <> '16'
and Cancel_Date between @date and @dateend
union all
SELECT 'Total Retail Locations' AS Expr1, COUNT(Dim_Outlet.FDMSAccountNo) AS [Total Retail Locations]
FROM Dim_Outlet INNER JOIN
stg_FDMS_Card_Entitlements ON Dim_Outlet.FDMSAccountNo = stg_FDMS_Card_Entitlements.FDMSAccountNo
WHERE (stg_FDMS_Card_Entitlements.[Product Code] = '78') AND (Dim_Outlet.Account_Status = '16') AND (Dim_Outlet.MCC_Code IN ('5013', '5021', '5045',
'5094', '5172', '5310', '5311', '5411', '5422', '5441', '5451', '5462', '5499', '5531', '5533', '5541', '5542', '5712', '5734', '5944', '5983', '8011', '8021',
'8031', '8041', '8042', '8043', '8049', '8050', '8062', '8071', '8099', '5137', '5139', '5611', '5621', '5631', '5641', '5651', '5661', '5681', '5691', '5697',
'5698', '5947'))
union All
SELECT 'Total Travel & Entertainment Locations',
count (distinct Dim_Outlet.FDMSAccountNo) as [Total Travel & Entertainment Locations]
FROM Dim_Outlet INNER JOIN
stg_FDMS_Card_Entitlements ON Dim_Outlet.FDMSAccountNo = stg_FDMS_Card_Entitlements.FDMSAccountNo
INNER JOIN Dim_MCC ON stg_FDMS_Card_Entitlements.MCCCode = Dim_MCC.MCC
where stg_FDMS_Card_Entitlements.[Product Code] ='78'
AND dbo.[Dim_Outlet].[Account_Status]='16'
AND dim_outlet.MCC_Code in ('5811'
,'5812'
,'5813'
,'5814'
,'5945')
or (stg_FDMS_Card_Entitlements.[Product Code] =N'78')
AND (dbo.[Dim_Outlet].[Account_Status]='16' )
and (dbo.Dim_MCC.industry_code in ( 'AL', 'HM'))
union all
SELECT 'Total Service Locations',
COUNT(DISTINCT Dim_Outlet.FDMSAccountNo) AS [Total Service Locations]
FROM Dim_Outlet INNER JOIN
stg_FDMS_Card_Entitlements ON Dim_Outlet.FDMSAccountNo = stg_FDMS_Card_Entitlements.FDMSAccountNo INNER JOIN
Dim_MCC ON stg_FDMS_Card_Entitlements.MCCCode = Dim_MCC.MCC
WHERE (stg_FDMS_Card_Entitlements.[Product Code] = '78')
AND (Dim_Outlet.Account_Status = '16')
AND (Dim_Outlet.MCC_Code IN ('8111', '8211', '8220', '8241', '8244', '8249', '8299', '8351', '8398', '8641',
'8651', '8661', '8675', '8699', '8734', '8911', '8931', '8999'))
OR(stg_FDMS_Card_Entitlements.[Product Code] = N'78')
AND (Dim_Outlet.Account_Status = '16')
AND (Dim_MCC.Industry_Code IN ('CS', 'AR', 'TR', 'UT', 'BS', 'SP', 'RP', 'AE', 'GS'))
union all
SELECT 'Total Ecommerce Locations',
Count(distinct Dim_Outlet.FDMSAccountNo) AS [Total Ecommerce Locations]
FROM stg_FDMS_Card_Entitlements
INNER JOIN Dim_Outlet ON stg_FDMS_Card_Entitlements.FDMSAccountNo = Dim_Outlet.FDMSAccountNo
WHERE (Dim_Outlet.DBA_Name Like '%www%'
AND dbo.[Dim_Outlet].[Account_Status]='16'
AND stg_FDMS_Card_Entitlements.[Product Code] ='78')
and TB_IND in ('w','x')
union all
SELECT 'Total Ecommerce Locations1',
Count(distinct Dim_Outlet.FDMSAccountNo) AS [Total Ecommerce Locations1]
FROM stg_FDMS_Card_Entitlements
INNER JOIN Dim_Outlet ON stg_FDMS_Card_Entitlements.FDMSAccountNo = Dim_Outlet.FDMSAccountNo
WHERE (Dim_Outlet.DBA_Name not like '%www%'
AND dbo.[Dim_Outlet].[Account_Status]='16'
AND stg_FDMS_Card_Entitlements.[Product Code] ='78')
and TB_IND in ('w')
union all
SELECT 'Total Other Locations',
Count( distinct Dim_Outlet.FDMSAccountNo) AS [Total Other Locations]
FROM Dim_Outlet INNER JOIN
stg_FDMS_Card_Entitlements ON Dim_Outlet.FDMSAccountNo = stg_FDMS_Card_Entitlements.FDMSAccountNo
INNER JOIN Dim_MCC ON stg_FDMS_Card_Entitlements.MCCCode = Dim_MCC.MCC
WHERE stg_FDMS_Card_Entitlements.[Product Code] ='78'
AND dbo.[Dim_Outlet].[Account_Status]='16'
AND dim_outlet.MCC_Code in ('5713'
,'5714'
,'5718'
,'5719'
,'5722'
,'5732'
,'5733'
,'5735'
,'5912'
,'5921'
,'5931'
,'5932'
,'5933'
,'5935'
,'5937'
,'5940'
,'5941'
,'5942'
,'5943'
,'5946'
,'5948'
,'5949'
,'5950'
,'5960'
,'5962'
,'5963'
,'5964'
,'5965'
,'5966'
,'5967'
,'5968'
,'5969'
,'5970'
,'5971'
,'5972'
,'5973'
,'5975'
,'5976'
,'5977'
,'5978'
,'5992'
,'5993'
,'5994'
,'5995'
,'5996'
,'5997'
,'5998'
,'5999'
,'2741'
,'2791'
,'2842'
,'5039'
,'5044'
,'5046'
,'5047'
,'5051'
,'5065'
,'5072'
,'5074'
,'5085'
,'5099'
,'5111'
,'5122'
,'5131'
,'5169'
,'5192'
,'5193'
,'5198'
,'5199'
,'5200'
,'5211'
,'5231'
,'5251'
,'5261'
,'5271'
,'5300'
,'5309'
,'5331'
,'5399'
,'5511'
,'5521'
,'5532'
,'5551'
,'5561'
,'5571'
,'5592'
,'5598'
,'5599'
,'5655'
,'5699'
,'3064'
,'3441'
,'3582'
,'5974'
,'6015'
,'6050'
,'6534')
union all
SELECT
'Outlets lost this quarter1', COUNT (distinct Dim_Outlet.FDMSAccountNo)
FROM stg_FDMS_Card_Entitlements
INNER JOIN Dim_Outlet ON stg_FDMS_Card_Entitlements.FDMSAccountNo = Dim_Outlet.FDMSAccountNo
WHERE (stg_FDMS_Card_Entitlements.[Product Code] in ('75','78'))
and Dim_Outlet.Account_Status <> '16'
and Cancel_Date between @date and @dateend
|
|
|
Lamprey
Flowing Fount of Yak Knowledge
3832 Posts |
Posted - 10/15/2012 : 13:16:33
|
This isn't a soltion per se, but you might be able to combine some queries. For example, you could combine the first three queries into one, then you could pivot the results in order to fit it into a similar format as you have now:SELECT
'Total number of Merchants',
COUNT (DISTINCT CASE WHEN Dim_Outlet.Account_Status = '16' THEN Dim_Outlet.ParentID ELSE NULL END) AS TotalNumberOfMerchants,
'Merchants added this quarter',
COUNT (DISTINCT CASE WHEN Dim_Outlet.Account_Status = '16' AND Open_Date between @date and @dateend THEN Dim_Outlet.ParentID ELSE NULL END) AS MerchantsAddedThisQuarter,
'Merchants lost this quarter',
COUNT (DISTINCT CASE WHEN Dim_Outlet.Account_Status <> '16' AND Cancel_Date between @date and @dateend THEN Dim_Outlet.ParentID ELSE NULL END) AS MerchantsLostThisQuarter,
FROM
stg_FDMS_Card_Entitlements
INNER JOIN
Dim_Outlet
ON stg_FDMS_Card_Entitlements.FDMSAccountNo = Dim_Outlet.FDMSAccountNo
WHERE
stg_FDMS_Card_Entitlements.[Product Code] in ('75','78')
|
 |
|
|
jackv
Flowing Fount of Yak Knowledge
United Kingdom
1771 Posts |
Posted - 10/16/2012 : 01:44:56
|
1) Look at the "WHERE Dim_Outlet.DBA_Name not like '%www%'" - that one will have to scan the full table.
2) Have you checked the Execution Plan , can you see anything that may look supicious - such as unexpected scans, nested loops etc
Jack Vamvas
--------------------
http://www.sqlserver-dba.com |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47157 Posts |
|
| |
Topic |
|
|
|
Speeding up my query
