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).
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)
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.