Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 5 of 5 total |
Best Way to Use Memory Tables |
Tue, May 7 2013 6:10 AM | Permanent Link |
kamran | Hi
Just need to know about using memory tables. 1. If I have an existing dbisam table .. can i just change the existing databasename of that file to 'Memory' and start using it as a memory file ? 2. Is there an easy way to define the memory fields from an existing standard dbisam file? 3. Any other tips for usage/performance/setup ? Kind regards Kamran |
Tue, May 7 2013 7:45 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | kamran
>1. If I have an existing dbisam table .. can i just change the existing databasename of that file to >'Memory' and start using it as a memory file ? Yes but don't forget that means 1) the file is now only going to be visible/available to the user that created the in-memory table and 2) the contents will not automatically be created from the contents of the disk file and will not be asved back to disk if any alterations are made. >2. Is there an easy way to define the memory fields from an existing standard dbisam file? This is the routine I wrote. It creates the table from the persistent field definitions stored in a TDBISAMTable component procedure SelfMakeMemoryTable(WhichTable, CloneTable: TDBISAMTable; SetExclusive, AndOpen: boolean; const MakSesh: TDBISAMSession); var Cntr: integer; Maker: TDBISAMQuery; // MakerSession:TDBISAMSession; iFlds: string; tblName: string; idxFlds: TStringList; idxDesc: TStringList; const dq = '"'; function GetDefaultExpression(Fld: TField): string; begin Result := ''; if Fld.DefaultExpression <> '' then begin Result := ' DEFAULT ' + Fld.DefaultExpression; end; end; function GetFieldType(Fld: TField): string; begin case Fld.DataType of ftInteger: Result := 'INTEGER'; ftString, ftFixedChar: Result := 'VARCHAR(' + IntToStr(Fld.Size) + ')'; ftBoolean: Result := 'BOOLEAN'; ftDate: Result := 'DATE'; ftFloat: Result := 'FLOAT'; ftTime: Result := 'TIME'; ftMemo: Result := 'MEMO'; ftBlob: Result := 'BLOB'; ftGraphic: Result := 'GRAPHIC'; ftFmtMemo: Result := 'BLOB(0,3)'; ftTypedBinary: Result := 'BLOB(0,7)'; ftDateTime: Result := 'TIMESTAMP'; ftSmallInt: Result := 'SMALLINT'; ftWord: Result := 'WORD'; ftAutoInc: Result := 'AUTOINC'; ftLargeInt: Result := 'LARGEINT'; ftCurrency: Result := 'MONEY'; ftBCD: Result := 'DECIMAL(0 , 4)'; ftBytes: Result := 'BYTES(' + IntToStr(Fld.Size) + ')'; else Result := 'VARCHAR(' + IntToStr(Fld.Size) + ')'; end; end; procedure GetIdxInfo(FldList, DescList: string; DescWanted: boolean); var lCntr: integer; begin idxFlds.Clear; idxDesc.Clear; for lCntr := 1 to CountOccurancesOf(FldList, ';') + 1 do idxFlds.Add(SubFld(FldList, ';', lCntr)); if DescWanted and (DescList <> '') then begin for lCntr := 1 to CountOccurancesOf(DescList, ';') + 1 do idxDesc.Add(SubFld(DescList, ';', lCntr)); end; end; function FormIndex(idx: TDBISAMIndexDef; IsPrimary: boolean): string; var idxCntr: integer; begin Result := ''; GetIdxInfo(idx.Fields, idx.DescFields, (ixDescending in idx.Options)); if idxFlds.Count > 0 then begin if not IsPrimary then Result := 'CREATE '; if ixCaseInsensitive in idx.Options then Result := Result + ' NOCASE '; if IsPrimary then Result := Result + 'PRIMARY KEY (' else Result := Result + 'INDEX IF NOT EXISTS "' + idx.Name + '" ON "\Memory\' + tblName + '" ('; iFlds := dq + idxFlds[0] + dq; if (ixDescending in idx.Options) and (idxDesc.IndexOf(idxFlds[0]) > -1) then iFlds := iFlds + ' DESC'; iFlds := iFlds + ','; for idxCntr := 1 to idxFlds.Count - 1 do begin iFlds := iFlds + dq + idxFlds[idxCntr] + dq; if (ixDescending in idx.Options) and (idxDesc.IndexOf(idxFlds[idxCntr]) > -1) then iFlds := iFlds + ' DESC'; iFlds := iFlds + ','; end; Delete(iFlds, Length(iFlds), 1); Result := Result + iFlds + ') '; Result := Result + ' COMPRESS NONE '; if not IsPrimary then Result := Result + ';'; end; end; begin idxFlds := TStringList.Create; idxDesc := TStringList.Create; if CloneTable <> nil then begin tblName := CloneTable.TableName; CloneTable.DatabaseName := 'Memory'; CloneTable.Close; CloneTable.Exclusive := True; end else begin tblName := WhichTable.TableName; WhichTable.Close; WhichTable.Exclusive := True; end; Maker := MakeDBISAMQuery('Memory', MakSesh); Maker.SQL.Clear; Maker.SQL.Add('DROP TABLE IF EXISTS "\Memory\' + tblName + '";'); Maker.SQL.Add('CREATE TABLE IF NOT EXISTS "\Memory\' + tblName + '"'); Maker.SQL.Add('('); for Cntr := 0 to WhichTable.FieldList.Count - 1 do begin if WhichTable.FieldList[Cntr].FieldKind = fkData then Maker.SQL.Add('"' + WhichTable.FieldList[Cntr].FieldName + '" ' + GetFieldType(WhichTable.FieldList[Cntr]) + GetDefaultExpression(WhichTable.FieldList[Cntr]) + ','); end; for Cntr := 0 to WhichTable.IndexDefs.Count - 1 do begin if ixPrimary in WhichTable.IndexDefs[Cntr].Options then begin Maker.SQL.Add(FormIndex(WhichTable.IndexDefs[Cntr], True)); Break; end; end; Maker.SQL.Add('LOCALE CODE 0'); Maker.SQL.Add('USER MAJOR VERSION 1'); Maker.SQL.Add(');'); for Cntr := 0 to WhichTable.IndexDefs.Count - 1 do begin if not (ixPrimary in WhichTable.IndexDefs[Cntr].Options) then begin Maker.SQL.Add(FormIndex(WhichTable.IndexDefs[Cntr], False)); end; end; Maker.ExecSQL; Maker.Close; Maker.UnPrepare; Maker.Free; idxFlds.Free; idxDesc.Free; if CloneTable <> nil then begin CloneTable.Exclusive := SetExclusive; CloneTable.Active := AndOpen; end else begin WhichTable.Exclusive := SetExclusive; WhichTable.Active := AndOpen; end; end; >3. Any other tips for usage/performance/setup ? Yup - it may not be worth bothering. Small tables are liable to be entirely buffered by DBISAM/Windows and large tables could take a while to load up. If its a table that's isn't being thrashed a lot then start questioning why you're putting it into memory. My reasons were 1) isloation 2) preventing clutter on the disk and 3) self cleaning in the event of a crash. Roy Lambert [Team Elevate] |
Tue, May 7 2013 8:03 AM | Permanent Link |
kamran | Hi Roy
Thats really helpful.. Thanks Kamran Roy Lambert wrote: kamran >1. If I have an existing dbisam table .. can i just change the existing databasename of that file to >'Memory' and start using it as a memory file ? Yes but don't forget that means 1) the file is now only going to be visible/available to the user that created the in-memory table and 2) the contents will not automatically be created from the contents of the disk file and will not be asved back to disk if any alterations are made. >2. Is there an easy way to define the memory fields from an existing standard dbisam file? This is the routine I wrote. It creates the table from the persistent field definitions stored in a TDBISAMTable component procedure SelfMakeMemoryTable(WhichTable, CloneTable: TDBISAMTable; SetExclusive, AndOpen: boolean; const MakSesh: TDBISAMSession); var Cntr: integer; Maker: TDBISAMQuery; // MakerSession:TDBISAMSession; iFlds: string; tblName: string; idxFlds: TStringList; idxDesc: TStringList; const dq = '"'; function GetDefaultExpression(Fld: TField): string; begin Result := ''; if Fld.DefaultExpression <> '' then begin Result := ' DEFAULT ' + Fld.DefaultExpression; end; end; function GetFieldType(Fld: TField): string; begin case Fld.DataType of ftInteger: Result := 'INTEGER'; ftString, ftFixedChar: Result := 'VARCHAR(' + IntToStr(Fld.Size) + ')'; ftBoolean: Result := 'BOOLEAN'; ftDate: Result := 'DATE'; ftFloat: Result := 'FLOAT'; ftTime: Result := 'TIME'; ftMemo: Result := 'MEMO'; ftBlob: Result := 'BLOB'; ftGraphic: Result := 'GRAPHIC'; ftFmtMemo: Result := 'BLOB(0,3)'; ftTypedBinary: Result := 'BLOB(0,7)'; ftDateTime: Result := 'TIMESTAMP'; ftSmallInt: Result := 'SMALLINT'; ftWord: Result := 'WORD'; ftAutoInc: Result := 'AUTOINC'; ftLargeInt: Result := 'LARGEINT'; ftCurrency: Result := 'MONEY'; ftBCD: Result := 'DECIMAL(0 , 4)'; ftBytes: Result := 'BYTES(' + IntToStr(Fld.Size) + ')'; else Result := 'VARCHAR(' + IntToStr(Fld.Size) + ')'; end; end; procedure GetIdxInfo(FldList, DescList: string; DescWanted: boolean); var lCntr: integer; begin idxFlds.Clear; idxDesc.Clear; for lCntr := 1 to CountOccurancesOf(FldList, ';') + 1 do idxFlds.Add(SubFld(FldList, ';', lCntr)); if DescWanted and (DescList <> '') then begin for lCntr := 1 to CountOccurancesOf(DescList, ';') + 1 do idxDesc.Add(SubFld(DescList, ';', lCntr)); end; end; function FormIndex(idx: TDBISAMIndexDef; IsPrimary: boolean): string; var idxCntr: integer; begin Result := ''; GetIdxInfo(idx.Fields, idx.DescFields, (ixDescending in idx.Options)); if idxFlds.Count > 0 then begin if not IsPrimary then Result := 'CREATE '; if ixCaseInsensitive in idx.Options then Result := Result + ' NOCASE '; if IsPrimary then Result := Result + 'PRIMARY KEY (' else Result := Result + 'INDEX IF NOT EXISTS "' + idx.Name + '" ON "\Memory\' + tblName + '" ('; iFlds := dq + idxFlds[0] + dq; if (ixDescending in idx.Options) and (idxDesc.IndexOf(idxFlds[0]) > -1) then iFlds := iFlds + ' DESC'; iFlds := iFlds + ','; for idxCntr := 1 to idxFlds.Count - 1 do begin iFlds := iFlds + dq + idxFlds[idxCntr] + dq; if (ixDescending in idx.Options) and (idxDesc.IndexOf(idxFlds[idxCntr]) > -1) then iFlds := iFlds + ' DESC'; iFlds := iFlds + ','; end; Delete(iFlds, Length(iFlds), 1); Result := Result + iFlds + ') '; Result := Result + ' COMPRESS NONE '; if not IsPrimary then Result := Result + ';'; end; end; begin idxFlds := TStringList.Create; idxDesc := TStringList.Create; if CloneTable <> nil then begin tblName := CloneTable.TableName; CloneTable.DatabaseName := 'Memory'; CloneTable.Close; CloneTable.Exclusive := True; end else begin tblName := WhichTable.TableName; WhichTable.Close; WhichTable.Exclusive := True; end; Maker := MakeDBISAMQuery('Memory', MakSesh); Maker.SQL.Clear; Maker.SQL.Add('DROP TABLE IF EXISTS "\Memory\' + tblName + '";'); Maker.SQL.Add('CREATE TABLE IF NOT EXISTS "\Memory\' + tblName + '"'); Maker.SQL.Add('('); for Cntr := 0 to WhichTable.FieldList.Count - 1 do begin if WhichTable.FieldList[Cntr].FieldKind = fkData then Maker.SQL.Add('"' + WhichTable.FieldList[Cntr].FieldName + '" ' + GetFieldType(WhichTable.FieldList[Cntr]) + GetDefaultExpression(WhichTable.FieldList[Cntr]) + ','); end; for Cntr := 0 to WhichTable.IndexDefs.Count - 1 do begin if ixPrimary in WhichTable.IndexDefs[Cntr].Options then begin Maker.SQL.Add(FormIndex(WhichTable.IndexDefs[Cntr], True)); Break; end; end; Maker.SQL.Add('LOCALE CODE 0'); Maker.SQL.Add('USER MAJOR VERSION 1'); Maker.SQL.Add(');'); for Cntr := 0 to WhichTable.IndexDefs.Count - 1 do begin if not (ixPrimary in WhichTable.IndexDefs[Cntr].Options) then begin Maker.SQL.Add(FormIndex(WhichTable.IndexDefs[Cntr], False)); end; end; Maker.ExecSQL; Maker.Close; Maker.UnPrepare; Maker.Free; idxFlds.Free; idxDesc.Free; if CloneTable <> nil then begin CloneTable.Exclusive := SetExclusive; CloneTable.Active := AndOpen; end else begin WhichTable.Exclusive := SetExclusive; WhichTable.Active := AndOpen; end; end; >3. Any other tips for usage/performance/setup ? Yup - it may not be worth bothering. Small tables are liable to be entirely buffered by DBISAM/Windows and large tables could take a while to load up. If its a table that's isn't being thrashed a lot then start questioning why you're putting it into memory. My reasons were 1) isloation 2) preventing clutter on the disk and 3) self cleaning in the event of a crash. Roy Lambert [Team Elevate] |
Tue, May 7 2013 8:52 AM | Permanent Link |
kamran | Hi Roy I just pasted and compiled but there are some parts that are coming up with errors. Sorry too much going on here for me to easily decode .. thanks ... *** procedure GetIdxInfo(FldList, DescList: string; DescWanted: boolean); var lCntr: integer; begin idxFlds.Clear; idxDesc.Clear; -----> error here>for lCntr := 1 to CountOccurancesOf(FldList, ';') + 1 do idxFlds.Add(SubFld(FldList, ';', lCntr)); if DescWanted and (DescList <> '') then begin ------> error here > for lCntr := 1 to CountOccurancesOf(DescList, ';') + 1 do idxDesc.Add(SubFld(DescList, ';', lCntr)); end; end; ..... ..... -----> error here ! > Maker := MakeDBISAMQuery('Memory', MakSesh); Maker.SQL.Clear; Maker.SQL.Add('DROP TABLE IF EXISTS "\Memory\' + tblName + '";'); Maker.SQL.Add('CREATE TABLE IF NOT EXISTS "\Memory\' + tblName + '"'); Maker.SQL.Add('('); for Cntr := 0 to WhichTable.FieldList.Count - 1 do begin All 3 errors above with "undeclared identifiers" 1. CountOccurancesOf 2. SubFld 3. MakeDBISAMQuery Kamran |
Tue, May 7 2013 10:24 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | kamran
This is what happens when I just copy 1 function out of a file function CountOccurancesOf(InStr, CompStr: string): integer; var strCntr: integer; begin Result := 0; for strCntr := 1 to Length(InStr) do if InStr[strCntr] = CompStr then inc(Result); end; Roy Lambert |
This web page was last updated on Thursday, March 28, 2024 at 06:05 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |