View or Stored procedure

[ThomasSoerensen]

How fast are we talking by the way?

If you're existing report is pretty much instaneous, then I wouldn't bother spending any more time on the 'best' way.

We are not talking instantaneous. it depends a lot on the hardware I use to run it.
I have about 300 queries like the one I just simulated, I use a stored procedure to execute all the views and dump the result into a result table.
On my "workstation" laptop it takes (6-8) hours.
On my "gaming pc" it takes 1 hour.
On my db server it takes 10 minutes.

So I would like to increase performance because it is not always that I am close to the db server when I need to run the queries.

[Spacecadet]

Thanks.

could you explain in a few words what you mean by "even select it back out for you if you want" after the data is dumped into the result table. What is this and why could one need it?

if you want to see the results from the report tables the stored procedure can populate the report tables, then query them to give you the results straight away

e.g

create procedure reports(@refresh int)
as

   if @refresh = 1 
   begin 
     truncate report_table

      select *
         into report_table
        from base_table
      where.....
   end

   select *
   from report_table

Indexes are going to slow your inserts down whether the table is populated or not.
Dropping the index and then recreating it after the insert can have some significant performance benefits

[Spacecadet]

SELECT TOP (100) PERCENT 'SCC1' AS [Check], dbo.t_users_with_roles_and_profiles_all.SID,
<snip>
ORDER BY dbo.t_users_with_roles_and_profiles_all.BNAME

Get rid of the top 100 percent

if you don't need the results in a particular order then get rid of the order by clause

[ThomasSoerensen]

Get rid of the top 100 percent

if you don't need the results in a particular order then get rid of the order by clause

when I get rid of the TOP 100 Percent, which was copies from the view definition, the execution time wsa reduced form 7:43 to 6:55.

I will have a hard time getting rid of the Order clause as the result table is seen by the end receiver and they like the results to be ordered nicely. Let me just see what the impact of removing it is.

[Spacecadet]

I will have a hard time getting rid of the Order clause as the result table is seen by the end receiver and they like the results to be ordered nicely. Let me just see what the impact of removing it is.

actually, checking the code again, you're just inserting at that point
you only need to order by when you finally present the results to the user.

Never rely on table storage order for ordering your results. data in RDBMS systems is not position aware... rows do not care who their neighbours are!

[Spacecadet]

and why is the same table being selected 4 times but the same columns are being used to join the tables each and every time?
The is no reason for this at all except to waste processor time and memory

FROM dbo.t_users_with_roles_and_profiles_all 

INNER JOIN
dbo.t_users_with_roles_and_profiles_all AS t_users_with_roles_and_profiles_all_1 ON 
dbo.t_users_with_roles_and_profiles_all.BNAME = t_users_with_roles_and_profiles_all_1.BNAME AND 
dbo.t_users_with_roles_and_profiles_all.ScanID = t_users_with_roles_and_profiles_all_1.ScanID 

INNER JOIN
dbo.t_users_with_roles_and_profiles_all AS t_users_with_roles_and_profiles_all_2 ON 
dbo.t_users_with_roles_and_profiles_all.BNAME = t_users_with_roles_and_profiles_all_2.BNAME AND 
dbo.t_users_with_roles_and_profiles_all.ScanID = t_users_with_roles_and_profiles_all_2.ScanID 

INNER JOIN
dbo.t_users_with_roles_and_profiles_all AS t_users_with_roles_and_profiles_all_3 ON 
t_users_with_roles_and_profiles_all_1.BNAME = t_users_with_roles_and_profiles_all_3.BNAME AND 
t_users_with_roles_and_profiles_all_1.ScanID = t_users_with_roles_and_profiles_all_3.ScanID

[ThomasSoerensen]

actually, checking the code again, you're just inserting at that point
you only need to order by when you finally present the results to the user.

Never rely on table storage order for ordering your results. data in RDBMS systems is not position aware... rows do not care who their neighbours are!

When not having the Order clause execution time dropped to 6:42.

