Home Forums SQL Server 7,2000 Performance Tuning select statement is too slow RE: select statement is too slow

  • February 12, 2013 at 3:02 am

    #1586896

    Table Creation

    /****** Object: Table [dbo].[tblGLJnlEntrySum] Script Date: 02/12/2013 04:55:15 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[tblGLJnlEntrySum](

    [BatchID] [bigint] NOT NULL,

    [RecID] [bigint] NOT NULL,

    [GLBatchID] [bigint] NOT NULL,

    [GLRecID] [bigint] IDENTITY(1,1) NOT NULL,

    [DivCode] [varchar](3) NOT NULL,

    [DivName] [varchar](50) NOT NULL,

    [CurrencyID] [varchar](3) NOT NULL,

    [GLAcctNbrP1] [varchar](30) NOT NULL,

    [GLAcctNbrP2] [varchar](30) NULL,

    [GLAcctNbrP3] [varchar](30) NULL,

    [GLAcctNbrP4] [varchar](30) NULL,

    [GLAcctNbrP5] [varchar](30) NULL,

    [GrpGLAcctNbr] [varchar](255) NOT NULL,

    [GrpGLAcctType] [varchar](2) NULL,

    [AcctPeriod] [varchar](2) NOT NULL,

    [AcctYear] [varchar](4) NOT NULL,

    [AcctBalance] [money] NULL,

    [ProcessedDate] [datetime] NULL,

    [ProcessedFlag] [bit] NOT NULL,

    [GPProcessID] [bigint] NULL,

    [GPBatchID] [char](15) NULL,

    [GPErrorFlag] [int] NULL,

    [GPDatabase] [varchar](60) NULL,

    [CreatedDate] [datetime] NOT NULL,

    [CreatedUser] [varchar](255) NOT NULL,

    [UpdatedDate] [datetime] NULL,

    [UpdatedUser] [varchar](255) NULL,

    [GLAcctDescP1] [varchar](60) NULL,

    [GLAcctDescP2] [varchar](60) NULL,

    [GLAcctDescP3] [varchar](60) NULL,

    [GLAcctDescP4] [varchar](60) NULL,

    [GLAcctDescP5] [varchar](60) NULL,

    [GLAcctDesc] [varchar](255) NULL,

    [glPeriodYear] [int] NULL,

    [glPeriodMonth] [int] NULL,

    [glEntityCode] [varchar](50) NULL,

    [glEntityDesc] [varchar](100) NULL,

    [glAcctCode] [varchar](50) NULL,

    [glSubEntityCode] [varchar](50) NULL,

    [glSubEntityDesc] [varchar](100) NULL,

    [glAcctTypeCode] [varchar](50) NULL,

    [glAcctTypeDesc] [varchar](100) NULL,

    [flexCode01] [varchar](50) NULL,

    [flexDesc01] [varchar](100) NULL,

    [flexCode02] [varchar](50) NULL,

    [flexDesc02] [varchar](100) NULL,

    [flexCode03] [varchar](50) NULL,

    [flexDesc03] [varchar](100) NULL,

    [flexCode04] [varchar](50) NULL,

    [flexDesc04] [varchar](100) NULL,

    [flexCode05] [varchar](50) NULL,

    [flexDesc05] [varchar](100) NULL,

    [flexCode06] [varchar](50) NULL,

    [flexDesc06] [varchar](100) NULL,

    [flexCode07] [varchar](50) NULL,

    [flexDesc07] [varchar](100) NULL,

    [flexCode08] [varchar](50) NULL,

    [flexDesc08] [varchar](100) NULL,

    [flexCode09] [varchar](50) NULL,

    [flexDesc09] [varchar](100) NULL,

    [flexCode10] [varchar](50) NULL,

    [flexDesc10] [varchar](100) NULL,

    [glAcctYTDBalance] [money] NULL,

    [glAcctMonthDebitAmt] [money] NULL,

    [glAcctMonthCreditsAmt] [money] NULL,

    [glAcctMonthNetChangeAmt] [money] NULL,

    [CurrencyCode] [varchar](3) NULL,

    [FileCreateDate] [datetime] NULL,

    [grpEntityID] [varchar](2) NULL,

    [grpEntityErrorFlag] [varchar](1) NULL,

    [grpSubEntityID] [varchar](3) NULL,

    [grpSubEntityErrorFlag] [varchar](1) NULL,

    [grpGLAcctCode] [varchar](4) NULL,

    [grpGLAcctCodeErrorFlag] [varchar](1) NULL,

    [grpGLAcctNumberErrorFlag] [varchar](1) NULL,

    [recordErrorFlag] [varchar](1) NULL,

    [recordSource] [varchar](50) NULL,

    [createdBy] [varchar](50) NULL,

    [createTimeStamp] [datetime] NULL,

    [updatedBy] [varchar](50) NULL,

    [updateTimeStamp] [datetime] NULL,

    CONSTRAINT [tblGLJnlEntrySum_PK] PRIMARY KEY CLUSTERED

    (

    [GLRecID] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    GO

    ALTER TABLE [dbo].[tblGLJnlEntrySum] WITH NOCHECK ADD CONSTRAINT [FK_tblGLJnlEntrySum_tblDivision_DIM] FOREIGN KEY([DivCode])

    REFERENCES [dbo].[tblDivision_DIM] ([DivCode])

    GO

    ALTER TABLE [dbo].[tblGLJnlEntrySum] CHECK CONSTRAINT [FK_tblGLJnlEntrySum_tblDivision_DIM]

    GO

    ALTER TABLE [dbo].[tblGLJnlEntrySum] ADD CONSTRAINT [DF_tblGLJnlEntrySum_CurrencyID] DEFAULT ('USD') FOR [CurrencyID]

    GO

    ALTER TABLE [dbo].[tblGLJnlEntrySum] ADD CONSTRAINT [DF__tblGLJnlE__Proce__703EA55A] DEFAULT ((0)) FOR [ProcessedFlag]

    GO

    ALTER TABLE [dbo].[tblGLJnlEntrySum] ADD CONSTRAINT [DF__tblGLJnlE__Creat__7132C993] DEFAULT (getdate()) FOR [CreatedDate]

    GO

    ALTER TABLE [dbo].[tblGLJnlEntrySum] ADD CONSTRAINT [DF__tblGLJnlE__Creat__7226EDCC] DEFAULT (suser_sname()) FOR [CreatedUser]

    GO

    ALTER TABLE [dbo].[tblGLJnlEntrySum] ADD CONSTRAINT [DF_tblGLJnlEntrySum_createdBy] DEFAULT (suser_sname()) FOR [createdBy]

    GO

    ALTER TABLE [dbo].[tblGLJnlEntrySum] ADD CONSTRAINT [DF_tblGLJnlEntrySum_createTimeStamp] DEFAULT (getdate()) FOR [createTimeStamp]

    GO

    Trigger used on top of table

    /****** Object: Trigger [dbo].[tblGLJnlEntrySum_TRU01] Script Date: 02/12/2013 04:56:18 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TRIGGER [dbo].[tblGLJnlEntrySum_TRU01]

    ON [dbo].[tblGLJnlEntrySum]

    FOR UPDATE

    AS

    BEGIN

    update tblGLJnlEntrySum

    set UpdatedDate = getdate(),

    UpdatedUser = IsNull(deleted.UpdatedUser,suser_sname())

    from deleted

    where tblGLJnlEntrySum.GLRecID = deleted.GLRecID

    END

    GO

    Indexes

    /****** Object: Index [IX_tblGLJnlEntrySum] Script Date: 02/12/2013 04:57:26 ******/

    CREATE NONCLUSTERED INDEX [IX_tblGLJnlEntrySum] ON [dbo].[tblGLJnlEntrySum]

    (

    [DivCode] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]

    GO

    USE [dbSDUSAReporting]

    GO

    /****** Object: Index [tblGLJnlEntrySum_IX01] Script Date: 02/12/2013 04:58:33 ******/

    CREATE NONCLUSTERED INDEX [tblGLJnlEntrySum_IX01] ON [dbo].[tblGLJnlEntrySum]

    (

    [DivCode] ASC,

    [GLAcctNbrP1] ASC,

    [AcctPeriod] ASC,

    [AcctYear] ASC,

    [AcctBalance] ASC

    )

    INCLUDE ( [GLAcctNbrP2],

    [GLAcctNbrP3],

    [GLAcctNbrP4],

    [GLAcctNbrP5]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]

    GO

    USE [dbSDUSAReporting]

    GO

    /****** Object: Index [tblGLJnlEntrySum_IX02] Script Date: 02/12/2013 04:58:56 ******/

    CREATE NONCLUSTERED INDEX [tblGLJnlEntrySum_IX02] ON [dbo].[tblGLJnlEntrySum]

    (

    [DivCode] ASC,

    [AcctYear] ASC,

    [AcctPeriod] ASC,

    [GrpGLAcctNbr] ASC,

    [GLAcctNbrP1] ASC,

    [GLRecID] ASC,

    [CurrencyID] ASC,

    [AcctBalance] ASC

    )

    INCLUDE ( [GLAcctNbrP2],

    [GLAcctNbrP3],

    [GLAcctNbrP4],

    [GLAcctNbrP5],

    [GrpGLAcctType],

    [GLAcctDescP1],

    [GLAcctDescP2],

    [GLAcctDescP3],

    [GLAcctDescP4],

    [GLAcctDescP5]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]

    GO

    USE [dbSDUSAReporting]

    GO

    /****** Object: Index [tblGLJnlEntrySum_PK] Script Date: 02/12/2013 04:59:14 ******/

    ALTER TABLE [dbo].[tblGLJnlEntrySum] ADD CONSTRAINT [tblGLJnlEntrySum_PK] PRIMARY KEY CLUSTERED

    (

    [GLRecID] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]

    GO