Query Optimization - 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

Author

Topic

SQLCode
Posting Yak Master

143 Posts

Posted - 2004-03-03 : 16:36:29

I have a query that does an avg() function on top of several joins and filters. To add it, it has 15+ million records. It is well indexed. I have tried hard to optimize it. Are there any rules for adding where clause for filtering in any particular order?
I ran Execution plan, did not understand what parallelism is. that seems to be taking the most %
we have sql2000. I also checked www.sql-server-performance.com.
If anyone has any suggestions pls. help me.

TIA

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-03-03 : 17:22:12

Can you post the Query?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-03-03 : 18:47:05

yes, please ! it's a little easier to help if you show us what you've got so far!

- Jeff

- Jeff

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2004-03-03 : 21:45:46

maybe some table/index/view DDL as well.

-ec

SQLCode
Posting Yak Master

143 Posts

Posted - 2004-03-04 : 09:05:31

SELECT
avg(cast(tab1.col1 as float)) r1,
tab2.year,
tblC.CMType
FROM
tab1 INNER JOIN tab2 ON
tab1.DateKey = tab2.DateKey
INNER JOIN tblC ON
tab1.CKey = tblC.CKey
and tblC.ysnR = 1
INNER Join tblL ON
tab1.QKey = tblL.QKey
and tblL.ysnInclude=1
WHERE
tab2.Date >= '2003-01-01' AND
tab2.Date <= '2004-02-26' AND
tab1.R >= 1 AND
tab1.R <= 5 AND
tblC.CMType in ('CBT','VBT','DDD','HOTr')

group by tab2.year,tblC.CMType

ALTER TABLE tab1 WITH NOCHECK ADD
CONSTRAINT [c1] DEFAULT (0) FOR [RC]
GO

CREATE INDEX [IX1] ON [dbo].[tab1]([DateKey]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IX2] ON [dbo].[tab1]([CKey]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IX3] ON [dbo].[tab1]([DateKey], [CKey], [QKey], [R], [RC]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[tab1] ADD
CONSTRAINT [FK1] FOREIGN KEY
(
[CKey]
) REFERENCES [dbo].[tab3] (
[CKey]
)
GO

tab1 has 20 million records, tab2 and others has a few thousands.
Other tables also has indexes on the join colmns and they are clustered indexes

nfsoft
Starting Member

36 Posts

Posted - 2004-03-04 : 19:46:06

I think if you avoid "WHERE" You can save something.
If possible avoid avg(cast(...)) or avg(convert(...)) -- I am running on my instinct, You have to check this.

SELECT
cast(avg(tab1.col1) as float) r1,
tab2.year,
tblC.CMType
FROM tab1
INNER JOIN tab2 ON
tab1.DateKey = tab2.DateKey and
tab1.R >= 1 AND
tab1.R <= 5 AND
tab2.Date >= '2003-01-01' AND
tab2.Date <= '2004-02-26'
INNER JOIN tblC ON
tab1.CKey = tblC.CKey and
tblC.CMType in ('CBT','VBT','DDD','HOTr') and
and tblC.ysnR = 1
INNER Join tblL ON
tab1.QKey = tblL.QKey
and tblL.ysnInclude=1
group by tab2.year,tblC.CMType

Nuno Ferreira

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources