Reply to: View or Stored procedure

[ThomasSoerensen]

You might want to index the columns you filter on invite views e.g object, field, low and high, if they are reasonably selective.

winning tip

moved execution time to 53 seconds

[ThomasSoerensen]

You might want to index the columns you filter on invite views e.g object, field, low and high, if they are reasonably selective.

thanks

[doodab]

You might want to index the columns you filter on invite views e.g object, field, low and high, if they are reasonably selective.

[ThomasSoerensen]

You're right. We are also going to get implicit conversions caused by the AND (LOW < '21') AND (HIGH >= '21') which won't help.

With the info so far and without seeing the query plan, my first instinct is to dump the contents of the views filtered by Scan ID into temporary tables. Then add indexes onto all columns used in joins. Then I would use those temp tables in place of the views in the main query filtered on ScanID. Oh and I'd lose the Top 100 when querying against the temp tables.

this is a good idea. But one that requires me to create a few hundred tables hmmm.

[ThomasSoerensen]

So all in all I have concluded that stored procedures is the way to go.

That was my main question.

But thank you also for the increased performance.

I am sure I will benefit in the next days when I have to generate a report on a very large dataset. I estimate the t_roles_and_profiles_all table to be in the tens of millions of records

[ThomasSoerensen]

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.

I cannot see how I can attach a file to a forum post.
But I will read the link . thank you.

[rsingh]

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.

You're right. We are also going to get implicit conversions caused by the AND (LOW < '21') AND (HIGH >= '21') which won't help.

With the info so far and without seeing the query plan, my first instinct is to dump the contents of the views filtered by Scan ID into temporary tables. Then add indexes onto all columns used in joins. Then I would use those temp tables in place of the views in the main query filtered on ScanID. Oh and I'd lose the Top 100 when querying against the temp tables.

[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.

[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]

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.

[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.

[rsingh]

Another question: What are datatypes for High and Low?

[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]

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.

[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.