hello all,
I’m creating a CR using the SalesForce.com driver as a data source.
This simple report needs to return some fields from the case table, and some fields from the EmailMessage table. Those two tables are related by a common ID.
In the Database Expert, I have an inner join on those two ID columns. I also have a condition on the case table to return only one case.
The problem I’m experiencing is that CR generates two SQL statements. One for each table. The Case table SQL has the condition to return the one case. The EmailMessage table SQL has no conditions on it, so I believe it is returning all the records in the table, and then matching them to the results of the other query. The performance is awful.
How can I push the processing and join to the database to improve my performance?
– SQL generated by CR–
www.salesforce.com
SELECT CreatedDate, ParentId
FROM EmailMessage
EXTERNAL JOIN EmailMessage.ParentId={?www.salesforce.com: Case.Id}
www.salesforce.com
SELECT CaseNumber, CreatedDate, Origin, Id
FROM Case
WHERE CaseNumber=‘00072202’
EXTERNAL JOIN Case.Id={?www.salesforce.com: EmailMessage.ParentId}
Thanks,
Nick
hagnik 
(BOB member since 2006-08-01)