Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jun 2010
    Posts
    25

    Unanswered: Performance Tuning on views

    Hi,

    I have a query which retrieves data from 4-5 tables. To restrict the acess directly to the tables, views have been created on all these tables. These views are just select * from the tables. Two of these 5 tables have 700 Million and 8 Million rows respectively. And all the tables are having indexes.
    My issue here is that my query on views take three times more then the duration it takes to retrive data directly from tables. e.g. To retrieve 1 Miliion rows, it takes just 7-10 minutes on tables but on views it takes more than 30 minutes.
    When I check the query plan for both the options, I can see that indexes are being picked up but still the views are very slow.

    Creating indexes on views is not feasible option for me as it requires DDL changes and so much testing efforts.

    Can anybody has any solution for this?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd be willing to bet that your problem is "memory pressure" on the client and that there isn't any issue on the server.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jun 2010
    Posts
    25
    Can you please elaborate more on what you mean by memory pressure?
    I have tried this query on different servers but the outcome is same.

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    do you always do the one with the views first and then the one with the tables? I am just asking because this is a common mistake when perf tuning, because the data gets cached in the first run, and then the second run with the tables does not have to get the data from disk.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Jun 2010
    Posts
    25
    Here its the opposite case. We used to query on tables since many years and now we want to do it through views because of security reasons.

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I meant in your testing. Like back to back executions.

    Are your views really "CREATE VIEW myview AS SELECT * FROM myTable"?

    Have you ran a trace and looked at the differences? Are the execution plans identical?

    I never use SELECT * in production code, and I slap the kids in my shop that do. I wondering if resolving the column names might be an issue.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by thrasymachus View Post
    i never use select * in production code, and i slap the kids in my shop that do.
    + 1
    George
    Home | Blog

  8. #8
    Join Date
    Jun 2010
    Posts
    25
    When we did testing, we first ran on tables and then on views. So that way views should be quick.

    Yes, some views are created with select * from tablename format.

    We did check the execution plans for both and they are not same. Query on tables picks up clustered index on column A whereas the query on view picks the non clustered composite index on columns A and B. We tried to force the index but still the hints for force indexes are getting ignored.


    Quote Originally Posted by Thrasymachus View Post
    I meant in your testing. Like back to back executions.

    Are your views really "CREATE VIEW myview AS SELECT * FROM myTable"?

    Have you ran a trace and looked at the differences? Are the execution plans identical?

    I never use SELECT * in production code, and I slap the kids in my shop that do. I wondering if resolving the column names might be an issue.

  9. #9
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I googled....

    Chapter 14 - Improving SQL Server Performance

    "One of the most common performance and scalability problems are queries that return too many columns or too many rows. One query in particular that returns too many columns is the often-abused SELECT * FROM construct. Columns in the SELECT clause are also considered by the optimizer when it identifies indexes for execution plans. Using a SELECT * query not only returns unnecessary data, but it also can force clustered index scans for the query plan, regardless of the WHERE clause restrictions."
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  10. #10
    Join Date
    Jun 2010
    Posts
    25
    Thanks Thrasymachus for the instant replies.
    I tried creating view with selecting all column names instead of select *, but still no luck with the performance improvement.

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Don't name all of the columns, that is the problem. List only the columns that you will use.

    Memory pressure results from storing result sets on the client (like SSMS does). Use a more appropriate tool like sqlcmd.exe for this kind of test.

    As another item, NEVER time the first retrieval of data unless you specifically invalidate all of the caches before you start. If the full retrieval has been done recently (even for SELECT *), the plan and some or all of the data will probably be cached. This caching will result in invalid timing results.

    Constructing timing tests for large tables and queries isn't simple. There are many factors that play a part, and those can result in wild swings in performance for no apparent reason unless you understand what is happening "behind the scenes" of the query.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  12. #12
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Pat makes some good points about memory pressure here

    So let me ask you these questions then?

    What is consuming one million rows at once in a usable fashion? A web application? Something with a front end? Who or what is capable of groking that much data at once? Ever hear of server side pagination?

    Or is this some back end processing or ETL something or other?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  13. #13
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    And hold on here, no more blind chess until I see some code and some ddl
    To restrict the acess directly to the tables
    Are the views doing some filtering? Or is this just strictly a security thing. Some views are updateable you know.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  14. #14
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Supposing that the views are truly just
    Code:
    select (all fields) from tablename
    , then the views do not do anything for security that good old table permissions can not do. Well, except for add complexity, that is.

  15. #15
    Join Date
    Jun 2010
    Posts
    25
    Hey Guys,

    Thanks for providing some options to tune the query. I tried the forceseek option after the "FROM TABLE" clause and it boosted up the performance drastically. The old query which used to take around 40-50 minutes now giving back the result in 6-8 minutes with this approach.

Posting Permissions

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