I've 2 database as following
1- eALBUM
2- ePENYATA
In eALBUM, I've table as following
CREATE TABLE [dbo].[pusat](
[Kod_pusat] [nvarchar](4) NULL,
[Keterangan_pusat] [nvarchar](50) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Albmas](
[NOMBOR_GAJI] [nvarchar](6) NOT NULL,
[STATUS] [nvarchar](2) NULL,
[NAMA] [nvarchar](40) NULL,
[JANTINA] [nvarchar](2) NULL,
[TARAF_PERKAHWINAN] [nvarchar](1) NULL,
[BIL_ANAK] [nvarchar](1) NULL,
[KETURUNAN] [nvarchar](2) NULL,
[UGAMA] [nvarchar](2) NULL,
[WARGANEGARA] [nvarchar](2) NULL,
[TARIKH_LAHIR] [smalldatetime] NULL,
[TARIKH_MULA_KHIDMAT] [smalldatetime] NULL,
[JENIS_LANTEKAN] [nvarchar](2) NULL,
[GRED_GAJI] [nvarchar](6) NULL,
[TARIKH_SAH_KHIDMAT] [smalldatetime] NULL,
[TARIKH_BERHENTI] [smalldatetime] NULL,
[KOD_NEGERI_LAHIR] [nvarchar](2) NULL,
[TKH_LANTIK_GRED] [datetime] NULL,
[TKH_SAH_GRED_SEMASA] [datetime] NULL,
[NO_K/P] [nvarchar](12) NULL,
[JENIS_KONTREK] [nvarchar](2) NULL,
[MULA_KONTREK] [datetime] NULL,
[AKHIR_KONTREK] [smalldatetime] NULL,
[KOD_PUSAT] [nvarchar](4) NULL,
[EMPLOY_STATUS] [nvarchar](2) NULL,
[BIL_PGKT_SSB] [nvarchar](2) NULL,
[BIL_TANGGA_SSB] [nvarchar](2) NULL,
[JENIS_KWSP] [nvarchar](2) NULL,
[TARIKH_TUKAR_PENCEN] [smalldatetime] NULL,
[KETERANGAN_PUSAT] [nvarchar](40) NULL,
[KETERANGAN_JAWATAN] [nvarchar](40) NULL,
[ADDRESS1] [nvarchar](40) NULL,
[ADDRESS2] [nvarchar](40) NULL,
[ADDRESS3] [nvarchar](40) NULL,
[ADDRESS4] [nvarchar](40) NULL,
[POSKOD] [nvarchar](5) NULL,
[KGT] [varchar](2) NULL,
[Nota] [ntext] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
In ePENYATA, I've table as following
CREATE TABLE [dbo].[tbl_Penyata](
[idx] [int] IDENTITY(-2147483648,1) NOT NULL,
[nogaji] [char](6) NOT NULL,
[TkhTransaksi] [date] NOT NULL,
[NoSiri] [varchar](6) NULL,
[kodBank] [char](6) NOT NULL,
[AkaunBank] [varchar](16) NOT NULL,
[singkatan_bank] [nvarchar](10) NULL,
[AktPst] [char](4) NOT NULL,
[edtype] [char](2) NOT NULL,
[edcode] [char](4) NOT NULL,
[eln_payslip] [varchar](30) NULL,
[edAmount] [decimal](10, 2) NOT NULL,
CONSTRAINT [pk_tbl_penyata] PRIMARY KEY CLUSTERED
(
[idx] ASC,
[nogaji] ASC,
[TkhTransaksi] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [ps_TkhTransaksi]([TkhTransaksi])
) ON [ps_TkhTransaksi]([TkhTransaksi])
/*
tbl_Penyata using partitioned tables. This is the info
object_id TbName index_name index_type_desc partition_scheme data_space_id function_name function_id
----------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------- -------------------------------------------------------------------------------------------------------------------------------- -----------
1266103551 tbl_Penyata dbo.tbl_Penyata_UQ1 NONCLUSTERED ps_TkhTransaksi 65601 pf_TkhTransaksi 65536
1266103551 tbl_Penyata dbo.tbl_Penyata_UQ2 NONCLUSTERED ps_TkhTransaksi 65601 pf_TkhTransaksi 65536
1266103551 tbl_Penyata dbo.tbl_Penyata_UQ3 NONCLUSTERED ps_TkhTransaksi 65601 pf_TkhTransaksi 65536
1266103551 tbl_Penyata pk_tbl_penyata CLUSTERED ps_TkhTransaksi 65601 pf_TkhTransaksi 65536
object_id TbName index_id partition_number rows index_name index_type_desc data_space_id FILEGROUP_NAME function_id Pf_Name type_desc boundary_value_on_right destination_data_space_id parameter_id value
----------- -------------------------------------------------------------------------------------------------------------------------------- ----------- ---------------- -------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------ ------------- -------------------------------------------------------------------------------------------------------------------------------- ----------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------ ----------------------- ------------------------- ------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1266103551 tbl_Penyata 1 1 166296 pk_tbl_penyata CLUSTERED 65601 PRIMARY 65536 pf_TkhTransaksi RANGE 1 1 1 2012-06-01 00:00:00.000
1266103551 tbl_Penyata 1 2 337293 pk_tbl_penyata CLUSTERED 65601 PRIMARY 65536 pf_TkhTransaksi RANGE 1 2 1 2012-12-01 00:00:00.000
1266103551 tbl_Penyata 1 3 219018 pk_tbl_penyata CLUSTERED 65601 PRIMARY 65536 pf_TkhTransaksi RANGE 1 3 1 2013-06-01 00:00:00.000
1266103551 tbl_Penyata 1 4 0 pk_tbl_penyata CLUSTERED 65601 PRIMARY 65536 pf_TkhTransaksi RANGE 1 4 1 2013-12-01 00:00:00.000
1266103551 tbl_Penyata 1 5 0 pk_tbl_penyata CLUSTERED 65601 PRIMARY 65536 pf_TkhTransaksi RANGE 1 5 NULL NULL
1266103551 tbl_Penyata 13 1 166296 dbo.tbl_Penyata_UQ1 NONCLUSTERED 65601 PRIMARY 65536 pf_TkhTransaksi RANGE 1 1 1 2012-06-01 00:00:00.000
1266103551 tbl_Penyata 13 2 337293 dbo.tbl_Penyata_UQ1 NONCLUSTERED 65601 PRIMARY 65536 pf_TkhTransaksi RANGE 1 2 1 2012-12-01 00:00:00.000
1266103551 tbl_Penyata 13 3 219018 dbo.tbl_Penyata_UQ1 NONCLUSTERED 65601 PRIMARY 65536 pf_TkhTransaksi RANGE 1 3 1 2013-06-01 00:00:00.000
1266103551 tbl_Penyata 13 4 0 dbo.tbl_Penyata_UQ1 NONCLUSTERED 65601 PRIMARY 65536 pf_TkhTransaksi RANGE 1 4 1 2013-12-01 00:00:00.000
1266103551 tbl_Penyata 13 5 0 dbo.tbl_Penyata_UQ1 NONCLUSTERED 65601 PRIMARY 65536 pf_TkhTransaksi RANGE 1 5 NULL NULL
1266103551 tbl_Penyata 14 1 166296 dbo.tbl_Penyata_UQ2 NONCLUSTERED 65601 PRIMARY 65536 pf_TkhTransaksi RANGE 1 1 1 2012-06-01 00:00:00.000
1266103551 tbl_Penyata 14 2 337293 dbo.tbl_Penyata_UQ2 NONCLUSTERED 65601 PRIMARY 65536 pf_TkhTransaksi RANGE 1 2 1 2012-12-01 00:00:00.000
1266103551 tbl_Penyata 14 3 219018 dbo.tbl_Penyata_UQ2 NONCLUSTERED 65601 PRIMARY 65536 pf_TkhTransaksi RANGE 1 3 1 2013-06-01 00:00:00.000
1266103551 tbl_Penyata 14 4 0 dbo.tbl_Penyata_UQ2 NONCLUSTERED 65601 PRIMARY 65536 pf_TkhTransaksi RANGE 1 4 1 2013-12-01 00:00:00.000
1266103551 tbl_Penyata 14 5 0 dbo.tbl_Penyata_UQ2 NONCLUSTERED 65601 PRIMARY 65536 pf_TkhTransaksi RANGE 1 5 NULL NULL
1266103551 tbl_Penyata 15 1 166296 dbo.tbl_Penyata_UQ3 NONCLUSTERED 65601 PRIMARY 65536 pf_TkhTransaksi RANGE 1 1 1 2012-06-01 00:00:00.000
1266103551 tbl_Penyata 15 2 337293 dbo.tbl_Penyata_UQ3 NONCLUSTERED 65601 PRIMARY 65536 pf_TkhTransaksi RANGE 1 2 1 2012-12-01 00:00:00.000
1266103551 tbl_Penyata 15 3 219018 dbo.tbl_Penyata_UQ3 NONCLUSTERED 65601 PRIMARY 65536 pf_TkhTransaksi RANGE 1 3 1 2013-06-01 00:00:00.000
1266103551 tbl_Penyata 15 4 0 dbo.tbl_Penyata_UQ3 NONCLUSTERED 65601 PRIMARY 65536 pf_TkhTransaksi RANGE 1 4 1 2013-12-01 00:00:00.000
1266103551 tbl_Penyata 15 5 0 dbo.tbl_Penyata_UQ3 NONCLUSTERED 65601 PRIMARY 65536 pf_TkhTransaksi RANGE 1 5 NULL NULL
*/
My SQL statement as following,
select top 1 nogaji,
tkhtransaksi as tkhtrx,
nosiri, kodBank,AkaunBank,singkatan_bank,
t2.NAMA,t2.[NO_K/P],t2.KOD_PUSAT,
t3.Keterangan_pusat,t2.STATUS
from dbo.tbl_Penyata t1
--album & pusat
inner join eALBUM.dbo.Albmas t2 on t1.nogaji=t2.NOMBOR_GAJI
inner join eALBUM.dbo.pusat t3 on t2.KOD_PUSAT=t3.Kod_pusat
where t1.nogaji='047911'
and DATEPART(YEAR,tkhtransaksi)=DATEPART(YEAR,'20121201')
and DATEPART(MONTH,tkhtransaksi)=DATEPART(MONTH,'20121201')
This is my Execution Plan,
Please advice me to adding Index
Need help on indexing
Posted - 01/15/2013 : 12:27:48
