Hello,

I am using sqldb to connect to a postgres database. Using wiki instructions, my final object is a TSQLQuery for each table. So, this basically is a TDataset and to read fields I know that I can use First(), Next(), EOF() and RecNo

But these are rather slow. Many fields in the tables refer to the primary key in other tables, which does not match the RecNo. How can I jump fast a table to the record which has a particular PrimaryKey?

thanks,

Use a local index and tlocalindex.find() (MSEgui). SQLdb has local index too AFAIK.
MSEgui has additionally tlookupbuffer and fast lookup field linking by local index and direct data access without dataset scrolling.

Martin

Ping,

No sqldb specific answers on this one? I did some googling and also searched for TPrimaryKey or LocalIndex in sqldb without results ... =(

thanks,

Felipe Monteiro de Carvalho wrote

Ping, No sqldb specific answers on this one? I did some googling and also searched for TPrimaryKey or LocalIndex in sqldb without results ... =(

To my knowledge there is no fast search/locate in sqldb. Maybe we should have a look at Martin Schreibers' implementation.

Michael.

Most code is in lib/common/db/msebufdataset.pas. Lookup buffers are in lib/common/db/mselookupbuffer.pas. Heavy stuff. ;-)

Martin

Martin Schreiber wrote

Most code is in lib/common/db/msebufdataset.pas. Lookup buffers are in lib/common/db/mselookupbuffer.pas. Heavy stuff. ;-)

How much from the other stuff would I need to compile your DB modules?

Any beginner guides to stablish a connection, open a table, read/write some fields, go to a different record, go to a record which has FieldX=Y?

Does it support Postgres?

Does it work in Mac OS X?

Well, sqldb has the indexes. It's just an additional index routine.

Michael.

You can use indices and locate with TSQLQuery as follows:

  SQLQuery1.AddIndex('idx_no_art','no_art',[]);
  SQLQuery1.IndexName:='idx_no_art';
  SQLQuery1.Open;
  ...
  SQLQuery1.Locate('no_art','200295',[]);

Unfortunately, Locate uses a simple linear search. Note also issue 0020514.

Ludo

Felipe Monteiro de Carvalho wrote

On Fri, Oct 21, 2011 at 4:50 PM, Martin Schreiber

The whole non visual MSEgui infrastructure.

Any beginner guides to stablish a connection, open a table, read/write some fields, go to a different record,

Almost the same as sqldb.

go to a record which has FieldX=Y?

Use tlookupbuffer.find() for example. In order to get a lookup value without scrolling use the returned bookmark in tmsebufdataset.currentbmas*[] or use a lookup field without setting LookupCache.

Does it support Postgres?

Yes.

Does it work in Mac OS X?

No.

BTW, because of the dead of BerliOS MSEide+MSEgui and MSEuniverse are now Gitorious:

http://gitorious.org/mseide-msegui
http://gitorious.org/mseuniverse

The SVN repositories are not updated anymore.

Martin

Thanks, that's really interresting, but it does not seam to be useful in my case. Basically my application is a cgi app which receives a request, does some modifications in the database and then sends back some other data based on the request and on the database and then it just quits.

I get the value of the primary key of the table from the request, so I though that because it is the primary key I would be able to quickly jump to it. But it seams that not? From what I understood the solutions are first going through the entire table and indexing it to make future lookups faster. But this does not seam to use the fact that my field is the primary key.

From my experience doing a loop

while not SQLQuery.EOF do
compare
SQLQuery.Next

is *really* slow.

But I doubt that first indexing the whole table and then looking up the value 1 time will be any faster.

I am thinking of moving to FastCGI, but I'm not sure if then I would be able to index the table and gain speed in future requests. maybe...

Can't you do the lookup in the DB server by a SQL join for example?

Martin

Can't you do the lookup in the DB server by a SQL join for example?

Or, if you need do some processing in between, use 2 queries and build the "where" clause for the 2nd from the results of the 1st. If you don't need the full table, don't use select *.

Ludo

Yes, but of course this is the complete wrong way. Of course this is slow.

You must do a

With TSQLQuery do
   begin
   SQL.Text:='SELECT * FROM MyTable where KeyField=:MyKey';
   ParamByName('MyKey').AsString:=Mykey; // MyKey you got through the request.
   Open;
   try
     if (EOF and BOF) then
       // no data, raise an exception
       Raise Exception.CreateFmt('Value "%s" is not an existing key value',[MyKey]);
     // Do some stuff with the record
   finally
     Close;
   end;
   end;

Then you will get only the record you need, and it will be lightning fast if you have used the primary key as the field to select on.

Michael.