| Author |
Topic |
|
tijoen
Starting Member
4 Posts |
 Posted - 2008-09-02 : 18:52:35
|
| Hi!I implemented a way to create infinite flags using VARCHAR and I would like if it is good or not to what I am going to create.Here, imagine these simple tables:Clients, Objects and ClientsObjectsClients and Objects are a very simple table with nothing but an ID columnClientsObjects is a table with an ID column and two other column representing the IDs that are in Clients and Objectsex ClientsObjects:clo_id, cli_id, obj_idwhere clo_id is the ID column and primary key on ClientsObjectscli_id is the ID column and primary key at Clientsobj_id is the ID column and primary key at Objectsso, a client can have several objects, for each I need a row inside this table, ok?with the bitwise flags inside one column I wouldn't need this tableI would only need cli_obj_flags, and inside this column will have all obj_id that it hasI created procedures called IsBitSet, RemoveBit and SetBit, with those I can manipulate a field that can contain infinite bitshere how they are implemented inside a column:lets say you would like to turn on positions 3, 32 and 65 of a bitthe result in the varchar column would be:8,1,4you just have do div the position you want to turn on for 32 and then you set a bit with that position in the array3 is 8 in a bit1 is 0 position in a bit, 0 if the first position of a bit, 32 less 31 is 1 and one in this case is the 0 position of the second bit we turned onwell, its hard to explain and my english is not that goodanyone that understands this post and want to give a hand...is this good? will performance be severed?because I wouldn't need a third table, neither mounts of integer data to the sql to map, on another hand it would always call the IsBitSet to check if the Client has X objectThanks in advance!Jonathan |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2008-09-03 : 04:18:51
|
| Bitmasks aren't anything new. They can be useful, they can be a pain.The thing you'll need to watch for is query performance. Indexing that field is useless, seeing as every query that filters on that field will be using a function, essentially preventing index seeks. If you're only going to be filtering on various bitmasks of that field when you query the table, then you'll be getting a table scan. If there's under a hundred rows in the table, that may not be noticeable. If the row count climbs into the thousands, you may see slow queries against that table.--Gail ShawSQL Server MVP |
 |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-09-03 : 10:38:36
|
quote:
Originally posted by tijoen
I created procedures called IsBitSet, RemoveBit and SetBit, with those I can manipulate a field that can contain infinite bits
How are you storing these infinite bits? varchar(max) datatype?Boycotted Beijing Olympics 2008 |
|
|
|
tijoen
Starting Member
4 Posts |
Posted - 2008-09-03 : 10:52:29
|
| varchar(255)I am testing the performance right now withe scalar functions I createdin the mean time i will create an user defined type of this and check performancei will come back with the results |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-09-03 : 11:12:07
|
| With any large number or records and flags, your performance is going to be horrible.With a small number of flags, you'd be better off with bigint and using the built-in bitwise operators.Bigint will handle 31 different flags.Boycotted Beijing Olympics 2008 |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2008-09-03 : 11:22:28
|
| Performance will be pretty bad on large numbers of rows because of the inability to use any indexes.--Gail ShawSQL Server MVP |
|
|
|
tijoen
Starting Member
4 Posts |
Posted - 2008-09-03 : 11:23:32
|
| yeah, initially I created this so I could have more than 31 flags but didn't think about it using in a large scale, only for little flags I would need to havenow I am building a user defined type and I am kinda excited because never did this before with sql servereven tho I think it will not have a good performance, i still want to do it hehethanks,Joe |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-09-03 : 13:39:14
|
| I've seen binary columns used for this type of thing where each binary position contained 8 flags (0-7 for the 1st postion, 8-13 for the second, etc.). To re-state part of what Gail said: Assuming you have a cli_obj_flag in clients table, this method works quite well if you only need to find all objects for a client. However, if you want to find all the clients with a specific object set then it becomes very expensive.Don't be tempted into developing code that few will be able to maintain because "its cool". Stick with what is proven and is standard practice: assiciation tableBe One with the OptimizerTG |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-09-03 : 16:26:39
|
| Exactly what do you think a "user-defined" datatype is, and what do you think it is going to get you?User-defined datatypes are at best annoying, and at worst deceiving.Boycotted Beijing Olympics 2008 |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2008-09-03 : 18:56:05
|
>> even tho I think it will not have a good performance, i still want to do it hehethat's a strange instinct...
elsasoft.org |
|
|
|
tijoen
Starting Member
4 Posts |
Posted - 2008-09-04 : 15:16:44
|
guys, it worked pretty well!I don't know what I am doing wrong, because I probably am... Its working wonderfuly! (even better than association! thats why I am probably doing something wrong  )Made some tests with 3 million recordsthe select using flags.IsBitSet(x) took me 0 seconds in any time I ran itthe normal association select took me 3 seconds the first time I ran it and a second time it took 0 secondsbut wow, thats impressive!do you guys want me to share the code?So you can see whats up with itI can zip it with the sql statements that I used in the testsJoe |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-09-05 : 08:15:11
|
| I'd be curuious to see the table structures (including indexes and PK) of the tables for both methods and the sql statements used to query from themBe One with the OptimizerTG |
|
|
|
|
Infinit flags? is it good?