Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Location
    Newcastle, Australia
    Posts
    51

    Unanswered: Unpredictable view performance. Do i need to use hints?

    Hi all

    I have a database view that seems to perform better the larger the resultset returned. Very strange. I can only guess that the query optimiser is making bad decisions when a query that will return a small number of rows is executed

    For example

    select * from my_view where my_id > 1000 and my_id < 2000 takes 3 seconds and returns about 1000 rows

    select * from my_view where my_id > 1000 and my_id < 1500 takes 10 seconds and returns about 500 rows

    select * from my_view where my_id > 1000 and my_id < 1100 takes 60 seconds and returns about 100 rows

    select * from my_view where my_id > 1000 and my_id < 1050 takes 10 seconds and returns about 50 rows

    select * from my_view where my_id > 1000 and my_id < 1010 takes 3 seconds and returns about 10 rows

    Using SQL Analyser, I have looked at the info on the "estimated execution plan" and the plans are different.

    Can anyone provide any tips as to how I would identify what is wrong and perhaps force sql server to use a specific execution plan?

    I'm fairly new to the "option" SQL clause so any info would be great.

    Matt

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    Are the views indexed ?

  3. #3
    Join Date
    Oct 2003
    Location
    Newcastle, Australia
    Posts
    51
    Originally posted by rnealejr
    Are the views indexed ?
    Hi

    The view contains subselects so I don't think it can be indexed.

    I've managed to get more consistent performance from the view by slightly modifying the sql.

    Does anyone out there know of any URLs that discuss how to interpret execution plans?

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    Check out this webcast from ms:

    webcast

  5. #5
    Join Date
    Oct 2003
    Location
    Newcastle, Australia
    Posts
    51
    Originally posted by rnealejr
    Check out this webcast from ms:

    webcast
    Perfect

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •