Re: [Q] optmizing postgres for 'single client' / many small queries

Well, actually
somebody has written a C++ ORM
that allows to do things (just a sketch)

class L: CDbCapable
{
  public:
  int prop1;
  int prop2;
}


class A:  CDbCapable
{
  QArray<L>   list_of_props_xyz;

}


int main ()
{
   A inst1;

   inst1.create_or_update_DbSchemaIfNeeded();


   inst1.readFromDb();
   //modifying something

   inst1.writeToDb();

}



As it is well known C++ lack of Reflection prevents it from having
standardise  Data serialization
libraries to files or to Databases.

So in-house a mechanism was developed to do the above.  It took some
time and it is not possible to just yank it out.


Of course, internally in the ORM's implementation a somewhat
questionable decision was made that
to process arrays of 'children' for a given instance would require
separate SQL statements.


That's where the problem comes from, I understand what needs to be done
to redesign the approach/etc.  And that
will take more time than currently is available.

Therefore, I just wanted to ask if there there are some things in Pg
that can I can experiment with
(local client/server communcations via IPC,  reducing the speed of SQL
parses, any other possible tricks)



Thank you  in advance




On Wed, 02 Sep 2009 14:26 -0400, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:
> "V S P" <toreason@fastmail.fm> writes:
> > The application typicall goes like this
>
> > select id, child_tb_key_id, <fields with parms> from tb1
>
> > then for each selected row above
> >      select from the child table do a select (like the above)
>
> > and so on -- many levels deep
>
> Seems like you need to fix your data representation so that this
> operation can be collapsed into one query.  The main problem looks
> to be a bogus decision to have separate child tables rather than
> one big table with an extra key column.
>
>             regards, tom lane
--
Vlad P
author of C++  ORM  http://github.com/vladp/CppOrm/tree/master


--
http://www.fastmail.fm - mmm... Fastmail...