Previously I have presented the results to the user by exporting the content of the tables either to Excel or to an Access db that the user get. The user then sorts and filters as desired.

The nature of the use of the database is that the base data is loaded, generated base tables are generated, all the queries are run and results are stored in tables (one table for one query result - ie. the result of the SCC1 query goes into a table and the result of SCC2 query goes to a different table.), summary data are generated (mostly counting rows in views ) and used in a hand written report.

So it is not really a use case where the user is in need of extracting a result and then do something and then extract a result again. It is not very interactive and iterative - more like a once and for all type of operation.

For this I don't know if another way to see the results are needed.

Of course if the user is to have the db installed and use it unsupervised by me then they might change use behaviour and something else is needed.

[MarillionFan]

When not having the Order clause execution time dropped to 6:42.

Previously I have presented the results to the user by exporting the content of the tables either to Excel or to an Access db that the user get. The user then sorts and filters as desired.

The nature of the use of the database is that the base data is loaded, generated base tables are generated, all the queries are run and results are stored in tables (one table for one query result - ie. the result of the SCC1 query goes into a table and the result of SCC2 query goes to a different table.), summary data are generated (mostly counting rows in views ) and used in a hand written report.

So it is not really a use case where the user is in need of extracting a result and then do something and then extract a result again. It is not very interactive and iterative - more like a once and for all type of operation.

For this I don't know if another way to see the results are needed.

Of course if the user is to have the db installed and use it unsupervised by me then they might change use behaviour and something else is needed.

Do you specifically have to show the detail. A refined query for the summary report would run quicker.

[ThomasSoerensen]

Do you specifically have to show the detail. A refined query for the summary report would run quicker.

The details are part of the delivery.
the user needs the details. They are needed for root cause analysis performed by the user.

[ThomasSoerensen]

and why is the same table being selected 4 times but the same columns are being used to join the tables each and every time?
The is no reason for this at all except to waste processor time and memory

FROM dbo.t_users_with_roles_and_profiles_all 

INNER JOIN
dbo.t_users_with_roles_and_profiles_all AS t_users_with_roles_and_profiles_all_1 ON 
dbo.t_users_with_roles_and_profiles_all.BNAME = t_users_with_roles_and_profiles_all_1.BNAME AND 
dbo.t_users_with_roles_and_profiles_all.ScanID = t_users_with_roles_and_profiles_all_1.ScanID 

INNER JOIN
dbo.t_users_with_roles_and_profiles_all AS t_users_with_roles_and_profiles_all_2 ON 
dbo.t_users_with_roles_and_profiles_all.BNAME = t_users_with_roles_and_profiles_all_2.BNAME AND 
dbo.t_users_with_roles_and_profiles_all.ScanID = t_users_with_roles_and_profiles_all_2.ScanID 

INNER JOIN
dbo.t_users_with_roles_and_profiles_all AS t_users_with_roles_and_profiles_all_3 ON 
t_users_with_roles_and_profiles_all_1.BNAME = t_users_with_roles_and_profiles_all_3.BNAME AND 
t_users_with_roles_and_profiles_all_1.ScanID = t_users_with_roles_and_profiles_all_3.ScanID

this query is selecting from 1 table and 4 views:
table t_users_with_roles_and_profiles_all
view v_oa_S_TCODE_SCC1
view v_oa_S_TABU_CLI_CLIIDMAINT_X
view v_oa_S_TABU_DIS_ACTVT_02_DICBERLS_SS
view v_oa_S_CLNT_IMP_ACTVT_21

these views are querying table t_roles_and_profiles_all

when working with views at least the performance would be completely horrible if I did not join the view with its own copy of t_users_with_roles_and_profiles_all

so in order to have it run each view is joined with its own copy of the t_roles_and_profiles_all table, and then you get the join 4 times in the code.

Here is a link to a picture of the relationsships.
What I need is the Users with each observation of where they have a role that has the values described in the views.

This is the reasoning behind doing the join 4 times.

I guess I could experiment and write the query with just 1 join between the table and the 4 views - but I believe I tried this a long time ago and it was a complete performance hog.