Ad hoc Queries query

[ConvertFromOldNGs]

by Dr Mike Lance >> Mon, 10 Sep 2001 22:25:37 GMT

I teach Jade at Christchurch Polytechnic Institute of Technology.
One of the joys of being an academic is being able to 'play' with technology.
One of the down-sides is the concern that what gets taught is just play and not related to industry realities.

I've recently been pondering how best to write ad hoc queries in Jade.
In database courses a heavy emphasis is placed on using SQL for reporting purposes.
I am aware that many database administrators end up spending a lot of time optimising queries to run on large data sets. Jade is a database wrapped up in a programming environment, but is it used like SQL is? Does anybody send time and effort in Jade writing ad hoc queries for reporting?

The following is an exercise I've adapted from a database course.
There are Customers.
Customers have a credit limit.
Customers make Orders.
Orders have a date, an amount and an isPaid boolean field.

Assorted queries are required to be made on this data..
ie
How may Customers have orders which have not been payed?
What is the total of unpaid orders for each customer?
How many customers have unpaid orders of more than $xxx?
Which customers have ordered and payed for more than $xxx in the last yyy days?

One way of implementing these queries is to start adding calc methods to Customer and constructing external key directories. The use of public properties or accessor / get methods for every property makes things easier.

I am concerned that this type of approach quickly 'pollutes' the system with very specialised query methods. (My fictitious client can always think of a new query variant.)

Is there a smarter way of dealing with ad hoc queries?


- Mike Lance

[ConvertFromOldNGs]

by Craig Shearer >> Mon, 10 Sep 2001 23:29:43 GMT

Hi Mike

Interesting to get an academic's opinion on this... but one that is pretty relevant to us trying to develop business systems using JADE.

From the outset, I'd say that querying a relational database using SQL will always be easier than performing ad-hoc queries in JADE. That's what an RDBMS is good at. And, it's hard to beat the simplicity of using SQL to perform queries, as compared to performing queries in JADE which at present need to be performed by writing code to iterate through collections, etc.

I also agree that DBAs spend a lot of time optimising queries (or more likely, the physical database structure) to get good performance. In JADE, you don't have a DBA role - that is done by individual developers. One of the joys of an object database is being able to structure classes and the relationships between them in meaningful ways, so that the database can perform well from the outset, without requiring the talents(!) of a DBA to tune it. However, the downside of this is that classes have access paths optimised for their expected use, and this tends not to support ad-hoc queries well.

I would say that a DBA adding indexes to relational database tables is equivalent to a JADE programmer adding new collections, etc. to support ad-hoc queries in a manner that wasn't initially envisaged when the system was developed.

What are the solutions? Well, you could design a relational view on your JADE database, so that you can use SQL for perform ad-hoc queries... however, I don't have any direct experience of this with large sets of data. How well does this perform?

The second approach would be to use the JADE Report Writer, due for release in 5.2. Developers have been trying out pre-release versions of this, and this is a good approach to handling ad-hoc queries. However, I suspect that some work would be required to add collections, etc. as required to make performance acceptable. One of the results of adding a report writer to JADE is that JADE now has a query language, albeit hidden from developers. In current versions of the report writer, the generated query is written out to a log file - the syntax and capabilities of this seem very similar to SQL. I understand that there are future plans to allow developers to write queries using this language, which would make certain types of query (such as those you propose) very easy to implement.

On the topic of polluting the schema with specialised query methods, you can always put all query functionality in a sub-schema. Then you get to hide all of this "pollution".

Just my thoughts...

Craig.