Performance Tuning - 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 2008 Forums

Transact-SQL (2008)

Performance Tuning

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-11-11 : 05:57:41

I am trying to improve the table design for the following query.
Note that the field DataDate is used in the where clause.

select * from tblMain where DataDate = '08 nov 2011'

The table design is as follows:

CREATE TABLE [dbo].[tblMain](
[Currency] [char](3) NOT NULL,
[DDate] [datetime] NOT NULL,
[Field3] [float] NOT NULL,
[Field4] [float] NULL,
[Field5] [float] NULL,
[Field6] [float] NULL,
CONSTRAINT [PK_tblMain] PRIMARY KEY CLUSTERED
(
[Currency] ASC,
[DDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

The fields Currency and DDate is used as Primary Keys because they are unique for each record.

Do you have any suggestions on how to improve on this query performance please?
Thanks

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-11 : 06:02:56

create index ix_tblMain_01 on tblMain (DDate)

or better change the PK to (DDate, Currency)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-11 : 06:07:22

currently waht does execution plan suggests for above query?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-11 : 06:16:57

It's a table (or clustered index) scan from the structure given.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-11-11 : 06:29:03

quote:
Originally posted by visakh16

currently waht does execution plan suggests for above query?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

i can run the estimated execution plan. But I do not see anything about mentioning what index to use.
Where do I find this information?
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-11 : 06:50:58

quote:
Originally posted by arkiboys

quote:
Originally posted by visakh16

currently waht does execution plan suggests for above query?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

i can run the estimated execution plan. But I do not see anything about mentioning what index to use.
Where do I find this information?
Thanks

nope. look at actual execution plan and see whether its currently doing a clustered index scan as Nigel suggested. See what all predicates its looking for and based on it create a suitable index

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-11 : 07:22:03

quote:
Originally posted by arkiboys

quote:
Originally posted by visakh16

currently waht does execution plan suggests for above query?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

i can run the estimated execution plan. But I do not see anything about mentioning what index to use.
Where do I find this information?
Thanks

If the table structure is as you've said then there's no point. Change the indexes as I suggested then see what it does.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-11-11 : 07:54:22

quote:
Originally posted by nigelrivett

create index ix_tblMain_01 on tblMain (DDate)

or better change the PK to (DDate, Currency)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

created a non clustered index on DDate.
Ran actual execution plan but still do not get any notes on indexes.
I only see the diagrams which also shows an index seek now.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-11 : 08:08:05

>> shows an index seek now.

That shows you that the index is being used.
I suspect that the PK with currency as the first column is pretty useless. Eiother change it to have date first (in which case you can get rid of this index) or add an identity and put the PK on that and make currency, ddate a unique index.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-11-11 : 08:45:25

Thank you

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-11-11 : 10:47:25

After adding the non clustered index, there is about 500 less of logical reads. But the elapsed time is about 100 ms more.
I can see that there is less of logical reads but why is there a higher elapsed time?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-11 : 11:20:33

Now it will do an indexed read followed by a lookup.
If you are are worried about 100 ms then you should check exactly what data you need
also change the index to ([DDate],[Currency],[Field3,[Field4],[Field5],[Field6]) or the pk I suggested.

Also your timing could be when the table is in memory - have you ried when it needs to read from disk?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2011-11-11 : 11:24:54

quote:

i can run the estimated execution plan. But I do not see anything about mentioning what index to use.
Where do I find this information?
Thanks

The missing index feature has limitations so it may not always show you a suggestion and the suggestion may not always be a good one. And I think this is only in SSMS 2008. Here is a link that shows you what the index suggestion could look like:

[url]http://sqlblog.com/blogs/ben_nevarez/archive/2009/09/25/the-missing-indexes-feature.aspx[/url]

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-11-11 : 11:34:23

Thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources