1

I have a pretty simple table SQL Table (currently Azure SQL, may stay there or end up in local SQL Server 19 instance if that matters), about 100,000 records, that's mostly for reading but will gradually be written to.

CREATE TABLE [dbo].[xCatalog](
    [catalogID] [int] IDENTITY(1,1) NOT NULL,
    [title] [nvarchar](255) NOT NULL,
    [dateA] [datetime] NULL,
    [dateB] [datetime] NULL,
    [textA] [nvarchar](25) NULL,
    [textB] [nvarchar](255) NULL,
    [textC] [nvarchar](255) NULL,
 CONSTRAINT [PK_xCatalog] PRIMARY KEY CLUSTERED 
(
    [catalogID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO 

Of note is the [title] nvarchar(255) field, which will be what users are primarily searching using a query formatted as bellow using "ocean" as the example [title] search term

SELECT TOP(500) [catalogID],[title],[dateA],[dateB],[textA],[textB],[textC] 
FROM [dbo].[xCatalog] 
WHERE ([title] LIKE '%ocean%') 
ORDER BY [catalogID] ASC

It runs fairly ok, 1-3 seconds, but I'm really interested in diving into how/if to improve it as frankly I have more of only a surface level understanding of stuff like Indexes and other optimizations.

The query is being generated by a front end program I don't believe I can change, hence why it's selecting a limited amount sorted by the key to return for virtual scrolling, and why I'm not exploring replacing the LIKE with Contains or anything like that.

So I'm really trying to come at this from the DB side and just understand what best practice would be and what people's thoughts are on this kind of case as far as what kind of Index to use, or if switching to ColumnStore would be applicable (prob too small?), etc. We also are using a basic tier Azure SQL db so maybe for this kind of operation we would benefit from bumping that up slightly.

Any stuff like that so I can know it going forward and confidently be able to explain I've done x or y to help it but also maybe wildcard searching large text is just sometimes expensive so what should my expectations be.

I'll toss in the simple execution plan in case it's helpful:

query execution plan enter image description here

Thank you anyone for input! I know there's many similar questions I'll continue to research but sometimes it feels like they're slightly different and I figure what's the harm in asking too.

2
  • 2
    If you cannot change the query, you have exceptionally little opportunity to improve performance. If you relax that and allow the query to change, you can get an order of magnitude or more improvement. Dec 29, 2023 at 2:50
  • ^ageed. There's almost nothing you can do if you're unable to change the query.
    – J.D.
    Dec 29, 2023 at 3:56

1 Answer 1

2

The execution plan seems reasonable - the existing PK is about as good as it gets. We can read the table in order and short-circuit as we find 500 matching rows. If you can't change the query there isn't much you can do in the way of optimization - more memory, faster CPU & faster storage might help.

If you can change the query, there are a couple of options to look at:

  • FullText search (CONTAINS)

If this works for you, use it. It doesn't do the same thing as LIKE %% though. It works well for searching general text but doesn't always work well with codes etc.

With this technique, you can maintain the exact behaviour of LIKE %% with significantly improved performance. Unfortunately, there is no built-in support for ngrams/trigrams so this is a lot of work. You also have extra overhead and storage associated with the ngrams index. For a table with 100K rows, it's probably not worth it. I've used it and it can work well - but full-text is a much simpler option unless you really need the behaviour of LIKE %%. You could also consider using another system like ElasticSearch which can use ngrams/trigrams.

  • Using a narrower index to get top 500

Something like this might help to squeeze a bit extra performance out of LIKE %% if you really need to.

/* A narrower index might help.  Essentially a duplicate of PK though */

/*
CREATE UNIQUE NONCLUSTERED INDEX IX_xCatalog_catalogID_title ON dbo.xCatalog(catalogID) INCLUDE(title)
*/

CREATE TABLE #Matches(
    catalogID INT PRIMARY KEY
)

/* Get items that match criteria */
INSERT  INTO #Matches(
    catalogID
)
SELECT TOP(500) [catalogID]
FROM [dbo].[xCatalog] 
WHERE ([title] LIKE '%ocean%') 
ORDER BY [catalogID] ASC

/* Join back to table to return other columns */
SELECT TOP(500) C.[catalogID],C.[title],C.[dateA],C.[dateB],C.[textA],C.[textB],C.[textC] 
FROM #Matches M 
JOIN [dbo].[xCatalog] C ON M.catalogID = C.catalogID
ORDER BY M.[catalogID] ASC
  • Other

There are a variety of other things that might work depending on your requirements. Adding tags for searching might work for example. You could maybe cache common search queries - which should work great if the data is relatively static.

2
  • Adding the IX_xCatalog_catalogID_title index could be a unique index.
    – Dan Guzman
    Dec 29, 2023 at 12:30
  • @DanGuzman. Yes, it probably should be unique - I've updated my answer. Dec 29, 2023 at 13:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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