SQL Server Performance Forum – Threads Archive
index suggestion
I have a table which has a column with a open/close flag. This is an int col which also has a combination of other flags. but when i query for open/close i do a search for where condition (col & 2) = 0. Although i have an index on the flags col im not sure it is helping.any suggestions ?
Have you checked the execution plan of it.
Hi ya, the index is unlikely to be helping unless it covers the query (i.e. you are not selecting/using any columns in the query other than the ones in the index) with flags the only real way to get around it is to not have them as a mask, to force the use of the index or to create a computed column for the flag you’re interested in and index that column Cheers
Twan
execution plan suggests that it is using the index.
im thinking of creating an other short int column and indexing it. im not sure that will help
Hi ya, how many rows are there for each of the flag’s values? the index would only help if the flag you’re selecting on causes a substantially smaller number of rows to be returned than what is in the table… e.g. open=1 is great if only 1% of the table has that state, but not so good if 30% of the table has that state Cheers
Twan
Actually that is the problem. The open=1 can vary. sometimes all may be open=1 and sometimes open=1 may be 0 <img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ /> <br />all i need is a count of such open=1 or open=0
Is it using an index on int that is bitmap of flags? There is a bit data type on mssql server. You can use it for your flags. However, indexing flags is too complex problem to handle without additional information like table structure and typical queries accessing that table.
do you think the bit column can speed up things ? I work for a security firm. there is no way i can post any detailed explanation of the structures <img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ /><br /><br />but if you need any more info i can provide you that…
Bit columns solutions is on the same line with Twan’s suggestion. They are implemented the similar way you implement your int bitmap, only it is easier to query them using normal sql syntax and you can index them without creating computed columns. However to know if it is worth to index them additional info I mentioned is needed and even more we would need to know the number of rows in the table, the number of rows that satisfies specific condition, the number of rows matching each flag’s value… for critical queries. You can try to post the structure without real column names but with data types, index structure and so on. Example:
create table a(
pkColumn int identity(1,1),
col1 varchar(50),
BitMap column int,
)
go
There are N rows in the table, critical query is:
select … from a where [email protected] and flag1 = @flag1 where flagN = bitMap[N] There are X rows where flagN = 0 and N-X rows where flagN = 1, Z rows from table a matches condition col1 = @col1.
…
something like that.
]]>