| Subject | Re: [firebird-support] Re: GUID as Primary Key |
|---|---|
| Author | David Johnson |
| Post date | 2004-03-03T11:32:32Z |
I was running 13 selects per transaction, which is comparable to what I expect in my final application. You can only run about 12 TPS on this system - the transaction "overheads" outweigh the actual query requirements.
The GUID's were stored in the standard string format (no tweaking), like this:
{1BF8AE80-6CC1-11D8-9D20-00400506FAF5}
So the distinctive part of the index was first, and the repetitive part last.
The query was parameterized, so it was only prepared once. The prepare can take longer than the actual query.
The timing was done by selecting 4,000,000 records randomly from the 4,000,000 record database, representing the worst possible scenario for any DBMS optimization mechanism since the odds were in favor of hammering the buffer pool on every select.
The test was sensitive enough to isolate the overheads involved in passing parameters around the in Delphi components from the actual performance of the query. About 30% performance improvement over the Delphi components would be expected by coding directly to the gds32 API because of the time involved in moving variants around.
DBXpress and IBXpress were both unable to complete the test. The former crashed in the Borland DLL after exactly 2080 selects, regardless of version of interbase or firebird that was used, and the latter has a memory leak so it crashed well before the 4,000,000 selects were completed (and slowed down dramatically at some point when it started running in virtual memory instead of physical).
The GUID's were stored in the standard string format (no tweaking), like this:
{1BF8AE80-6CC1-11D8-9D20-00400506FAF5}
So the distinctive part of the index was first, and the repetitive part last.
The query was parameterized, so it was only prepared once. The prepare can take longer than the actual query.
The timing was done by selecting 4,000,000 records randomly from the 4,000,000 record database, representing the worst possible scenario for any DBMS optimization mechanism since the odds were in favor of hammering the buffer pool on every select.
The test was sensitive enough to isolate the overheads involved in passing parameters around the in Delphi components from the actual performance of the query. About 30% performance improvement over the Delphi components would be expected by coding directly to the gds32 API because of the time involved in moving variants around.
DBXpress and IBXpress were both unable to complete the test. The former crashed in the Borland DLL after exactly 2080 selects, regardless of version of interbase or firebird that was used, and the latter has a memory leak so it crashed well before the 4,000,000 selects were completed (and slowed down dramatically at some point when it started running in virtual memory instead of physical).
----- Original Message -----
From: David Johnson
To: firebird-support@yahoogroups.com
Sent: Wednesday, March 03, 2004 3:09 AM
Subject: Re: [firebird-support] Re: GUID as Primary Key
CHAR 38
----- Original Message -----
From: Luke Young
To: firebird-support@yahoogroups.com
Sent: Wednesday, March 03, 2004 12:43 AM
Subject: [firebird-support] Re: GUID as Primary Key
What was the GUID stored as?
Thank-you all btw
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]
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]