LS

I tried to add a billion records in a testtable with an SP:

PROCEDURE "addRecords" (IN "RECID1" INTEGER, IN "RECID2" INTEGER, IN "DAT1"
TIMESTAMP, IN "DAT2" TIMESTAMP)
BEGIN
DECLARE Tbl CURSOR FOR stmt;
DECLARE I INTEGER;
DECLARE II INTEGER;
SET I = RECID1;  //is 1
SET II = RECID2; // is 1000000000
PREPARE stmt FROM 'SELECT * FROM Tbl';
OPEN Tbl;
WHILE I <= II DO
  INSERT INTO Tbl (veld1, veld2, veld3, datum1, datum2) VALUES (I, '_' +
  CAST(I AS VARCHAR(10)), CURRENT_TIMESTAMP, DAT1, DAT2); //dat1 and dat2
are valid timestamps
  SET I=I+1;
END WHILE;
CLOSE Tbl;

This encounters in an error (I guess because the veld is only
varchar(10)) -after a few hours. When I try to open (or even repair) the
table this error occurs.

============================================================================
====
SQL Error (ElevateDB 1.02 Build 1)
============================================================================
====

ElevateDB Error #100 There is an error in the metadata for the table Tbl
(Signature, password, or character set (ANSI/Unicode) mismatch)

The table is about 2Gig, so I guess a lot (maybe all but one record is
inserted the right way). How to repair such a fault, and why is the metadata
corrupted (as the error says) and not the table?

Regards, Harry

>>This encounters in an error (I guess because the veld is only varchar(10))

Should read: this results in an error (I guess because the "veld2" field is
only varchar(10))

Regards, Harry


"Harry de Boer" <harry@staaf.nl> schreef in bericht
news:18436AB3-15CF-41A2-9BC6-EDC85876783D@news.elevatesoft.com...
> LS
>
> I tried to add a billion records in a testtable with an SP:
>
> PROCEDURE "addRecords" (IN "RECID1" INTEGER, IN "RECID2" INTEGER, IN
"DAT1"
> TIMESTAMP, IN "DAT2" TIMESTAMP)
> BEGIN
> DECLARE Tbl CURSOR FOR stmt;
> DECLARE I INTEGER;
> DECLARE II INTEGER;
> SET I = RECID1;  //is 1
> SET II = RECID2; // is 1000000000
> PREPARE stmt FROM 'SELECT * FROM Tbl';
> OPEN Tbl;
> WHILE I <= II DO
>    INSERT INTO Tbl (veld1, veld2, veld3, datum1, datum2) VALUES (I, '_' +
>    CAST(I AS VARCHAR(10)), CURRENT_TIMESTAMP, DAT1, DAT2); //dat1 and dat2
> are valid timestamps
>    SET I=I+1;
> END WHILE;
> CLOSE Tbl;
>
> This encounters in an error (I guess because the veld is only
> varchar(10)) -after a few hours. When I try to open (or even repair) the
> table this error occurs.
>
>
============================================================================
> ====
> SQL Error (ElevateDB 1.02 Build 1)
>
============================================================================
> ====
>
> ElevateDB Error #100 There is an error in the metadata for the table Tbl
> (Signature, password, or character set (ANSI/Unicode) mismatch)
>
> The table is about 2Gig, so I guess a lot (maybe all but one record is
> inserted the right way). How to repair such a fault, and why is the
metadata
> corrupted (as the error says) and not the table?
>
> Regards, Harry
>
>

Harry de Boer wrote:

> LS
>
> I tried to add a billion records in a testtable with an SP:
>
> PROCEDURE "addRecords" (IN "RECID1" INTEGER, IN "RECID2" INTEGER, IN "DAT1"
> TIMESTAMP, IN "DAT2" TIMESTAMP)
> BEGIN
> DECLARE Tbl CURSOR FOR stmt;
> DECLARE I INTEGER;
> DECLARE II INTEGER;
> SET I = RECID1;  //is 1
> SET II = RECID2; // is 1000000000
> PREPARE stmt FROM 'SELECT * FROM Tbl';
> OPEN Tbl;
> WHILE I <= II DO
>    INSERT INTO Tbl (veld1, veld2, veld3, datum1, datum2) VALUES (I, '_' +
>    CAST(I AS VARCHAR(10)), CURRENT_TIMESTAMP, DAT1, DAT2); //dat1 and dat2
> are valid timestamps
>    SET I=I+1;
> END WHILE;
> CLOSE Tbl;
>
> This encounters in an error (I guess because the veld is only
> varchar(10)) -after a few hours. When I try to open (or even repair) the
> table this error occurs.
>
> ============================================================================
> ====
> SQL Error (ElevateDB 1.02 Build 1)
> ============================================================================
> ====
>
> ElevateDB Error #100 There is an error in the metadata for the table Tbl
> (Signature, password, or character set (ANSI/Unicode) mismatch)
>
> The table is about 2Gig, so I guess a lot (maybe all but one record is
> inserted the right way). How to repair such a fault, and why is the metadata
> corrupted (as the error says) and not the table?
>
> Regards, Harry
>
>

Harry,
   Do you need to turn on large file support? Also why not start at
999,999,990 and see if it fails when it hits 1 billion? Then you know
whether the problem is with the table structure or the table size.

Dave

Harry,


<< This encounters in an error (I guess because the veld is only
varchar(10)) -after a few hours. >>

What is the original error that you see ?

<< When I try to open (or even repair) the table this error occurs. >>

That error means that one or more of the physical table files does not match
the metadata in the catalog.  It's just phrased in the opposite manner
because in most cases it is assumed that the table data is the desired
table, but for some reason the catalog isn't the correct version.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

I tried what Dave suggested but the record is added to the length of the
field (10 char), so it seems that it does some trimming itself. Shouldn't an
error occur here? I don't know if this also falls under Ole's mentioned
"silently truncating data" but if so then he certainly has a point here I
guess (I believe there are quite some consequences on solving this, reading
the posts, so maybe you can point out a workaround?)

I don't have the original error (shame on me, but I thought a repair would
*repair* it). Still I wonder why it was goning wrong, the harddisk is big
enough, tyhe CPU usage (Win Task manager - I know: not the best way) never
came up to 60% and it was the only process running on the pc. I just thought
this should never happen.

If an error occurs why can't a repair do the trick. If this would happen on
one of our a customer's site, are we "screwed"?

I know that inserting a billion records this way is maybe not a 'real life'
cas, but I wanted to stress test ElevateDB and see what the query results in
msecs where on a very large dataset.

Regards, Harry



"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> schreef in bericht
news:66E3C412-E96E-4BEB-A99D-9544A0D929A6@news.elevatesoft.com...
> Harry,
>
>
> << This encounters in an error (I guess because the veld is only
> varchar(10)) -after a few hours. >>
>
> What is the original error that you see ?
>
> << When I try to open (or even repair) the table this error occurs. >>
>
> That error means that one or more of the physical table files does not
match
> the metadata in the catalog.  It's just phrased in the opposite manner
> because in most cases it is assumed that the table data is the desired
> table, but for some reason the catalog isn't the correct version.
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>
>

Harry de Boer wrote:

>
> I know that inserting a billion records this way is maybe not a 'real life'
> cas, but I wanted to stress test ElevateDB and see what the query results in
> msecs where on a very large dataset.


Harry,
     I agree with you, but how on earth are you going to build an index
for a billion row table? My "little" million row table took 30 minutes
for one index based on a large text string. How much RAM is needed to
build an index on a billion rows?

Dave

Dave,

For this *test* there was no index involved

Regards, Harry

"Dave Harrison" <daveh_18824@spammore.com> schreef in bericht
news:E95BF069-7116-4B8C-BF45-ED3DE96BE93F@news.elevatesoft.com...
> Harry de Boer wrote:
>
> >
> > I know that inserting a billion records this way is maybe not a 'real
life'
> > cas, but I wanted to stress test ElevateDB and see what the query
results in
> > msecs where on a very large dataset.
>
>
> Harry,
>       I agree with you, but how on earth are you going to build an index
> for a billion row table? My "little" million row table took 30 minutes
> for one index based on a large text string. How much RAM is needed to
> build an index on a billion rows?
>
> Dave

Harry,

<< I tried what Dave suggested but the record is added to the length of the
field (10 char), so it seems that it does some trimming itself. Shouldn't an
error occur here? I don't know if this also falls under Ole's mentioned
"silently truncating data" but if so then he certainly has a point here I
guess (I believe there are quite some consequences on solving this, reading
the posts, so maybe you can point out a workaround?) >>

Yes, it's the same issue as Ole mentioned.

<< I don't have the original error (shame on me, but I thought a repair
would *repair* it). Still I wonder why it was goning wrong, the harddisk is
big enough, tyhe CPU usage (Win Task manager - I know: not the best way)
never
came up to 60% and it was the only process running on the pc. I just thought
this should never happen. >>

It shouldn't happen under normal circumstances.  I'll kick something off
tonight and see what I find tomorrow.

<<  If an error occurs why can't a repair do the trick. If this would happen
on one of our a customer's site, are we "screwed"? >>

I can't comment on this any further without knowing what the original error
was.  I'll have to try and replicate it here.  There are situations where a
repair won't correct a table if it thinks that the table is being used in an
improper context, i.e. an encrypted table was restored over top of an
un-encrypted table or an ANSI table was copied over a Unicode table.  The
idea is that the repair shouldn't make the situation worse.

--
Tim Young
Elevate Software
www.elevatesoft.com

Harry de Boer wrote:

> Dave,
>
> For this *test* there was no index involved

Harry,
    I dare you to create an index on a billion row table. Hey, I double
dare you.

Dave

Harry,

I finally got some time to run the billion row insert test, and there is
indeed a bug with EDB that is causing itself to overwrite its own header
when the file size goes about 2 gigs (High(Integer)).   Basically, due to
the fifty freaking versions of TStream.Seek that Borland has felt the need
to implement, I accidentally passed soFromBeginning (Word) instead of
soBeginning (enumeration), and this was causing the compiler to pick the
Integer version of the TStream.Seek method instead of the correct, and
desired, Int64 version of the TStream.Seek method.

A fix will be available within the next couple of days.

--
Tim Young
Elevate Software
www.elevatesoft.com