September 23, 2008 at 11:31 am
I have this query:
SET STATISTICS TIME ON
SET STATISTICS IO ON
SELECT
CUR_CIS.CIS_ID,
CUR_MASTER_OR_CIS.CIS_ID as MASTER_OR_CIS_ID,
CUR_cur.cur_acct_num,
CUR_cur.ARRANGEMENT_CLASSIFICATION,
CUR_cur.CUR_ACCT_CRNCY_CODE,
srv.service_type_code, cust_agrt.CUST_AGRMNT_NUM as cust_agrmnt_num,
sum(rev.TRN_METRIC) as sum_volume,
max(rev.time_key) as latest_time_key,
count(distinct rev.time_key) as actual_months,
cast(null as numeric(11,2)) as actual_avg_monthly_volume,
cast(null as money) as rate_of_latest_month,
cast(null as numeric(11,6)) as std_rate,
cast(null as money) as std_fee,
cast(null as money) as fee
FROM
bap_prod.dbo.revenue_fact rev,
bap_prod.dbo.service_type_dim srv,
bap_prod.dbo.cis_customer_dim cis,--Edwin
bap_prod.dbo.cis_customer_dim CUR_cis,
bap_prod.dbo.cis_customer_dim CUR_MASTER_OR_CIS,
bap_prod.dbo.current_account_dim cur, --Edwin
bap_prod.dbo.current_account_dim CUR_cur,
bap_prod.dbo.CUST_AGRMNT_DIM cust_agrt
WHERE
(rev.CUST_AGRMNT_KEY=cust_agrt.CUST_AGRMNT_KEY)
and
(rev.time_key between 131-11 and 131)
and
(rev.service_type_key=srv.service_type_key)
and
srv.service_type_code in
(
select service_type_code from APA_Service_Type_Code
)
and
rev.cust_key=cis.cust_key
and
rev.cur_acct_key=cur.cur_acct_key
and
CUR_cis.cis_id=cis.cis_id
and
CUR_CIS.COMMRCL_CORP_TYPE in ('commercial', 'corporate')
and
isnull(CUR_cis.CUST_CLOSE_DATE,'9999-12-31')> '2008-08-01'--@current_date
and
CUR_cis.active_row_ind='Y'
and
CUR_MASTER_OR_CIS.cis_id=CUR_cis.master_or_cis_ID
and
CUR_MASTER_OR_CIS.active_row_ind='Y'
and
CUR_MASTER_OR_CIS.COMMRCL_CORP_TYPE in ('commercial', 'corporate')
and
isnull(CUR_MASTER_OR_CIS.CUST_CLOSE_DATE,'9999-12-31')> '2008-08-01'--@current_date
and
CUR_cur.active_row_ind='Y'
and
CUR_cur.cur_acct_num<>'XXXXXXXXXXXX'
and
isnull(CUR_cur.CUR_ACCT_CLOSE_DATE,'9999-12-31')> '2008-08-01' --@current_date
and
CUR_cur.cur_acct_num=cur.cur_acct_num
group by
CUR_CIS.CIS_ID,
CUR_MASTER_OR_CIS.CIS_ID,
CUR_cur.CUR_ACCT_CRNCY_CODE,
CUR_cur.cur_acct_num,
srv.service_type_code,
CUR_cur.ARRANGEMENT_CLASSIFICATION,
cust_agrt.CUST_AGRMNT_NUM
Former DBA wrote this.
I am trying to re-write it using INNER JOIN's
and see if performance can be increased.
What are the bottlenecks that I should be focusing on?
Here is STATS output:
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
(504994 row(s) affected)
Table 'CIS_CUSTOMER_DIM'. Scan count 54, logical reads 19439, physical reads 38, read-ahead reads 11175, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 2, logical reads 13737485, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CURRENT_ACCOUNT_DIM'. Scan count 10, logical reads 26991, physical reads 18, read-ahead reads 24626, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'APA_Service_Type_Code'. Scan count 1, logical reads 2, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SERVICE_TYPE_DIM'. Scan count 233, logical reads 1168, physical reads 1, read-ahead reads 11, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'REVENUE_FACT'. Scan count 5, logical reads 1359471, physical reads 0, read-ahead reads 1355241, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CUST_AGRMNT_DIM'. Scan count 5, logical reads 4864, physical reads 3, read-ahead reads 4770, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 783361 ms, elapsed time = 520644 ms.
Here is Schema of CIS_CUSTOMER_DIM
-------------------------------------
CREATE TABLE [dbo].[CIS_CUSTOMER_DIM](
[CUST_KEY] [int] IDENTITY(50,1) NOT NULL,
[CIS_KEY] [char](17) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CUST_TYPE_CODE] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[COMMRCL_CORP_TYPE] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[LAST_UPD_TS] [dbo].[LAST_UPT_TS] NOT NULL,
[CIS_ID] [char](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CUST_NAME] [varchar](81) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CUST_TYPE_NAME] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SCOTIA_CARD_NUM] [char](13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SIC_CODE] [char](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CUST_SOURCE_NAME] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EMPLOYEE_QTY] [dbo].[QUANTITY] NULL,
[PREF_LANG_NAME] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ANNUAL_SALE_AMT] [money] NULL,
[ADDR_STREET] [varchar](81) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ADDR_CITY_NAME] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ADDR_PROV_NAME] [dbo].[PROVINCE] NULL,
[ADDR_POST_CODE] [char](9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ADDR_CNTRY_CODE] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ADDR_CNTRY_NAME] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CARDS_NUM] [char](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MASTER_CIS] [char](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BRANCH_REP] [char](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BRANCH_REP2] [char](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CONTACT_NAME] [char](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CONTACT_TITLE] [char](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CONTACT_PHONE] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ADVERTIS_CODE] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ADVERTIS_DESC] [char](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DATE_ESTABLISH] [datetime] NULL,
[CUST_START_DATE] [datetime] NULL,
[CUST_CLOSE_DATE] [datetime] NULL,
[BRANCH_TRANSIT_NUM] [char](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BRANCH_NAME] [varchar](45) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IG_CODE] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BRANCH_MANAGER_CODE] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ACTIVE_ROW_IND] [dbo].[INDICATOR] NULL,
[CBI] [char](9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SC_REPORTING_FLAG] [bit] NULL CONSTRAINT [DF_cis_customer_dim_SC_REPORTING_FLAG] DEFAULT (0),
[MASTER_OR_CIS_ID] [char](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EMP_NUM] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DM_CIS_STATUS] [char](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ESM_MANAGER] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SC_REPORT_FLAG] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BORROWER] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Business_Unit] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Portfolio_Segment] [char](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ANNUAL_SALE_LAST_UPD_DATE] [datetime] NULL,
[GTB_ON_FLAG] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[GTB_ON_DATE] [datetime] NULL,
[GTB_ON_PRODUCT] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DUNS] [char](11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BUSINESS_LINE_SEGMENT] [nvarchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MISER_CUSTOMER_NUMBER] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CUSTOMER_GLOBAL_ID] [char](16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CUSTOMER_EMAIL] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MANAGER_INFORMATION] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [CIS_CUST_CUST_KEY] PRIMARY KEY CLUSTERED
(
[CUST_KEY] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 99) ON [cis_customer_dim]
) ON [cis_customer_dim]
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_bindrule @rulename=N'[dbo].[CHECK_YES_NO]', @objname=N'[dbo].[CIS_CUSTOMER_DIM].[ACTIVE_ROW_IND]' , @futureonly='futureonly'
GO
/****** Object: Statistic [_dta_stat_235968017_37_6_1] Script Date: 09/23/2008 13:28:40 ******/
CREATE STATISTICS [_dta_stat_235968017_37_6_1] ON [dbo].[CIS_CUSTOMER_DIM]([ACTIVE_ROW_IND], [CIS_ID], [CUST_KEY])
It has about 20 indexes but I still haven't figured it out how
to generate the script for all the indexes in the table.
I will find it out and update the posting...
Indexes in CIS_CUSTOMER_DIM
------------------------------
CREATE INDEX CIS_CUST_CIS_ID ON CIS_CUSTOMER_DIM(CIS_ID)
CREATE INDEX CIS_CUST_CIS_KEY ON CIS_CUSTOMER_DIM(CIS_KEY)
CREATE INDEX CIS_CUST_SIC_CODE ON CIS_CUSTOMER_DIM(SIC_CODE)
CREATE INDEX CIS_CUST_MASTER_CIS ON CIS_CUSTOMER_DIM(MASTER_CIS)
CREATE INDEX CIS_CUST_BRANCH_TRANSIT_NUM ON CIS_CUSTOMER_DIM(BRANCH_TRANSIT_NUM)
CREATE INDEX CIS_CUST_NAME ON CIS_CUSTOMER_DIM(CUST_NAME)
CREATE INDEX CIS_CUST_CBI ON CIS_CUSTOMER_DIM(CBI)
CREATE INDEX CIS_CUST_BRANCH_MANAGER_CODE ON CIS_CUSTOMER_DIM(BRANCH_MANAGER_CODE)
CREATE INDEX CIS_CUST_MASTER_OR_CIS_ID ON CIS_CUSTOMER_DIM(MASTER_OR_CIS_ID)
CREATE INDEX CIS_CUST_TYPE_CODE ON CIS_CUSTOMER_DIM(CUST_TYPE_CODE)
CREATE INDEX CIS_CUST_EMP_NUM ON CIS_CUSTOMER_DIM(EMP_NUM)
CREATE INDEX CIS_CUST_COMMRCL_CORP_TYPE ON CIS_CUSTOMER_DIM(COMMRCL_CORP_TYPE)
CREATE INDEX CIS_CUST_CLOSE_DATE ON CIS_CUSTOMER_DIM(CUST_CLOSE_DATE)
CREATE INDEX CIS_CUST_ACTIVE_ROW_IND ON CIS_CUSTOMER_DIM(ACTIVE_ROW_IND)
CREATE INDEX CIS_CUST_ANNUAL_SALE_AMT ON CIS_CUSTOMER_DIM(ANNUAL_SALE_AMT)
CREATE INDEX CIS_CUST_SC_REPORT_FLAG ON CIS_CUSTOMER_DIM(SC_REPORT_FLAG)
CREATE INDEX CIS_CUST_DM_CIS_STATUS ON CIS_CUSTOMER_DIM(DM_CIS_STATUS)
CREATE INDEX CIS_CUST_borrower ON CIS_CUSTOMER_DIM(BORROWER)
CREATE INDEX CIS_CUST_LAST_UPD_TS ON CIS_CUSTOMER_DIM(LAST_UPD_TS)
CREATE INDEX CIS_Business_unit ON CIS_CUSTOMER_DIM(Business_Unit)
CREATE INDEX CIS_portfolio_segment ON CIS_CUSTOMER_DIM(Portfolio_Segment)
CREATE INDEX CIS_DUNS ON CIS_CUSTOMER_DIM(DUNS)
CREATE INDEX CIS_SEGMENT ON CIS_CUSTOMER_DIM(BUSINESS_LINE_SEGMENT)
CREATE INDEX _dta_index_CIS_CUSTOMER_DIM_13_235968017__K6_K1_40 ON CIS_CUSTOMER_DIM(CIS_ID, CUST_KEY, MASTER_OR_CIS_ID)
CREATE INDEX _dta_index_CIS_CUSTOMER_DIM_13_235968017__K37_K6_7_31 ON CIS_CUSTOMER_DIM(ACTIVE_ROW_IND, CIS_ID, CUST_NAME, CUST_START_DATE)
September 23, 2008 at 11:42 am
Just as a knee jerk reaction, and with no sample data etc to go on, I'd think it was your isnull datetimes throwing off the use of appropriate indexes.
If it's truly a datetime column and it's NULL but you want it to be included anyhow you could write it as AND (column > '2008-08-01' OR column IS NULL)
From a quick glance, those are inner joins, Just not the ANSI 92 version of them. An outer join would look like table.column *= Table2.column or table.column =* Table2.column. They should be avoided due to undeterministic results when specified int he Where clause.
September 23, 2008 at 12:11 pm
Luke L,
Thanks for a quick response.
So you think INNER JOIN will not help much
if I replace all those
WHERE
t1.c1 = t2.c1
....
Like this?
FROM
bap_prod.dbo.revenue_fact rev
INNER JOIN bap_prod.dbo.CUST_AGRMNT_DIM cust_agrt
ON rev.CUST_AGRMNT_KEY = cust_agrt.CUST_AGRMNT_KEY
AND rev.time_key between 131-11 and 131
INNER JOIN bap_prod.dbo.service_type_dim srv
ON rev.service_type_key=srv.service_type_key
INNER JOIN PA.dbo.APA_Service_Type_Code apa
ON rev.service_type_code = apa.service_type_code
.....
September 23, 2008 at 12:20 pm
All that would do would perhaps provide a bit better readability for you.
If however some of the join conditions were added to the inner join specification you may see some performance benefits because the join happens before the filter so you may have a small subset of records to deal with... but much of that depends on your data and indexes so your mileage may vary. You could try to turn them all into inner joins and then and things to the join predicate that would make sense.
FROM bap_prod.dbo.revenue_fact rev
INNER JOIN bap_prod.dbo.CUST_AGRMNT_DIM cust_agrt
ON rev.CUST_AGRMNT_KEY=cust_agrt.CUST_AGRMNT_KEY)
and (rev.time_key between 131-11 and 131)
Also, I see things like
srv.service_type_code in
(
select service_type_code from APA_Service_Type_Code
)
You could try to re-write that as another inner join but I believe the biggest improvement would be getting rid of those ISNULL statements...
EDIT: I suppose if I'd read your response a bit more thoroughly I'd have seen you adding things to the join predicate. Yes that may help, but it also may not, it all depends on your indexes...
September 23, 2008 at 1:26 pm
Luke L,
What my STATS output tells?
Can you translate it for me please?
September 23, 2008 at 1:45 pm
I'm not an expert on looking at statistics and being able to tell you what they mean...
I do better with execution plans, so I'll just drop back and punt on that one. it seems like there is a lot of IO activity for this query, which could signify a possible bottle neck, but I'd take a look at the execution plan or perhaps run it through the DTA and see what kind of results you get back from it.
Grant has a great ebook on execution plans which you can read here and he covers the basics pretty well here in chapter 1.
http://www.simple-talk.com/sql/performance/execution-plan-basics/
September 23, 2008 at 1:57 pm
The original query returned 504,994 records.
I re-wrote it.
But my version returns 504,600 records
I have to figure out why it returns a different number or records.
My version:
SELECT
cis.CIS_ID,
cis.CIS_ID as MASTER_OR_CIS_ID,
cur.cur_acct_num,
cur.ARRANGEMENT_CLASSIFICATION,
cur.CUR_ACCT_CRNCY_CODE,
srv.service_type_code,
cust_agrt.CUST_AGRMNT_NUM as cust_agrmnt_num,
SUM(rev.TRN_METRIC) as sum_volume,
MAX(rev.time_key) as latest_time_key,
COUNT(distinct rev.time_key) as actual_months,
CAST(null as numeric(11,2)) as actual_avg_monthly_volume,
CAST(null as money) as rate_of_latest_month,
CAST(null as numeric(11,6)) as std_rate,
CAST(null as money) as std_fee,
CAST(null as money) as fee
FROM
bap_prod.dbo.revenue_fact rev
INNER JOIN
bap_prod.dbo.CUST_AGRMNT_DIM cust_agrt
ON rev.CUST_AGRMNT_KEY = cust_agrt.CUST_AGRMNT_KEY
AND rev.time_key between 131-11 and 131
INNER JOIN
bap_prod.dbo.service_type_dim srv
ON rev.service_type_key = srv.service_type_key
INNER JOIN
APA.dbo.APA_Service_Type_Code apa
ON srv.service_type_code = apa.service_type_code
INNER JOIN
bap_prod.dbo.cis_customer_dim cis
ON rev.cust_key = cis.cust_key AND cis.COMMRCL_CORP_TYPE in ('commercial', 'corporate')
AND ISNULL(cis.CUST_CLOSE_DATE,'9999-12-31') > '2008-08-01'
AND cis.active_row_ind = 'Y'
AND cis.COMMRCL_CORP_TYPE in ('commercial', 'corporate')
INNER JOIN
bap_prod.dbo.current_account_dim cur
ON rev.cur_acct_key = cur.cur_acct_key
AND cur.active_row_ind = 'Y'
AND cur.cur_acct_num <> 'XXXXXXXXXXXX'
AND ISNULL(cur.CUR_ACCT_CLOSE_DATE,'9999-12-31') > '2008-08-01'
group by
CIS.CIS_ID,
cur.CUR_ACCT_CRNCY_CODE,
cur.cur_acct_num,
srv.service_type_code,
cur.ARRANGEMENT_CLASSIFICATION,
cust_agrt.CUST_AGRMNT_NUM
September 23, 2008 at 2:08 pm
I'm more concerned at this point about READABILITY,
good LOGIC and try to AVOID REDUNDANCY.
Then I will take a closer look at Execution Plan.
From a quick view it looks like
79% - Table Scan on REVENUE_FACT
9% - Parallelllism / Repartitioning streams. Partition columns: REVENUE_FACT.CUST_KEY
Not sure what " Parallelllism / Repartitioning streams" means?
September 23, 2008 at 2:22 pm
you are grouping by one less column in your query, that could be the difference. Try pulling some of your join predicates back out into the where statement and see how you record count changes... perhaps you need a left join instead of an inner somewhere.
Also for performance I'd really be interested what kind of difference you see when you get rid of the ISNULLs they have to be killing your index usage right now. I'm referring to...
bap_prod.dbo.cis_customer_dim cis
ON rev.cust_key = cis.cust_key AND cis.COMMRCL_CORP_TYPE in ('commercial', 'corporate')
AND ISNULL(cis.CUST_CLOSE_DATE,'9999-12-31') > '2008-08-01'
-- Try this AND (cis.CUST_CLOSE_DATE > '2008-08-01' OR cis.CUST_CLOSE_DATE IS NULL)
AND cis.active_row_ind = 'Y'
AND cis.COMMRCL_CORP_TYPE in ('commercial', 'corporate')
INNER JOIN
bap_prod.dbo.current_account_dim cur
ON rev.cur_acct_key = cur.cur_acct_key
AND cur.active_row_ind = 'Y'
AND cur.cur_acct_num <> 'XXXXXXXXXXXX'
AND ISNULL(cur.CUR_ACCT_CLOSE_DATE,'9999-12-31') > '2008-08-01'
-- Try this AND (cur.CUR_ACCT_CLOSE_DATE > '2008-08-01' OR cur.CUR_ACCT_CLOSE_DATE IS NULL)
September 23, 2008 at 2:26 pm
September 23, 2008 at 10:11 pm
Have you tried using the Database Tuning Advisor (this is in the Query menu in Management Studio) ?
This will analyse the query and make suggestions about indexes that can improve the performance of the query and estimates how much improvement the suggests may make to the query
One caveat : the suggested indexes may impact the performance of updates.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply