Post reply

Best performance of database and queries

  • November 1, 2014 at 4:33 pm

    #312221

    Hello,

    Say I have a table called orders which has about 40 columns, and could potentially grow to 100k + I have a primary key on that to query this table what would be the best approach could I use a simple Select col1,col2 etc or would it be better to use an indexed view? And call the view from my application.

    Reason I ask is because a lot of people on other forurms have stated using views do not increase performance and as I'm at the early stage of development with this application I want to build it with performance in mind.

    So I thought ill come here and ask the DBA's

    Thanks

  • November 1, 2014 at 5:21 pm

    #1756184

    Not enough information to really give you an answer. What are you trying to accomplish, for one.

  • November 1, 2014 at 5:25 pm

    #1756186

    Shortly:

    -View is just a sql statement.

    -Indexed views have advantages and disadvantages.

    -If the sql statement that you'll put in the view's definition does join multiple tables and includes aggregate function(s) than with indexed view you'll have performance improvements, otherwise no.

    -Indexed view means additional maintenance of another table. It must have a clustered index.

    My opinion for your case is that you shouldn't create a view for your table.

    Igor Micev,My blog: www.igormicev.com

  • November 2, 2014 at 12:50 pm

    #1756214

    With no information, I'm with Lynn. It's impossible to say what you need to do to improve performance.

    But, whatever other forum you were on is partly correct. A view, a regular view, does not improve performance at all because it's just a query (as was mentioned). This doesn't improve or hurt performance. A materialized view, and indexed view, can improve performance because it can change the shape of the data being stored to support a different approach to accessing the data. But, there are trade-offs there since there is no overhead on data modifications, so you want to be very careful when creating these and only create them for the right reasons.

    For most performance problems, just having the correct indexes in place with up to date statistics are enough. Don't start looking at more obscure solutions like materialized views until you've exhausted all the standard tuning mechanisms.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • November 2, 2014 at 4:31 pm

    #1756238

    .Netter (11/1/2014)

    Hello,

    Say I have a table called orders which has about 40 columns, and could potentially grow to 100k + I have a primary key on that to query this table what would be the best approach could I use a simple Select col1,col2 etc or would it be better to use an indexed view? And call the view from my application.

    Reason I ask is because a lot of people on other forurms have stated using views do not increase performance and as I'm at the early stage of development with this application I want to build it with performance in mind.

    So I thought ill come here and ask the DBA's

    Thanks

    To start with, you need at least 2 tables... Order and OrderDetail.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 5 posts - 1 through 4 (of 4 total)

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