Home DADE
New Blog Posts: Merging Reports - Part 1 and Part 2

Conceptual problem in plugin model

edited June 2014 in DADE
After many many years of NOT encountering anything like this, I have
discovered a horrible problem with how most DADE plugins (in my case
daNexusDB but standard ones too) implement GetFieldsForSQL(aList: TList;
aSQL: TStrings) . Put simply, if you are not able to supply a search
criterion to make the aSQL run efficient (and in my example I CAN'T as
its for a detail DataView), then with large databases the Wizard for
editing the SQL becomes unusable, with timeouts for me still kicking in
after 60+ seconds. This is a serious problem if the database engine
doesn't allow you to do anything clever to get a complex SQL to return
an empty dataset, which is all the GetFieldsForSQL needs for building
its list of TppFields.


The full account of how this problem manifested itself for and the
rather drastic solution that I've had to come up with as a workaround is
explained here:

http://www.nexusdb.com/forums/showthread.php?t=18567

Not saying I can think of a better way to do this than how its currently
done, given that RB is blind to the limitations of the database engine
so can only just run the SQL to get a TDataSet back, but this is a
horrible headache, nonetheless.

Comments

  • edited June 2014

    There is no conceptual problem. :)

    The Query Designer is used to create/edit a SQL query. When the user presses
    Ok, the SQL statement is validated by the database engine. Put a breakpoint
    in the GetFieldsForSQL method and when it is called examine the call stack.
    In my testing is is being called by TdaSQL.Valid, which is called by the
    QueryDesigner.

    To optimize performance, RB adds a Where 'c' <> 'c' search condition, so the
    query will return zero records. (Note that is the same solution NexusDB tech
    support suggested in the referenced thread.)

    You say "with large databases..." A SQL database engine can execute a query
    against a large database with instantaneous performance. To optimize
    performance, the tables need to be indexed on the join/search fields. (Same
    goes for sorting.) The SQL database engine uses the indexes, rather than
    looking thru the entire table. I recommend examining the join conditions
    and search conditions and make sure you have table indexes on the relevant
    fields.

    If you can't get the query optimized then ask NexusDB support for optimizing
    tips.



    --

    Nard Moseley
    Digital Metaphors
    www.digital-metaphors.com

    Best regards,

    Nard Moseley
    Digital Metaphors
    www.digital-metaphors.com
  • edited June 2014
    Thanks for the reply Nard,

    However, everything you've written is already understood and anticipated
    by me. All joins in my query use correctly indexed columns, but as good
    as NexusDB is, there are known weaknesses with (necessary) Outer Joins,
    plus for some reason "WHERE FALSE" doesn't produce anything like the
    performance needed in multi-join queries (see the NG thread I mentioned)
    . NexusDB don't seem to care too much about this and maybe, given the
    knock on effect it has for RB, you might want to raise it with them?

    I have had to written thousands of highly complex SQL statements for the
    rest of our application and I'm very good at getting the best of out the
    NexusDB engine. If my hands weren't tied by the need for our end users
    to use the Query Builder wizard I could easily hand-craft the SQL along
    these lines and it would all run in an instant:

    SELECT * INTO #TmpTable
    FROM HugeMainTable
    WHERE
    FALSE;
    -- instant return (31 ms) using office Nexus trick you cite

    SELECT * FROM
    #TmpTable
    JOIN LargeXTable
    JOIN LargeYTable
    JOIN LargeXTable
    ETC
    WHERE

    -- nothing in #TmpTable so SQL returns almost immediately (about 280ms
    in tests)

    But I can't do that, so the first time I open the Report Designer I now
    create the DDL for my entire database on the fly, build an empty "in
    memory" database using this DDL, and point the 2 or 3 DADE calls that
    need to just get empty datasets to that database. It works well,
    but...REALLY!!! :)

    Paul



This discussion has been closed.