I have a question as to how CR joins, queries and returns data when multiple SQL command objects are used. I'm interested in how efficient these are and trying to work out the best database access method.
Say a report has 2 SQL commands. Each one queries different data from the same database.
Command 1 has a parameter that acts as a filter.
Command 2 has no such thing - it is simply a "Select * From Table1"
In the Database Expert, they are linked together via their appropriate primary key / foreign key. The idea here is that the link will automatically filter the 2nd command to the filtered 1st command.
When the report is run, the parameter field for Command1 is presented. I enter the appropriate filter.
When I use SQL Server Profiler to look at what is happening at the server, I see that 2 queries are fired off. The first is filtered as per the parameter, the second is not.
It looks like the entire dataset for the 2nd command is coming back to the client, and then linked to the first using the joins I configured in the Database Expert. Is this right?
I was hoping that the server would join these 2 command object up and send a filtered recordset back to the client.
Is it better to simply have 1 command that does the joining (the complete SQL), or is this method OK?
Hope this makes sense.