How can I select data in JADE?

[ConvertFromOldNGs]

by JCAM >> Thu, 28 Jul 2005 22:11:49 GMT

Hello All,

I am evaluating JADE product .

Somebody can help me in order to select data (objects) in JADE DB?,
Is possible to use the SQL statments ?

I wait for your inputs.

Thanks

Juan Carlos Alarcon

[ConvertFromOldNGs]

by Houston >> Mon, 1 Aug 2005 3:43:11 GMT

No need for anything so old-fashioned ... use the same Jade language constructs to access both transient and persistent data. Check out the online training, tutorials, and demos:

http://www.jadeworld.com/Jade6/jade6_jolt.htm http://www.jadeworld.com/Jade6/jade6_tutorials.htm http://www.jadeworld.com/downloads/Jade6/Erewhon.pdf http://www.jadeworld.com/downloads/Jade6/Erewhon.zip

[ConvertFromOldNGs]

by Jaded >> Thu, 4 Aug 2005 10:16:15 GMT

You may think SQL old fashioned but a lot of the time it beats having to navigate through your data.

And besides, surely you'd much rather write:

select count(*) from Customer where Name = 'Smith'

than

vars
c: Integer;
cust : Customer;
begin
foreach cust in Customer.instances where cust.name = 'Smith' do
c := c + 1;
endforeach;
write c;
end;


and I bet, given an appropriate index and a million customers, the SQL would execute a hell of a lot faster too!

[ConvertFromOldNGs]

by John Munro >> Thu, 4 Aug 2005 10:31:09 GMT

The speed will be the same - it's still just doing lookups on indexed data, the only difference is in the language the developer writes in.

Something very similar to this was actually on the roadmap last year under "Adhoc Query Engine" but seems to have disappeared in later roadmaps...


John Munro

FileVision UK Ltd.
Atlantic House
Imperial Way
Reading
RG2 0TD

Telephone: +44 (0) 118 903 6066
Fax: +44 (0) 118 903 6100
Email: john.munro@filevision.com
Web: http://www.filevision.com

The contents of this communication are confidential and are only intended to be read by the addressee. We apologize if you receive this communication in error and ask that you contact FileVision UK Ltd. immediately to arrange for its return. The use of any information contained in this communication by an unauthorized person is strictly prohibited. FileVision UK Ltd. cannot accept responsibility for the accuracy or completeness of this communication as it is being transmitted over a public network. If you suspect this message may have been intercepted or amended, please inform FileVision UK Ltd.

[ConvertFromOldNGs]

by Jaded >> Thu, 4 Aug 2005 19:22:33 GMT

John

I think that's a pretty bald assertion that the speed will be the same. Actually, it's patent nonsense, simply because each statement in JADE has to be interpretted before it's executed. It will never achieve the speed of a query being executed by a relational database engine.

But, the whole point of my post is that sometimes navigational access to data insn't always the best way to do things, and the real point I was trying to make was that some sort of adhoc query engine with a nice simple syntax would be beneficial.

[ConvertFromOldNGs]

by Houston >> Mon, 8 Aug 2005 3:28:25 GMT

So you are saying that sql is not interpreted, or that it's interpretation is more optimised?

[ConvertFromOldNGs]

by Jaded >> Mon, 8 Aug 2005 7:38:51 GMT

Sure SQL is interpretted once to generate a query plan. Thereafter the query plan is executed.

But since you're challenging me to provide evidence, I did a simple test.

I created a simple class in Jade with an Integer and a Decimal - called it Customer. Then I created 100,000 of them and executed the following:

vars
c : Customer;
count : Integer;
t : Decimal[19];
begin
t := app.relativeMachineTime();
foreach c in Customer.instances where c.balance > 50 do
count := count + 1;
endforeach;

write count;
write "toook " & (app.relativeMachineTime() - t).String;
epilog
end;


This takes about 7 seconds to execute on my P4 2GHz laptop.


Then, in MS SQL Server, I created a Customer table with Id (int) and Balance (money) columns. I inserted 100,000 rows with random balances.

Then I exeucted the following query:

select count(*) from Customer where balance > 50

This takes about 60mS to execute.

So, yes, I think I'd say that this is a little more efficient.


But then you'd probably argue that this isn't a typical case.

OK, so what say I want to do a "like" query across my customers? Say SELECT * FROM Customer WHERE Name LIKE '%Smith%'

In Jade, I'm forced into something like:

foreach c in Customer.instances where c.name.pos("Smith", 1) <> -1 do
...
endforeach;


And this is much much slower! And, this isn't an unrealistic example.

[ConvertFromOldNGs]

by Gerard O'Brien >> Tue, 9 Aug 2005 20:59:31 GMT

Either use keys in both cases the most efficient way they're meant to be used for each case or don't use keys at all.

In your example you force Jade to fetch every object but your SQL query only traverses key tables that have been preloaded from record data.

[ConvertFromOldNGs]

by Jaded >> Tue, 9 Aug 2005 21:35:21 GMT

OK, so if I build a collection of Customers by balance, then I could cut down the time as it would only force JADE to load those that matched the key. Seems like an odd thing to do though. But in the SQL example, the only key on the table is the primary key which was the Id field, not the balance. So, it's not scanning any key tables. (And who cares what it's doing underneath - the whole point is that it's much much faster!)

As for the second example, please tell me how I should build a collection of Customers so I can do a free text search! Not possible! (Or certainly not efficiently both in terms of runtime performance and developer time.)

[ConvertFromOldNGs]

by Patwos >> Wed, 10 Aug 2005 1:09:55 GMT

This could very easily become a religious war with both sides dreaming up examples of where their preferred technology out-performs the other. For example, one could easily come up with a more complex example that in an OO world will return the required information in very short order, even with interpreted Jade code, but take magnitudes of time longer in a relational SQL select statement world. A much less complex example could be as follows:

Suppose you have a table of purchase orders, keyed by the purchase order ID field as per your previous example. You now want to know how many purchases customer #1234 has made so you'd write something like the following, assuming you have the customer number inserted as custId in the "PurchaseOrder" table:

select count(*) from PurchaseOrder where custId = 1234

As you add more entries to the PurchaseOrder table, this will of course naturally take increasingly longer to execute as it has an increasing number of rows in the table to traverse when processing the select statement. Now, the semi-equivalent JADE code:

cust.allPurchases.size

Time to execute is not affected by how many purchase order instances there are.


As I said at the beginning, this could easily become a religious war so why not simply agree that there are situations where either technology will easily outperform the other and use whichever technology suits the requirements of the development at hand.

eg: There are situations where it is appropriate and right to choose and use tools other than Jade to develop your solution.

Given complex real world requirements to solve in an application, at this point in time I would much rather use Jade as my development tool of choice to develop that application as my development time is much faster than it was with the other technologies I used to use. If simple queries like the ones you have used as examples take a little longer to process then that is a price I am more than willing to pay for the benefit of faster development time. I can of course utilise some of the time I saved developing the application to optimise any common query requirements of the end users as appropriate to reduce this time.

Looking at the 6.1 road map, the new Relational Population Service seems to allow us to have the best of both worlds. Develop the application faster and still have the option of running adhoc SQL queries over an appropriately populated relational database.

Hope that helps,
Pat.