Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 5 of 5 total
Thread Best Way to Use Memory Tables
Tue, May 7 2013 6:10 AMPermanent 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 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate 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 AMPermanent 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 AMPermanent 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 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

kamran


This is what happens when I just copy 1 function out of a file Frown

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
Image