Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 9 of 9 total |
Best integer |
Thu, Dec 14 2006 3:06 PM | Permanent Link |
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/ | |
Thu, Dec 14 2006 4:34 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Fri, Dec 15 2006 4:42 AM | Permanent Link |
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/ | |
Fri, Dec 15 2006 2:24 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Mon, Dec 18 2006 4:44 AM | Permanent Link |
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/ | |
Mon, Jan 22 2007 4:42 AM | Permanent Link |
Chris Erdal | 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) |
Mon, Jan 22 2007 7:58 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Tue, Jan 23 2007 8:47 AM | Permanent Link |
Chris Erdal | "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) |
Tue, Jan 23 2007 2:02 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
This web page was last updated on Thursday, April 18, 2024 at 10:42 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |