Post reply

Using Triggers

  • June 21, 2005 at 2:22 am

    #566949

    To bring things back on topic...

    1. As Remi said, check to see if you have indices on all of your foreign keys that are referenced in the views

    2. Have you considered indexed views?  Perhaps some of these 50 views could be materialised somehow - depends on what you are doing in them.  If you are doing strange things with case statements then you may not be able to materialize them - Read BOL for more info

    3. You say that it is sometimes timing out and other times not...  Could you possibly run your query with READ UNCOMMITTED isolation level - because it is running so long it may be blocking others or others will be more likely to block it.

    I managed to get something that referenced probably 10 different UDFs & views to run about 100 times faster by ensuring I had indices and made one view indexed.  I also cached some of the data into another table.

    Your trigger idea may not be so far off the mark - but it might be nicer to put the logic into a stored proc.  Eg, if you are updating some table which will influence the value of a field in a row in another table (some sort of sum for example), then have the SP perform the update in the original table AND the modification in the other - at least then you can see the logic in the one place.  Have used this on a credit card processing thing once that kept some detailed totals grouped by different fields.

  • June 21, 2005 at 2:48 am

    #566964

    Ok guys, this might seem like a strange question but how about creating an SP with a lot more logic that retrieves data from a smaller subset of tables in multiple steps, the subsets being dependant on what needs to be seen in each scenario.

    Your first step may evaluate exactly what data is required and therefore which of the various tables/views you need to access. After each step you can add the data retrieved to a table variable and at the end just return the contents of the table variable.

    I know this goes somewhat against the concept of using set based methods as much as possible by breaking it down into procedural steps, but in a case like this it might just be worth it.

    Additionally it might make it easier to understand and therefore support the code in the longer term.

Viewing 2 posts - 31 through 31 (of 31 total)

You must be logged in to reply to this topic. Login to reply