| Subject | Re: [firebird-support] Who's using some GUID (Global Unique IDentifiers) in FB? |
|---|---|
| Author | David Johnson |
| Post date | 2004-04-10T00:38:09Z |
I will address the performance question first:
If you would provide a code snippet, then I can apply your suggestion to my test suite for comparison, and I will send you a copy of my test suite. The test suite is written in Object Pascal (Delphi) using IBX, IBO, and SQLExpress. I will need the DDL to create the column, and the code to translate a GUID record to the base64 representation.
I haven't tried this yet, but others have reported no measurable difference between 38 character GUID and 4 byte INTEGER keys up to 1,000,000 records. My own computations suggest that the performance differential will start to show at about 4,000,000 records with 1 more I/O (10 milliseconds) per retrieval. I will have to add these test cases when I have some free time.
When I am seeing a 50% performance improvement by using IBO with parameters dereferenced by index, I see the connectivity tools as being a more serious bottleneck than the indexing scheme. Until I have a native GDS based testcase, my tests probably won't have the sensitivity to detect the performance differential.
From what I have measured and read from others experience on this list, it would not buy enough in _my_ circumstances to be worthwhile. Other circumstances do exist, and there are places where the compression would be worthwhile. If I were writing for palm or something where space was at a premium, that would be a very different story. Ann's response shows how your suggestion can be maximized, possibly beyond what your computations suggested.
Support issue:
If you recognize it as a GUID right away then that is true. However, you would need to know that it was a GUID in advance. The standard text representation, on the other hand, is instantly recognizable as a GUID in any context by anyone with even minimal exposure to the concept. That can save valuable time when a problem costs you $500,000 per hour in lost business. At $0.001 per megabyte, you would need to save 138,889 megabytes to equal one second's worth of support time. Since the performance benefit is marginal, the payoff direction is obvious in _my_ environment. In other circumstances, my choice might be different.
If you would provide a code snippet, then I can apply your suggestion to my test suite for comparison, and I will send you a copy of my test suite. The test suite is written in Object Pascal (Delphi) using IBX, IBO, and SQLExpress. I will need the DDL to create the column, and the code to translate a GUID record to the base64 representation.
I haven't tried this yet, but others have reported no measurable difference between 38 character GUID and 4 byte INTEGER keys up to 1,000,000 records. My own computations suggest that the performance differential will start to show at about 4,000,000 records with 1 more I/O (10 milliseconds) per retrieval. I will have to add these test cases when I have some free time.
When I am seeing a 50% performance improvement by using IBO with parameters dereferenced by index, I see the connectivity tools as being a more serious bottleneck than the indexing scheme. Until I have a native GDS based testcase, my tests probably won't have the sensitivity to detect the performance differential.
From what I have measured and read from others experience on this list, it would not buy enough in _my_ circumstances to be worthwhile. Other circumstances do exist, and there are places where the compression would be worthwhile. If I were writing for palm or something where space was at a premium, that would be a very different story. Ann's response shows how your suggestion can be maximized, possibly beyond what your computations suggested.
Support issue:
If you recognize it as a GUID right away then that is true. However, you would need to know that it was a GUID in advance. The standard text representation, on the other hand, is instantly recognizable as a GUID in any context by anyone with even minimal exposure to the concept. That can save valuable time when a problem costs you $500,000 per hour in lost business. At $0.001 per megabyte, you would need to save 138,889 megabytes to equal one second's worth of support time. Since the performance benefit is marginal, the payoff direction is obvious in _my_ environment. In other circumstances, my choice might be different.
----- Original Message -----
From: Brad Pepers
To: firebird-support@yahoogroups.com
Sent: Friday, April 09, 2004 10:34 AM
Subject: Re: [firebird-support] Who's using some GUID (Global Unique IDentifiers) in FB?
>> Wouldn't it make sense to convert the GUID to/from base64 so you get 6
>> bits per byte instead of 4 when using hex? That way your GUID's can be
>> char(22) instead of char(32) to hold 128 bits. It would save you ~1MB on
>> the database with 100,000 rows and more if you also have FK's.
>
> I jsut spent five years on a rapid response/support team. Text is
> readable with any tool, and the standard format is instantly identifiable.
> Base64 could be anything.
>
> At $1.00 per gigabyte at Office Depot, storage space is not worth as much
> as my time. If space was a consideration then I would agree, but 1MB is
> $0.001 worth of storage. It takes a lot of MB's to make up for the cost of
> my time.
I suppose so though base64 isn't anything odd or binary. You can easily
represent it with the characters A-Z, a-z, 0-9, and two other normal
characters so it is just regular text. Instead of packing 4 bits per byte
though you are getting 6 at no loss of being human readable and it might be
enough to make the difference in IO costs when you have a lot of records.
Seems to me to be a simple change that uses less space, might make selects
faster when you have tons of records, and doesn't lose you anything in human
readability!
--
Brad Pepers
brad@...
Yahoo! Groups Sponsor
ADVERTISEMENT
------------------------------------------------------------------------------
Yahoo! Groups Links
a.. To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/
b.. To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com
c.. Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
[Non-text portions of this message have been removed]