SQL query optimization

1

I have a table with 4 columns
table_1
id, name,code,time
what I am trying to do is
select * from table1 where code in (30)
it takes 25 seconds to return 70k columns. I have indexed "code" column it still takes same time. But when I filter it down with name = 'user1' and code in (30). it returns data back in just over 1 second. however I don't want to filter it down to name because I want the data to return all the users assign 30.

2

hi

hope this helps

select 
      col1 -- ONLY columns you need 
	, col2  
from 
    table1 
where 
     code = 30 -- IN changed to = 
	 
-- filtered index ( where code = 30 )

-- columnstore index

-- In-memory queries
3

What indexes exist on the table? In particular, what is the clustered index on the table?

What is the datatype of column "code"?

Which version of SQL (SQL2016 Ent, SQL2008 Std, etc.) are you running? Are you using data compression on the table?

4

Without enough information its hard to understand. In my opinion, you may need a NC Index to support your query.

5

A nonclustered index won't help on a SELECT * query, unless you include all columns in the table in that index -- which means you've duplicated the entire table.

1 Like
6

Another possible pertinent question would be... returning 70K rows to where??? The screen???