SQL Server Performance

Why Clustered Used Instead of Other Indexes

Discussion in 'Performance Tuning for DBAs' started by PAMUR, Sep 9, 2005.

  1. PAMUR New Member

    Dear All,

    I have taken up tuning SQL/PL-SQL and the Database already in production. Kindly bare with me and help me.

    A particular query is troubling me a lot. This is a common join for many where clauses. Its a search query and based on the selection of search, a diff where clause is concatenated to the main query and executed.

    All where clauses are not sargable and the query is taking a long time to run. I tried to make the where clauses sargable, no matter what I tried the query is only using clustered indexes placed on the tables and not other relevent indexes present. And performing an Index Scan.

    When I remove the clustered index it is then using the relevent index. Why is it so?
    I shouldn't be doing that. And I can't use hints as its a common join fordiff where clauses.

    Thanks in advnce.
    Usha

    Usha Rani
  2. Adriaan New Member

    If you say PL-SQL, aren't you talking about Oracle?
  3. PAMUR New Member

    Sorry!, Actually am talking about SQL-Sever. <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br /><br />Usha Rani
  4. mmarovic Active Member

    it's hard to help you unless you post your query. Hopefully it is not not multipage script.
  5. PAMUR New Member

    Hi,<br />Following is the script. Have Diff. where clauses for the same. One of which is given in the query. I have the IX_Users as Clustered Index. But if I make it on clustered then it performs table scan does not use the index. If there is any other Clustered Indx ex. ON g_user_ID it uses that and not the IX_Users.<br /><br />Also an Index scan is performed on MemberDetail and Company<br /><br /><br /><br />SELECT<br /> u.u_First_Name AS FirstName,u.u_Last_Name AS LastName, u.u_First_Name + SPACE(1) + u.u_Last_Name AS FullName, <br /> Case WHEN o.i_organization_id IS Null THEN + 'Non-Member'<br /> WHEN o.i_organization_id = 0 THEN ms.u_name ELSE (o.u_name + SPACE(1) + ms.u_name) END AS Status,<br /> ch.u_Name AS Chapter,<br /> CASE WHEN ms.i_memberStatus_id IS NULL THEN 10 ELSE ms.i_memberStatus_id END i_MemberStatus_Id,<br /> CASE WHEN u.u_work_telephone_number &lt;&gt; SPACE(1) THEN u.u_work_telephone_number ELSE c.u_telephone_number END AS WorkPhone,<br /> u.u_email_address AS Email, u.g_User_id AS userID, u.u_work_city, u.u_work_region_name, u.u_work_country_name, <br /> u.u_gender, c.u_name AS Company,c.u_industry,c.i_Company_id, <br /> c.u_projected_sales, c.u_projected_fte,m.i_organization_id,o.u_name,<br /> md.b_YOD_OptOut,md.i_Chapter_id, md.u_Period_join, md.u_partner_name, md.u_college_names,md.d_birth_date,<br /> ch.u_name as u_ChapName, r2.u_DisplayName as CountryName, r1.u_displayName as RegionName <br /><br />FROM <br />dbo.users u LEFT JOIN<br />dbo.Company c ON c.g_User_id = u.g_User_id AND c.b_current = 1 LEFT JOIN <br /> dbo.MemberDetail md ON md.g_User_id = u.g_User_id LEFT JOIN<br />dbo.Chapter ch ON ch.i_Chapter_Id = md.i_Chapter_id LEFT JOIN <br /> Members m ON m.g_User_Id = u.g_User_id Left JOIN <br />db:confused:rganization o ON o.i_Organization_id = m.i_Organization_id LEFT Join<br /> MemberStatus ms ON ms.i_memberStatus_id = m.i_memberstatus_id LEFT JOIN <br />dbo.Region r1 ON r1.u_Name = u.u_work_region_name AND r1.i_Type = 2 LEFT JOIN<br /> dbo.Region r2 ON r2.u_Name = u.u_work_country_name AND r2.i_Type = 1<br />WHERE (u_Last_Name &gt;= 'A' And u_Last_Name &lt; 'B') <br /><br /><br />Execution Plan:<br /><br /><pre id="code"><font face="courier" size="2" id="code">|--Compute Scalar(DEFINE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Expr1009]=<u>.[u_First_Name]+Convert(space(1))+<u>.[u_Last_Name], [Expr1010]=If ([o].[i_Organization_id]=NULL) then 'Non-Member' else If ([o].[i_Organization_id]=0) then Convert([ms].[u_Name]) else ([o].[u_Name]+Convert(space(1<br /> |--Hash Match(Right Outer Join, HASH<img src='/community/emoticons/emotion-6.gif' alt=':(' />[r2].[u_Name])=(<u>.[u_work_country_name]), RESIDUAL<img src='/community/emoticons/emotion-6.gif' alt=':(' /><u>.[u_work_country_name]=[r2].[u_Name]))<br /> |--Clustered Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[YEO_Commerce].[dbo].[Region].[IX_Region] AS [r2]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[r2].[i_Type]=1) ORDERED FORWARD)<br /> |--Hash Match(Right Outer Join, HASH<img src='/community/emoticons/emotion-6.gif' alt=':(' />[r1].[u_Name])=(<u>.[u_work_region_name]), RESIDUAL<img src='/community/emoticons/emotion-6.gif' alt=':(' /><u>.[u_work_region_name]=[r1].[u_Name]))<br /> |--Clustered Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[YEO_Commerce].[dbo].[Region].[IX_Region] AS [r1]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[r1].[i_Type]=2) ORDERED FORWARD)<br /> |--Nested Loops(Left Outer Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[m].[i_MemberStatus_id]))<br /> |--Nested Loops(Left Outer Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[m].[i_organization_id]))<br /> | |--Hash Match(Left Outer Join, HASH<img src='/community/emoticons/emotion-6.gif' alt=':(' /><u>.[g_User_id])=([m].[g_user_id]), RESIDUAL<img src='/community/emoticons/emotion-6.gif' alt=':(' />[m].[g_user_id]=<u>.[g_User_id]))<br /> | | |--Nested Loops(Left Outer Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[md].[i_Chapter_id]))<br /> | | | |--Hash Match(Left Outer Join, HASH<img src='/community/emoticons/emotion-6.gif' alt=':(' /><u>.[g_User_id])=([md].[g_User_id]), RESIDUAL<img src='/community/emoticons/emotion-6.gif' alt=':(' />[md].[g_User_id]=<u>.[g_User_id]))<br /> | | | | |--Hash Match(Left Outer Join, HASH<img src='/community/emoticons/emotion-6.gif' alt=':(' /><u>.[g_User_id])=([c].[g_User_id]), RESIDUAL<img src='/community/emoticons/emotion-6.gif' alt=':(' /><u>.[g_User_id]=[c].[g_User_id]))<br /> | | | | | |--Clustered Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[YEO_Commerce].[dbo].[users].[IX_users] AS <u>), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' /><u>.[u_Last_Name] &gt;= 'A' AND <u>.[u_Last_Name] &lt; 'B') ORDERED FORWARD)<br /> | | | | | |--Clustered Index Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[YEO_Commerce].[dbo].[Company].[PK_Company] AS [c]), WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />Convert([c].[b_current])=1))<br /> | | | | |--Clustered Index Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[YEO_Commerce].[dbo].[MemberDetail].[PK_MemberDetail] AS [md]))<br /> | | | |--Clustered Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[YEO_Commerce].[dbo].[Chapter].[PK_Chapter] AS [ch]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[ch].[i_Chapter_id]=[md].[i_Chapter_id]) ORDERED FORWARD)<br /> | | |--Index Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[YEO_Commerce].[dbo].[Members].[IX_Members] AS [m]))<br /> | |--Clustered Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[YEO_Commerce].[dbo].[Organization].[PK_Organization] AS [o]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[o].[i_Organization_id]=[m].[i_organization_id]) ORDERED FORWARD)<br /> |--Clustered Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[YEO_Commerce].[dbo].[MemberStatus].[MemberStatus_PK] AS [ms]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[ms].[i_MemberStatus_id]=[m].[i_MemberStatus_id]) ORDERED FORWARD)<br /></font id="code"></pre id="code"><br /><br />
  6. FrankKalis Moderator

    How many rows are we talking about?
    How long does the query run?
    Will there be a change when you change


    WHERE (u_Last_Name >= 'A' And u_Last_Name < 'B')

    to


    WHERE (u_Last_Name >= 'A%' And u_Last_Name < 'B%')




    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  7. mmarovic Active Member

    How many rows are in each table involved? What is an index structure on big tables?
    Not related to your question:
    u.u_First_Name + SPACE(1) + u.u_Last_NameAS FullName,
    There is no need for column specified above since you already return first and last name. Better construct full name on a client. That will decrease amount of data passed through the network.
  8. PAMUR New Member

    Hi,

    Duration is 953 milliseconds.

    No. of rows:

    Users - 13284
    Members - 13214
    MemberDetails - 13230
    Company - 13937 -- its CompanyUsers not a Company Master -- Users to company One to Many relationship.



    Usha Rani
  9. mmarovic Active Member

    Are statistics on tables involved up-to-date?
  10. PAMUR New Member

    Hi,



    Frank!, using 'A%' doesn't make a diff. Actually i have tuned this query from multiple nested views to this and tuned where clause from using like '%SomeName%' to this. There is a significant performance benefit by it. But still its taking so many millisecs.

    Marovic!, Statistics are up_to_date. Yep there is no need of that column. I just left it because it has to be changed in code.

    And the Indexes are as follows:



    Table NameClusteredIndexNameField Names
    UsersPK_Usersu_last_name, u_first_name
    yesIX_Usersg_user_id-- (NewId())
    user8u_first_name,u_last_name,u_email_address

    CompanyyesPK_companyi_company_id
    IX_Companyg_User_id, u_industry
    IX_company_1u_name
    IX_Company_2u_title,u_description_u_url

    MembersyesPK_Membersi_pid
    IX_Membersg_user_id,i_member_status_id,i_organisation_id


    MemberDetailYesPK_MemberrDetailMemberId
    MemberDetail22MemberId,g_user_id,d_date_join,d_birth_date,i_chapter_id
    IX_Member_Deatili_memberStatus_id
    MemberDetail11111g_user_id,i_memberStatus_id,i_chapter_id
    MemberDetail14i_chapter_id,g_user_id,i_memberStatus_id
    IX_UserIdg_user_id
    IX_Birth_Dated_Birth_Date




    Usha Rani
  11. PAMUR New Member

    Hello!<br /><br />There are many where clauses passed to this main query. Several variations in search crieteria. To give u an Idea - Folowing<br /><br /><br />WHERE (LEFT(lastname,1) = 'A') AND ((select Case when DatePart(dayofyear,getDate()) &gt;= DatePart(dayofyear,d_birth_date) then DateDiff(year,d_birth_date, GetDate()) else DateDiff(year,d_birth_date, GetDate()) - 1 end) &lt; 20 ) order by lastname, firstname ASC OPTION (MAXDOP 1) <br /><br />WHERE (firstname + ' ' + lastname) like '%name%' order by lastname, firstname ASC<br /><br /><br />WHERE u_industry in (2) -- = or any will do as per frount end design<br /> and ((select Case when DatePart(dayofyear,getDate()) &gt;= DatePart(dayofyear,d_birth_date) <br />then DateDiff(year,d_birth_date, GetDate()) else DateDiff(year,d_birth_date, GetDate()) - 1 end) &gt;= 20 )<br /><br /><br />[<img src='/community/emoticons/emotion-6.gif' alt=':(' />]<br /><br />Which mostly have been tuned. But still runs for 900 to 2000 milliseconds.<br /><br /><br /><br /><br />Usha Rani
  12. FrankKalis Moderator

    None of your WHERE clause is sargable. It's no suprise that you see index scans.


    WHERE (LEFT(lastname,1) = 'A')

    can be replaced by


    WHERE lastname LIKE 'A%'

    to use an index.
    What is this DATEPART stuff supposed to do?

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  13. PAMUR New Member

    Frank!

    I told u I made my where clauses sargable these where as is previously. At present they are sargable as per BOL.


    In spite of providing proper where clause the query is so. please look at my question on top, the query has sagable where clause.


    d_birth_date <= Case When DatePart(dayofyear,d_birth_date)
    <= DatePart(dayofyear,getDate())
    Then DATEADD(yy,-20,GETDATE()) Else DATEADD(yy,-21,GETDATE()) end


    Please help.


    Usha Rani
  14. FrankKalis Moderator

    Oops, was apparently a bit absent-minded. Can you put your question for me again in easy words [?]

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  15. PAMUR New Member

    Dear Frank!

    He he heee. Don't mind. May be I misslead u.

    This is a common join for many where clauses.
    All where clauses passed to this query are made sargable at persent - (Not so previously)

    How much ever I try the query is using clustered indexes and performing index scan.
    Other indexes usefull for where clause are present but the query is not using them.
    If I remove clustered its using table scan.
    If I make the index used by where clause clustered thaen its using it and performing index seek on Users but again index scan on member detail, Members and company.

    Why is this behaving so?


    Hope am clear now. Please help.



    Usha Rani
  16. mmarovic Active Member

    1. MemberDetail - try next index configuration:
    a) pk - memberId nonclustered
    b) clustered on g_user_id
    c) non-clustered on d_date_join,d_birth_date,i_chapter_id
    d) non-clustered on i_memberStatus_id, i_chapter_id
    e) non-clustered on i_chapter_id, i_memberStatus_id
    f) non_clustered on birth_date if you don't move that column to users table.

    Test index fragmentation on data modification if you decide it helps your selects.

    2. Why is birth_date column of MemberDetail table instead of Users table? That may make sense in some cases, but I'm not sure if this is one.

    3. Try to add index on company (g_user_id, b_current). See if making this index clustered helps.
    If it helps you can test if any of other indexes starting with g_user_id can be dropped.
    If you make new index clustered follow fragmentation after putting in production.

    4. I don't have answer to your original question I'm sure about.
    However, it is known (and I don't know if it's true even after sp4) that sql 2k QO often overestimates effectivness of clustered index compared to non-clustered one.
    Occassionaly I have to put hints to speed-up queries when it happens.

    5. If you have queries based on region data you may add index on u_name and/or include that column in some other non-clustered indexes.

    Test one change at time.
  17. mmarovic Active Member

    quote:
    d_birth_date <= Case When DatePart(dayofyear,d_birth_date)
    <= DatePart(dayofyear,getDate())
    Then DATEADD(yy,-20,GETDATE()) Else DATEADD(yy,-21,GETDATE()) end
    PAMUR if you post it as an example of sargable query, I'm afraid you are wrong. That where clause is not sargable and index can't be really effectively used.
    Was that query supposed to check if person is teenager or yunger?
  18. FrankKalis Moderator

    I believe this to check the age of a person. And actually that's what I am wondering, too. It really isn't sargable.<br />Not sure, but I think this might take advantage of an index<br /><pre id="code"><font face="courier" size="2" id="code"><br />SELECT * <br /> FROM Orders<br /> WHERE CustomerID LIKE 'A%' <br /> AND <br /> ((CAST(CONVERT(CHAR(<img src='/community/emoticons/emotion-11.gif' alt='8)' />,GETDATE(), 112) AS INT)<br /> - <br /> CAST(CONVERT(CHAR(<img src='/community/emoticons/emotion-11.gif' alt='8)' />, OrderDate, 112) AS INT))/10000) &lt; 8<br /><br /><br />SELECT * <br /> FROM Orders<br /> WHERE CustomerID LIKE 'A%' <br /> AND <br /> (0+CONVERT(CHAR(<img src='/community/emoticons/emotion-11.gif' alt='8)' />,CURRENT_TIMESTAMP, 112)<br /> - <br /> CONVERT(CHAR(<img src='/community/emoticons/emotion-11.gif' alt='8)' />, OrderDate, 112))/10000 &lt; 8<br /></font id="code"></pre id="code"><br /><br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  19. FrankKalis Moderator

  20. mmarovic Active Member

    I doubt intention was simply:
    d_birth_date <= DATEADD(yy,-20,GETDATE())
    but it's too confusing to be sure.
  21. PAMUR New Member

    Very funny Frank. I will try ur suggestions.


    Have made changes to MemberDetail as suggested by Marovic. Duration has improved to 600 milliseconds, but yet index scan is taking place.


    Yep the date where clause is not yet sargable. I will try out Franks advice. Though the where clause am trying now is sargable.

    WHERE (u_Last_Name >= 'A%' And u_Last_Name < 'B%').

    Trying IndexDefragmentation and other suggestions.

    Why is index scan happening?

    As per database design I haven't designed it. But to make changes it will take time as it is in production. Have planned out for phase wise changes. But for now I have to do the best I can to improve performance.


    Have clustered index on Company for company ID. b_current is a bit field. So I guss it doesn't help much.


    d_birth_date <= DATEADD(yy,-20,GETDATE())
    The above is what exactly I tried but they wanted as given above for exact rounded age calc upto the date.

    Thank you.








    Usha Rani
  22. FrankKalis Moderator

    Mirko,
    caution! Your statement is a bit too simplified


    DECLARE @dt DATETIME
    SET @dt = '19850912 18:00:00.000'
    SELECT
    CASE
    WHEN @dt <= DATEADD(yy,-20,GETDATE())
    THEN 'Today''s my birthday'
    ELSE 'Too young'
    END
    , DATEADD(yy,-20,GETDATE())



    ------------------- ------------------------------------------------------
    Too young 1985-09-12 14:58:33.513

    (1 row(s) affected)

    In case you have a time portion in the birthday column greater than the time portion of GETDATE(), it will ignore that your birthday is today.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  23. mmarovic Active Member

    Ok, Frank, thanks for clarification. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />I was too busy (read: lazy) to figure it out myself. For the same reason, I'll wait other suggestions to be tested then see if it is really necessary to tune that specific query. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  24. PAMUR New Member

    Hi,<br /><br />I have a question related to the above join. Its a common query related to many where caluses. Now each time this quer is executed with a diff. where clause how does SQL Server react to it? I mean in terms of execution plans etc. Whant to know from experts like u.<br /><br />[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Will post results of ur suggetions ASAP.<br /><br /><br />Thank You.<br /><br />Usha Rani
  25. PAMUR New Member

    Hi,

    1) Even after following all Suggestions the query with where clause:

    WHERE (u_Last_Name >= 'A' And u_Last_Name < 'B')

    WHERE (u_Last_Name Like'A%')


    is running at duration of 600 milliseconds. First Execution 1000 milliseconds and other executions 600 avg.

    No change in Execution Plan.



    2) Age calc. where clause taking 1500 to 2000 milliseconds with Parellelism. Using OPTION (MAXDOP 1 ) its taking 1200 to 1800 milliseconds.


    INdex Scan is being performed on MemberDetail, Members and Company. For no.2 Age calc Index scan is performed on all tables.



    Usha Rani
  26. mmarovic Active Member

    quote:Now each time this quer is executed with a diff. where clause how does SQL Server react to it?
    For different queries new cost calculation will be performed and execution plan created.

    Please test effects of other suggested index changes suggested and let us know if it helped.
  27. FrankKalis Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by PAMUR</i><br />I have a question related to the above join. Its a common query related to many where caluses. Now each time this quer is executed with a diff. where clause how does SQL Server react to it? I mean in terms of execution plans etc. Whant to know from experts like u.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />To ensure that SQL Server will not reuse a previously generated execution plan, issue a DBCC FREEPROCCACHE before you execute a modified statement. SQL Server must generate a new plan to be executed. Now you can check if it made a difference. But please don't do this on a production server. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  28. PAMUR New Member

    Dear Mavoric and Frank,<br /><br />----------------------<br /><br />By Mavoric<br />Please test effects of other suggested index changes suggested and let us know if it helped.<br />----------------------------------------------<br /><br />I have tested as per ur suggestions there is no change. I have mentioned this in above posting. [<img src='/community/emoticons/emotion-6.gif' alt=':(' />]<br /><br />Frank! thank you. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />I asked that question because, I wnated to be sure that there will be multiple execution plans created - one for each Where clause. And I also wanted to clearly ask u, in that case there is no point in having a common join, as am not benefiting by it. <br /><br />Each Where clause can have its own JOIN for best performance. That means We have to Realign the join as per driving table as per each where clause. Which means when I realign the join according to each where clause then Index Seek can happen instead of Index scan. Am I right in thinking So?<br /><br />Hope am clear! [<img src='/community/emoticons/emotion-6.gif' alt=':(' />]<br /><br />Looking forward for ur valuable suggestions. <br /><br /><br /><br /> <br /><br /><br /><br />Usha Rani
  29. mmarovic Active Member

    PAMUR, from your answer it was not quite clear to me if you applied further suggestions and which queries you have tested against new configuration.


    quote:I asked that question because, I wnated to be sure that there will be multiple execution plans created - one for each Where clause. And I also wanted to clearly ask u, in that case there is no point in having a common join, as am not benefiting by it.

    Each Where clause can have its own JOIN for best performance. That means We have to Realign the join as per driving table as per each where clause. Which means when I realign the join according to each where clause then Index Seek can happen instead of Index scan. Am I right in thinking So?
    Yes, that's right, you will have equal or in most cases better peformance if you build queries case by case. I guess in most cases you don't have to join all tables that were outer joined in original query and you can also use inner instaed of outer join.
  30. PAMUR New Member

    Hi,


    OK. that will take a lot of change. Have to check for the viability.

    Now again, How do we make the Index Seek happen Insead of Index scan for the where clauses specified, and Improve performance. Nothing seem to be working. I have created all Indexes suggested by u. I have defragmented tables which need to. Still not much benefit. Still taking lots of time.

    Keeping in mind with what ever discussed above - What is the best solution?

    As a thought - even if I happen to make this common join a Stored Proc and then pass Where clause as parameter there is not going to be much benefit right?


    About making Age clause sargable - d_birth_date is a datetime field and has only date. even If I use
    where d_birth_date <= DATEADD(yy,-20,GETDATE()) the index on that field is not being used. Its using Clustered index IX_UserID.



    Thank you.

    Usha Rani
  31. mmarovic Active Member

    quote:Now again, How do we make the Index Seek happen Insead of Index scan for the where clauses specified, and Improve performance. Nothing seem to be working. I have created all Indexes suggested by u. I have defragmented tables which need to. Still not much benefit. Still taking lots of time.
    Are you talking about the first query you posted?
    Since you can't put index hints because of fix part of the query other possibility is to make index on last_name and first_name clustered, but that will cause a lot of page splitting and fragmentation during inserts.
    So, the best solution IMO is to build queries in a way you described in previous post. I've done it a few times in a past and I had no performance problems.
    Btw, how many rows in users table satisfies condition u_last_name like 'A%'?
  32. PAMUR New Member


    Question
    --------------------------
    how many rows in users table satisfies condition u_last_name like 'A%'?
    ----------------------------


    It returns 511 rows and duration is 1033 milliseconds Executing after DBCC DropCleanBuffers from Query analyser of Development Server.

    Usha Rani
  33. mmarovic Active Member

    500 out of 13000 should be restriction significant enough non-clustered index to be selected. Have you tried to force maxdop=1 on that query?
  34. PAMUR New Member

    Yes!, I tried and still the same. As usual it performs a seek on Users table if created clustered index on lastname and firstname but member detail, Member and Company has an index scan.

    If I remove clustered index and make g_user_id clustered it uses this index but not non clustered index on lastnaem and firstname

    If I don't at all have a clustered index(no clustered index on users) then it performs a Table scan on users and index scan on other large tables involved.

    This is the confusion actually.


    I ran a simple query on Users Table
    Select FieldLIst
    From Users
    WHERE u_Last_Name LIKE 'A%'

    or


    WHERE (u_Last_Name >= 'A' And u_Last_Name < 'B')

    *with out any clustered index and have relevent indexes any way -- Performed Table Scan
    *With clustered on g_user_id performing an Indx scan
    *with clustered on lastname and firstname an Index seek has been performed.

    Why?

    Only solution seems to be to force index. That's Ridiculous!




    Usha Rani
  35. mmarovic Active Member

    If I were you I would start re-writing solution as already mentioned.
    It is not too complex to include just necessary tables and appropriate join conditions.
  36. PAMUR New Member

    Ok. I take ur suggestion and implement.

    On a simple query too this does't work?

    Select FieldLIst
    From Users
    WHERE u_Last_Name LIKE 'A%'

    What should I depict from this?




    Usha Rani
  37. FrankKalis Moderator

  38. PAMUR New Member

    Yes! I have. That too thorough. No additional Indexes or Indexed views suggested.

    Usha Rani
  39. mmarovic Active Member

    quote:Ok. I take ur suggestion and implement.

    On a simple query too this does't work?
    In that case you are able to put a hint when you build a query.

    About why it doesn't work on simple query, I already admited that I don't know for sure. It uderestimates non-clustered index effectivness, I guess on wide index like last_name + first_name even more. When thing like that happens I check if statistics are up-to-date and if so I just put a proper index hint.
  40. PAMUR New Member

    Dear Frank and Mavoric,

    Thank You for ur Help and Patience. Thanks a looooooooooooooot




    Usha Rani
  41. FrankKalis Moderator

    Have we helped? [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  42. PAMUR New Member

    That's a Million Dollar Question. Any way yes u have cleared my confusion.<br />Thanks. [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />Usha Rani

Share This Page