CODEWITHSUNDEEP
t-sqlsql-server-2012sqlperformance
JsCoder
JsCoder

Reputation: 1733

SQL Server 2012 poor performance when selecting using LIKE from a large table

I have a table with ~1M rows and run the following SQL against it:

select * from E where sys like '%,141,%'

which takes 2-5 seconds to execute (returning ~10 rows), I need it to be 10 times faster at least, is it something which can be achieved with SQL Server 2012?

A sample sys value (sys values length ranges from 5 to 1000 characters):

1,2,3,7,9,10,11,12,14,17,28,29,30,33,35,37,40,41,42,43,44,45,46,47,48,50,51,53,55,63,69,
72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,97,109,110,111,113,117,
119,121,122,123,124,130,131,132,133,134,135,139,141,146

The table's DDL:

CREATE TABLE [dbo].[E](
    [o] [int] NOT NULL,
    [sys] [varchar](8000) NULL,
    [s] [varchar](8000) NULL,
    [eys] [varchar](8000) NULL,
    [e] [varchar](8000) NULL,
 CONSTRAINT [PK_E] PRIMARY KEY CLUSTERED 
(
    [o] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Upvotes: 2

Views: 805

Answers (4)

Jeff Moden
Jeff Moden

Reputation: 3503

I realize this is an older post but...

If you're absolutely hell bent on storing denormalized data in a table, convert it to XML so you can at least index it.

However, the best thing to do would be to normalize that data by splitting it out into a one to many lookup table (as robert Harvey suggested above).

Upvotes: 1

Magnus
Magnus

Reputation: 47036

If you can't change the table schema you can enable Full-Text search and create a full text index on the table and then do:

select * from E where CONTAINS(sys, ",141,")

Upvotes: 3

TheZachHill
TheZachHill

Reputation: 56

The LIKE Operator is always going to be slower because this forces SQL Server to scan each row for the data you are looking for. below is a alternative to LIKE that may work a little better (although will still scan the data).

SELECT * FROM E WHERE CHARINDEX(',141,', sys) > 0

Upvotes: 1

Robert Harvey
Robert Harvey

Reputation: 180948

Your like clause is causing a full table scan.

If you want instant performance for this query, you will need a one-to-many table that contains the following fields:

E_Key  <-- Foreign Key, points to primary key of E table
sys    <-- Each record contains one number, not multiple numbers 
           separated by commas

You can then index sys, and use an ordinary WHERE clause.

Upvotes: 6

Related Questions