Performance Issue 1

[alvintse]

With CR 9, which method provide the best performance?

- selecting the tables using database expert; that will include many fields that not needed for the report.

- add command; write the sql statement and select only the fields needed for the report.

Thanks for your comments.

 

[synapsevampire]

The methods are similar as you only query the database for the fields you're using in the report. Check Database->Show SQL Query to see what's being passed.

Best performance would be to create a Stored Procedure on the database.

-k

 

[LidoDeJesolo]

How about reporting on SQL views rather than tables? That would let your SQL server do all the work.

Lido
Development & Reporting
UK

 

[synapsevampire]

Lido: That may not buy you anything, check with your dba.

The best results are using a Stored Procedure as you have a precompiled execution plan on most databases, though I always suggest basing all SQL (SP's, whatever) on Views based on the tables for reusability and maintenance sake.

Optimizing SQL is a complex matter, and having a View does NOT mean that SQL will execute faster, conceivably it could take longer.

-k

 

[LidoDeJesolo]

Didn't know that!

Thanks for the tips!!

Lido
Development & Reporting
UK

 

[Mack2]

If you do not know how to write a stored proc, put your code in a command. We did some testing and commands are almost as fast as stored procs. Commands have other benefits that stored procs do not. Once your code is in a command, you will see a performance gain. Do you have any formula's in your report?

 

[alvintse]

Yes, that exactly what I concerned. Writing a stored procedure is very time consuming and somehow it is difficult for other people to modify it.
I do have many formula in the report. Does it matter?
By the way, what other benefits commands have that store procedure do not?

Thanks.

 

[synapsevampire]

A Command is a pretty generic term... Perhaps you mean a Function on a database? Please elaborate on what you mean by a command.

-k