SQL Server Performance Forum – Threads Archive

Why speed is slow with these indexes

Hi, I have created 2 similar tables, both have 9899999 records each. Structure of tables is as shown below TABLE 1 <pre id="code"> STRUCTURE ——— CREATE TABLE [dbo].[xID]( [c0] [int] IDENTITY(1,1) NOT NULL, [c1] [char](13) NULL, [c2] [char](16) NULL, [c3] [varchar](50) NULL ) ON [PRIMARY] INDEX —– CREATE NONCLUSTERED INDEX [xID_1] ON [dbo].[xID] ( [c1] DESC, [c2] DESC )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] </pre id="code"> TABLE 2 <pre id="code"> STRUCTURE ——— CREATE TABLE [dbo].[xID_1]( [c0] [int] IDENTITY(1,1) NOT NULL, [c1] [char](13) NULL, [c2] [char](16) NULL, [c3] [varchar](50) NULL ) ON [PRIMARY] INDEX —– CREATE NONCLUSTERED INDEX [IDX1] ON [dbo].[xID_1] ( [c1] ASC )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] CREATE NONCLUSTERED INDEX [IDX2] ON [dbo].[xID_1] ( [c2] ASC )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] </pre id="code"> I create a table with 8999 records to check SELECT speed for both table TABLE 3 <pre id="code"> CREATE TABLE [dbo].[xID_Text]( [c1] [char](13) NULL, [c2] [char](16) NULL ) ON [PRIMARY] </pre id="code"> Now i am running follow queries and <pre id="code"> SECTION A SELECT xID.* FROM xID INNER JOIN xID_Text ON xID.c1=xID_Text.c1 AND xID.c2=xID_Text.c2 SECTION B SELECT xID.* FROM xID_1 xID INNER JOIN xID_Text ON xID.c1=xID_Text.c1 AND xID.c2=xID_Text.c2 </pre id="code"> Here is comparison <img src=’http://img396.imageshack.us/img396/7933/sql1ao1.gif’ border=’0′ /> Here is execution plan for both SECTIONS SECTION A <img src=’http://img104.imageshack.us/img104/9411/sql2zf6.gif’ border=’0′ /> SECTION B <img src=’http://img240.imageshack.us/img240/9871/sql3xa1.gif’ border=’0′ /> My reason for this exercise is to check if i can create individual index rather create composite. 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). Any suggestion? ________________________________________________ Be great in act, as you have been in thought.

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.

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.

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.

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by Adriaan 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. <hr height="1" noshade id="quote"></blockquote id="quote"> clustered index created for both table on c0. reindex both tables. Here is query and execution plan QUERY 1 SELECT xID.* FROM xID INNER JOIN xID_Text ON xID.c1=xID_Text.c1 AND xID.c2=xID_Text.c2 |–Nested Loops(Inner Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[db].[dbo].[xID].[ID], [Expr1008]) OPTIMIZED WITH UNORDERED PREFETCH) |–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) | |–Table Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[db].[dbo].[xID_Text])) | |–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) |–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) QUERY 2 SELECT xID.* FROM xID_1 xID INNER JOIN xID_Text ON xID.c1=xID_Text.c1 AND xID.c2=xID_Text.c2 |–Parallelism(Gather Streams) |–Filter(WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[db].[dbo].[xID_1].[c2] as [xID].[c2]=[db].[dbo].[xID_Text].[c2])) |–Nested Loops(Inner Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[xID].[ID], [Expr1007]) OPTIMIZED WITH UNORDERED PREFETCH) |–Nested Loops(Inner Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[db].[dbo].[xID_Text].[c1], [Expr1006]) OPTIMIZED WITH UNORDERED PREFETCH) | |–Table Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[db].[dbo].[xID_Text])) | |–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) |–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) ________________________________________________ Be great in act, as you have been in thought.

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?

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.

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.

]]>