SQL Server Performance

query performance tuning

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by ashwin, Dec 18, 2007.

  1. ashwin New Member

    Hi martin,
    Structure of TestCase table

    Column Name Data Type Allow Nulls
    TestCaseID numeric(18, 0) No (primary key) (clustered, unique, primary key located on PRIMARY) TestCaseID, Flagship
    StaffID int Checked
    Title nvarchar(1000) Yes
    Objective varchar(3000) Yes
    Assumptions varchar(4000) Yes
    Steps ntext Yes
    ExpectedResults ntext Yes
    ProdSuiteId bigint Yes
    Product nvarchar(50) Yes
    DBType nvarchar(25) Yes
    Component nvarchar(3000) Yes
    Type nvarchar(50) Yes
    OperatingSys nvarchar(50) Yes
    Server nvarchar(50) Yes
    Status nvarchar(50) Yes
    Priority nvarchar(50) Yes
    Author nvarchar(50) Yes
    CreationDate nvarchar(50) Yes
    AssignedTo nvarchar(50) Yes
    Flagship_A nvarchar(5) Yes
    Flagship nvarchar(50) No (primary key)
    Webserver nvarchar(20) Yes
    Webbrowser nvarchar(20) Yes
    AppServer nvarchar(20) Yes
    Attachment nvarchar(20) Yes
    Feature nvarchar(3000) Yes
    Modules nvarchar(3000) Yes
    SmokeTest nvarchar(50) Yes
    Protocol nvarchar(50) Yes
    Configuration nvarchar(50) Yes
    Objects nvarchar(50) Yes
    ProdReqID nvarchar(50) Yes
    UserName char(10) Yes
    Result char(14) Yes
    HostName nvarchar(30) Yes
    automation varchar(70) Yes
    Beta nvarchar(10) Yes
    GA nvarchar(10) Yes
    RequirementId nvarchar(3000) Yes
    IsDeleted int Yes

    Data space : 648.273 MB
    Index space : 3.531 MB
    Row Count : 362114

    Structure of TestResult table

    Column Name Data Type Allow Nulls
    Flagship nvarchar(9) Yes
    TestCaseID numeric(10, 0) No (nonclustered located on PRIMARY) TestCaseID
    TestResultId numeric(18, 0) No (primary key) (clustered, unique, primary key located on PRIMARY) TestResultId
    TestedBy nvarchar(50) Yes
    Result nvarchar(50) Yes
    UserName nvarchar(50) Yes
    BuildTested nvarchar(25) Yes
    Defect nvarchar(50) Yes
    Comments ntext Yes
    TestEnv nvarchar(50) Yes
    CreationDate nvarchar(50) Yes
    Feature nvarchar(3000) Yes
    Product nvarchar(50) Yes
    Protocol nvarchar(50) Yes
    StartTime nvarchar(50) Yes
    EndTime nvarchar(50) Yes
    IsDeleted int No

    Data space : 1,280.758 MB
    Index space : 64.359 MB
    Row Count : 2952275

    there is only one index on TestCaseID column in TestResult table, apart from primary key indices. There are no
    indexes on 'automation', 'type', 'configuration'. Please suggest what sort of indexing should i use and on which
  2. martins New Member

    Build an index on each one of those 4 fields and test it to see if there is any difference in performance. Also look at your execution plan to see if the indexes are begin used.
  3. ashwin New Member

    Hi martins,
    But I think, indexes to work properly you should have distinct values for columns in the database. Index seek suffers due to null values.Also i went through the execution plan for the query and found that 'scanning clustered indexes' involves more cost(72%). Is there any substitute for nvl() function of oracle in sql server 2005. Please suggest.
  4. martins New Member

    Unfortunately I do not know Oracle at all, so I would not know if there is a similar function in SQL2k5. Sure, the index seek will probably suffer with all the null values..but it might still be better than scanning the clustered index. You won't know if you don't give it a try...
  5. ashwin New Member

    Hi martin,
    I created an index on one of the four columns in Test1 table, but the performance got detoriated further. Now it took 5:00 mins, which took 1:54 mins
    previously. Please suggest.
  6. martins New Member

    Ashwin, it is very difficult for me to give you an exact answer that will immediately solve your performance issues. You will have to play around with different options, check you execution plans and test them thoroughly. A lot comes down to your specific environment, hardware, software, data structure etc.
    If I were you, I would try different things and check the execution plan after each attempt. Create 4 indexes first (on the 4 fields) and see what your performance is like. After that you can maybe try to create one index covering all 4 of those fields...and again test and check your execution plans. If all of this proves to be insufficient, how about creating different stored procs based on the availability of the search criteria supplied...or create one proc with different queries based on the available search parameters.
    There are many different options you can explore, but you have to try them one by one and see what works in your environment.
  7. satya Moderator

  8. ashwin New Member

    Thanks Satya for your suggestion,
    Replacing the coalesce() function instead if isnull() definately improved performance. The query took only 1:54 mins.
    I created an index on one of the column and the performance detoriated because it took 5mins. But since i have dropped
    the index, it should have taken 1:54 mins again, but it is contradicting the behaviour and takes 3:38 mins. I am unable to
    understand the reason behind it. Is it still referring to the previously created index, if so please suggest how to remove permenantly.
    thanks for your help!
  9. satya Moderator

    When you have created that index, have you rebuild the index & performed update statistics on the table?
  10. ashwin New Member

    I created the index but didn t rebuild the index and performed any statistics on the table. Can you please tell how to do that and
    why should we be doing that?
  11. satya Moderator

    Refer to the books online for DBCC DBREINDEX(if using SQL 2000) to rebuild index or use ALTER INDEX to rebuild in order to update the statistics with this new index for a better performance.
  12. ashwin New Member

    For doing that do I need to create index again and rebuild it, since I have already dropped the index I created previously. Please suggest.

  13. satya Moderator

    Yes and also ensure to update statistics if this is not a clustered index.
  14. ashwin New Member

    It is not a clustered index for sure, but how to update statistics for the table.
  15. satya Moderator

  16. ashwin New Member

    Indexes are not working in this case, so i would modify the queries depending on the user input
    for example can i use something like this

    from Test1 a , Test2 b
    isnull(a.user,'') = isnull('ashwin',a.user)
    and isnull(a.city,'') = isnull('california',a.city)
    and isnull(b.color,'') = isnull(@color,b.color)
    if @brandname is not null
    and isnull(b.brandname,'') = isnull(@brandname,b.brandname)
    if @tag is not null
    and isnull(b.tag,'') = isnull(@tag,b.tag)
    if @price is not null
    and isnull(b.price,'') = isnull(@price,b.price)

    this query exists in a stored procedure, which gets called from a jsp page. Again this query is used as
    subquery in the SP where I have to do the same modifications. Will that work? Please suggest an appropriate
    url if you know.

    Also is there any way we can reclaim(delete) the space allocated for previously created "index".

  17. Adriaan New Member

    You really need to start learning the basic SQL syntax - you are confusing very basic functionality.
    You are trying to use IF expressions 'inside' a single query statement. Note that the actual SELECT query ends at the first IF, because IF is a control-of-flow keyword that can only come between successive queries. Your procedure is doing three free-floating IF checks in succession after the query, and in the end there is no action to be undertaken under those conditions. The syntax you seem to need here would be CASE.
    You do not seem to understand ISNULL: it returns the second parameter only if the first parameter is null, else it always returns the first parameter. So your expression ISNULL('ashwin',a.user) always returns the string ashwin.
    Then you are comparing this string to ISNULL(a.user, ''). Note that when you are comparing any expression that has a null value, then the comparison always returns false (except if you use the IS NULL or IS NOT NULL syntax). So there is no point in replacing the null with an empty string, because the empty string is different from ashwin too.
    You should not use all those functions in your WHERE statement - they are forcing your query to ignore any indexes available for those columns, and do a table scan.
  18. ashwin New Member

    While going through the execution plans for the query, I observed that Index scan for the clustered index scan on primary key of one of the table involves
    lot of cost i.e 42%. Does this add up to the performance degradation? Please suggest.
  19. ashwin New Member

    from Test1 a , Test2 b
    isnull(a.user,'') = isnull('ashwin',a.user)
    and isnull(a.city,'') = isnull('california',a.city)
    and isnull(b.color,'') = isnull(@color,b.color)

    I have replaced the above earlier query with

    from Test1 a , Test2 b
    a.user = case
    when @user is null then a.user
    else @user
    and a.city = case
    when @city is null then a.city
    else @city
    and b.color = case
    when @color is null then b.color
    else @color
    you can see i have removed the 'isnull()' and 'coalesce()' function, but still the performance has not improved much. Please suggest.
    thanks in advance!!
  20. Adriaan New Member

    Your tables probably do not have indexes on the User, City and/or Color columns.
    On top of that, you are in no way joining your two tables, so SQL Server will repeat all rows from table Test2 for each row found in table Test1. This is known as a Cartesian product, which can be helpful in some circumstances, but most of the time the results are meaningless.
    Like I said, you need to learn basic SQL.

Share This Page