|
|
|
Forum Member
 
Group: Forum Members
Last Login: 2012-01-13 15:57:45
Posts: 48,
Visits: 1 645
|
|
Hello!
When executing such a query on OclPs:
ObjectComposition.allInstances->select(CoReDetailObject=self)
it produces this SQL:
SELECT ObjectComp_1.ECO_ID FROM ObjectComposition ObjectComp_1
WHERE ((SELECT CoReObject_2.ECO_ID FROM CoReObject CoReObject_2
WHERE ObjectComp_1.CoReDetailObject = CoReObject_2.ECO_ID) = (SELECT CoReObject_1.ECO_ID
FROM CoReObject CoReObject_1 WHERE CoReObject_1.ECO_ID = @P0ID0))
and this SQL fails
Why does it give so strange and unoptimal SQL query? Setting UseSQL92Joins in PersistenceMapper does not change anything.
Best regards,
Dmitry
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 2010-08-11 16:02:44
Posts: 338,
Visits: 890
|
|
What is the context of this OCL operation?
Could you please set the context for 'self' and try the query again.
Also is it MSSQL?
Anyway it should generate valid SQL or give runtime error on invalid OCL.
Cheers,
Dmitriy.
My Blog: http://dnagir.blogspot.com
|
|
|
|
|
Forum Member
Group: Forum Members
Last Login: 2012-01-13 15:57:45
Posts: 48,
Visits: 1 645
|
|
The context is correct. The same happens both in code (Ps) and in EcoSpaceDebugger. The same Ocl query in Ocl (not Ps) works fine but due to database size it is not optimal to execute allInstances.
I am using MS SQL Compact 3.5 over my own custom persistence.
Regarding the SQL I would say it looks really weird. If I use this Ocl: self.ObjectCompositionAsDetail (which renders the same set of objects), the SQL is perfectly correct and uses a join. Basically, I wonder why two logically equal Ocl produce two completely different SQL, one of which is wrong.
Take care,
Dmitry
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 2010-08-11 16:02:44
Posts: 338,
Visits: 890
|
|
Dmitry Slabko (2008-11-27)
The context is correct. The same happens both in code (Ps) and in EcoSpaceDebugger. The same Ocl query in Ocl (not Ps) works fine but due to database size it is not optimal to execute allInstances.
If you are sure the context is correct then it it definitely is a bug.
I assume you have model like this:
ObjectComposition -ObjectCompositionAsDetail-*-----1-CoReDetailObject- Detail
Dmitry Slabko (2008-11-27)
Regarding the SQL I would say it looks really weird.
Cannot agree more.
Dmitry Slabko (2008-11-27)
If I use this Ocl: self.ObjectCompositionAsDetail (which renders the same set of objects), the SQL is perfectly correct and uses a join. Basically, I wonder why two logically equal Ocl produce two completely different SQL, one of which is wrong.
I think that's fine if you get different SQL using different OCL (thou they are logically identical).
But it's a problem that a SQL is just incorrect and wrong.
I saw couple of reports like this on ECO bugs. This scares me a bit.
Cheers,
Dmitriy.
My Blog: http://dnagir.blogspot.com
|
|
|
|
|
Supreme Being
Group: Administrators
Last Login: 2010-11-30 12:17:13
Posts: 1 230,
Visits: 1 382
|
|
While I agree that the generated SQL looks weird, and is far from optimal, I just tested this on SqlServer 2005, and it results in the correct set of objects... What kind of error do you get from SqlServer compact?
The OCL is as you noted equivalent to "self.ObjectCompositionAsDetail". This gives better much better performance in OCL (it doesn't even have to be executed in PS, it would give the same performance in memory). The shorter OCL is also much easier to translate to SQL.
/Jonas Hogstrom [CapableObjects]
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 2010-08-11 16:02:44
Posts: 338,
Visits: 890
|
|
I also tried it on SqlServer 2000.
And it seems this query works (not sure about the validity of the result thou).
I just modified the query for NW DB:
SELECT ObjectComp_1.ProductID FROM Products ObjectComp_1
WHERE ((SELECT CoReObject_2.SupplierID FROM Suppliers CoReObject_2
WHERE ObjectComp_1.SupplierID = CoReObject_2.SupplierID) = (SELECT CoReObject_1.SupplierID
FROM Suppliers CoReObject_1 WHERE CoReObject_1.SupplierID = 1234))
Assuming:
ObjectComposition <-> Products
CoReObject <-> Supliers
So that must be a problem with Compact Edition of MSSQL.
Cheers,
Dmitriy.
My Blog: http://dnagir.blogspot.com
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 2010-08-11 16:02:44
Posts: 338,
Visits: 890
|
|
Here is the SQL to check validity. Play with the parameter testId.
It seems the query always returns valid result (the same the last simple query).
declare @testId int
set @testId = 3
-- Class.allInstances->select(OtherRelation = self)
SELECT ObjectComp_1.ProductID FROM Products ObjectComp_1
WHERE ((SELECT CoReObject_2.SupplierID FROM Suppliers CoReObject_2
WHERE ObjectComp_1.SupplierID = CoReObject_2.SupplierID) = (SELECT CoReObject_1.SupplierID
FROM Suppliers CoReObject_1 WHERE CoReObject_1.SupplierID = @testId))
-- self.RelationName
select ProductID from Products
where SupplierID = @testId
My Blog: http://dnagir.blogspot.com
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 2010-08-11 16:02:44
Posts: 338,
Visits: 890
|
|
I've shown this query to the DBA.
He was frustrated a bit 
Here is a bit more readable version that works with Northwind that is generated by OCL: "Products.allInstances->select(Suplier=mySupplier)"
SELECT prods1.ProductID FROM Products prods1
WHERE
(
-- Links supplier and product
(SELECT sups2.SupplierID FROM Suppliers sups2
WHERE prods1.SupplierID = sups2.SupplierID)
=
-- This select is stupid - it just returns supplierId
(SELECT sups1.SupplierID
FROM Suppliers sups1 WHERE sups1.SupplierID = @supplierId)
)
ECO should be able to see that OCL to SQL really should be:
select ProductID from Products
where SupplierID = supplierId
As tests showed 1st query executes ~3 times slower than 2nd and uses 5 times more operations.
And this is on a really small Database (NW). On real production DB it will be much slower.
So is there some way to fine-tune such OCLs?
Especially that we were promised years ago to be able to do so (by Malcolm Groves).
Cheers,
Dmitriy.
My Blog: http://dnagir.blogspot.com
|
|
|
|
|
Forum Member
Group: Forum Members
Last Login: 2012-01-13 15:57:45
Posts: 48,
Visits: 1 645
|
|
I presume that the problematic SQL is valid for a "full" MS SQL, though may not be parsed by SQL CE which says that the query is wrong and points at the first sub-select. And I agree that the SQL optimizer of Eco could produce a much clearer output which could work on any SQL server.
Best regards,
Dmitry
|
|
|
|
|
Forum Member
Group: Forum Members
Last Login: 2012-01-13 15:57:45
Posts: 48,
Visits: 1 645
|
|
One more thought: it would be very handy to be able to set up if the SQL server supports, or not, sub-selects... The perfect place for this would be SqlDatabaseConfig field of a PersistenceMapper.
Take care,
Dmitry
|
|