1

I'm using these two tables in a SQL Server 2014 database:

CREATE TABLE #FORMULAS 
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [DataSourceAddress] [nvarchar](255) NULL,
    [Description] [nvarchar](255) NULL
)

CREATE INDEX [IX_FORMULAS] 
ON #FORMULAS ([DataSourceAddress] ASC)

CREATE TABLE #PARAMETERS
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Datetime] DateTime NULL,
    [ParameterName] [nvarchar](255) NULL,
    [Value] float NULL
)

CREATE INDEX [IX_PARAMETERS] 
ON #PARAMETERS([ParameterName] ASC)

INSERT INTO #FORMULAS ([DataSourceAddress], [Description])
VALUES ('(parameter1+parameter2+parameter3)/3', 'Formula description 1'),
       ('parameter4', 'Formula description 2'),
       ('(parameter5*1000)', 'Formula description 3');

INSERT INTO #PARAMETERS ([Datetime], [ParameterName], [Value])
VALUES ('2016-01-26 11:40:00.000', 'parameter1', 1),
       ('2016-01-26 11:40:00.000', 'parameter2', 2),
       ('2016-01-26 11:40:00.000', 'parameter3', 3),
       ('2016-01-26 11:40:00.000', 'parameter4', 4),
       ('2016-01-26 11:40:00.000', 'parameter5', 5),
       ('2016-01-26 11:40:00.000', 'parameter6', 6),
       ('2016-01-26 11:44:00.000', 'parameter1', 1),
       ('2016-01-26 11:44:00.000', 'parameter2', 2),
       ('2016-01-26 11:44:00.000', 'parameter3', 3),
       ('2016-01-26 11:44:00.000', 'parameter4', 4),
       ('2016-01-26 11:44:00.000', 'parameter5', 5),
       ('2016-01-26 11:44:00.000', 'parameter6', 6);

I want to select the parameters of the table #FORMULAS that match with the column [ParameterName] of the table #PARAMETERS.

For example, take the value '(parameter1+parameter2+parameter3)/3' of the table #FORMULAS and receive all the rows of the table #PARAMETERS that contains 'parameter1','parameter2','parameter3'.

I'm using the following select:

SELECT  
    A.[Datetime],
    A.[ParameterName],
    B.[Description],
    A.[Value]
FROM   
    #PARAMETERS A
JOIN 
    #FORMULAS B ON CHARINDEX(A.[ParameterName], B.[DataSourceAddress]) > 0

The #PARAMETERS table has more than 160000 rows (with 1174 unique ParameterNames), #FORMULAS has 3283 rows. The query takes a lot of time to finish, more than 10 minutes.

Is there a faster way to do the query?

0
3

Wildcard matching strings with good performance will always be tricky, but here's a best-effort attempt.

First off, your indexing can be improved. The index on #PARAMETERS only includes the actual ParameterName column, which is fine if you need a list of parameters, but whenever you need any other column, the index will be useless to SQL Server, and it'll revert to scanning the entire table instead. I would propose fixing this by changing the index to a clustered index:

CREATE UNIQUE CLUSTERED INDEX [IX_PARAMETERS]
    ON #PARAMETERS (ParameterName, [Datetime])
    WITH (DROP_EXISTING=ON);     -- Remove this row if the index doesn't already exist.

Clustered indexes include every column in the table, which automatically makes a clustered index a covering indexes.

From here, I've updated your query to do three things:

  • Collect each unique parameter in #PARAMETERS.
  • For each unique parameter, find matching formulas in #FORMULAS. This is the expensive part, and no index will really improve this search because the search condition (LIKE) is not sargable. This means that, index or not, SQL Server will go through each and every row in the table (a so-called scan), for every parameter.
  • Finally, join #PARAMETERS to get all the dates and values. This is a relatively cheap and efficient operation. This is where the modified index on #PARAMETERS helps.

Here's the resulting query:

SELECT  A.[Datetime],
        p.[ParameterName],
        B.[Description],
        A.[Value]
FROM (SELECT DISTINCT ParameterName FROM #PARAMETERS) p
CROSS APPLY (
    SELECT *
    FROM #FORMULAS B WITH (FORCESCAN)
    WHERE B.DataSourceAddress LIKE N'%'+p.ParameterName+N'%') AS B
INNER JOIN #PARAMETERS AS A ON
    p.ParameterName=A.ParameterName;

I added WITH (FORCESCAN) to eliminate key lookups. You can try the query with or without this hint.

If I had a lot more time to work on this and it was a super-critical query, I would probably (a) remodel the tables and/or (b) consider storing the data in #FORMULAS as XML data and add XML indexes, which would eliminate the LIKE match.

Footnote: Text matching like you do here (LIKE and CHARINDEX are pretty much the same here) will include formulas with the parameters "parameter10" and "parameter11" even when you're just searching for "parameter1".

8
  • I have a problem with similar parameters using 'LIKE' clause. For example, If you add the row '('2016-01-26 11:40:00.000','param',7)' in the #PARAMETERS TABLE, you'll see that the result returns rows with the parameter 'param' but there isn't parameters named 'param' in the #FORMLAS table. Do you have any idea to solve this problem? – JosepB Jan 28 '16 at 14:39
  • I think that replace 'WHERE B.DataSourceAddress LIKE N'%'+p.ParameterName+N'%')' for 'WHERE (B.[DataSourceAddress] = p.ParameterName OR B.[DataSourceAddress] like N'%[^0-9a-z]'+p.ParameterName+N'[^0-9a-z]%'))' could be a possible solution... – JosepB Jan 28 '16 at 15:08
  • It'll produce exactly the same results, but the OR will complicate the query and make it more expensive. – Daniel Hutmacher Jan 28 '16 at 15:15
  • 1
    @JosepBacardit: An alternative could be to specify all possible delimiters on both sides of the parameter name: … LIKE N'%[+-*/()]'+p.ParameterName+N'[+-*/()]%'. And to account for the names exactly at the beginning and exactly at the end of the source string, just make sure they are delimited on both sides: '+' + B.DataSourceAddress + '+' LIKE …. – Andriy M Jan 28 '16 at 16:56
  • 1
    @AndriyM, yes. Just remember to escape the minus sign. :) – Daniel Hutmacher Jan 28 '16 at 16:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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