Professional Web Applications Themes

Performance question - Microsoft SQL / MS SQL Server

Assuming you are not using indexed views, you might think about a stored procedure if performance is your concern. HTH -- Ray Higdon MCSE, MCDBA, CCNA --- "Tomer" <com> wrote in message news:phx.gbl...  scratch, ...

  1. #1

    Default Re: Performance question

    Assuming you are not using indexed views, you might think about a stored
    procedure if performance is your concern.

    HTH

    --
    Ray Higdon MCSE, MCDBA, CCNA
    ---
    "Tomer" <com> wrote in message
    news:phx.gbl... 
    scratch, 


    Ray Guest

  2. #2

    Default Performance question

    Hi all,

    Please help me with the following performance issue:

    I have two views; Each of them combines about 5 large tables and returns
    about 15 columns. Each view is already used by some part of my system.

    I need to retrieve some of this data for a new component.

    I wonder whether it would be acceptable design to build new view that
    combine these two views, retrieving only 5 columns from the 30 columns
    available.

    From Performance aspect, will I do better if I create the view from scratch,
    or SQL is smart enough to not ask for redundant tables and data?

    Thanks in advanced,
    Tomer



    Tomer Guest

  3. #3

    Default Re: Performance question

    I'm not using indexed views.
    Anyhow, I will use the view from a stored procedure.

    Does your recommendation changes?

    "Ray Higdon" <com> wrote in message
    news:##phx.gbl... 
    > scratch, 
    >
    >[/ref]


    Tomer Guest

  4. #4

    Default Re: Performance question

    Hi Tomer,

    Stored procedures are faster than ad-hoc queries because their execution
    plans are stored in the procedure cache, thus requiring less I/O. It is
    possible for stored procedures to run slower than ad-hoc queries if the
    search value cannot be determined until run time. This can happen as when
    using following type of stored procedure creation:
    create procedure how_fast parm int as
    begin
    select x from y where z=parm
    end

    In general, frequently used simple operations should be contained in stored
    procedures.

    For more information regarding SQL Server Performance, please refer to the
    following articles
    110352 INF: Optimizing Microsoft SQL Server Performance
    http://support.microsoft.com/?id=110352

    Hope it helps.

    Regards,

    Michael Shao
    Microsoft Online Partner Support
    Get Secure! - www.microsoft.com/security
    This posting is provided "as is" with no warranties and confers no rights.

    Michael Guest

  5. #5

    Default Performance question

    I've built a query, in the form of a view, that is quite complex. It is full
    of sub-queries and CASE statements. It takes a while to run so I know there
    are ways I can make it execute more efficiently. So, I have a couple of
    questions.

    1. Is there a performance advantage to defining relationships between
    tables, i.e. diagrams, as opposed to not defining relationships? JOINS in
    the query certainly create a relationship, but does creating a diagram to
    define a relationship make the query run any faster?

    2. Would moving the subqueries into their own views make this larger query
    run any faster? It certainly would make the main query "look" neater because
    then it would be made up of a list of fields in the SELECT clause with all
    the tables and views JOIN'd together in the FROM clause, instead of having
    all these complex CASE statements and such. I wonder if a subquery is pd
    and executed faster than having to access a separate view.

    I use the Query yzer to build all these queries and then save them to
    the database as views. Editing the main query can be a little bersome,
    but only because of the way it looks, with all the subqueries and CASE
    statements. I'd like to create all the separate views out of the subqueries
    for the sake of maintenance, but I don't want to do it at the expense of
    execution speed.

    Any thoughts and/or suggestions would be greatly appreciated.

    Rob

    P.S. I'd post the query, but it is just too large and would, in my opinion,
    just confuse the issue.


    Robert Guest

Similar Threads

  1. Performance Question
    By Mark A in forum Coldfusion - Advanced Techniques
    Replies: 7
    Last Post: March 14th, 08:08 PM
  2. UDR Performance Question
    By Sean in forum Informix
    Replies: 9
    Last Post: March 2nd, 06:59 PM
  3. Question about performance
    By rashadrivera webforumsuser@macromedia.com in forum Macromedia Flash Actionscript
    Replies: 0
    Last Post: January 30th, 10:58 AM
  4. performance question...
    By HomeBrewer webforumsuser@macromedia.com in forum Macromedia Director Lingo
    Replies: 2
    Last Post: September 25th, 06:02 PM
  5. A performance question
    By hairybobby webforumsuser@macromedia.com in forum Macromedia Director Lingo
    Replies: 1
    Last Post: August 5th, 11:06 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139