0

recently was asked to help with query optimization

The table looks like this: 

create table dbo.Table
( 
 id int identity primary key clustered ,
 column_1 varchar(64) not null  ,
 Date datetime not null ,
 Column_2 varchar (32) not null ,
 Column_3 int not null 
)

and select looks like

select * from Table where column_1 = @value1 and Date > @value2

I propose to show columns names instead of * in select , because it can help avoid loading unneeded data, also propose create nonclustered index on column_1. However, execution plan still shows the same amount of memory used by query.

What else should I check or add into the query?

Cameron
  • 2,574
  • 22
  • 37
Andrey
  • 1,629
  • 13
  • 37
  • 65

1 Answers1

2

You can optimize the query by using indexes. The one you want would be on column_1 and date:

create index idx_table_column1_date on table(column_1, date);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • i already create them (composite index) but execution plane didn't change, is there anything else i should check? or i should create separate indexes for column_1 and date columns – Andrey Nov 07 '14 at 19:30
  • What's the execution plan say? – Cameron Nov 07 '14 at 19:33
  • 1
    @Andrey How many rows in the table? How many match the predicate? What is the datatype of `@value1`? – Martin Smith Nov 07 '14 at 19:35
  • @MartinSmith I generate 1000000 rows in the table `@value1` is varchar which declared and looks like 'HR' – Andrey Nov 07 '14 at 19:39
  • 1
    @Andrey - How many of them match the condition? Also are you sure your query is using `=` not `LIKE`? Your comment originally said `'%HR%'` – Martin Smith Nov 07 '14 at 19:41
  • @MartinSmith sorry to confuse you `@value1` = 'HR' I generate 1 mil records (with loop statment) and 43 000 rows are matching – Andrey Nov 07 '14 at 19:43
  • 1
    That's 4.3%. This might be over the tipping point. Do you get a seek if you do `SELECT column_1, date` with the same `WHERE` clause (instead of `SELECT *`)? – Martin Smith Nov 07 '14 at 19:44
  • @MartinSmith what you mean by tipping point? you think after this point my indexes useless? – Andrey Nov 07 '14 at 19:45
  • 1
    @Andrey - See [Why aren’t those nonclustered indexes being used?](http://www.sqlskills.com/blogs/kimberly/why-arent-those-nonclustered-indexes-being-used/) – Martin Smith Nov 07 '14 at 19:46
  • @MartinSmith execution plan show me same numbers if I use `select * ` or `select column_1` – Andrey Nov 07 '14 at 19:47
  • 2
    @Andrey Actually I just noticed this bit in your question "execution plan still shows the same amount of memory used by query". Quite possibly you are focussing on some number that doesn't make any sense to be focussing on. Both queries will just require minimal amounts of memory anyway if you are looking at the memory grant figure. You should be looking at things like scan vs seek. – Martin Smith Nov 07 '14 at 19:51
  • @MartinSmith I noticed strange ting execution plane still show me that its doing scan by clustered index which is on ID column, looks like its not using nonclustered at all – Andrey Nov 07 '14 at 19:56