Re: What is more efficient?

On Mon, 30 Mar 1998 22:21:09 -0500, "Robert Augustyn" <raugustyn_at_sprint.ca> wrote:

>Hi,
>I am in the process of setting up a reporting for our app. and would like
>your opinion on what would be a better ( more efficient solution )
>For most reports data will be extracted from two tables and the union of
>those would be nedded to for some.
>There are two scenarios which I am considering ( if you think that I am mad
>and you think that it should be done in complitly different way please let
>me know) one creating indexes on those tables and just run query against
>those the other would be generating temp table which would be a subset of
>the records and run the reports against that temp table.
>There are around 7 different reports to be run per customers for about 130
>customers.
>thanks in advance
>robert
>
>

Surely, the most efficient method is to first prepare a temporary table, BEFORE running any report and then run the reports as many times as you need. Note that the temporary table would be created only once - if you were planning to create the temporary table each time a report was run, that would hold practically the same performance than running the report without the temporary table.

I would follow this approach (creating a temporary table) only if I was dealing with very large tables and have tried all performance tuning features available with no success.

Imagine if someone changes the underlying tables while reports are being run. Your temporary table wouldn't get updated - that's the most serious concern. On the other hand, if you're working e.g. on a DataWarehouse project and users won't be updating tables then go ahead and make a temporary table.

Hope this helps,

Nuno Guerreiro Received on Tue Mar 31 1998 - 00:00:00 CEST