Michael R Mattys
michael.mattys at adelphia.net
Wed Oct 29 22:55:46 CST 2003
John,
This should bring back some memories.
Message 4 in thread
From: jwelz at hotmail.com (jwelz at hotmail.com)
Subject: Re: bitwise operators
Newsgroups: comp.databases.ms-access
View this article only
Date: 2000/02/08
It is possible to perform a bitwise query in Jet 3.5 without using a
function. Rather than doing the operation in the 'Where' clause, do it
in the Join. I keep a 'join table' in my front end. I generally limit
this to single byte bit masks to keep the size of the join table down.
The join table is laid out with 8 fields headed 'b1, b2, b4, b8, b16,
b32, b64, b128'. In each field are all the values for which the And
operation returns true, there are 128 values for each field, and the
concept of row is irrelevant. I sort and index the data. Field b1 has
the values '1, 3, 5, 7, 9...', Field b2 has the values '2, 3, 6, 7, 10,
11...', Field b4 has the values '4, 5, 6, 7, 12, 13, 14, 15, 20...'
....and field b128 has the values '128, 129, 130, 131....' The
operation is analagous to a 'Where' clause comparing a field value with
an IN(1, 2, 5...) list, without the syntactic nonsense of generating a
complex list on the fly, though that is a 'Where' approach that can be
made to work.
To query on a bit, the sql reads:
"Select [fields] From [tblData] Inner Join [JoinTable] on
[tblData].[AndedField] = [JoinTable].b" & 2 ^ (bytBitNumber - 1)
Where you pass the bit position (1 through 8) to the variable
bytBitNumber (or just the number value of the bit(s or'ed)).
By joining a field on more than one column in the join table, and in
conjunction with the not operator, you can query on several bit
positions and values concurrently.
I find this method significantly faster than calling a function for a
calculated field since the query engine does the work very efficiently
(rather than calling a function n thousand times for n thousand records
queried). This approach works with any version of sql that supports an
inner join. Used for bitwise queries on bytes, the join table stores a
mere 1k of data (datatype byte for all 8 fields, 128 rows, same amount
again for indexing I assume). If you need to And a bit higher than the
128 (8th bit), I find it more efficient to store two bytes to keep the
join table small and fast. Alternatively, you could parse the field
into hi/low bytes or more as required for larger data types.
Although a violation of normalization rules, the ability to store 8
boolean values in a single field gives measurable performance gains and
I use it in any table where there is more than a single two state
field. Well commented and documented usage should cause no problems.
A byte can also store three state (or more) fields should that be
expedient though I'd consider the latter two cases only in an extremely
static design that needed a bit of tweaking since design enhancement
maintenance requires customized extraction of values to forms and
reports. Design requirement changes could require extensive changes to
code and subsequent developers not familiar with the methodology would[...
didn't bother to get the rest.]Michael R. Mattys
Try MattysMapLib for MapPoint at
www.mattysconsulting.com
----- Original Message -----
From: "John Colby" <jcolby at colbyconsulting.com>
To: "Access Developers discussion and problem solving"
<accessd at databaseadvisors.com>
Sent: Wednesday, October 29, 2003 8:43 PM
Subject: RE: [AccessD] brain farts
> >In Access, try using a CINT(Frm_GroupVisible) etc to force it to an Int
> before doing the "OR" comparison.
>
> Tried that, no joy.
>
> John W. Colby
> www.colbyconsulting.com
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Stuart
> McLachlan
> Sent: Wednesday, October 29, 2003 8:24 PM
> To: John Colby; accessd at databaseadvisors.com
> Subject: RE: [AccessD] brain farts
>
>
> On 29 Oct 2003 at 17:48, John Colby wrote:
>
> > I tried this and it's giving me errors. This is the actual built up SQL
> > statement.
> >
> > UPDATE MsysForms SET [FRM_GroupOpen] = [FRM_GroupOpen] ~ 4,
> > [FRM_GroupVisible] = [FRM_GroupVisible] ~ 4, [FRM_GroupEdit] =
> > [FRM_GroupEdit] ~ 4, [FRM_GroupAddRec] = [FRM_GroupAddRec] ~ 4,
> > [FRM_GroupDelRec] = [FRM_GroupDelRec] ~ 4 WHERE (((MsysForms.FRM_ID) In
> > (28,34,33,35,32,29,31,30)));
> >
> > Is that as you understand it to be used? Likewise it didn't like the
> > vertical bar.
> >
>
> I posted a second one, a couple of seconds later which for some reason
> didn't make it to the list.
>
> In that I said "Sorry, I thought I was still scanning the dba-SQL list".
>
> In Access, try using a CINT(Frm_GroupVisible) etc to force it to an Int
> before doing the "OR" comparison.
>
>
>
>
> --
> Stuart McLachlan
> Lexacorp Ltd
> Application Development, IT Consultancy
> http://www.lexacorp.com.pg
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
>
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com