SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Speeding up my query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

masond
Constraint Violating Yak Guru

447 Posts

Posted - 10/15/2012 :  11:48:49  Show Profile  Reply with Quote
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

4614 Posts

Posted - 10/15/2012 :  13:16:33  Show Profile  Reply with Quote
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')
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2076 Posts

Posted - 10/16/2012 :  01:44:56  Show Profile  Visit jackv's Homepage  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/16/2012 :  09:16:53  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000