Hi, Look's like a great resources of references for indexes use. Thanks
Hilaire
Brent Pinkney a écrit :
Hi,
I have just released the latest version of the Lava SQL engine for Chris's Magma OODB.
This version is distinguished by being able to optimise some queries to use Magma's indexes instead of materialising each element and checking it in the image.
You will need an image with:
Squeak 3.9
MagmaServerLoader-cmm.25 from Squeaksource
Lava-brp.36 from Squeaksource
LavaTesting-brp.34 from Squeaksource
Open TestRunner and run LavaTestCase and LavaOptimisedTestCase. 88
tests should pass.
This creates a Magma repository of poeple and their vehicles in ~/lava. It may take some time to create the LavaTestResource.
Open the Transcript and a Workspace.
Evaluate:
LavaTestResource current schema explore.
and explore how lava maps Magma collections in the repository to a relational schema.
- To see the 'raw' Magma collections, evaluate:
LavaTestResource current vehicles explore.
LavaTestResource current people explore.
- Use Magma's complex queries to find and print all the Pink* people
who are not too old:
(LavaTestResource current people
where: [ :p | (p familyName match: 'Pink*') & (p age < 50) ])
do: [ :p | Transcript show: p fullName; tab; show: p age; tab; show: p vehicle; cr ]
- Evalute this to help see how Lava turns SQL into Smalltalk code:
LavaTestResource current session preferences
compileProcessorSilently: false;
removeProcessorAfterExecution: false;
tryToOptimiseQueries: true.
- Now try a the same query using Lava instead of Magma:
(LavaTestResource current session execute: '
select p.fullName, p.age
from people p
where p.familyName like ''Pink%'' and p.age < 50
')
do: [ :row | Transcript show: row fullName; tab; show: row age; tab; cr ]
- Browse class LaProcessorLava. This is the class Lava created to
execute the SQL. The #executeSubquery1 method contains the logic.
Notice how Lava was able to optimise the (p.familyName like 'Pink%') term using a Magma where: clause.
- Try a join to get the people with vehicles:
(LavaTestResource current session execute: '
select p.fullName, p.age, v.registration
from people p, vehicles v
where p.familyName like ''Pink%'' and p.age < 50 and p.vehicleKey = v.vehicleKey
')
do: [ :row | Transcript show: row fullName; tab; show: row age; tab; show: row registration; cr ]
Browse LaProcessorLava >> #executeSubquery1 and notice
how the join is implemented as a nested loop.
that Lava cannot (yet) optimise a join - the (p.familyName like
'Pink%') term is not longer optimised by Magma.
- To explore the importance of begin able to optimise Lava queries to
use Magma's underlying indices, use the LavaLargeTestResource.
This Lava test case creates a Magma database of electricity reading transactions. The number of transactions stored may be very large:
Evalute:
LavaLargeTestResource current createTransactions: 5000. "This may take some time."
and
LavaLargeTestResource current session preferences
compileProcessorSilently: false;
removeProcessorAfterExecution: false;
tryToOptimiseQueries: true.
- Now evaluate this query:
(LavaLargeTestResource current session execute: '
select * from transactions where deviceId = 222 and agentName = ''midrak'' and reading > 3000
')
do: [ :row | Transcript show: row deviceId; tab; show: row agentName; tab; show: row reading; cr ]
This should execute in a few seconds.
Browse LaProcessorLava >> #executeSubquery1 and notice
how Lava has optimised the (deviceId = 222) and (agentName =
''midrak'') terms
The unindexed (reading > 3000) term is not optimised.
Re-evaluate with tryToOptimiseQueries: false.in 13) above and retry
the query in 15) above. It will take a lot longer.
Comments welcomed
Brent
Magma mailing list Magma@lists.squeakfoundation.org http://lists.squeakfoundation.org/mailman/listinfo/magma