Indexing

I have a table with 5 million rows. This table has 25 columns. I am writing
a view which use 7 of its columns in JOINs with other tables. To speed up
this view I am thinking about creating index(es). Should I create one index
which has those 7 columns or 7 indexes for each column? Do my joins should
follow the same order as the columns in the index?
Thanks"JY" <jy1970us@.yahoo.com> wrote in message
news:hOBnf.1019$El.172939@.news20.bellglobal.com...
>I have a table with 5 million rows. This table has 25 columns. I am writing
>a view which use 7 of its columns in JOINs with other tables. To speed up
>this view I am thinking about creating index(es). Should I create one index
>which has those 7 columns or 7 indexes for each column? Do my joins should
>follow the same order as the columns in the index?
> Thanks
>
Read up on how indexing works.
There are a lot of questions regarding their use.
For your specific example, I am making the following assumptions.
1. You don't do massive updates/inserts to this 5 million row table.
2. You are not doing any other queries on this table other than your view.
With that in mind, try it both ways and look at the execution plans.
A couple of notes:
When creating the index using all 7 columns in the join. The column index
order should be from most selective to least selective. (i.e. The first
column should have the most unique values in it, the second column should
have the next highest number of unique values in it. The last column should
have the fewest unique values in it).
As far as your join order, don't worry about it. SQLs Query Optimizer
should be able to figure it out.
Good luck.
Rick Sawtell
MCT, MCSD, MCDBA|||
"Rick Sawtell" <Quickening@.msn.com> wrote in message
news:O$SPOrAAGHA.208@.tk2msftngp13.phx.gbl...
> "JY" <jy1970us@.yahoo.com> wrote in message
> news:hOBnf.1019$El.172939@.news20.bellglobal.com...
> Read up on how indexing works.
> There are a lot of questions regarding their use.
> For your specific example, I am making the following assumptions.
> 1. You don't do massive updates/inserts to this 5 million row table.
> 2. You are not doing any other queries on this table other than your
> view.
> With that in mind, try it both ways and look at the execution plans.
> A couple of notes:
> When creating the index using all 7 columns in the join. The column index
> order should be from most selective to least selective. (i.e. The first
> column should have the most unique values in it, the second column should
> have the next highest number of unique values in it. The last column
> should have the fewest unique values in it).
> As far as your join order, don't worry about it. SQLs Query Optimizer
> should be able to figure it out.
>
>
> Good luck.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
>
Hi Rick,
Thanks for your suggestion. My query is taking 40.73 minutes to complete.
I created an index on those 7 columns with the column index order from most
selective to least selective. Now the query is taking 33.41 minutes which is
good but it is still taking a lot of time.
I run Index Tuning Wizard for my query and it suggests an index which
suggests an index. Creating it and using it, my query takes 37.89 minutes.
Can you suggest anything else?
Thanks|||when you say the joins use 7 of the columns do you mean
1) it joins on all 7 columns to each other table
(table1.col1 = table2.col1 and ... and table1.col7 = table2.col7)
or
2) or that only 7 of the columns are used in any of the joins
(join 1: table1.col1 = table2.col1, join 2: table1.col1 = table3.col1
and table1.col3 = table3.col3, etc.)
?
only if you will join on all 7 columns in at least one join, should you
create an index on all 7 columns [even then, consider the selectivity]
otherwise, examine your joins and choose the most selective ones for the
indexes. also, one index may be good for more than one set of joins -
e.g., join 1 is on col1 and col2, join 2 is on col1, col2 and col3 -- a
single index on col1, col2 and col3 will cover both (if col1 and col2
are the first 2 columns in the index...)
also, make sure the tables you are joining to are indexed properly to
match - otherwise performance won't increase all that much
[posting DDL for the tables (including existing keys and indexes) and
views would help]
JY wrote:
> I have a table with 5 million rows. This table has 25 columns. I am writin
g
> a view which use 7 of its columns in JOINs with other tables. To speed up
> this view I am thinking about creating index(es). Should I create one inde
x
> which has those 7 columns or 7 indexes for each column? Do my joins should
> follow the same order as the columns in the index?
> Thanks
>|||> Hi Rick,
> Thanks for your suggestion. My query is taking 40.73 minutes to complete.
> I created an index on those 7 columns with the column index order from
> most selective to least selective. Now the query is taking 33.41 minutes
> which is good but it is still taking a lot of time.
> I run Index Tuning Wizard for my query and it suggests an index which
> suggests an index. Creating it and using it, my query takes 37.89 minutes.
> Can you suggest anything else?
> Thanks
>
>
Are there indexes on the JOIN columns in the other tables that you are
joining to?
Take a look at the Estimated Execution Plan for your query and see what it
shows.
If you have TABLE SCANS, these columns are good candidates for an index.
It's hard to say without seeing your table structure, indexes and the query
itself.
Rick|||"Rick Sawtell" <Quickening@.msn.com> wrote in message
news:umXm37BAGHA.3372@.TK2MSFTNGP12.phx.gbl...
> Are there indexes on the JOIN columns in the other tables that you are
> joining to?
> Take a look at the Estimated Execution Plan for your query and see what it
> shows.
> If you have TABLE SCANS, these columns are good candidates for an index.
> It's hard to say without seeing your table structure, indexes and the
> query itself.
> Rick
>
ok Rick here is the information:
EXEC sp_addtype N'T_CODE', N'int', N'null'
GO
EXEC sp_addtype N'T_VALUE', N'int', N'null'
GO
EXEC sp_addtype N'T_IDENTIFIER', N'numeric(18,0)', N'not null'
GO
EXEC sp_addtype N'T_AMOUNT', N'decimal(10,2)', N'null'
GO
EXEC sp_addtype N'T_AMOUNT4', N'decimal(12,4)', N'null'
GO
EXEC sp_addtype N'T_LONG_NAME', N'varchar (50)', N'null'
GO
CREATE TABLE [ESTUB] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[PERIOD_NUMBER] [T_VALUE] NULL ,
[PAYGROUP] [T_CODE] NULL ,
[EMPLOYEE_ID] [T_IDENTIFIER] NULL ,
[LOCATION_CODE] [T_CODE] NULL ,
[DEPARTMENT_CODE] [T_CODE] NULL ,
[CHEQUE_TYPE] [T_CODE] NULL ,
[SEQUENCE] [varchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PERIOD_OVERRIDE] [T_VALUE] NULL ,
[CODE_TYPE] [T_CODE] NULL ,
[PAY_CODE] [T_CODE] NULL ,
[CURR_HOURS] [T_AMOUNT] NULL ,
[RATE] [T_AMOUNT4] NULL ,
[CURR_AMT] [T_AMOUNT] NULL ,
[MTD_HOURS] [T_AMOUNT] NULL ,
[MTD_AMT] [T_AMOUNT] NULL ,
[YTD_HOURS] [T_AMOUNT] NULL ,
[YTD_AMT] [T_AMOUNT] NULL ,
[ACCOUNT_NUMBER] [T_LONG_NAME] NULL ,
[SORT_ORDER] [T_VALUE] NULL ,
[PAYFREQCODE] [T_CODE] NULL ,
[DISP_STATUS] [T_CODE] NULL ,
[TRANS_CODE] [T_CODE] NULL ,
CONSTRAINT [PK_ESTUB_BCK] PRIMARY KEY NONCLUSTERED
(
[ID]
) WITH FILLFACTOR = 100 ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE INDEX [IX_ESTUB] ON [dbo].[ESTUB]([EMPLOYEE_ID], [PAY_CODE],
[PERIOD_NUMBER], [PAYGROUP], [PAYFREQCODE], [CHEQUE_TYPE]) ON [PRIMARY]
GO
CREATE TABLE [PAYROLL_GROUPING] (
[PAYGROUP] [smallint] NOT NULL ,
[COMPANY_ID] [numeric](18, 0) NOT NULL ,
[PAYROLL_NUMBER] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[PAYFREQCODE] [smallint] NOT NULL ,
CONSTRAINT [PK_PAYROLL_GROUPING] PRIMARY KEY NONCLUSTERED
(
[PAYGROUP]
) WITH FILLFACTOR = 100 ON [PRIMARY] ,
CONSTRAINT [IX_PAYROLL_GROUPING] UNIQUE NONCLUSTERED
(
[COMPANY_ID],
[PAYROLL_NUMBER],
[PAYFREQCODE]
) WITH FILLFACTOR = 100 ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX [IX_PAYGROUP] ON
[dbo].[PAYROLL_GROUPING]([PAYGROUP]) WITH FILLFACTOR = 100 ON [PRIMARY]
GO
CREATE TABLE [EMPLOYEE_EXT] (
[EMPLOYEE_ID] [T_IDENTIFIER] NOT NULL ,
[PAYFREQCODE] [T_CODE] NULL ,
[JOB] [T_CODE] NULL ,
[ROE_REASON] [T_CODE] NULL ,
[ROE_STATUS] [T_CODE] NULL ,
[WCBCODE] [T_CODE] NULL ,
[SPLITCODE] [T_CODE] NULL ,
[COMMISSION] [T_CODE] NULL ,
[PERIOD_OVERRIDE] [T_VALUE] NULL ,
[EMP_EXTCODE] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PAYGROUP] [T_CODE] NULL ,
[DELETED_FLAG] [int] NULL CONSTRAINT [DF_EMPLOYEE_EXT_DELETED_FLAG] DEFAULT
(0),
CONSTRAINT [PK_EMPLOYEE_EXT] PRIMARY KEY NONCLUSTERED
(
[EMPLOYEE_ID]
) WITH FILLFACTOR = 100 ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [SYSTEM_CODE_TABLE] (
[CODE_TYPE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CODE_VALUE] [int] NOT NULL ,
[LANG_TYPE] [int] NOT NULL ,
[SHORT_DESC] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DESCRIPTION] [varchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ACTIVE] [bit] NOT NULL ,
[SORT] [int] NULL ,
[REFERENCE] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EXT_CODE] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_SYSTEM_CODE_TABLE] PRIMARY KEY NONCLUSTERED
(
[CODE_TYPE],
[CODE_VALUE],
[LANG_TYPE]
) WITH FILLFACTOR = 100 ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [PAYROLL_PERIOD_EXT] (
[PERIOD_NUMBER] [int] NOT NULL ,
[paygroup_code] [int] NULL ,
[PAYFREQCODE] [int] NULL ,
[START_DATE] [datetime] NULL ,
[END_DATE] [datetime] NULL ,
[EXT_PERIOD] [smallint] NULL ,
[PAY_CYCLE] [smallint] NULL ,
[DEDUCTION_CYCLE] [smallint] NULL ,
[CHEQUE_DATE] [datetime] NULL ,
[PROCESS_DATE] [datetime] NULL ,
CONSTRAINT [PK_PAYROLL_PERIOD_EXT] PRIMARY KEY CLUSTERED
(
[PERIOD_NUMBER]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [PAY_REQUEST] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[PAYGROUP_CODE] [smallint] NULL ,
[COMPANY_ID] [numeric](18, 0) NULL ,
[PAYROLL_NUMBER] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PERIOD_NUMBER] [int] NULL ,
[REPORT_GENERATE] [numeric](1, 0) NULL ,
[PURGE_FISCALMEMOS] [numeric](1, 0) NULL ,
[RUN_TYPE] [numeric](1, 0) NULL ,
[PRINT_TIMECARDS] [numeric](1, 0) NULL ,
[SPECIAL_REPORTS] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[CONSOLIDATION_REPORT] [numeric](1, 0) NULL ,
[USER_FIELD] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FREQUENCY_CODE] [int] NULL ,
[PAY_DATE] [datetime] NULL ,
[NEWPERIOD_CLEAR] [numeric](1, 0) NULL ,
[PAY_CYCLE] [numeric](1, 0) NULL ,
[DEDUCTION_CYCLE] [numeric](1, 0) NULL ,
[GENERATE_DATE] [datetime] NULL ,
[PROCESS_DATE] [datetime] NULL ,
[YEAR_END] [int] NULL ,
CONSTRAINT [PK_PAY_REQUEST] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE INDEX [IX_PAY_REQUEST] ON [dbo].[PAY_REQUEST]([PERIOD_NUMBER],
[RUN_TYPE]) ON [PRIMARY]
GO
And the Query is
SELECT
CAST(pg.PAYROLL_NUMBER AS varchar(10)) + '$' + ee.EMP_EXTCODE AS
EmployeeKey,
CAST(pg.PAYROLL_NUMBER AS varchar(10)) AS CompanyId,
CAST(ee.EMP_EXTCODE AS varchar(10)) AS EmployeeId,
ctpf.DESCRIPTION AS Frequency,
YEAR(ppe.cheque_date) as Year,
MONTH(ppe.cheque_date) as Month,
CAST(ppe.EXT_PERIOD AS varchar(10)) AS Period_Number,
CONVERT(varchar(10),ppe.start_date,101) AS Start_Date,
CONVERT(varchar(10),ppe.end_date,101) AS End_Date,
CONVERT(varchar(10),ppe.cheque_date,101) AS Cheque_Date,
ctct.DESCRIPTION AS Cheque_Type,
ctrt.Description AS Run_Type,
CAST(ISNULL(es.sequence,'') AS varchar(10)) AS Sequence,
ctp.EXT_CODE AS Hed,
ctp.DESCRIPTION AS Hed_Description,
isNull(es.curr_hours, 00.00) AS Curr_Hours,
es.rate AS Rate,
isNull(es.curr_amt, 00.00) AS Curr_Amt,
isNull(es.mtd_hours, 00.00) AS Mtd_Hours,
isNull(es.mtd_amt, 00.00) AS Mtd_Amt,
isNull(es.ytd_hours, 00.00) AS Ytd_Hours,
isNull(es.ytd_amt, 00.00) AS Ytd_Amt,
CAST(ISNULL(es.trans_code,0) AS varchar(10)) AS Trans_Code
FROM dbo.estub es with(nolock)
INNER JOIN
dbo.payroll_grouping pg with (nolock)
ON es.PAYGROUP = pg.PAYGROUP
INNER JOIN
dbo.employee_ext ee with (nolock)
ON es.EMPLOYEE_ID = ee.EMPLOYEE_ID
INNER JOIN
dbo.system_code_table ctpf with (nolock)
ON es.PAYFREQCODE = ctpf.CODE_VALUE
AND ctpf.CODE_TYPE = 'payfreq'
AND ctpf.LANG_TYPE = 1
INNER JOIN
dbo.payroll_period_ext ppe with (nolock)
ON es.PERIOD_NUMBER = ppe.PERIOD_NUMBER
INNER JOIN
dbo.system_code_table ctct with (nolock)
ON es.CHEQUE_TYPE = ctct.CODE_VALUE
AND ctct.CODE_TYPE = 'chequetype'
AND ctct.LANG_TYPE = 1
INNER JOIN
dbo.system_code_table ctp with (nolock)
ON es.PAY_CODE = ctp.CODE_VALUE
AND ctp.CODE_TYPE = 'paycode'
AND ctp.LANG_TYPE = 1
INNER JOIN
dbo.pay_request pr with (nolock)
ON pr.period_number = es.PERIOD_NUMBER
INNER JOIN
dbo.system_code_table ctrt with (nolock)
ON pr.RUN_TYPE = ctrt.CODE_VALUE
AND ctrt.CODE_TYPE = 'runtype'
AND ctrt.LANG_TYPE = 1|||"Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
news:uPflI7BAGHA.664@.TK2MSFTNGP10.phx.gbl...
> when you say the joins use 7 of the columns do you mean
> 1) it joins on all 7 columns to each other table
> (table1.col1 = table2.col1 and ... and table1.col7 = table2.col7)
> or
> 2) or that only 7 of the columns are used in any of the joins
> (join 1: table1.col1 = table2.col1, join 2: table1.col1 = table3.col1 and
> table1.col3 = table3.col3, etc.)
> ?
> only if you will join on all 7 columns in at least one join, should you
> create an index on all 7 columns [even then, consider the selectivity]
> otherwise, examine your joins and choose the most selective ones for the
> indexes. also, one index may be good for more than one set of joins -
> e.g., join 1 is on col1 and col2, join 2 is on col1, col2 and col3 -- a
> single index on col1, col2 and col3 will cover both (if col1 and col2 are
> the first 2 columns in the index...)
> also, make sure the tables you are joining to are indexed properly to
> match - otherwise performance won't increase all that much
> [posting DDL for the tables (including existing keys and indexes) and
> views would help]
>
here is the information:
EXEC sp_addtype N'T_CODE', N'int', N'null'
GO
EXEC sp_addtype N'T_VALUE', N'int', N'null'
GO
EXEC sp_addtype N'T_IDENTIFIER', N'numeric(18,0)', N'not null'
GO
EXEC sp_addtype N'T_AMOUNT', N'decimal(10,2)', N'null'
GO
EXEC sp_addtype N'T_AMOUNT4', N'decimal(12,4)', N'null'
GO
EXEC sp_addtype N'T_LONG_NAME', N'varchar (50)', N'null'
GO
CREATE TABLE [ESTUB] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[PERIOD_NUMBER] [T_VALUE] NULL ,
[PAYGROUP] [T_CODE] NULL ,
[EMPLOYEE_ID] [T_IDENTIFIER] NULL ,
[LOCATION_CODE] [T_CODE] NULL ,
[DEPARTMENT_CODE] [T_CODE] NULL ,
[CHEQUE_TYPE] [T_CODE] NULL ,
[SEQUENCE] [varchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PERIOD_OVERRIDE] [T_VALUE] NULL ,
[CODE_TYPE] [T_CODE] NULL ,
[PAY_CODE] [T_CODE] NULL ,
[CURR_HOURS] [T_AMOUNT] NULL ,
[RATE] [T_AMOUNT4] NULL ,
[CURR_AMT] [T_AMOUNT] NULL ,
[MTD_HOURS] [T_AMOUNT] NULL ,
[MTD_AMT] [T_AMOUNT] NULL ,
[YTD_HOURS] [T_AMOUNT] NULL ,
[YTD_AMT] [T_AMOUNT] NULL ,
[ACCOUNT_NUMBER] [T_LONG_NAME] NULL ,
[SORT_ORDER] [T_VALUE] NULL ,
[PAYFREQCODE] [T_CODE] NULL ,
[DISP_STATUS] [T_CODE] NULL ,
[TRANS_CODE] [T_CODE] NULL ,
CONSTRAINT [PK_ESTUB_BCK] PRIMARY KEY NONCLUSTERED
(
[ID]
) WITH FILLFACTOR = 100 ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE INDEX [IX_ESTUB] ON [dbo].[ESTUB]([EMPLOYEE_ID], [PAY_CODE],
[PERIOD_NUMBER], [PAYGROUP], [PAYFREQCODE], [CHEQUE_TYPE]) ON [PRIMARY]
GO
CREATE TABLE [PAYROLL_GROUPING] (
[PAYGROUP] [smallint] NOT NULL ,
[COMPANY_ID] [numeric](18, 0) NOT NULL ,
[PAYROLL_NUMBER] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[PAYFREQCODE] [smallint] NOT NULL ,
CONSTRAINT [PK_PAYROLL_GROUPING] PRIMARY KEY NONCLUSTERED
(
[PAYGROUP]
) WITH FILLFACTOR = 100 ON [PRIMARY] ,
CONSTRAINT [IX_PAYROLL_GROUPING] UNIQUE NONCLUSTERED
(
[COMPANY_ID],
[PAYROLL_NUMBER],
[PAYFREQCODE]
) WITH FILLFACTOR = 100 ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX [IX_PAYGROUP] ON
[dbo].[PAYROLL_GROUPING]([PAYGROUP]) WITH FILLFACTOR = 100 ON [PRIMARY]
GO
CREATE TABLE [EMPLOYEE_EXT] (
[EMPLOYEE_ID] [T_IDENTIFIER] NOT NULL ,
[PAYFREQCODE] [T_CODE] NULL ,
[JOB] [T_CODE] NULL ,
[ROE_REASON] [T_CODE] NULL ,
[ROE_STATUS] [T_CODE] NULL ,
[WCBCODE] [T_CODE] NULL ,
[SPLITCODE] [T_CODE] NULL ,
[COMMISSION] [T_CODE] NULL ,
[PERIOD_OVERRIDE] [T_VALUE] NULL ,
[EMP_EXTCODE] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PAYGROUP] [T_CODE] NULL ,
[DELETED_FLAG] [int] NULL CONSTRAINT [DF_EMPLOYEE_EXT_DELETED_FLAG] DEFAULT
(0),
CONSTRAINT [PK_EMPLOYEE_EXT] PRIMARY KEY NONCLUSTERED
(
[EMPLOYEE_ID]
) WITH FILLFACTOR = 100 ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [SYSTEM_CODE_TABLE] (
[CODE_TYPE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CODE_VALUE] [int] NOT NULL ,
[LANG_TYPE] [int] NOT NULL ,
[SHORT_DESC] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DESCRIPTION] [varchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ACTIVE] [bit] NOT NULL ,
[SORT] [int] NULL ,
[REFERENCE] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EXT_CODE] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_SYSTEM_CODE_TABLE] PRIMARY KEY NONCLUSTERED
(
[CODE_TYPE],
[CODE_VALUE],
[LANG_TYPE]
) WITH FILLFACTOR = 100 ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [PAYROLL_PERIOD_EXT] (
[PERIOD_NUMBER] [int] NOT NULL ,
[paygroup_code] [int] NULL ,
[PAYFREQCODE] [int] NULL ,
[START_DATE] [datetime] NULL ,
[END_DATE] [datetime] NULL ,
[EXT_PERIOD] [smallint] NULL ,
[PAY_CYCLE] [smallint] NULL ,
[DEDUCTION_CYCLE] [smallint] NULL ,
[CHEQUE_DATE] [datetime] NULL ,
[PROCESS_DATE] [datetime] NULL ,
CONSTRAINT [PK_PAYROLL_PERIOD_EXT] PRIMARY KEY CLUSTERED
(
[PERIOD_NUMBER]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [PAY_REQUEST] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[PAYGROUP_CODE] [smallint] NULL ,
[COMPANY_ID] [numeric](18, 0) NULL ,
[PAYROLL_NUMBER] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PERIOD_NUMBER] [int] NULL ,
[REPORT_GENERATE] [numeric](1, 0) NULL ,
[PURGE_FISCALMEMOS] [numeric](1, 0) NULL ,
[RUN_TYPE] [numeric](1, 0) NULL ,
[PRINT_TIMECARDS] [numeric](1, 0) NULL ,
[SPECIAL_REPORTS] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[CONSOLIDATION_REPORT] [numeric](1, 0) NULL ,
[USER_FIELD] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FREQUENCY_CODE] [int] NULL ,
[PAY_DATE] [datetime] NULL ,
[NEWPERIOD_CLEAR] [numeric](1, 0) NULL ,
[PAY_CYCLE] [numeric](1, 0) NULL ,
[DEDUCTION_CYCLE] [numeric](1, 0) NULL ,
[GENERATE_DATE] [datetime] NULL ,
[PROCESS_DATE] [datetime] NULL ,
[YEAR_END] [int] NULL ,
CONSTRAINT [PK_PAY_REQUEST] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE INDEX [IX_PAY_REQUEST] ON [dbo].[PAY_REQUEST]([PERIOD_NUMBER],
[RUN_TYPE]) ON [PRIMARY]
GO
And the Query is
SELECT
CAST(pg.PAYROLL_NUMBER AS varchar(10)) + '$' + ee.EMP_EXTCODE AS
EmployeeKey,
CAST(pg.PAYROLL_NUMBER AS varchar(10)) AS CompanyId,
CAST(ee.EMP_EXTCODE AS varchar(10)) AS EmployeeId,
ctpf.DESCRIPTION AS Frequency,
YEAR(ppe.cheque_date) as Year,
MONTH(ppe.cheque_date) as Month,
CAST(ppe.EXT_PERIOD AS varchar(10)) AS Period_Number,
CONVERT(varchar(10),ppe.start_date,101) AS Start_Date,
CONVERT(varchar(10),ppe.end_date,101) AS End_Date,
CONVERT(varchar(10),ppe.cheque_date,101) AS Cheque_Date,
ctct.DESCRIPTION AS Cheque_Type,
ctrt.Description AS Run_Type,
CAST(ISNULL(es.sequence,'') AS varchar(10)) AS Sequence,
ctp.EXT_CODE AS Hed,
ctp.DESCRIPTION AS Hed_Description,
isNull(es.curr_hours, 00.00) AS Curr_Hours,
es.rate AS Rate,
isNull(es.curr_amt, 00.00) AS Curr_Amt,
isNull(es.mtd_hours, 00.00) AS Mtd_Hours,
isNull(es.mtd_amt, 00.00) AS Mtd_Amt,
isNull(es.ytd_hours, 00.00) AS Ytd_Hours,
isNull(es.ytd_amt, 00.00) AS Ytd_Amt,
CAST(ISNULL(es.trans_code,0) AS varchar(10)) AS Trans_Code
FROM dbo.estub es with(nolock)
INNER JOIN
dbo.payroll_grouping pg with (nolock)
ON es.PAYGROUP = pg.PAYGROUP
INNER JOIN
dbo.employee_ext ee with (nolock)
ON es.EMPLOYEE_ID = ee.EMPLOYEE_ID
INNER JOIN
dbo.system_code_table ctpf with (nolock)
ON es.PAYFREQCODE = ctpf.CODE_VALUE
AND ctpf.CODE_TYPE = 'payfreq'
AND ctpf.LANG_TYPE = 1
INNER JOIN
dbo.payroll_period_ext ppe with (nolock)
ON es.PERIOD_NUMBER = ppe.PERIOD_NUMBER
INNER JOIN
dbo.system_code_table ctct with (nolock)
ON es.CHEQUE_TYPE = ctct.CODE_VALUE
AND ctct.CODE_TYPE = 'chequetype'
AND ctct.LANG_TYPE = 1
INNER JOIN
dbo.system_code_table ctp with (nolock)
ON es.PAY_CODE = ctp.CODE_VALUE
AND ctp.CODE_TYPE = 'paycode'
AND ctp.LANG_TYPE = 1
INNER JOIN
dbo.pay_request pr with (nolock)
ON pr.period_number = es.PERIOD_NUMBER
INNER JOIN
dbo.system_code_table ctrt with (nolock)
ON pr.RUN_TYPE = ctrt.CODE_VALUE
AND ctrt.CODE_TYPE = 'runtype'
AND ctrt.LANG_TYPE = 1