cancel
Showing results for 
Search instead for 
Did you mean: 

Multiple "command" connections to the database. Linking + Performance

rod_weir
Explorer
0 Kudos

Hi all,

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.

Thanks,

Rod

Accepted Solutions (1)

Accepted Solutions (1)

0 Kudos

Hi Ron,

Jason who is a Command SQL Guru will likely add more info but if you are going to use multiple Command Objects why not put all of the data collecting into one SQL Statement? Much more efficient than querying the data twice.

If you must though do this server side by using a Stored Procedure, DB's are more efficient collecting data than CR will ever be.

Thank you

Don

rod_weir
Explorer
0 Kudos

Hi Don,

Thanks for your reply.

Yes, I think using just the one command is the way to go, and I'm in the process now of updating all of the reports that use multiple commands to use a single, consolidated SQL command.

The original reason why I had multiple commands was to try to abstract the data somewhat. Other people editing the report can easily see the different queries that make up the report. By looking at the Database Expert Linking diagram, you could see the different relationships.

I guess its just as easy to examine the SQL of the single command object in the report to see it. If you need to see the relationships, paste it into SQL Server management studio and hit the "Design" button.

Thanks,

Rod

Former Member
0 Kudos

Rod & Don,

Not sure what I can add to this one. Sounds like you guys have it sorted out. The answer is in fact to bring everything in with a single command.

The reason for the poor performance is that when you try to link multiple commands (or a single command to other tables for that matter), CR stops trying to push any additional processing back to the server.

So, if you have 3 commands all pulling in data, CR will will run all 3 commands and bring the all of the data back to the local workstation. Then CR will perform the all of the joins and any other processing locally.

Why is that a big deal? #1) DB servers are designed to crunch numbers and process data very efficiently. #2) They usually reside on servers with far more processing power that of a typical workstation pc. #3) Network bandwidth. There is no point in filling up your network with tons of unneeded data. It just slows data retrieval.

If you want to make the relationships easier to understand for others who may come behind you to work on the reports, simply add inline comments to your SQL.

-


Rule of thumb... Multiple commands = bad... and any number of commands + other linked tables = bad... If you're going to use commands, do your very best to get everything into a single command.

Exceptions... (you know there has to be a couple) Data from multiple, unlinked servers which makes the situation unavoidable... and using a command as the data source for a dynamic LoV. Because you aren't linking the LoV command to the main data set AND because you aren't adding any of the LoV command fields directly to the design surfce, you can do this without any additional performance penalty.

HTH,

Jason

rod_weir
Explorer
0 Kudos

Hi Don and Jason,

Thanks for your help. Great information Jason. I wish I knew this about 15 years ago! At least now I know where some of my performance issues are coming from. I scanned the CR helpfile and could not find any of this information. It might be a good idea to thoroughly explain this in the official CR documentation.

Best regards,

Rod

Edited by: Rod Weir on Feb 22, 2011 11:47 PM

Former Member
0 Kudos

I figured this one out the hard way myself. I got my start writing reports in Access where you could build query on top of query and it didn't matter. The performance was the same weather you crammed everything into a single query or linked 12 separate queries together.

Of course you can also create link multiple database views or even build views that include other views and still be in good shape... But... CR Commands aren't views they are simple treated as ad-hoc queries and can't be referenced by other queries server side.

Anyway, glad you got everything straightened out. Good luck!

Jason

rod_weir
Explorer
0 Kudos

Thanks. The one thing I wish CR commands did was to re-write any SQL that contains a "Select *" into just the fields that were actually used by the report....the way that a standard CR connection to a database view works...

When you connect CR to a database view, the underlying view might be "Select * From Table1". All of the fields are displayed in the CR designer which is great for designing reports and having quick and easy access to all fields that might be in a view. When the actual query is fired off against the database at preview time, you can see from the SQL Server Profiler that only the fields that are referenced are part of the select statement.

Would be great if commands did a similar thing to save on network traffic.

I might make a re-writer myself using the CR object model and some .NET code.

Best practice = optimize any SQL in a command to only select fields that are part of the report. If you use "Select *", you will be pulling all of those un-needed fields client-side for nothing.

Just another example of the evils of "Select *".

0 Kudos

Correct... Why would CR modify the Command SQL? Commands are there so you have full control of what you want to get from the DB. If you create a connection and drop the tables and fields into the report then CR generate the SELECT for each field.

If you don't want SELECT * then don't use it in Commands.

Answers (1)

Answers (1)

0 Kudos

Thanks Jason.... great info and if I could I would make this one a sticky...We can only do so many...