SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Need help on indexing
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

wkm1925
Posting Yak Master

207 Posts

Posted - 01/15/2013 :  12:27:48  Show Profile  Reply with Quote
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

srimami
Posting Yak Master

160 Posts

Posted - 01/16/2013 :  03:46:52  Show Profile  Reply with Quote
There is already Clustered Index created on Penyata (Constraint) but it is not taking the help of the Index while executing the query. Create Non Clustered Index on Albmas table on the columns you are using in Join condition and create Non Clustered Index on Penyata table on columns that are used in Join condition.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.91 seconds. Powered By: Snitz Forums 2000