Improve performance of query with conditional filtering

This type of generic search query tends to be rough on performance.

In addition to the suggestion to use temp tables to store the results of the string parsing, there are a couple other things you could do:

Add indexes

It's usually recommended that each table have a clustered index (although it seems there is still room for debate): Will adding a clustered index to an existing table improve performance?

In addition to that, you will probably also want to add indexes on the fields that you're searching on.

In this case, that might be something like:

1. SKU (for searches on SKU alone)
2. Store, SKU (for searches on Store and the combination of both Store and SKU)

Keep in mind that if the query matches too many records, these indexes might not be used. Also keep in mind that making the indexes cover the query can improve performance: Why use the INCLUDE clause when creating an index?

Here is a link to Microsoft's documentation on creating indexes: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-index-transact-sql

Use dynamic SQL to build the query

I need to preface this with a warning. Please be aware of SQL injection, and make sure to code appropriately! How to cleanse dynamic SQL in SQL Server -- prevent SQL injection

Building a dynamic SQL query allows you to write more streamlined and direct SQL, and thus allows the optimizer to do a better job. This is normally something to be avoided, but I believe it fits this particular situation.

Here is an example (should be adjusted to take SQL injection into account as needed):

DECLARE @sql VARCHAR(MAX) = '
    SELECT SKU, Store, ColumnA
    FROM myTable
    WHERE 1 = 1
';

IF @skus IS NOT NULL BEGIN
    SET @sql += ' AND SKU IN (' + @skus + ')';
END

IF @stores IS NOT NULL BEGIN
    SET @sql += ' AND Store IN (' + @stores + ')';
END

EXEC sp_executesql @sql;