Performance questions about queries

[Veli Izzet]

Hi all,

1- While using a query for a report, is it better to make calculations
on the query, and pass the calculated result to the report or vice versa?

2- Does this change according to the number of calculated fields?

3- Any other "best practices" worth mentioning?

Thanks for answers.

 

[Paul Overway]

1. It is usually better to make the calculations in the report whenever
possible.

2. No

3. Queries used for reports should be snapshot. Create index(es) in the
underlying tables, especially if used for joins. Minimize the data and
columns returned in the query to what the report actually requires (i.e.,
include a where clause if possible).

 

[Veli Izzet]

Thanks Paul,

I have some queries which are maybe redundant and most probably have
fields more than necessary. The project I started started ad-hoc, so no
planning.

Now, I am afraid of mingling with those. Is there a tool that checks for
1-Redundant fields in queries
2- The queries that have different names but are essentially the same.

 

[Paul Overway]

Not that I'm aware of. However, redundant fields usually show up as
tblname.field or Expr1000. If you run a query and see something like that,
it would probably be a good idea to review/modify it.