View or Stored procedure

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

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.

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

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.

[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 might want to index the columns you filter on invite views e.g object, field, low and high, if they are reasonably selective.

thanks

[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