Optimize sql query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Optimize sql query

Author

Topic

schamp
Starting Member

13 Posts

Posted - 2010-10-02 : 14:03:00

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.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-10-03 : 04:13:42

Not enough information.

Post the query, the table definitions, the index definitions and, if possible, upload the execution plan somewhere.

How many rows does the query return. How long is 'hell long time'?

--
Gail Shaw
SQL Server MVP

schamp
Starting Member

13 Posts

Posted - 2010-10-05 : 05:43:29

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'

--------
have applied Non clustered index on dateCreation and intEmailID

---

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)

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-10-05 : 05:52:16

Table definition, index definition. Any chance you can save and upload an exec plan somewhere?

--
Gail Shaw
SQL Server MVP

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-10-05 : 05:54:44

[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
LEFT OUTER JOIN dbo.TABLE9 AS agauthLoc ON agauthLoc.intEmployeeID = agauth.intEmployeeID
WHERE
dateCreation >= '2010-08-30' and dateCreation <= '2010-08-31'
and intWanSource IN (14, 26, 9, 13)
and strStatusType <> 'Closed'
[/code]
Doesn't look too horrible. Are all the objects actual tables or are 1 or more of them views?

We can't tell which table(s) your WHERE clause is filtering but an index on intWanSource might be a good idea -- this looks quite selective.

Is there an index for each join condition?

Can you post the execution plan? (include actual execution plan)

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources