select statement is too slow

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Performance Tuning » select statement is too slow

select statement is too slow

Rate Topic

Display Mode

Topic Options

Author

Message

p.panneerselvam

Posted Monday, February 11, 2013 11:47 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, February 12, 2013 6:55 AM
Points: 5, Visits: 12

Hi,

I have 81 columns in my table. and record count=6690237. clustered index is been applied on PK.

when i execute below select statement it is taking 3 min to process.

select distinct divCode
,convert(varchar(4),glPeriodYear) + '/' + right('00' + convert(varchar(2),glPeriodMonth),2) + '/' + '01' as glPeriod
from tblGLJnlEntrySum
where divCode = @vsDivCode
order by divCode,glPeriod desc

Please tell me how i can speed up my query. need some suggestion on performance tuning.

Post #1418763

GilaMonster

Posted Tuesday, February 12, 2013 12:49 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:18 PM
Points: 37,744, Visits: 30,025

Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Is the distinct really necessary?

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1418775

p.panneerselvam

Posted Tuesday, February 12, 2013 3:02 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, February 12, 2013 6:55 AM
Points: 5, Visits: 12

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

Post #1418826

GilaMonster

Posted Tuesday, February 12, 2013 3:13 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:18 PM
Points: 37,744, Visits: 30,025

Execution plan please.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1418827

anthony.green

Posted Tuesday, February 12, 2013 3:42 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

Also what other indexes do you have on the tables asside from the Primary Keys?

Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1418847

p.panneerselvam

Posted Tuesday, February 12, 2013 4:55 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, February 12, 2013 6:55 AM
Points: 5, Visits: 12

Execution plan

Cost
select 0%
Parallelism(Gather Streams) 0%
Sort(Distinct Sort) 0%
Parallelism(Repartition Streams) 0%
Hash Match(Partial Aggregate) 3%
Compute Scalar 0%
Clustered Index Scan[tblGLJnlEntrySum].tblGLJnlEntrySum_PK 97%

Inaddition i am getting message like below
/*
Missing Index Details
The Query Processor estimates that implementing the following index could improve the query cost by 97.4146%.
*/

/*
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[tblGLJnlEntrySum] ([DivCode])
INCLUDE ([glPeriodYear],[glPeriodMonth])
*/

so is that means that i have to create suc index mentioned above?

Post #1418880

Abu Dina

Posted Tuesday, February 12, 2013 5:06 AM

SSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 10:34 AM
Points: 494, Visits: 2,158

p.panneerselvam (2/12/2013)

The suggested index should improve your query yes. At the moment, SQL Server is having to scan the entire table looking for records where divCode = @vsDivCode. With the new index and included columns the scan should become an index seek on the new indexed column.

-----------------------------------
http://www.SQL4n00bs.com

Post #1418887

p.panneerselvam

Posted Tuesday, February 12, 2013 5:19 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, February 12, 2013 6:55 AM
Points: 5, Visits: 12

Hi,

my issue got fixed with the help of excution plan. i was missing required non-clustered index on top of my table.

Thanks

Post #1418891

GilaMonster

Posted Tuesday, February 12, 2013 6:54 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:18 PM
Points: 37,744, Visits: 30,025

I take it you didn't read the article I referenced, as it shows how to save and attach the graphical plan.

Also, please post SQL 2008 questions in the SQL 2008 forums.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1418934

« Prev Topic | Next Topic »

Permissions