![]() | |
#11
| |||
| |||
|
|
There are a few possible limits involved. 1) In a normal file the fixed length portion of the record must fit on a single data file page. Indexes must reside on the fixed length portion of the record. Page sizes currently go up to 4K so indexes must be in the first 4K of the record. The index location is relaxed somewhat with compressed files but I believe the compressed file total record length is limitted to a 64K internal buffer. 2) The operation must fit into the 64K data buffer or 53K normal worst case client / server limitted by network stack which I have seen software chop to 32K and routers start dropping data at 16K. You can use Get_Chunk* and Update_Chunk* operations to work on a data buffer sized portion of the record at a time. 3) The total length of a record (fixed + variable) is limitted to 4GB (32 bit pointer). The practical limit is smaller. 4) Total single file size is limitted to 64GB with 4K pages, smaller with smaller pages. You can have as many files as you want. *) IDS does not support chunk operations. IDS is pretty much a legacy product and is in transition support. It has been superceeded by the Pervasive.SQL V8 Security release. Hmmm.... very interesting. He gave a mixed mode example creating the file through one API (SQL Create Table statement) and accessing it through the transactional API. And in fact the way LVAR() fields are stored in the current product has changed to allow more than one LVAR() per record which requires some pointers to be stored in the record. So with the current product running that create table statement would produce a file that did not quite match the example struct given. If the file is created through the Btrieve API it can still be created to match the example or if accessed through the SQL API it would be transparent. Check out the current documentation at: http://www.pervasive.com/developerZone/ It would also be possible to break the one arbitrarily large "file" that needs to be stored into any number of arbitrary sized chunks, and add a segment / sequence to the record. Although in all honesty it is probably better to just store the file as a file and eliminate the complexity and overhead of accessing a file through a database engine. Leonard On Fri, 7 Nov 2003 23:42:47 +0100, "GTi" <nospam (AT) online (DOT) com> wrote: Dave, Is this "maximum" limit restricted to Btrieve, or is this a common to all Databases ? Using files on disk is handy, but when using IDS it is not handy anymore... GTi "David Reed" <d (AT) nospam (DOT) com> wrote in message news:RHTqb.178482$7B1.37388 (AT) news04 (DOT) bloor.is.net.cable.rogers.com... You're correct about the maximum. Funny you mention pictures. I was faced with a similar situation earlier this year. I ended up just storing a path to an external file containing the picture. Once I hit the record I wanted, I'd just open the file at that location, which was a file in the folder I was in. ...dave "GTi" <nospam (AT) online (DOT) com> wrote in message news:kCTqb.1855$%W3.9488 (AT) amstwist00 (DOT) .. Thanks David, In your case you have a maximum length of 4000 bytes in the comment field - right? And what if the comment fields is over 4000 bytes. What happens then? comment LVAR(4000)) As I can see it, you MUST set a maximum length to something. If it is 4000, 10000 or 400000 it MUST be a value. Is that correct? Today I store the files on the disk, and the filename is the key value of my record. That gives me no limits on the file size. But it should be in the database. Say - For some years ago a digital camera was producing pictures of 1-2 MB. Today it have 5-6 MB. If I limited this to 3 MB for some years ago, I have been lost today. So if I go for this I must set a limit for the records to 100 MB - say 200 MB to be sure if I can still use it in the future (not limited to pictures). Or ? "David Reed" <d (AT) nospam (DOT) com> wrote in message news:SeTqb.178225$7B1.108881 (AT) news04 (DOT) bloor.is.net.cable.rogers.com... The following code uses the Windows SDK GlobalAlloc. Feel free to substitute the malloc of your choice. All my files were created via Novell's XQL, so I don't have code to tell you how to create it. The XQL statement was: create table TABLE using "TABLE .DAT" DCOMPRESS pagesize 4096 (recordID INT(4), code CHAR(8), reserved CHAR(16), flags INT(4), commentText LVAR(4000)) with index (recordID UNIQUE, code UNIQUE MOD) Here's how I access it in the code: Ok, start with a structure for the fixed portion of the record: // // Table Structure // Record length: Variable: Fixed portion = 32 // Defined flags: None // // Record Layout: Bytes Description // ~~~~~ ~~~~~~~~~~~ // 1-4 Record ID // 5-12 Code // 13-28 Reserved // 29-32 Record Flags // 33-4032 Text (variable) // #define CODE_LENGTH 8 #define RESERVED_LENGTH 16 #define FIXED_LENGTH 32 #define VARIABLE_LENGTH 4000 #define TOTAL_LENGTH FIXED_LENGTH + VARIABLE_LENGTH typedef struct TABLEStruct { long recordID; char code[CODE_LENGTH]; char reserved[RESERVED_LENGTH]; long flags; } TABLEDef; typedef struct TABLEKey0Struct { long recordID; } TABLEKey0Def; typedef struct TABLEKey1Struct { char code[COMMENTS_CODE_LENGTH]; } TABLEKey1Def; EXTERN TABLEDef TABLE; EXTERN TABLEKey1Def TABLEKey1; EXTERN HANDLE hMemTableText; EXTERN char *pTableText; * * * * You can do the following in the main routine: // // Allocate space for the text portion of the table // hMemTableText = GlobalAlloc(GPTR, TOTAL_LENGTH); pTableText = (char *)hMemTableText; if(hMemCommentText == NULL || pCommentText == NULL) { /* Error */ } * * * * * In the routine you use to access all of the variable length file: char code[] = "SomeText"; strcpy(TABLE.code, code); dataBufferLength = TOTAL_LENGTH; rcode = BTRVID((BTI_WORD)GETEQUAL, positionBlock, pTableText , &dataBufferLength, TABLE.code, (BTI_SINT)1, (BTI_BUFFER_PTR)&clientID); if(rcode == 0) { memcpy(&TABLE, pTableText, FIXED_LENGTH); /* Variable portion starts at pTableText[FIXED_LENGTH] */ } * * * * * If you're just cycling through, set the data buffer length to FIXED_LENGTH and pass the data structure instead of pTable Text. You'll get rcode == 22, but you won't have the overhead of getting (potentially) a bunch of additional data for records you may end up skipping. ******* Bill Bach, if you're reading, is there much overhead savings in this? Anyway, hope this helps. David Reed Schedule Masters, Inc. |
#12
| |||
| |||
|
|
The following code uses the Windows SDK GlobalAlloc. Feel free to substitute the malloc of your choice. All my files were created via Novell's XQL, so I don't have code to tell you how to create it. The XQL statement was: create table TABLE using "TABLE .DAT" DCOMPRESS pagesize 4096 (recordID INT(4), code CHAR(8), reserved CHAR(16), flags INT(4), commentText LVAR(4000)) with index (recordID UNIQUE, code UNIQUE MOD) Here's how I access it in the code: Ok, start with a structure for the fixed portion of the record: // // Table Structure // Record length: Variable: Fixed portion = 32 // Defined flags: None // // Record Layout: Bytes Description // ~~~~~ ~~~~~~~~~~~ // 1-4 Record ID // 5-12 Code // 13-28 Reserved // 29-32 Record Flags // 33-4032 Text (variable) // #define CODE_LENGTH 8 #define RESERVED_LENGTH 16 #define FIXED_LENGTH 32 #define VARIABLE_LENGTH 4000 #define TOTAL_LENGTH FIXED_LENGTH + VARIABLE_LENGTH typedef struct TABLEStruct { long recordID; char code[CODE_LENGTH]; char reserved[RESERVED_LENGTH]; long flags; } TABLEDef; typedef struct TABLEKey0Struct { long recordID; } TABLEKey0Def; typedef struct TABLEKey1Struct { char code[COMMENTS_CODE_LENGTH]; } TABLEKey1Def; EXTERN TABLEDef TABLE; EXTERN TABLEKey1Def TABLEKey1; EXTERN HANDLE hMemTableText; EXTERN char *pTableText; * * * * You can do the following in the main routine: // // Allocate space for the text portion of the table // hMemTableText = GlobalAlloc(GPTR, TOTAL_LENGTH); pTableText = (char *)hMemTableText; if(hMemCommentText == NULL || pCommentText == NULL) { /* Error */ } * * * * * In the routine you use to access all of the variable length file: char code[] = "SomeText"; strcpy(TABLE.code, code); dataBufferLength = TOTAL_LENGTH; rcode = BTRVID((BTI_WORD)GETEQUAL, positionBlock, pTableText , &dataBufferLength, TABLE.code, (BTI_SINT)1, (BTI_BUFFER_PTR)&clientID); if(rcode == 0) { memcpy(&TABLE, pTableText, FIXED_LENGTH); /* Variable portion starts at pTableText[FIXED_LENGTH] */ } * * * * * If you're just cycling through, set the data buffer length to FIXED_LENGTH and pass the data structure instead of pTable Text. You'll get rcode == 22, but you won't have the overhead of getting (potentially) a bunch of additional data for records you may end up skipping. ******* Bill Bach, if you're reading, is there much overhead savings in this? Anyway, hope this helps. David Reed Schedule Masters, Inc. |
#13
| |||
| |||
|
|
Sorry -- I've been sick lately & neglected my newsgroup duties. ;-( A few comments... 1) It *IS* much more efficient to scan records by reading only the first small set of bytes. Even on a local engine with no network access, I've found a good performance gain in reading the first 4 bytes of a record over reading the entire record. It reduces the load on the memory transfer, on the disk, and on the Btrieve engine. In fact, this is one of the tricks I use to make my KeyCheck utility run faster on the QuickScan setting. 2) For storing large files in the Btrieve database, remember that you can do it, but only with chunking operations. For best compatibility, you must read in the chunks with a 57K buffer at a time. (Don't try to read 64K -- although this may work on a local engine, the overhead of network communications will cause this to fail in some client/server environments.) You'll therefore grab the first block that contains the length, then parse the length and grab it with multiple 57K blocks until the entire object has been read in. 3) Now -- does this make sense? Not really. a) Large files requires Variable-length records, or at least data compression. Activating data compression can limit the number of records you can get on a page in the 7.x or 8.x file formats, causing wasted space. If you then optimize this to eliminate wasted space, then your page size (now less than 4K) will restrict the total size of the file. b) Variable pages are more prone to corruption than regular pages, and compressed data can be trashed with a single-bit error. I tell developers to avoid these unless they have a very good business case for it. c) Your record will be split into multiple variable pages. Pages can move around in all newer file formats, which can cause fragmentation of the data you are trying to read. This can turn a series of random reads into a disk-thrashing nightmare. When I/O is slow, your database will be slow, and performance will suffer. There is no way to defrag a Btrieve file (at this time, wink wink) in place without rebuilding it periodically. d) Haing such a large file also means that the disk will be fragmented at the OS-level. This makes the frag problem even worse. e) What if you corrupt a file in a server crash? Rebuilding such a large file will take forever. f) Reading data via chunking causes numerous Btrieve reads to take place, which can place undue load on the database engine. In short, file system access is much faster, cleaner, less susceptable to corruption, can be easily defragmented, and it keeps your Btrieve files clean. I'd just store the relative pathname to the files, if it were me. Try to avoid full pathnames, as changing them in the event of a change to your app path (or volume) can be painful. Goldstar Software Inc. Building on Btrieve(R) for the Future(SM) Bill Bach BillBach (AT) goldstarsoftware (DOT) com http://www.goldstarsoftware.com *** Pervasive.SQL Service & Support Classes *** Chicago: November 18-20: See our web site for details! David Reed wrote: The following code uses the Windows SDK GlobalAlloc. Feel free to substitute the malloc of your choice. All my files were created via Novell's XQL, so I don't have code to tell you how to create it. The XQL statement was: create table TABLE using "TABLE .DAT" DCOMPRESS pagesize 4096 (recordID INT(4), code CHAR(8), reserved CHAR(16), flags INT(4), commentText LVAR(4000)) with index (recordID UNIQUE, code UNIQUE MOD) Here's how I access it in the code: Ok, start with a structure for the fixed portion of the record: // // Table Structure // Record length: Variable: Fixed portion = 32 // Defined flags: None // // Record Layout: Bytes Description // ~~~~~ ~~~~~~~~~~~ // 1-4 Record ID // 5-12 Code // 13-28 Reserved // 29-32 Record Flags // 33-4032 Text (variable) // #define CODE_LENGTH 8 #define RESERVED_LENGTH 16 #define FIXED_LENGTH 32 #define VARIABLE_LENGTH 4000 #define TOTAL_LENGTH FIXED_LENGTH + VARIABLE_LENGTH typedef struct TABLEStruct { long recordID; char code[CODE_LENGTH]; char reserved[RESERVED_LENGTH]; long flags; } TABLEDef; typedef struct TABLEKey0Struct { long recordID; } TABLEKey0Def; typedef struct TABLEKey1Struct { char code[COMMENTS_CODE_LENGTH]; } TABLEKey1Def; EXTERN TABLEDef TABLE; EXTERN TABLEKey1Def TABLEKey1; EXTERN HANDLE hMemTableText; EXTERN char *pTableText; * * * * You can do the following in the main routine: // // Allocate space for the text portion of the table // hMemTableText = GlobalAlloc(GPTR, TOTAL_LENGTH); pTableText = (char *)hMemTableText; if(hMemCommentText == NULL || pCommentText == NULL) { /* Error */ } * * * * * In the routine you use to access all of the variable length file: char code[] = "SomeText"; strcpy(TABLE.code, code); dataBufferLength = TOTAL_LENGTH; rcode = BTRVID((BTI_WORD)GETEQUAL, positionBlock, pTableText , &dataBufferLength, TABLE.code, (BTI_SINT)1, (BTI_BUFFER_PTR)&clientID); if(rcode == 0) { memcpy(&TABLE, pTableText, FIXED_LENGTH); /* Variable portion starts at pTableText[FIXED_LENGTH] */ } * * * * * If you're just cycling through, set the data buffer length to FIXED_LENGTH and pass the data structure instead of pTable Text. You'll get rcode == 22, but you won't have the overhead of getting (potentially) a bunch of additional data for records you may end up skipping. ******* Bill Bach, if you're reading, is there much overhead savings in this? Anyway, hope this helps. David Reed Schedule Masters, Inc. |
#14
| |||
| |||
|
|
Bill, Thanks for the feedback. I fully agree with you Files on disk is a god solution. I have a client that want to access my Btrieve data using JS and Tomcat with P.SQL. But I thing we may have a problem when he need to read the files on disk (text files and pictures). I can do it with ISAPI without problem, but I'm not sure about Tomcat/JS (located on another server). But I got a loot of god comments to NOT store files on database. It can be solved but is not recommended . GTi "Bill Bach" <bbach (AT) cncdsl (DOT) com> wrote in message news:3FAD0550.F1C61568 (AT) cncdsl (DOT) com... Sorry -- I've been sick lately & neglected my newsgroup duties. ;-( A few comments... 1) It *IS* much more efficient to scan records by reading only the first small set of bytes. Even on a local engine with no network access, I've found a good performance gain in reading the first 4 bytes of a record over reading the entire record. It reduces the load on the memory transfer, on the disk, and on the Btrieve engine. In fact, this is one of the tricks I use to make my KeyCheck utility run faster on the QuickScan setting. 2) For storing large files in the Btrieve database, remember that you can do it, but only with chunking operations. For best compatibility, you must read in the chunks with a 57K buffer at a time. (Don't try to read 64K -- although this may work on a local engine, the overhead of network communications will cause this to fail in some client/server environments.) You'll therefore grab the first block that contains the length, then parse the length and grab it with multiple 57K blocks until the entire object has been read in. 3) Now -- does this make sense? Not really. a) Large files requires Variable-length records, or at least data compression. Activating data compression can limit the number of records you can get on a page in the 7.x or 8.x file formats, causing wasted space. If you then optimize this to eliminate wasted space, then your page size (now less than 4K) will restrict the total size of the file. b) Variable pages are more prone to corruption than regular pages, and compressed data can be trashed with a single-bit error. I tell developers to avoid these unless they have a very good business case for it. c) Your record will be split into multiple variable pages. Pages can move around in all newer file formats, which can cause fragmentation of the data you are trying to read. This can turn a series of random reads into a disk-thrashing nightmare. When I/O is slow, your database will be slow, and performance will suffer. There is no way to defrag a Btrieve file (at this time, wink wink) in place without rebuilding it periodically. d) Haing such a large file also means that the disk will be fragmented at the OS-level. This makes the frag problem even worse. e) What if you corrupt a file in a server crash? Rebuilding such a large file will take forever. f) Reading data via chunking causes numerous Btrieve reads to take place, which can place undue load on the database engine. In short, file system access is much faster, cleaner, less susceptable to corruption, can be easily defragmented, and it keeps your Btrieve files clean. I'd just store the relative pathname to the files, if it were me. Try to avoid full pathnames, as changing them in the event of a change to your app path (or volume) can be painful. Goldstar Software Inc. Building on Btrieve(R) for the Future(SM) Bill Bach BillBach (AT) goldstarsoftware (DOT) com http://www.goldstarsoftware.com *** Pervasive.SQL Service & Support Classes *** Chicago: November 18-20: See our web site for details! David Reed wrote: The following code uses the Windows SDK GlobalAlloc. Feel free to substitute the malloc of your choice. All my files were created via Novell's XQL, so I don't have code to tell you how to create it. The XQL statement was: create table TABLE using "TABLE .DAT" DCOMPRESS pagesize 4096 (recordID INT(4), code CHAR(8), reserved CHAR(16), flags INT(4), commentText LVAR(4000)) with index (recordID UNIQUE, code UNIQUE MOD) Here's how I access it in the code: Ok, start with a structure for the fixed portion of the record: // // Table Structure // Record length: Variable: Fixed portion = 32 // Defined flags: None // // Record Layout: Bytes Description // ~~~~~ ~~~~~~~~~~~ // 1-4 Record ID // 5-12 Code // 13-28 Reserved // 29-32 Record Flags // 33-4032 Text (variable) // #define CODE_LENGTH 8 #define RESERVED_LENGTH 16 #define FIXED_LENGTH 32 #define VARIABLE_LENGTH 4000 #define TOTAL_LENGTH FIXED_LENGTH + VARIABLE_LENGTH typedef struct TABLEStruct { long recordID; char code[CODE_LENGTH]; char reserved[RESERVED_LENGTH]; long flags; } TABLEDef; typedef struct TABLEKey0Struct { long recordID; } TABLEKey0Def; typedef struct TABLEKey1Struct { char code[COMMENTS_CODE_LENGTH]; } TABLEKey1Def; EXTERN TABLEDef TABLE; EXTERN TABLEKey1Def TABLEKey1; EXTERN HANDLE hMemTableText; EXTERN char *pTableText; * * * * You can do the following in the main routine: // // Allocate space for the text portion of the table // hMemTableText = GlobalAlloc(GPTR, TOTAL_LENGTH); pTableText = (char *)hMemTableText; if(hMemCommentText == NULL || pCommentText == NULL) { /* Error */ } * * * * * In the routine you use to access all of the variable length file: char code[] = "SomeText"; strcpy(TABLE.code, code); dataBufferLength = TOTAL_LENGTH; rcode = BTRVID((BTI_WORD)GETEQUAL, positionBlock, pTableText , &dataBufferLength, TABLE.code, (BTI_SINT)1, (BTI_BUFFER_PTR)&clientID); if(rcode == 0) { memcpy(&TABLE, pTableText, FIXED_LENGTH); /* Variable portion starts at pTableText[FIXED_LENGTH] */ } * * * * * If you're just cycling through, set the data buffer length to FIXED_LENGTH and pass the data structure instead of pTable Text. You'll get rcode == 22, but you won't have the overhead of getting (potentially) a bunch of additional data for records you may end up skipping. ******* Bill Bach, if you're reading, is there much overhead savings in this? Anyway, hope this helps. David Reed Schedule Masters, Inc. |
![]() |
| Thread Tools | |
| Display Modes | |
| |