Results 1 to 4 of 4

Thread: Fastest way

  1. #1
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Angry Unanswered: Fastest way

    I've got a complex view (lots of inner and outer joins from several tables) in my SQL Server 2000 Database.

    In my VB program, I've connected a grid to this view via an Ado Control
    but the performances are not so great (even bad).

    How can I boost the execution of this view ?


    Thx

  2. #2
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311

    Re: Fastest way

    I would look at those joins and see if there is any non-indexed scan first. You can also run the wizard to see if there is any room for improvement. Good luck!

  3. #3
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: Fastest way

    Hi again Joe from Texas (is it hot there at this moment ?)

    I've put indexes on all the needed columns
    but I've seen somewhere that we can create indexes on views

    would that help ???

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If you look up "Creating an Indexed View" in Books Online, you'll see that there are a lot of restrictions on what types of views can be indexed. In particular, you can't index your view if it contains any of these elements:
    Subqueries
    Outer joins
    Self joins
    DISTINCT keyword
    SUM functions that reference nullable expressions
    .
    .
    . etc...

    If your view is as complex as you say it is, there is a good chance that it cannot be indexed. Also, when you index a view SQL Server creates a permanent virtual table with the results of that view, and which is then updated any time any of the values in the underlying tables are updates. This can slow down other processing.

    I think you would be better off rewriting your statement as a stored procedure rather than as a view. Stored procedures are more efficient than views.

    Also, post your code and we may be able to find ways to make it more efficient.

    blindman

Posting Permissions

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