Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table which will hold 10 million rows. I'm seing performance issues when quering it, and wonder if I can somehow improve the schema or query, or if I need to look into sharding. I've used SQL Profiler to look at the execution plan, but I'm no DBA and I'm not sure exactly what the next step would be.

Here's what my schema looks like:

CREATE TABLE [dbo].[Items](
  [CompanyId] [uniqueidentifier] NOT NULL,
  [Code] [nvarchar](200) NOT NULL,
   [Name] [nvarchar](200) NOT NULL
      CONSTRAINT PK_Items PRIMARY KEY CLUSTERED 
      (
         [CompanyId], [Code], [Name] ASC
      )
 )

[Code] is typically less than 15 characters, [Name] is between 10 and 30. Each [CompanyId] has about 300 000 to 500 000 entries in the table.

This is what our SELECT's look like:

SELECT TOP 10 Code, Name 
FROM Items 
WHERE CompanyId = '1f42f616-cafb-421a-9c3c-217b12398853' 
  AND (Code LIKE 'a%' OR Name LIKE 'a%') 
ORDER BY Code ASC

I'm using a load tester to run this statement. If I run it 5 times every second, the execution time for the above statement is ~400ms. If I modify the above query so that it matches 10 rows ("a%" matches no rows) the execution time goes down to 100ms. But my application needs to work properly even if users search for nonexistant items.

If I increase the number of executions to 15/sec, the execution time jumps to 800ms. If I increase it to 50/sec the response time becomes ~5 second. As more and more requests are queued up, the SQL Server hits 100% CPU and queries starts to time out. I need a response time less than 500ms.

I'm thinking about sharding the database, but if a single database can only cope with 5 requests/second we will have to buy quite a number of database servers. It seems like this type of lookup should be fairly simple (no joins, all columns indexed, etc).

Edit 1

I've tried changing the schema to the following but it had no significant effect.

CREATE TABLE [dbo].[Items](
[CompanyId] [uniqueidentifier] NOT NULL,
[Code] [nvarchar](200) NOT NULL,
[Name] [nvarchar](200) NOT NULL
CONSTRAINT PK_Items PRIMARY KEY CLUSTERED 
    (
   [CompanyId], [Code] ASC
    )
)

CREATE INDEX Idx_Item_Search on [Items](CompanyId,  Name, Code)

Edit 2

Here's the execution plan. The first query matches no rows, while the second one matches >10 rows in the Name column. There's currently about 400k rows in the table.

Query plans

share|improve this question

closed as off-topic by Mark Storey-Smith, Paul White, RolandoMySQLDBA, Shawn Melton, bluefeet Dec 4 '13 at 12:05

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Mark Storey-Smith, Paul White, RolandoMySQLDBA, Shawn Melton, bluefeet
If this question can be reworded to fit the rules in the help center, please edit the question.

    
If changing indexes is an option, I would cluster on CompanyID, Code and add a NC index on Name. Your existing index can't be used because of the OR condition. –  JNK Dec 3 '13 at 18:00
2  
@JonofAllTrades It wouldn't surprise me if there's a table scan involved. The OR condition gets evaluated separately since it's parenthetical. To know for sure we really need to see the exec plan. –  JNK Dec 3 '13 at 18:20
1  
can you save the query execution plan XML and post it to gist.github.com or to pastebin? –  swasheck Dec 3 '13 at 18:33
4  
@Nitramk You just created a copy of the table with your 2nd index, it's pointless. With the distributions you described (300-500k entries per companyid) a table scan sounds inevitable so try cluster on companyid only, no other indexes. –  Mark Storey-Smith Dec 3 '13 at 18:36
1  
@Nitramk Your query cannot be answered by navigating a single index, it requires 3. An index to identify the rows associated with a companyid, another to find codes beginning with 'a' and another to find names beginning with 'a'. The optimiser recognises that it would take longer to do this than seeking into the clustered index on companyid and scanning all rows for that companyid. Your naive implementation is of course faster as it isn't doing the work of a RDBMS. Pattern matching strings would be better served by a Lucene/Solr type search tool. Maybe Full-Text in SQL Server if you have to. –  Mark Storey-Smith Dec 4 '13 at 13:47

1 Answer 1

The reason why you don't see much of benefit is because of the LIKE clause on column CODE. The optimizer would, in general, will not prefer such columns. The optimizer cannot directly resolve such types of queries/clauses.

I would suggest you to have an index on column CompanyId alone and see if that helps. Good luck!

share|improve this answer
    
Is that really correct? What I've read before is that SQL Server has no issue using an index with LIKE, if the LIKE clause is of type StartsWith% and not %InTheMiddle%. Is this info I've gotten incorrect? –  Nitramk Dec 4 '13 at 8:33
    
see the site myitforum.com/cs2/blogs/jnelson/archive/2007/11/16/108354.aspx . -------->>>>>>>>> If your filter criteria uses LIKE, but with a wildcard at the beginning (as in Name0 LIKE '%UTER') it's much less likely to use the index, but it still may at least perform an INDEX SCAN on a full or partial range of the index. –  Ram27 Dec 4 '13 at 20:31

Not the answer you're looking for? Browse other questions tagged or ask your own question.