August 18, 2014 at 9:49 am
Hi!
I need some help on trying to create indexes on two tables:
SF_Affiliate_Customer
SF_Affiliate_Customer_Account
on which the following query is based. I need to build indexes so that the query will perform better. Now its very slow..
SELECT DISTINCT C.[afflt_cust_natl_key],[as_of_dt]
FROM [dbo].[SF_Affiliate_Customer] C
WHERE
( [afflt_intrnl_cust_ind] = 'N'
AND [afflt_empl_ind] = 'N'
AND (ISNULL([phys_addr_st_rgn_cd],'')<>'CA' AND ISNULL([mlng_addr_st_rgn_cd],'')<>'CA')
)AND
C.[AS_OF_DT] = (SELECT MAX([AS_OF_DT]) FROM [dbo].[SF_Affiliate_Customer_Account] )
AND C.[Cust_Name] IS NOT NULL
AND C.[afflt_cust_natl_key] NOT IN(
SELECT
CC1.[afflt_cust_natl_key]
FROM [dbo].[SF_Affiliate_Customer_Account] CA
LEFT JOIN [dbo].[SF_Affiliate_Customer] C ON C.[afflt_cust_natl_key] = CA.[afflt_cust_natl_key] AND C.[AS_OF_DT] = CA.[AS_OF_DT]
LEFT JOIN [dbo].[SF_Affiliate_Customer_Account] CCA ON CCA.[acct_natl_key] = CA.[acct_natl_key] AND CCA.[AS_OF_DT] = CA.[AS_OF_DT]
LEFT JOIN [dbo].[SF_Affiliate_Customer] CC ON CC.[afflt_cust_natl_key] = CCA.[afflt_cust_natl_key] AND CA.[AS_OF_DT] = CCA.[AS_OF_DT]
LEFT JOIN [dbo].[SF_Affiliate_Customer_Account] CCA1 ON CC.[afflt_cust_natl_key] = CCA1.[afflt_cust_natl_key] AND CC.[AS_OF_DT] = CCA1.[AS_OF_DT]
LEFT JOIN [dbo].[SF_Affiliate_Customer_Account] CCA2 ON CCA2.[acct_natl_key] = CCA1.[acct_natl_key] AND CCA2.[AS_OF_DT] = CCA1.[AS_OF_DT]
LEFT JOIN [dbo].[SF_Affiliate_Customer] CC1 ON CC1.[afflt_cust_natl_key] = CCA2.[afflt_cust_natl_key] AND CC1.[AS_OF_DT] = CCA2.[AS_OF_DT]
LEFT JOIN [dbo].[SF_Cust_Natl_Key_Lookup] LKP ON CC1.[afflt_cust_natl_key] = LKP.[afflt_cust_natl_key]
WHERE CA.[afflt_cust_natl_key] IN(
SELECT C.[afflt_cust_natl_key]
FROM [dbo].[SF_Affiliate_Customer] C
WHERE (
[afflt_empl_ind] = 'Y'
OR [afflt_intrnl_cust_ind] = 'Y')
AND C.[AS_OF_DT] = (SELECT MAX([AS_OF_DT]) FROM [dbo].[SF_Affiliate_Customer_Account] )
)
)
AND C.[afflt_cust_natl_key] NOT IN (
SELECT [afflt_cust_natl_key]
FROM [dbo].[SF_Affiliate_Customer]
WHERE [prmy_ofcr_cd] = '076005'
AND cust_rec_typ = 'I'
UNION ALL
SELECT [afflt_cust_natl_key]
FROM dbo.SF_Exclude_Customer)
AND C.[afflt_cust_natl_key] NOT IN(
SELECT [afflt_cust_natl_key]
FROM [dbo].[SF_Affiliate_Customer] C
WHERE C.cust_rec_typ = 'I'
AND actv_cust_ind = 'N'
AND prmy_ofcr_cd IS NULL
AND (
(phys_addr_line_1 like '1801 Main%')
OR
(phys_addr_line_1 like '4400 Post Oak%' and tax_stat_cd is null)
))
August 18, 2014 at 11:08 am
It looks like the query itself could use some work, but I won't start there.
Basic indexing strategies are to have your index keys be columns that are involved in filtering (Where or ON clauses), are in the ORDER BY or GROUP BY, and then include other columns in the SELECT to avoid lookups (sometimes). The other thing you have to be aware of is the overall load on your SQL Server. What other queries access these tables and how. Indexing for single queries is not usually the best way to implement indexing.
Take a look at some free tools out there like sp_BlitxIndex[/url] or Jason Strate's Index Analysis SP[/url].
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 18, 2014 at 11:36 am
I don't have enough permissions and to run queries against sys. Hence I posted the query hoping to get some specific pointers. I've read some article and tried creating some indexes but they are not helping with the performance. May be I need to revisit the query itself.
I know for sure when I remove the follwing where clause my query speeds up, so it is the culprit. But can't figure out what's wrong or could be changed with it.
---------------------------------------
AND C.[afflt_cust_natl_key] NOT IN(
SELECT
CC1.[afflt_cust_natl_key]
FROM [dbo].[SF_Affiliate_Customer_Account] CA
LEFT JOIN [dbo].[SF_Affiliate_Customer] C ON C.[afflt_cust_natl_key] = CA.[afflt_cust_natl_key] AND C.[AS_OF_DT] = CA.[AS_OF_DT]
LEFT JOIN [dbo].[SF_Affiliate_Customer_Account] CCA ON CCA.[acct_natl_key] = CA.[acct_natl_key] AND CCA.[AS_OF_DT] = CA.[AS_OF_DT]
LEFT JOIN [dbo].[SF_Affiliate_Customer] CC ON CC.[afflt_cust_natl_key] = CCA.[afflt_cust_natl_key] AND CA.[AS_OF_DT] = CCA.[AS_OF_DT]
LEFT JOIN [dbo].[SF_Affiliate_Customer_Account] CCA1 ON CC.[afflt_cust_natl_key] = CCA1.[afflt_cust_natl_key] AND CC.[AS_OF_DT] = CCA1.[AS_OF_DT]
LEFT JOIN [dbo].[SF_Affiliate_Customer_Account] CCA2 ON CCA2.[acct_natl_key] = CCA1.[acct_natl_key] AND CCA2.[AS_OF_DT] = CCA1.[AS_OF_DT]
LEFT JOIN [dbo].[SF_Affiliate_Customer] CC1 ON CC1.[afflt_cust_natl_key] = CCA2.[afflt_cust_natl_key] AND CC1.[AS_OF_DT] = CCA2.[AS_OF_DT]
LEFT JOIN [dbo].[SF_Cust_Natl_Key_Lookup] LKP ON CC1.[afflt_cust_natl_key] = LKP.[afflt_cust_natl_key]
WHERE CA.[afflt_cust_natl_key] IN(
SELECT C.[afflt_cust_natl_key]
FROM [dbo].[SF_Affiliate_Customer] C
WHERE (
[afflt_empl_ind] = 'Y'
OR [afflt_intrnl_cust_ind] = 'Y')
AND C.[AS_OF_DT] = (SELECT MAX([AS_OF_DT]) FROM [dbo].[SF_Affiliate_Customer_Account] )
)
)
---------------------------------------
August 18, 2014 at 11:38 am
Here's another script. This one provides a few different angles for helping analyze potential missing indexes. Use it in conjunction with one of the other scripts to try and prevent duplicate indexes.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply