[Lazarus] Web development and XML database (Mattias)

[Graeme Geldenhuys]

On 16/11/2011, Juha Manninen <juha.manninen62 at ....> wrote:
>
> I guess your queries fetch from one table only. Complex SQL queries with
> many joins can be really slow, even with correct indexes.

Nope, we often fetch from many tables. True, single table queries are
just a couple of milliseconds, and multiple table queries are still
far under 1 second. See attached image - status bar values, using the
following query. This is just something I put quickly together as an
example. Not extensive, but an example none the less.

select distinct l.Username, a.City, c.NAME as CountryName,
  t.ADDRESSDESCRIPTION from LEARNER l
  inner join ADDRESS a on
       a.OWNER_OID = l.OID
  inner join COUNTRY c on
       c.OID = a.COUNTRY_OID
  inner join ADDRESSTYPE t on
       t.OID = a.ADDRESSTYPE_OID


Our reports use much more extensive queries, but results are still
instant (way under a second).


As an explanation of the values in the status bar of the screenshot.

  Record count - this should be obvious ;-)
  Time to execute....  - this is how long the sql execution took and
returned a resultset.
  Time to download... - this is how long it took to generate Objects
based on the resultset (our apps GUI & CGI use objects everywhere,
never datasets)

One of our screens that our CGI and GUI apps generates is a treeview.
It pulls back just enough information to build the tree. The treeview
contains about +- 70,000-80,000 nodes. Retrieving that data and
building objects from it is also near instant.

But yes, SQL queries can take long too - depending on what it must do.
I have worked on project where we had to do data conversion from a IBM
mainframe system to a Intel based SQL Server 2000 database. A full
data conversion had to be run on Friday's, and normally only ended on
Monday morning (if no failures occurred). That's 2.5-3.0 days
continuous running, resulting in a 8GB SQL Server database. But this
is an extreme case. :)

-- 
Regards,
  - Graeme -


_______________________________________________
fpGUI - a cross-platform Free Pascal GUI toolkit
http://fpgui.sourceforge.net
-------------- next part --------------
A non-text attachment was scrubbed...
Name: sql_results.png
Type: image/png
Size: 12685 bytes
Desc: not available
URL: <http://lists.lazarus-ide.org/pipermail/lazarus/attachments/20111116/6a923004/attachment-0003.png>