View or Stored procedure

[rsingh]

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.

From what you say about these views, I believe they are unnecessary. At the moment you have nested views based upon tables which are then joined back onto. You may be better off refactoring these into the main SQL statement. Would you be able to post a query plan for the query? Could you also post the DDL for these views please?

Also try updating the stats for the tables and indexes before running your proc. As you have had a large data load, your indexes will be fragmented and your statistics likely to be out of date resulting in bad query plans. You may also need to recompile the procedure so that you do not use an old unrepresentative query plan.

[rsingh]

Are the following fields indexed in the underlying tables?
-ScanID
-CompositeRole
-BName

[ThomasSoerensen]

From what you say about these views, I believe they are unnecessary. At the moment you have nested views based upon tables which are then joined back onto. You may be better off refactoring these into the main SQL statement. Would you be able to post a query plan for the query? Could you also post the DDL for these views please?

Also try updating the stats for the tables and indexes before running your proc. As you have had a large data load, your indexes will be fragmented and your statistics likely to be out of date resulting in bad query plans. You may also need to recompile the procedure so that you do not use an old unrepresentative query plan.

Let me see if I read you correctly.

You believe the views are unnecessary or the multiple joins are unnecessary?

The views are not querying the table that is joined. The views query another table called t_roles_and_users_all.

I am not sure how to post a query plan as it spans several screen widths.

But it seems it is the multiple joins take a majority of the processing power it says "clustered index scan - cost 23%"

The views are here:
v_oa_S_TCODE_SCC1:
SELECT TOP (100) PERCENT CompositeRole, Role, OBJECT, AUTH, FIELD, LOW, HIGH, ScanID, PROFILE, RowSource
FROM dbo.t_roles_and_profiles_all
WHERE (OBJECT = 'S_TCODE') AND (FIELD = 'TCD') AND (LOW IN ('*', 'SCC1')) OR
(OBJECT = 'S_TCODE') AND (FIELD = 'TCD') AND (LOW < 'SCC1') AND (HIGH >= 'SCC1')
ORDER BY CompositeRole, Role

v_oa_S_TABU_CLI_CLIIDMAINT_X:
SELECT TOP (100) PERCENT CompositeRole, Role, OBJECT, AUTH, FIELD, LOW, HIGH, ScanID, PROFILE, RowSource
FROM dbo.t_roles_and_profiles_all
WHERE (OBJECT = 'S_TABU_CLI') AND (FIELD = 'CLIIDMAINT') AND (LOW IN ('X', '*'))
ORDER BY CompositeRole, Role

v_oa_S_TABU_DIS_ACTVT-02_DICLBERS_SS:
SELECT TOP (100) PERCENT dbo.t_roles_and_profiles_all.CompositeRole, dbo.t_roles_and_profiles_all.Role, dbo.t_roles_and_profiles_all.OBJECT,
dbo.t_roles_and_profiles_all.AUTH, dbo.t_roles_and_profiles_all.FIELD, dbo.t_roles_and_profiles_all.LOW, dbo.t_roles_and_profiles_all.HIGH,
dbo.t_roles_and_profiles_all.ScanID, dbo.sapUST12.FIELD AS FIELD2, dbo.sapUST12.VON, dbo.sapUST12.BIS, dbo.t_roles_and_profiles_all.PROFILE,
dbo.t_roles_and_profiles_all.RowSource
FROM dbo.t_roles_and_profiles_all INNER JOIN
dbo.sapUST12 ON dbo.t_roles_and_profiles_all.OBJECT = dbo.sapUST12.OBJCT AND dbo.t_roles_and_profiles_all.AUTH = dbo.sapUST12.AUTH
WHERE (dbo.t_roles_and_profiles_all.OBJECT = 'S_TABU_DIS') AND (dbo.t_roles_and_profiles_all.FIELD = 'ACTVT') AND (dbo.t_roles_and_profiles_all.LOW IN ('*', '02')) AND
(dbo.sapUST12.FIELD = 'DICBERCLS') AND (dbo.sapUST12.VON IN ('*', 'SS')) OR
(dbo.t_roles_and_profiles_all.OBJECT = 'S_TABU_DIS') AND (dbo.t_roles_and_profiles_all.FIELD = 'ACTVT') AND (dbo.t_roles_and_profiles_all.LOW < '02') AND
(dbo.sapUST12.FIELD = 'DICBERCLS') AND (dbo.sapUST12.VON < 'SS') AND (dbo.t_roles_and_profiles_all.HIGH >= '02') AND (dbo.sapUST12.BIS >= 'SS')
ORDER BY dbo.t_roles_and_profiles_all.CompositeRole, dbo.t_roles_and_profiles_all.Role

v_oa_S_CLNY_IMP_ACTVT_21:
SELECT TOP (100) PERCENT CompositeRole, Role, OBJECT, AUTH, FIELD, LOW, HIGH, ScanID, PROFILE, RowSource
FROM dbo.t_roles_and_profiles_all
WHERE (OBJECT = 'S_CLNT_IMP') AND (FIELD = 'ACTVT') AND (LOW IN ('*', '21')) OR
(OBJECT = 'S_CLNT_IMP') AND (FIELD = 'ACTVT') AND (LOW < '21') AND (HIGH >= '21')
ORDER BY CompositeRole, Role


I am not sure how I would go about updating the statistics. Could you describe or link to a description.

[rsingh]

Excellent - those definitions give us something to work with.

To save the query plan,right click on the graphical plan, select save as. Save as a .sqlplan file. It's a form of xml. Attach that file to a post. Anyone with management studio can open that .sqlplan file and it will be displayed as a graphical plan with all information in it.

Here's a link that explains the how and why of updating statistics.

[rsingh]

Are you using linked Access tables as linked servers for t_roles_and_profiles_all? The reason I ask is that I see * being used as a wildcard. SQLServer uses % and therefore I would be dubious as to results of your queries.

[rsingh]

Another question: What are datatypes for High and Low?

[Spacecadet]

Are you using linked Access tables as linked servers for t_roles_and_profiles_all? The reason I ask is that I see * being used as a wildcard. SQLServer uses % and therefore I would be dubious as to results of your queries.

the * isn't being used as a wild card for the SQL query - its an actual value in the table

LOW IN ('*', 'SCC1')

all the views have top (100) percent and order by clauses, which for large data sets is going to slow the queries down.

[ThomasSoerensen]

Are you using linked Access tables as linked servers for t_roles_and_profiles_all? The reason I ask is that I see * being used as a wildcard. SQLServer uses % and therefore I would be dubious as to results of your queries.

* is a specific value I am looking for in the data.

[ThomasSoerensen]

Another question: What are datatypes for High and Low?

High and Low are varchar (50)
I believe they have to be this ineficient datatype because the values in the dataset can be anything from null to a file-directory path or integers

[ThomasSoerensen]

the * isn't being used as a wild card for the SQL query - its an actual value in the table



all the views have top (100) percent and order by clauses, which for large data sets is going to slow the queries down.

When re-writing the queries into the stored procedures I will remove this.
Thank you for that.
It is just that when designing the query in the graphical view designer the auto-generated code includes the TOP 100 Percent part.