SQL Server Performance

Why speed is slow with these indexes

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by waqar, Jun 22, 2007.

  1. waqar Member

    Hi,<br /><br />I have created 2 similar tables, both have 9899999 records each. <br /><br />Structure of tables is as shown below<br /><br /><b>TABLE 1</b><br /><pre id="code"><font face="courier" size="2" id="code"><br /><font color="blue">STRUCTURE<br />---------</font id="blue"><br />CREATE TABLE [dbo].[xID](<br />[c0] [int] IDENTITY(1,1) NOT NULL,<br />[c1] [char](13) NULL,<br />[c2] [char](16) NULL,<br />[c3] [varchar](50) NULL<br />) ON [PRIMARY]<br /><font color="blue"><br />INDEX<br />-----</font id="blue"><br />CREATE NONCLUSTERED INDEX [xID_1] ON [dbo].[xID] <br />(<br />[c1] DESC,<br />[c2] DESC<br />)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]<br /></font id="code"></pre id="code"><br /><br /><b>TABLE 2</b><br /><pre id="code"><font face="courier" size="2" id="code"><br /><font color="blue">STRUCTURE<br />---------</font id="blue"><br />CREATE TABLE [dbo].[xID_1](<br />[c0] [int] IDENTITY(1,1) NOT NULL,<br />[c1] [char](13) NULL,<br />[c2] [char](16) NULL,<br />[c3] [varchar](50) NULL<br />) ON [PRIMARY]<br /><br /><font color="blue"><br />INDEX<br />-----</font id="blue"><br />CREATE NONCLUSTERED INDEX [IDX1] ON [dbo].[xID_1] <br />(<br />[c1] ASC<br />)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]<br /><br />CREATE NONCLUSTERED INDEX [IDX2] ON [dbo].[xID_1] <br />(<br />[c2] ASC<br />)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]<br /></font id="code"></pre id="code"><br /><br />I create a table with 8999 records to check SELECT speed for both table<br /><br /><b>TABLE 3</b><br /><pre id="code"><font face="courier" size="2" id="code"><br />CREATE TABLE [dbo].[xID_Text](<br />[c1] [char](13) NULL,<br />[c2] [char](16) NULL<br />) ON [PRIMARY]<br /></font id="code"></pre id="code"><br /><br />Now i am running follow queries and <br /><br /><pre id="code"><font face="courier" size="2" id="code"><br /><b>SECTION A</b><br />SELECT<br />xID.*<br />FROM<br />xID<br />INNER JOIN<br />xID_Text<br />ON<br />xID.c1=xID_Text.c1<br />AND<br />xID.c2=xID_Text.c2<br /><br /><b>SECTION B</b><br />SELECT<br />xID.*<br />FROM<br />xID_1 xID<br />INNER JOIN<br />xID_Text<br />ON<br />xID.c1=xID_Text.c1<br />AND<br />xID.c2=xID_Text.c2<br /></font id="code"></pre id="code"><br /><br />Here is comparison<br /><img src='http://img396.imageshack.us/img396/7933/sql1ao1.gif' border='0' /><br /><br />Here is execution plan for both SECTIONS<br /><b>SECTION A</b><br /><img src='http://img104.imageshack.us/img104/9411/sql2zf6.gif' border='0' /><br /><br /><b>SECTION B</b><br /><img src='http://img240.imageshack.us/img240/9871/sql3xa1.gif' border='0' /><br /><br />My reason for this exercise is to check if i can create individual index rather create composite.<br />On my production server i have a table holding about 50 million records. Due to different criteria i need to put multiple composite non clustered indexes (and some of the indexes have same columns repeating).<br />Any suggestion?<br /><br />________________________________________________<br />Be great in act, as you have been in thought.
  2. Adriaan New Member

    No PK? Then make the identity columns a PK (clustered).

    Sorry, but your images are not showing.

    And am I missing something here - you're joining on xID.NSN_SEARCH=xID_Text.NSN_SEARCH, but there is no NSN_Search column in your xID table.

    You're also mixing char and varchar columns. If the NSN_Search column is one of those char columns, then note that SQL Server has to convert between char and varchar, which means that it probably cannot use any index on either column, and will do a table scan instead.
  3. waqar Member

    quote:Originally posted by Adriaan

    No PK? Then make the identity columns a PK (clustered).

    Sorry, but your images are not showing.

    And am I missing something here - you're joining on xID.NSN_SEARCH=xID_Text.NSN_SEARCH, but there is no NSN_Search column in your xID table.

    You're also mixing char and varchar columns. If the NSN_Search column is one of those char columns, then note that SQL Server has to convert between char and varchar, which means that it probably cannot use any index on either column, and will do a table scan instead.
    Hi Adriaan,

    I have changed Column names.
    I am adding keys and will update table and output in a while.

    I am getting this option " * HTML is OFF", this is reason why my all tags and [code ] are not working.
    How to ON HTML any idea?


    ________________________________________________
    Be great in act, as you have been in thought.
  4. Adriaan New Member

    Don't bother with those images: tell us what the execution plan is saying.

    I would start with adding a PK (clustered) on the identity columns, then rebuild the indexes.
  5. waqar Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Adriaan</i><br /><br />Don't bother with those images: tell us what the execution plan is saying.<br /><br />I would start with adding a PK (clustered) on the identity columns, then rebuild the indexes.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />clustered index created for both table on c0.<br />reindex both tables.<br />Here is query and execution plan<br /><br /><b><font color="blue">QUERY 1</font id="blue"></b><br /><br />SELECT<br />xID.*<br />FROM<br />xID<br />INNER JOIN<br />xID_Text<br />ON<br />xID.c1=xID_Text.c1<br />AND<br />xID.c2=xID_Text.c2<br /><br /><br /> |--Nested Loops(Inner Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[db].[dbo].[xID].[ID], [Expr1008]) OPTIMIZED WITH UNORDERED PREFETCH)<br /> |--Nested Loops(Inner Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[db].[dbo].[xID_Text].[c1], [db].[dbo].[xID_Text].[c2], [Expr1007]) OPTIMIZED WITH UNORDERED PREFETCH)<br /> | |--Table Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[db].[dbo].[xID_Text]))<br /> | |--Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[db].[dbo].[xID].[xID_1]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[db].[dbo].[xID].[c1]=[db].[dbo].[xID_Text].[c1] AND [db].[dbo].[xID].[c2]=[db].[dbo].[xID_Text].[c2]) ORDERED FORWARD)<br /> |--Clustered Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[db].[dbo].[xID].[PK_xID]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[db].[dbo].[xID].[ID]=[db].[dbo].[xID].[ID]) LOOKUP ORDERED FORWARD)<br /> <br /><b><font color="blue">QUERY 2</font id="blue"></b><br /><br />SELECT<br />xID.*<br />FROM<br />xID_1 xID<br />INNER JOIN<br />xID_Text<br />ON<br />xID.c1=xID_Text.c1<br />AND<br />xID.c2=xID_Text.c2 <br /> <br /> <br /> |--Parallelism(Gather Streams)<br /> |--Filter(WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[db].[dbo].[xID_1].[c2] as [xID].[c2]=[db].[dbo].[xID_Text].[c2]))<br /> |--Nested Loops(Inner Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[xID].[ID], [Expr1007]) OPTIMIZED WITH UNORDERED PREFETCH)<br /> |--Nested Loops(Inner Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[db].[dbo].[xID_Text].[c1], [Expr1006]) OPTIMIZED WITH UNORDERED PREFETCH)<br /> | |--Table Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[db].[dbo].[xID_Text]))<br /> | |--Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[db].[dbo].[xID_1].[IDX1] AS [xID]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[xID].[c1]=[db].[dbo].[xID_Text].[c1]) ORDERED FORWARD)<br /> |--Clustered Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[db].[dbo].[xID_1].[PK_xID_1] AS [xID]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[xID].[ID]=[db].[dbo].[xID_1].[ID] as [xID].[ID]) LOOKUP ORDERED FORWARD)<br /><br />________________________________________________<br />Be great in act, as you have been in thought.
  6. Adriaan New Member

    Okay, so I presume you have tested this before the changes, and made a note of execution time - so how long does it take now?
  7. waqar Member

    quote:Originally posted by Adriaan

    Okay, so I presume you have tested this before the changes, and made a note of execution time - so how long does it take now?
    Query 1
    100 Rows= 989 ms
    1000 Rows = 6338 ms
    3000 Rows = 9209 ms

    Query 2
    100 Rows= 11993 ms
    1000 Rows = 10833 ms
    3000 Rows = 12898 ms

    Does this exercise prove
    INDEX A is better than INDEX B + INDEX C?

    INDEX A
    CREATE INDEX INDEX_A on xID (c1 ASC,c2 ASC)

    INDEX B
    CREATE INDEX INDEX_B on xID_1 (c1 ASC)
    INDEX C
    CREATE INDEX INDEX_C on xID_1 (c2 ASC)



    ________________________________________________
    Be great in act, as you have been in thought.
  8. Dale L New Member

    Its better to have a query with almost constant or slowly increasing execution time than the first one that take more time as your number of data goes up.

Share This Page