I've 2 database as following1- eALBUM2- ePENYATAIn eALBUM, I've table as followingCREATE 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 followingCREATE 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 infoobject_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 655361266103551 tbl_Penyata dbo.tbl_Penyata_UQ2 NONCLUSTERED ps_TkhTransaksi 65601 pf_TkhTransaksi 655361266103551 tbl_Penyata dbo.tbl_Penyata_UQ3 NONCLUSTERED ps_TkhTransaksi 65601 pf_TkhTransaksi 655361266103551 tbl_Penyata pk_tbl_penyata CLUSTERED ps_TkhTransaksi 65601 pf_TkhTransaksi 65536object_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.0001266103551 tbl_Penyata 1 2 337293 pk_tbl_penyata CLUSTERED 65601 PRIMARY 65536 pf_TkhTransaksi RANGE 1 2 1 2012-12-01 00:00:00.0001266103551 tbl_Penyata 1 3 219018 pk_tbl_penyata CLUSTERED 65601 PRIMARY 65536 pf_TkhTransaksi RANGE 1 3 1 2013-06-01 00:00:00.0001266103551 tbl_Penyata 1 4 0 pk_tbl_penyata CLUSTERED 65601 PRIMARY 65536 pf_TkhTransaksi RANGE 1 4 1 2013-12-01 00:00:00.0001266103551 tbl_Penyata 1 5 0 pk_tbl_penyata CLUSTERED 65601 PRIMARY 65536 pf_TkhTransaksi RANGE 1 5 NULL NULL1266103551 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.0001266103551 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.0001266103551 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.0001266103551 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.0001266103551 tbl_Penyata 13 5 0 dbo.tbl_Penyata_UQ1 NONCLUSTERED 65601 PRIMARY 65536 pf_TkhTransaksi RANGE 1 5 NULL NULL1266103551 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.0001266103551 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.0001266103551 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.0001266103551 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.0001266103551 tbl_Penyata 14 5 0 dbo.tbl_Penyata_UQ2 NONCLUSTERED 65601 PRIMARY 65536 pf_TkhTransaksi RANGE 1 5 NULL NULL1266103551 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.0001266103551 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.0001266103551 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.0001266103551 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.0001266103551 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.STATUSfrom dbo.tbl_Penyata t1--album & pusatinner join eALBUM.dbo.Albmas t2 on t1.nogaji=t2.NOMBOR_GAJIinner join eALBUM.dbo.pusat t3 on t2.KOD_PUSAT=t3.Kod_pusatwhere 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 - 2013-01-15 : 12:27:48
