mORMot Open Source

dansot

Member

Registered: 2012-11-12

Posts: 25

very slow query

My database is about 7 million records using SQLite

I am storing 4 longints in each record.

tinfo = class(TSQLRecord)
  private
    fs1,fs2,fs3,fs4: longint;
  published
    property sig1: longint read fs1 write fs1;
    property sig2: longint read fs2 write fs2;
    property sig3: longint read fs3 write fs3;
    property sig4: longint read fs4 write fs4;
  end;

procedure TForm4.FormCreate(Sender: TObject);
begin
  model:=tsqlmodel.Create([tinfo]);
  database:=tsqlrestserverdb.Create(model, extractfilepath(paramstr(0))+'data.dat');
  TSQLRestServerDB(database).CreateMissingTables(0);
  database.CreateSQLIndex(tinfo, 'info', true, 'index');
end;

This query takes so long it almost seems frozen.  I am not sure what I am doing wrong.

function sigfound(p1,p2,p3,p4: longint): boolean;
var info: tinfo;
begin
  info := tinfo.Create(Database,'sig1=? AND sig2=? AND sig3=? AND sig4=?',[p1,p2,p3,p4]);
  result:=(info.ID <> 0);
  info.Free;
end;

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,604

Website

Re: very slow query

You have to specify the fields to be indexed, for the index to work.

Something like CreateSQLMultiIndex(TInfo,['sig1','sig2','sig3','sig4'])

You may also use two Int64 variables instead of four longint.

dansot

Member

Registered: 2012-11-12

Posts: 25

Re: very slow query

Thank you, I have much to learn.  The documentation helps but sometimes it is good to have a person fluent in it to give direction.  Using the multiple indexes fixed the slowness and I am now using two int64 as you suggested.  Thank you again.