Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 10 of 12 total |
error on inserting 1.000.000.000 records |
Fri, Apr 6 2007 12:15 PM | Permanent Link |
"Harry de Boer" | 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 |
Fri, Apr 6 2007 12:28 PM | Permanent Link |
"Harry de Boer" | >>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 > > |
Fri, Apr 6 2007 2:12 PM | Permanent Link |
Dave Harrison | 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 |
Fri, Apr 6 2007 3:52 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@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 |
Sat, Apr 7 2007 6:11 AM | Permanent Link |
"Harry de Boer" | 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 > > |
Sun, Apr 8 2007 2:51 PM | Permanent Link |
Dave Harrison | 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 |
Mon, Apr 9 2007 9:17 AM | Permanent Link |
"Harry de Boer" | 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 |
Mon, Apr 9 2007 8:32 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Tue, Apr 10 2007 1:57 AM | Permanent Link |
Dave Harrison | 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 |
Tue, Apr 10 2007 8:58 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |