If I am adding an integer column, is there any integer size which will
provide a marginal benefit in speed compared with any other? I'm thinking
of less conversion in processing or processor handling. Like 32-bits being
native, so it will be a bit better than a 16- or 8-bit integer.

I think the numbers I'm going to be dealing with will be small, but I'd
not like to choose an integer that has a higher cost.

Thanks,

/Matthew Jones/

Matt,

<< If I am adding an integer column, is there any integer size which will
provide a marginal benefit in speed compared with any other? >>

Using the default processor word size is always fastest in terms of the CPU
speed, so 32-bit = Integer and 64-bit = LargeInt.

--
Tim Young
Elevate Software
www.elevatesoft.com

Many thanks. Is a boolean handled as a byte, or as a bitfield somehow?
Is "WHERE (MyBool = TRUE)" faster or slower (in the general case!) than
"WHERE (My32BitInt = 1)"?

I'm just wondering about whether to use integer or booleans for boolean
situations. In fact, what I'm really wondering is how to handle a multiple
inclusion WHERE. In essence, I have a number of complex queries based on
appearance of certain data in multiple tables. For example in the typical
invoicing situation you might want a list of all customers who have bought
stock code "ABC123". Then add in all customers who bought anything over
1,000 USD. Then add in some other odd criteria. Having got those, I want
to do various displays of the data, as a pivot, as graphs, and in other
forms. It therefore struck me that this was complicated, and probably not
very efficient to retrieve. Thus I'm pondering using a number of columns
in one of the tables which will start off all set to zero, and when the
criteria is changed a set of UPDATE queries will be run to set them to
zero, and then set them to 1 or some other suitable value if that
"customer" satisfies the criteria. I can then make the query a lot simpler
because I just say "WHERE (FlagField > 0)". This means the query will be
faster, but it means I have to update the database each time the criteria
changes, and I'm not sure I like the idea of that generally. It might feel
better if I had another table specifically for the criteria flags, but
then that just starts down the complications route for DBISAM again.

If anyone has comment on any of this, I'd very much like it! Thanks.

/Matthew Jones/

Matt,

<< Many thanks. Is a boolean handled as a byte, or as a bitfield somehow? >>

A Boolean is word-sized (2-bytes or 16-bits) in DBISAM.

<< Is "WHERE (MyBool = TRUE)" faster or slower (in the general case!) than
"WHERE (My32BitInt = 1)"? >>

The word size is much less important in WHERE conditions than making sure
that you have indexes available for optimization.  Reducing I/O is the key
to speed in that case.

<< I'm just wondering about whether to use integer or booleans for boolean
situations. >>

Always use Booleans.  They are the preferred and native type for
representing logical True/False/NULL values.

<< In fact, what I'm really wondering is how to handle a multiple inclusion
WHERE. In essence, I have a number of complex queries based on
appearance of certain data in multiple tables. For example in the typical
invoicing situation you might want a list of all customers who have bought
stock code "ABC123". Then add in all customers who bought anything over
1,000 USD. Then add in some other odd criteria. Having got those, I want to
do various displays of the data, as a pivot, as graphs, and in other forms.
It therefore struck me that this was complicated, and probably not very
efficient to retrieve. Thus I'm pondering using a number of columns in one
of the tables which will start off all set to zero, and when the
criteria is changed a set of UPDATE queries will be run to set them to zero,
and then set them to 1 or some other suitable value if that
"customer" satisfies the criteria. I can then make the query a lot simpler
because I just say "WHERE (FlagField > 0)". This means the query will be
faster, but it means I have to update the database each time the criteria
changes, and I'm not sure I like the idea of that generally. It might feel
better if I had another table specifically for the criteria flags, but then
that just starts down the complications route for DBISAM again. >>

Personally I would go with the actual comparisons and skip the updating
stuff.  Using an integer or Boolean comparison versus a string or some other
type of comparison won't improve your speed that much to make it worth all
of that work.

--
Tim Young
Elevate Software
www.elevatesoft.com

Thanks for the information on the Boolean.

> Personally I would go with the actual comparisons and skip the updating
> stuff.  Using an integer or Boolean comparison versus a string or some
> other type of comparison won't improve your speed that much to make it
> worth all of that work.

The problem I see is having confidence in my SQL to generate the right
results! Some of it is truly horrible, and by doing it in stages I can see
the intermediate result. But I'll keep this under review.

/Matthew Jones/

mattjones@cix.co.uk (Matthew Jones) wrote in
news:memo.20061218093848.1220I@nothanks.nothanks.co.uk:

> Thanks for the information on the Boolean.
>
>> Personally I would go with the actual comparisons and skip the
>> updating stuff.  Using an integer or Boolean comparison versus a
>> string or some other type of comparison won't improve your speed that
>> much to make it worth all of that work.
>

In a dim and distant previous existance when programs had overlays to
avoid going over 32kb RAM, and disk space was limited to 5 or 10 Mb, I
used to use a database which reserved the first byte of every index
record for 8 1-bit flags.

You were free to set/reset flags for any reason that suited you, and then
filter on any combination.

Worked lightning-fast!

perhaps an idea for EDB v2.0 ?

--
Chris
(XP-Pro + Delphi 7 Architect + DBISAM 4.24 Build 1)

Chris,

<< You were free to set/reset flags for any reason that suited you, and then
filter on any combination.

Worked lightning-fast!

perhaps an idea for EDB v2.0 ? >>

While I grant that bitmap operations can be very fast in modern processors,
I'm not sure if such an arrangement would be worth the extra time involved.


--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in
news:AD6377C6-658E-4B92-AB7F-D4AB4B9E6C13@news.elevatesoft.com:

> While I grant that bitmap operations can be very fast in modern
> processors, I'm not sure if such an arrangement would be worth the
> extra time involved.
>
>

from OUR point of view, it may well be worth YOUR extra time

But perhaps we can get somewhere down that path with an integer field and
some functions to manipulate a set of constants:
NEW_YORK = 2^^0
LONDON = 2^^1
FRIDAY = 2^^2
...
YELLOW = 2^^31

not much use for checking for one bit regardless of the others, but could
be useful for distinct sets, just one index instead of AND-ing several...

Nah, on second thoughts, I'm not sure if such an arrangement would be
worth the extra time involved. <bg>

--
Chris
(XP-Pro + Delphi 7 Architect + DBISAM 4.24 Build 1)

Chris,

<< from OUR point of view, it may well be worth YOUR extra time >>

Well, that's a no-brainer.  It's always worth my time from your point of
view.

<< But perhaps we can get somewhere down that path with an integer field and
some functions to manipulate a set of constants:

not much use for checking for one bit regardless of the others, but could be
useful for distinct sets, just one index instead of AND-ing several...

Nah, on second thoughts, I'm not sure if such an arrangement would be worth
the extra time involved. <bg> >>

The issue is how to integrate bit-level operations on such an integers into
the indexes.  It would require that the bit-level functions be
"index-aware", which usually means making them actual operators instead.
There's just a lot little hurdles to jump over on the way to the final end
result, and that is what I'm questioning in terms of time.  IOW, is the
resultant speed improvement *really* that great to warrant such an effort.
My initial feelings are no, that you won't get nearly as much improvement as
you think because database systems are I/O-bound mostly and improvements of
this nature subtract minor fractions of a percent to the total time required
for a long operation.  I can't tell you how many hours I'm spent with a
profiler working on improvement like this with DBISAM and EDB, especially in
terms of index operations, only to end up slicing a very small amount of
time off of the total.

--
Tim Young
Elevate Software
www.elevatesoft.com