Hi All,
Wasn't sure whether to start here or on the support list. Came
here because it partly relates to another thread. I was trying
to create a script to reproduce the problem described by Thomas
in the thread: [Firebird-devel] GEN_UUID() performance?
At first I had trouble seeing the problem, and that was because
for Classic and Super-Classic (ie. FB v2.5) the problem does
not show up if you do the test on a newly created database
(immediately after CREATE DATABASE). [Note the problem does
not show up on Super-Server at all.]
I've copied a script to demonstrate the issue below. It is
intended to be run as: isql -q -i script_name.sql
Run as shown below (with the CONNECT at line 12 commented out)
and it finishes in approx 2sec on all servers. Uncomment that
CONNECT line and it takes approx 20sec on classic and
superclassic.
So is this a complaint about performing too well? ;-) No, not
exactly. I am curious to know whether this attribute of the
classic servers is already known and understood.
Is the performance immediately after CREATE DATABASE extra fast
in these instances because the connection retains exclusive
access on the file? Or is it because it ignores the default 75
page buffers? (It seems that setting CACHE 512, for example,
on the connect statement bring it back to 2sec.)
Note the script outputs the result-set of the defined stored
procedure which gives the execution time in milliseconds. Note
too that this script can also be used to demonstrate the
variance between TIMESTAMP 'now' and CAST('now' AS TIMESTAMP).
Use TIMESTAMP 'now', in place of the two casts in the script,
and it always returns 0 msec.
--
Geoff Worboys
Telesis Computing
SET TERM ^ ;
-- These two lines here just to make it easier to run the script repeatedly.
CONNECT 'D:\Temp\PerfTest.fdb' user 'sysdba' password 'masterkey'^
DROP DATABASE^
CREATE DATABASE 'D:\Temp\PerfTest.fdb' pagesize 8192 user 'sysdba' password
'masterkey'^
COMMIT^
-- This line makes the difference between 2 sec and 20 seconds
-- when running on super-classic
--CONNECT 'D:\Temp\PerfTest.fdb' user 'sysdba' password 'masterkey'^
CREATE DOMAIN PK_DOM CHAR(16) CHARACTER SET OCTETS NOT NULL^
CREATE TABLE PERF_WITH_PK (
PK PK_DOM PRIMARY KEY
)^
CREATE PROCEDURE RUN_TEST(
INDESCRIP VARCHAR(30),
LIM INTEGER )
RETURNS (
DESCRIP VARCHAR(30),
ITERATIONS INTEGER,
MSEC INTEGER )
AS
DECLARE A TIMESTAMP;
DECLARE I INTEGER;
BEGIN
Descrip = InDescrip;
A = CAST('NOW' AS TIMESTAMP); --TIMESTAMP 'NOW';
I = 0;
WHILE (I < Lim) DO
BEGIN
I = I + 1;
INSERT INTO PERF_WITH_PK (PK) VALUES (GEN_UUID());
END
Iterations = I;
MSec = ((CAST('NOW' AS TIMESTAMP) - A) * 86400000);
END^
COMMIT^
EXECUTE PROCEDURE RUN_TEST('Simple Insert UUID', 100000)^
------------------------------------------------------------------------------
Xperia(TM) PLAY
It's a major breakthrough. An authentic gaming
smartphone on the nation's most reliable network.
And it wants your games.
http://p.sf.net/sfu/verizon-sfdev
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
