Conceptual problem in plugin model

rbuser

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.

nardmoseley

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

rbuser

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