Opimize SQL query

Question

Post reply

Opimize SQL query

amit-1143912

Right there with Babe

Points: 774
More actions
October 1, 2010 at 7:21 am

#229875

I have simple sql query with 8 tables of which 3 tables have 30, 60 and 80 lakhs of records
I have applied inner joins on tables to which 35 lakhs of records of displayed but to display these records its takes hell lot of time.
I have applied index on the date column used in where clause and have also applied index on primary keys of the tables having lakhs of records.
for date column in where clause instead of between I have used >= and <= operator.
Apart from above steps what other steps can I apply to improve the performance of the query.
October 1, 2010 at 8:02 am

This was removed by the editor as SPAM
October 1, 2010 at 8:25 am

This was removed by the editor as SPAM

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

amit-1143912 Right there with Babe Points: 774 More actions · Answer 1

SAMPLE CODE :

SELECT

ltr.intEmailID, ltr.strWanNumber, ltr.intPageCount,DATEADD(hh, 1, ltr.dateCreation) AS dateCreation,

stat.intStatus, ISNULL(stat.strComment, '') AS strComment,DATEADD(hh, 1, stat.dateUpdated) AS dateUpdated, stat.intStatusID,

intIVaultStatus, IVaultStatusDesc,

tmp.strMSPEmailId, tmp.strName

FROM

dbo.TABLE1 AS stat INNER JOIN dbo.TABLE7 AS vals ON stat.intStatus = vals.intStatusID

INNER JOIN dbo.TABLE2 AS ltr ON stat.intEmailID = ltr.intEmailID

INNER JOIN dbo.TABLE3 AS tmp ON ltr.intTemplateID = tmp.intTemplateID AND ltr.intTemplateVersion = tmp.intVersion

INNER JOIN dbo.TABLE4 AS cat ON tmp.intCategoryID = cat.intCategoryID

INNER JOIN dbo.TABLE5 AS tsk on tsk.intEmailid = ltr.intEmailid

INNER JOIN dbo.TABLE6 as delv on tsk.intDeliveryTaskTypeID = delv.intDeliveryMethodID

INNER JOIN dbo.TABLE8 AS agauth ON agauth.intEmployeeID = ltr.intCreatedByEmployeeID

INNER JOIN dbo.TABLE8 AS agupd ON agupd.intEmployeeID = stat.intEmployeeID

LET OUTER JOIN dbo.TABLE9 AS agauthLoc ON agauthLoc.intEmployeeID = agauth.intEmployeeID

WHERE

dateCreation >= '2010-08-30' and dateCreation <= '2010-08-31'

and intWanSource = 14 or intWanSource = 26 or intWanSource = 9 or intWanSource = 13

and strStatusType <> 'Closed'

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 2

What do the execution plans look like? Just because you put an index on the tables doesn't mean it's being used.

Also, pulling 35000+ rows out of the db in a query might just run slow, no matter what.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

amit-1143912 Right there with Babe Points: 774 More actions · Answer 3

stewartc, Thanks for reply. it worked

dateCreation ,intEmailID only these 2 columns have non clustered index.

How can the query be optimized further.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 4

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

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

amit-1143912 Right there with Babe Points: 774 More actions · Answer 5

TABLE DEFINITION :

TABLE 1:

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[TABLE1](

[intStatus] [int] NOT NULL,

[intEmailID] [int] NOT NULL,

[intStatusID] [tinyint] NOT NULL,

[AgentID] [int] NOT NULL,

[Updated] [int] NOT NULL,

[Comment] [varchar](1500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[dateUpdated] [datetime] NOT NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

---

INDEX DEFINITIONS :

CREATE INDEX [IDX_dateC] ON TABLE2 (dateC)

CREATE INDEX [IDX_intEmailID] ON TABLE1 (intEmailID)

amit-1143912 Right there with Babe Points: 774 More actions · Answer 6

amit-1143912

Right there with Babe

Points: 774

October 5, 2010 at 3:58 am

#1231553

.

amit-1143912 Right there with Babe Points: 774 More actions · Answer 7

amit-1143912

Right there with Babe

Points: 774

October 5, 2010 at 3:58 am

#1231554

.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 8

Execution plan please?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

amit-1143912 Right there with Babe Points: 774 More actions · Answer 9

I have executed 'include execution plan' its taking lot of time, I guess it would days to execute the query.

Ali Tailor Hall of Fame Points: 3769 More actions · Answer 10

amit-1143912 (10/1/2010)
I have simple sql query with 8 tables of which 3 tables have 30, 60 and 80 lakhs of records
I have applied inner joins on tables to which 35 lakhs of records of displayed but to display these records its takes hell lot of time.
I have applied index on the date column used in where clause and have also applied index on primary keys of the tables having lakhs of records.
for date column in where clause instead of between I have used >= and <= operator.
Apart from above steps what other steps can I apply to improve the performance of the query.

Hi

Amit

Try the query with the DTA against the database is in your scenario, just check what are the suggestions?

Ali
MCTS SQL Server2k8