Query optimization problems - 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 2000 Forums

Transact-SQL (2000)

Query optimization problems

Author

Topic

SQLCode
Posting Yak Master

143 Posts

Posted - 2008-07-16 : 14:46:06

Hi,
Thanks for the wonderful site and all help!!

I have the following 2 querries. One is taking < 1 sec and the other is not finishing and I have to kill it. Can you help figuring out why and how I can fix it please?

TIA
-- Q 1 (running for ever)
SELECT Count(Tab1.tID)
FROM Tab1
JOIN Tab2 ON Tab1."tID" = Tab2.tID
JOIN Tab3 ON Tab1.QID = Tab3.QID
WHERE
Tab1."DateT" >= {ts '2008-05-31 00:00:00.00'} AND
Tab1."DateT" < {ts '2008-06-01 00:00:00.00'} and
Tab2."TName" = 'A Name'

-- Q 2 (less than 1 sec)
SELECT Count(Tab1.tID)
FROM Tab1
JOIN Tab2 ON Tab1."tID" = Tab2.tID
JOIN Tab3 ON Tab1.QID = Tab3.QID
WHERE
Tab1."DateT" >= {ts '2008-05-31 00:00:00.00'} AND
Tab1."DateT" < {ts '2008-06-01 00:00:00.00'} and
Tab2."TID" = 1278361

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-16 : 14:52:29

What indexes do you have on these tables?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-16 : 15:02:16

Do not use ODBC functions calls!

-- Q 1 (running for ever)
SELECT		Count(Tab1.tID)
FROM		Tab1 
INNER JOIN	Tab2 ON Tab1.tID = Tab2.tID 
INNER JOIN	Tab3 ON Tab1.QID = Tab3.QID
WHERE		Tab1.DateT >= '2008-05-31'
		AND Tab1.DateT < '2008-06-01'
		and Tab2.TName = 'A Name'

-- Q 2 (less than 1 sec)
SELECT		Count(Tab1.tID)
FROM		Tab1 
INNER JOIN	Tab2 ON Tab1.tID = Tab2.tID 
INNER JOIN	Tab3 ON Tab1.QID = Tab3.QID
WHERE		Tab1.DateT >= '2008-05-31'
		AND Tab1.DateT < '2008-06-01'
		and Tab2.TID = 1278361

E 12°55'05.25"
N 56°04'39.16"

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-07-16 : 15:08:15

A quick glance makes me think you have an index on Tab2."TID" but not on Tab2."TName"

SQLCode
Posting Yak Master

143 Posts

Posted - 2008-07-16 : 15:19:04

A lot. Tab1 is the biggest one. It has 170 million recs. It does not have a pk but a lot of indexes. Others a have the usual recommended indexes and pks.

SQLCode
Posting Yak Master

143 Posts

Posted - 2008-07-16 : 15:19:42

TName also has an index

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-16 : 15:25:12

Please post the indexes as we can't read your mind nor see your environment.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-16 : 16:07:34

You should have three indexes looking like this.
The selectivity for one days is relative small (one day of 150 million records), so DateT should be indexed first.

CREATE NONCLUSTERED INDEX IX_Yak1 ON Tab1 (DateT, tID, qID)
CREATE NONCLUSTERED INDEX IX_Yak2 ON Tab2 (tID, tName)
CREATE NONCLUSTERED INDEX IX_Yak3 ON Tab3 (qID, tName)

E 12°55'05.25"
N 56°04'39.16"

SQLCode
Posting Yak Master

143 Posts

Posted - 2008-07-16 : 16:14:54

Yes, of course..
Here you go

CREATE TABLE [dbo].[Tab1] (
[PID] [int] NOT NULL ,
[TID] [int] NOT NULL ,
[TPID] [int] NOT NULL ,
[TTA] [int] NOT NULL ,
[QID] [int] NOT NULL ,
[QOID_Given] [int] NULL ,
[C] [bit] NULL ,
[DateTS] [datetime] NULL ,
[A] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AddDate] [datetime] NULL ,
[LastUpdateDate] [datetime] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Tab2] (
[TID] [int] NOT NULL ,
[TName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[QOnT] [int] NULL ,
[PRS] [int] NULL ,
[PPS] [int] NULL ,
[TA] [bit] NULL ,
[TAddDate] [datetime] NULL ,
[TLastUpdateDate] [datetime] NULL ,
[AddDate] [datetime] NULL ,
[LastUpdateDate] [datetime] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Tab3] (
[QID] [int] IDENTITY (1, 1) NOT NULL ,
[QText] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[QSource] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AddDate] [datetime] NULL ,
[LastUpdateDate] [datetime] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Tab1] WITH NOCHECK ADD
CONSTRAINT [PK_Tab1] PRIMARY KEY CLUSTERED
(
[TID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Tab1] ADD
CONSTRAINT [dwAdd__20202AA8] DEFAULT (getdate()) FOR [AddDate],
CONSTRAINT [dwLas__21144EE1] DEFAULT (getdate()) FOR [LastUpdateDate]
GO

CREATE INDEX [IX_1] ON [dbo].[Tab1]([PID], [TID]) ON [PRIMARY]
GO

CREATE INDEX [IX_2] ON [dbo].[TTQA]([TID]) ON [PRIMARY]
GO

CREATE INDEX [IX_3] ON [dbo].[Tab1]([TPID]) ON [PRIMARY]
GO

CREATE INDEX [IX_4] ON [dbo].[Tab1]([QOID_Given], [QID]) ON [PRIMARY]
GO

CREATE INDEX [IX_4] ON [dbo].[Tab1]([QID], [TPID]) ON [PRIMARY]
GO

CREATE INDEX [IX_5] ON [dbo].[Tab1]([PID], [TestID], [TTA]) ON [PRIMARY]
GO

CREATE INDEX [IX_6] ON [dbo].[Tab1]([DateTS], [TraineeID], [TID]) ON [PRIMARY]
GO

CREATE INDEX [IDX_7] ON [dbo].[Tab1]([QID]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Tab2] ADD
CONSTRAINT [DF22FC9753] DEFAULT (getdate()) FOR [AddDate],
CONSTRAINT [DF23F0BB8C] DEFAULT (getdate()) FOR [LastUpdateDate]
GO

CREATE UNIQUE INDEX [IDX_ID_Name] ON [dbo].[Tab2]([TID], [TName]) WITH FILLFACTOR = 80, PAD_INDEX ON [PRIMARY]
GO

CREATE INDEX [IX_dwTest_TName] ON [dbo].[Tab2]([TName]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[dwQuestion] ADD
CONSTRAINT [DF__3F98D601] DEFAULT (getdate()) FOR [dwAddDate],
CONSTRAINT [DF__408CFA3A] DEFAULT (getdate()) FOR [dwLastUpdateDate],
CONSTRAINT [PK_Q] PRIMARY KEY NONCLUSTERED
(
[QID]
) ON [PRIMARY]
GO

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-07-17 : 22:31:01

Did you check execution plan to see which is higher cost?

SQLCode
Posting Yak Master

143 Posts

Posted - 2008-07-21 : 09:38:18

The tName

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources