September 25, 2011 at 5:32 pm
Hello. I need help figuring out how to setup the correct table index for this table....
====================
CREATE TABLE [dbo].[tblMOR_ActByAcctDetail](
[TableID] [int] IDENTITY(1,1) NOT NULL,
[FiscalYear] [char](8) NULL,
[MonthYear] [char](6) NULL,
[WhatMonth] [varchar](2) NULL,
[MthSortNum] [int] NULL,
[AdminArea] [char](3) NULL,
[CompSortCode] [nvarchar](255) NULL,
[NamePrint] [nvarchar](70) NULL,
[NumbAcct] [char](15) NULL,
[NumbAcctA] [varchar](2) NULL,
[NumbAcctB] [varchar](3) NULL,
[NumbAcctC] [varchar](2) NULL,
[NumbAcctD] [varchar](2) NULL,
[NumbAcctE] [varchar](5) NULL,
[BudgetName] [varchar](50) NULL,
[FundNumber] [varchar](5) NULL,
[FundTypeIBIS] [char](4) NULL,
[FundType] [char](2) NULL,
[ServiceType] [char](1) NULL,
[MainFuncCode] [varchar](3) NULL,
[MainFuncCodeDescrip] [nvarchar](70) NULL,
[FunctionCode] [char](3) NULL,
[FuncCodeDescrip] [nvarchar](70) NULL,
[DeptMnemonic] [char](7) NULL,
[PermFees] [decimal](38, 2) NULL,
[TempFees] [decimal](38, 2) NULL,
[TotalFees] [decimal](38, 2) NULL,
[DesFees] [varchar](4) NULL,
[TempSales] [decimal](38, 2) NULL,
[TotalSales] [decimal](38, 2) NULL,
[DesSales] [varchar](17) NULL,
[PermOther] [decimal](38, 2) NULL,
[TempOther] [decimal](38, 2) NULL,
[TotalOther] [decimal](38, 2) NULL,
[DesOther] [varchar](12) NULL,
[TotalPermIncome] [varchar](1) NULL,
[TotalTempIncome] [varchar](1) NULL,
[TotalIncome] [varchar](1) NULL,
[PermExec] [decimal](38, 2) NULL,
[TempExec] [decimal](38, 2) NULL,
[TotalExec] [decimal](38, 2) NULL,
[DesExec] [varchar](9) NULL,
[PermAdmin] [decimal](38, 2) NULL,
[TempAdmin] [decimal](38, 2) NULL,
[TotalAdmin] [decimal](38, 2) NULL,
[DesAdmin] [varchar](13) NULL,
[PermAcadAdmin] [decimal](38, 2) NULL,
[TempAcadAdmin] [decimal](38, 2) NULL,
[TotalAcadAdmin] [decimal](38, 2) NULL,
[DesAcadAdmin] [varchar](22) NULL,
[PermAcad] [decimal](38, 2) NULL,
[TempAcad] [decimal](38, 2) NULL,
[TotalAcad] [decimal](38, 2) NULL,
[DesAcad] [varchar](14) NULL,
[PermStaffE] [decimal](38, 2) NULL,
[TempStaffE] [decimal](38, 2) NULL,
[TotalStaffE] [decimal](38, 2) NULL,
[DesStaffE] [varchar](12) NULL,
[PermStaffN] [decimal](38, 2) NULL,
[TempStaffN] [decimal](38, 2) NULL,
[TotalStaffN] [decimal](38, 2) NULL,
[DesStaffN] [varchar](16) NULL,
[PermTech] [decimal](38, 2) NULL,
[TempTech] [decimal](38, 2) NULL,
[TotalTech] [decimal](38, 2) NULL,
[DesTech] [varchar](12) NULL,
[PermFTM] [decimal](38, 2) NULL,
[TempFTM] [decimal](38, 2) NULL,
[TotalFTM] [decimal](38, 2) NULL,
[DesFTM] [varchar](21) NULL,
[PermSalReserve] [decimal](38, 2) NULL,
[TempSalReserve] [decimal](38, 2) NULL,
[TotalSalReserve] [decimal](38, 2) NULL,
[DesSalReserve] [varchar](23) NULL,
[PermFT1] [decimal](38, 2) NULL,
[TempFT1] [decimal](38, 2) NULL,
[TotalFT1] [decimal](38, 2) NULL,
[DesFT1] [varchar](12) NULL,
[PermFT2] [decimal](38, 2) NULL,
[TempFT2] [decimal](38, 2) NULL,
[TotalFT2] [decimal](38, 2) NULL,
[DesFT2] [varchar](13) NULL,
[PermNsaOthers] [decimal](38, 2) NULL,
[TempNsaOthers] [decimal](38, 2) NULL,
[TotalNsaOthers] [decimal](38, 2) NULL,
[DesNsaOthers] [varchar](27) NULL,
[PermGrad] [decimal](38, 2) NULL,
[TempGrad] [decimal](38, 2) NULL,
[TotalGrad] [decimal](38, 2) NULL,
[DesGrad] [varchar](19) NULL,
[PermNsNa] [decimal](38, 2) NULL,
[TempNsNa] [decimal](38, 2) NULL,
[TotalNsNa] [decimal](38, 2) NULL,
[DesNsNa] [varchar](25) NULL,
[PermWage] [decimal](38, 2) NULL,
[TempWage] [decimal](38, 2) NULL,
[TotalWage] [decimal](38, 2) NULL,
[DesWage] [varchar](5) NULL,
[PermDA] [decimal](38, 2) NULL,
[TempDA] [decimal](38, 2) NULL,
[TotalDA] [decimal](38, 2) NULL,
[DesDA] [varchar](28) NULL,
[PermAlloc] [decimal](38, 2) NULL,
[TempAlloc] [decimal](38, 2) NULL,
[TotalAlloc] [decimal](38, 2) NULL,
[DesAlloc] [varchar](15) NULL,
[TotalPermExpense] [varchar](1) NULL,
[TotalTempExpense] [varchar](1) NULL,
[TotalExpense] [varchar](1) NULL,
[PermSales] [decimal](38, 2) NULL,
[PermSalFrBen] [decimal](38, 2) NULL,
[TempSalFrBen] [decimal](38, 2) NULL,
[TotalSalFrBen] [decimal](38, 2) NULL,
[DesSalFrBen] [varchar](50) NULL,
[PermWgFrBen] [decimal](38, 2) NULL,
[TempWgFrBen] [decimal](38, 2) NULL,
[TotalWgFrBen] [decimal](38, 2) NULL,
[DesWgFrBen] [varchar](50) NULL,
[PermGdFrBen] [decimal](38, 2) NULL,
[TempGdFrBen] [decimal](38, 2) NULL,
[TotalGdFrBen] [decimal](38, 2) NULL,
[DesGdFrBen] [varchar](50) NULL,
[PermMedRFrBen] [decimal](38, 2) NULL,
[TempMedRFrBen] [decimal](38, 2) NULL,
[TotalMedRFrBen] [decimal](38, 2) NULL,
[DesMedRFrBen] [varchar](50) NULL,
[PermStFrBen] [decimal](38, 2) NULL,
[TempStFrBen] [decimal](38, 2) NULL,
[TotalStFrBen] [decimal](38, 2) NULL,
[DesStFrBen] [varchar](50) NULL,
[PermTecSFrBen] [decimal](38, 2) NULL,
[TempTecSFrBen] [decimal](38, 2) NULL,
[TotalTecSFrBen] [decimal](38, 2) NULL,
[DesTecSFrBen] [varchar](50) NULL,
[PermTotalFringBen] [decimal](38, 2) NULL,
[TempTotalFringBen] [decimal](38, 2) NULL,
[TotalTotalFringBen] [decimal](38, 2) NULL,
[DesTotalFringBen] [varchar](50) NULL,
[PermFringEquip] [decimal](38, 2) NULL,
[TempFringEquip] [decimal](38, 2) NULL,
[TotalFringEquip] [decimal](38, 2) NULL,
[DesFringEquip] [varchar](75) NULL,
[PermFringCap] [decimal](38, 2) NULL,
[TempFringCap] [decimal](38, 2) NULL,
[TotalFringCap] [decimal](38, 2) NULL,
[DesFringCap] [varchar](75) NULL,
[PermFringOverhd] [decimal](38, 2) NULL,
[TempFringOverhd] [decimal](38, 2) NULL,
[TotalFringOverhd] [decimal](38, 2) NULL,
[DesFringOverhd] [varchar](75) NULL,
[PermFringOther] [decimal](38, 2) NULL,
[TempFringOther] [decimal](38, 2) NULL,
[TotalFringOther] [decimal](38, 2) NULL,
[DesFringOther] [varchar](75) NULL,
[EndOfYearCode] [varchar](50) NULL,
[Budget] [nchar](10) NULL,
CONSTRAINT [PK_tblMOR_ActByAcctDetail] PRIMARY KEY CLUSTERED
(
[TableID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
====================
OTHER INFORMATION:
INDEXES: I don't think I have these setup correctly either. Don't understand it completely either. Do I need to "DO" anything with these indexes to keep them running good? Right now I have 3 indexes setup:
(1) Clustered - TableID
(1) Non-Clustered By FiscalYear and FundTypeIBIS
(1) Non-Clustered By FiscalYear and MonthYear
=====================================
I am **actively** searching by:
[FiscalYear] [char](8)
[MthSortNum] [int]
[FundTypeIBIS] [char](4)
[FundNumber] [varchar](5)
[NumbAcct] [char](15)
[NumbAcctA] [varchar](2)
[NumbAcctB] [varchar](3)
[NumbAcctC] [varchar](2)
[NumbAcctD] [varchar](2)
[NumbAcctE] [varchar](5)
[EndOfYearCode] [varchar]
=====================================
Based on how I am searching the data, what would be the best way to setup the index?
=====================================
Total number of records in table: 2,663,705
With more on way. We append new data each month.
=====================================
Some of my criteria includes:
WHERE (dbo.tblMOR_ActByAcctDetail.MthSortNum <= @MthSortNum) AND (dbo.tblMOR_ActByAcctDetail.FundTypeIBIS = @FundTypeIBIS) AND
(dbo.tblMOR_ActByAcctDetail.FiscalYear = @FiscalYear) AND (@txtSpecAcct1 <> '**' AND
dbo.tblMOR_ActByAcctDetail.NumbAcctA = @txtSpecAcct1 OR
@txtSpecAcct1 = '**') AND (@txtSpecAcct2 <> '***' AND dbo.tblMOR_ActByAcctDetail.NumbAcctB = @txtSpecAcct2 OR
@txtSpecAcct2 = '***') AND (@txtSpecAcct3 <> '**' AND dbo.tblMOR_ActByAcctDetail.NumbAcctC = @txtSpecAcct3 OR
@txtSpecAcct3 = '**') AND (@txtSpecAcct4 <> '**' AND dbo.tblMOR_ActByAcctDetail.NumbAcctD = @txtSpecAcct4 OR
@txtSpecAcct4 = '**') AND (@txtSpecAcct5 <> '*****' AND dbo.tblMOR_ActByAcctDetail.NumbAcctE = @txtSpecAcct5 OR
@txtSpecAcct5 = '*****') AND (dbo.tblMOR_LU_AdCpGp.FiscalYear = @FiscalYear) AND (dbo.tblMOR_LU_AdCpGp.LvlMatch = @DropDownList1) )
===========================
WHERE (@ServiceType <> 0 AND ServTypeCode = @ServiceType OR
@ServiceType = 0) AND (@ListAgFedNumb <> 'ALL' AND FundNumber = @ListAgFedNumb OR
@ListAgFedNumb = 'ALL') AND (@Mnemonic <> 'All Mnemonics' AND Access1Mnemonic = @mnemonic OR
@mnemonic = 'All Mnemonics') AND (FiscalYear = @FiscalYear) AND (RIGHT(@txtFunction, 1) = '0' AND @txtFunction <> 0 AND
MainFunctionCd = @txtFunction OR
RIGHT(@txtFunction, 1) <> '0' AND @txtFunction <> 0 AND FuncCode = @txtFunction OR
@txtFunction = 0) )
===========================
Note: Service Type, Mnemonic, Functions are now queried by a join with another table. We need to remove from the main table.
===========================
Thank you in advance!!!!!!!
September 26, 2011 at 1:37 am
This was removed by the editor as SPAM
September 26, 2011 at 1:42 am
This was removed by the editor as SPAM
September 26, 2011 at 2:47 am
I will check all out. Thank you for the links.
September 26, 2011 at 3:06 am
Also, do I need to "DO" anything with indexes to keep them running good?
September 26, 2011 at 3:09 am
mjbkm (9/26/2011)
Also, do I need to "DO" anything with indexes to keep them running good?
you need to make sure they do not get too fragmented lookup index maintenance
also the best way to get advice on indexing is to post the actual execution plan for your most used queries otherwise it can be difficult to give advice.
September 26, 2011 at 3:12 am
post the actual execution plan----------- I know how to view the execution plan when I run my query. How would I copy it out to post it?
September 26, 2011 at 3:27 am
This was removed by the editor as SPAM
September 26, 2011 at 5:00 am
The two indexes that both start with FiscalYear are questionable. Is that the most selective of the two columns in each index? Meaning, does it have the most unique values for the table? If not, the other columns might make better first columns. The first column on an index is very important because it's used by SQL Server to generate statistics, so you want it to appear as selective as possible. I suspect the Year value won't be very selective in most systems.
Just so you know, normalization helps performance. You really should normalize that structure.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply