Speeding up my query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Speeding up my query

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-10-15 : 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
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-10-15 : 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
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-10-16 : 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

52326 Posts

Posted - 2012-10-16 : 09:16:53

also consider suggestion to add a ,apping table which i gave here

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=179714

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources