Optimization recommendations needed ...

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)

Optimization recommendations needed ...

Author

Topic

The Enigma
Posting Yak Master

179 Posts

Posted - 2004-04-23 : 16:25:39

isnull(table1.datecol,'20030101') = isnull(@datevar,isnull(table1.datecol,'20030101'))

OK .. I use this under the following cases

get all data where datecol = @datevar.
In case , @datevar is null then I need all the records ...

However, this is giving me a lot of problems as related to optimization ... in case the table in question contains more than 10 million records ... how do I go about it ....

The table is something like this
datecol
volume
value
customer
executive
territory

The most common queries are on datecol

I have created a clustered index on datecol
and two more non clustered indexes

The query is taking about 5 minutes to execute .... need to optimize it more ... any recommendations from the gurus ...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-23 : 16:31:08

Got DDL and DML for us?

Tara

The Enigma
Posting Yak Master

179 Posts

Posted - 2004-04-23 : 16:44:07

CREATE TABLE [AOP_MASTER] (
[Date] [datetime] NULL ,
[Product_Code] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Volume] [numeric](20, 8) NULL ,
[Value] [numeric](20, 8) NULL ,
[Customer_cd] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Executive_cd] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Territory_cd] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

The table has about 10,000,000 records
The code has a condition on this table

AND ISNULL(AOP_Master.[Date],'20030101')
BETWEEN ISNULL(@Frmdat,ISNULL(AOP_Master.[Date],'20030101'))
AND ISNULL(@Enddat,ISNULL(AOP_Master.[Date],'20030101'))

Plus joins on [Customer_cd],
[Executive_cd] ,
[Territory_cd]

any Index recommendations ???

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-23 : 16:46:05

Could you post your entire query? It's hard for us to see what is going on with just a snippet.

We don't need the DML for 10,000,000 rows, just the DML for a few sample rows so that we can play with it on our machines.

Tara

The Enigma
Posting Yak Master

179 Posts

Posted - 2004-04-23 : 16:46:18

cannot post the full code ... so sorry ... have posted what i think would matter to make a decision ...

Out of the total query this table does a clustered index scan (clustered index is on datecol) but gives a 95 % cost for the same ...

The Enigma
Posting Yak Master

179 Posts

Posted - 2004-04-23 : 17:00:27

insert into aop_master
select '2004-05-01',',000000000000001041','1211.77485369','0.00000000','6303000040','011','110003' union
select '2004-05-02',',000000000000001041','1211.77485369','0.00000000','6303000040','011','110003' union
select '2004-05-03',',000000000000001041','1211.77485369','0.00000000','6303000040','011','110003' union
select '2004-05-04',',000000000000001041','1211.77485369','0.00000000','6303000040','011','110003' union
select '2004-05-05',',000000000000001041','1211.77485369','0.00000000','6303000040','011','110003' union
select '2004-05-06',',000000000000001041','1211.77485369','0.00000000','6303000040','011','110003' union
select '2004-05-07',',000000000000001041','1211.77485369','0.00000000','6303000040','011','110003' union
select '2004-05-08',',000000000000001041','1211.77485369','0.00000000','6303000040','011','110003' union
select '2004-05-09',',000000000000001041','1211.77485369','0.00000000','6303000040','011','110003' union
select '2004-05-10',',000000000000001041','1211.77485369','0.00000000','6303000040','011','110003' union
select '2004-05-11',',000000000000001041','1211.77485369','0.00000000','6303000040','011','110003' union
select '2004-05-12',',000000000000001041','1211.77485369','0.00000000','6303000040','011','110003' union
select '2004-05-13',',000000000000001041','1211.77485369','0.00000000','6303000040','011','110003' union
select '2004-05-14',',000000000000001041','1211.77485369','0.00000000','6303000040','011','110003' union
select '2004-05-15',',000000000000001041','1211.77485369','0.00000000','6303000040','011','110003' union
select '2004-05-16',',000000000000001041','1211.77485369','0.00000000','6303000040','011','110003' union
select '2004-05-17',',000000000000001041','1211.77485369','0.00000000','6303000040','011','110003' union
select '2004-05-18',',000000000000001041','1211.77485369','0.00000000','6303000040','011','110003' union
select '2004-05-19',',000000000000001041','1211.77485369','0.00000000','6303000040','011','110003' union
select '2004-05-20',',000000000000001041','1211.77485369','0.00000000','6303000040','011','110003' union
select '2004-05-21',',000000000000001041','1211.77485369','0.00000000','6303000040','011','110003' union
select '2004-05-22',',000000000000001041','1211.77485369','0.00000000','6303000040','011','110003' union
select '2004-05-23',',000000000000001041','1211.77485369','0.00000000','6303000040','011','110003' union
select '2004-05-24',',000000000000001041','1211.77485369','0.00000000','6303000040','011','110003' union
select '2004-05-25',',000000000000001041','1211.77485369','0.00000000','6303000040','011','110003' union
select '2004-05-26',',000000000000001041','1211.77485369','0.00000000','6303000040','011','110003' union
select '2004-05-27',',000000000000001041','1211.77485369','0.00000000','6303000040','011','110003' union
select '2004-05-28',',000000000000001041','1211.77485369','0.00000000','6303000040','011','110003' union
select '2004-05-29',',000000000000001041','1211.77485369','0.00000000','6303000040','011','110003' union
select '2004-05-30',',000000000000001041','1211.77485369','0.00000000','6303000040','011','110003' union
select '2004-05-31',',000000000000001041','1211.77485369','0.00000000','6303000040','011','110003' union
select '2004-04-01',',000000000000001041','1055.90476127','0.00000000','6303000040','011','110003' union
select '2004-04-02',',000000000000001041','1055.90476127','0.00000000','6303000040','011','110003' union
select '2004-04-03',',000000000000001041','1055.90476127','0.00000000','6303000040','011','110003' union
select '2004-04-04',',000000000000001041','1055.90476127','0.00000000','6303000040','011','110003' union
select '2004-04-05',',000000000000001041','1055.90476127','0.00000000','6303000040','011','110003' union
select '2004-04-06',',000000000000001041','1055.90476127','0.00000000','6303000040','011','110003' union
select '2004-04-07',',000000000000001041','1055.90476127','0.00000000','6303000040','011','110003' union
select '2004-04-08',',000000000000001041','1055.90476127','0.00000000','6303000040','011','110003' union
select '2004-04-09',',000000000000001041','1055.90476127','0.00000000','6303000040','011','110003' union
select '2004-04-10',',000000000000001041','1055.90476127','0.00000000','6303000040','011','110003' union
select '2004-04-11',',000000000000001041','1055.90476127','0.00000000','6303000040','011','110003' union
select '2004-04-12',',000000000000001041','1055.90476127','0.00000000','6303000040','011','110003' union
select '2004-04-13',',000000000000001041','1055.90476127','0.00000000','6303000040','011','110003' union
select '2004-04-14',',000000000000001041','1055.90476127','0.00000000','6303000040','011','110003' union
select '2004-04-15',',000000000000001041','1055.90476127','0.00000000','6303000040','011','110003' union
select '2004-04-16',',000000000000001041','1055.90476127','0.00000000','6303000040','011','110003' union
select '2004-04-17',',000000000000001041','1055.90476127','0.00000000','6303000040','011','110003' union
select '2004-04-18',',000000000000001041','1055.90476127','0.00000000','6303000040','011','110003' union
select '2004-04-19',',000000000000001041','1055.90476127','0.00000000','6303000040','011','110003' union
select '2004-04-20',',000000000000001041','1055.90476127','0.00000000','6303000040','011','110003' union
select '2004-04-21',',000000000000001041','1055.90476127','0.00000000','6303000040','011','110003' union
select '2004-04-22',',000000000000001041','1055.90476127','0.00000000','6303000040','011','110003' union
select '2004-04-23',',000000000000001041','1055.90476127','0.00000000','6303000040','011','110003' union
select '2004-04-24',',000000000000001041','1055.90476127','0.00000000','6303000040','011','110003' union
select '2004-04-25',',000000000000001041','1055.90476127','0.00000000','6303000040','011','110003' union
select '2004-04-26',',000000000000001041','1055.90476127','0.00000000','6303000040','011','110003' union
select '2004-04-27',',000000000000001041','1055.90476127','0.00000000','6303000040','011','110003' union
select '2004-04-28',',000000000000001041','1055.90476127','0.00000000','6303000040','011','110003' union
select '2004-04-29',',000000000000001041','1055.90476127','0.00000000','6303000040','011','110003' union
select '2004-04-30',',000000000000001041','1055.90476127','0.00000000','6303000040','011','110003' union
select '2004-05-01',',000000000000001087','909.46356789','0.00000000','6303000040','011','110003' union
select '2004-05-02',',000000000000001087','909.46356789','0.00000000','6303000040','011','110003' union
select '2004-05-03',',000000000000001087','909.46356789','0.00000000','6303000040','011','110003' union
select '2004-05-04',',000000000000001087','909.46356789','0.00000000','6303000040','011','110003' union
select '2004-05-05',',000000000000001087','909.46356789','0.00000000','6303000040','011','110003' union
select '2004-05-06',',000000000000001087','909.46356789','0.00000000','6303000040','011','110003' union
select '2004-05-07',',000000000000001087','909.46356789','0.00000000','6303000040','011','110003' union
select '2004-05-08',',000000000000001087','909.46356789','0.00000000','6303000040','011','110003' union
select '2004-05-09',',000000000000001087','909.46356789','0.00000000','6303000040','011','110003' union
select '2004-05-10',',000000000000001087','909.46356789','0.00000000','6303000040','011','110003' union
select '2004-05-11',',000000000000001087','909.46356789','0.00000000','6303000040','011','110003' union
select '2004-05-12',',000000000000001087','909.46356789','0.00000000','6303000040','011','110003' union
select '2004-05-13',',000000000000001087','909.46356789','0.00000000','6303000040','011','110003' union
select '2004-05-14',',000000000000001087','909.46356789','0.00000000','6303000040','011','110003' union
select '2004-05-15',',000000000000001087','909.46356789','0.00000000','6303000040','011','110003' union
select '2004-05-16',',000000000000001087','909.46356789','0.00000000','6303000040','011','110003' union
select '2004-05-17',',000000000000001087','909.46356789','0.00000000','6303000040','011','110003' union
select '2004-05-18',',000000000000001087','909.46356789','0.00000000','6303000040','011','110003' union
select '2004-05-19',',000000000000001087','909.46356789','0.00000000','6303000040','011','110003' union
select '2004-05-20',',000000000000001087','909.46356789','0.00000000','6303000040','011','110003' union
select '2004-05-21',',000000000000001087','909.46356789','0.00000000','6303000040','011','110003' union
select '2004-05-22',',000000000000001087','909.46356789','0.00000000','6303000040','011','110003' union
select '2004-05-23',',000000000000001087','909.46356789','0.00000000','6303000040','011','110003' union
select '2004-05-24',',000000000000001087','909.46356789','0.00000000','6303000040','011','110003' union
select '2004-05-25',',000000000000001087','909.46356789','0.00000000','6303000040','011','110003' union
select '2004-05-26',',000000000000001087','909.46356789','0.00000000','6303000040','011','110003' union
select '2004-05-27',',000000000000001087','909.46356789','0.00000000','6303000040','011','110003' union
select '2004-05-28',',000000000000001087','909.46356789','0.00000000','6303000040','011','110003' union
select '2004-05-29',',000000000000001087','909.46356789','0.00000000','6303000040','011','110003' union
select '2004-05-30',',000000000000001087','909.46356789','0.00000000','6303000040','011','110003' union
select '2004-05-31',',000000000000001087','909.46356789','0.00000000','6303000040','011','110003' union
select '2004-06-01',',000000000000001041','895.24553755','0.00000000','6303000040','011','110003' union
select '2004-06-02',',000000000000001041','895.24553755','0.00000000','6303000040','011','110003' union
select '2004-06-03',',000000000000001041','895.24553755','0.00000000','6303000040','011','110003' union
select '2004-06-04',',000000000000001041','895.24553755','0.00000000','6303000040','011','110003' union
select '2004-06-05',',000000000000001041','895.24553755','0.00000000','6303000040','011','110003' union
select '2004-06-06',',000000000000001041','895.24553755','0.00000000','6303000040','011','110003' union
select '2004-06-07',',000000000000001041','895.24553755','0.00000000','6303000040','011','110003' union
select '2004-06-08',',000000000000001041','895.24553755','0.00000000','6303000040','011','110003'

The Enigma
Posting Yak Master

179 Posts

Posted - 2004-04-23 : 17:01:12

will that do for some sample data

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-04-23 : 17:42:07

Enigma,

I would really consider breaking this into two seperate queries. Have one to give you all records and one to filter by date. If you don't do this, you're going to get stuck with some really bad recompiles and possibly run into problems with getting stuck on a bad execution plan. This can significantly slow down the query.

Can you at least post the complete FROM and WHERE clauses?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources