September 28, 2006 at 6:08 pm
September 28, 2006 at 6:21 pm
Move your rights to look-up table and do the search on it.
It will be scan anyway, but it will be scan on small table.
Than join this lookup to FTE_TABLE_SIT on indexed column.
select dwPlayerIx
from FTE_TABLE_SIT F
inner join RightsTable R ON R.dwRights_Public = F.dwRights_Public
where (R.dwRights_Public&4) > 0
You must have indexes on dwRights_Public columns in both tables to make it work.
_____________
Code for TallyGenerator
September 29, 2006 at 1:54 am
Any fuinction applied to a column will prevent index seeks, even if it's something simple like column+0
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 2, 2006 at 10:15 am
I realize that (R.dwRights_Public&4) makes agrument NON-SARGABLE. I found another solution that might work well for my sccenario: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=5970
Thanks,
Igor
October 2, 2006 at 6:26 pm
Which SARGABLE solution have you found over there?
_____________
Code for TallyGenerator
October 2, 2006 at 8:48 pm
Create and index on computed column (dwRights&4).
October 2, 2006 at 8:49 pm
I dunno wth he found but I kinda like the looks of the calculated column... could be sargeble that way...
--Jeff Moden
October 2, 2006 at 11:04 pm
Did you read anything about index on BIT column?
It does not work by definition.
It's strong recommendation in any book - don't create index on bit column.
You don't need to believe me or anyone else, but you just do the test. Create suggested computed column and lookup table with FK on indexed column.
And run 2 queries. One against computed column, another - using JOIN to lookup table.
It's better one time to see...
_____________
Code for TallyGenerator
October 3, 2006 at 6:06 am
Thanks Serqiy. Nope, I haven't actually lost my mind (yet). I think just about everyone except the guy who wrote that knows that creating indexes on bit columns is a bit counter-productive (that's a pun, son ).
Nope... it seemed to me that some calculated columns operating on the wide-bit-wise column make life a little easier and was trying to figure out if there was a way to make things indexable using those non-bit-defined calculated columns. But even when you combine some of the more heavily used combinations, you don't come up with anything unique enough to really be worthwhile as an index... You know me... I always have to try these things out
--Jeff Moden
October 3, 2006 at 6:57 pm
Jeff, Sergiy,
Thanks for your feedback. Computed column dwRights_Public&4 has, indeed, two distinct values (0 and 4), even though field type is defined as INT. As I mentioned before, most of the records will have value equal 0. Query using '...where (R.dwRights_Public&4) > 0' condition is, in fact, making use of index on computed column. I populated table in development and did run some tests. You can take my word on it
By the way, I did read something about index on BIT fields
Igor
October 3, 2006 at 7:59 pm
Sounds like you did your homework... glad it worked out.
--Jeff Moden
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply