How to get results fastly with 'column1 column2=@variable'-CodePudding

I am using SQL Server 2016. I have a table more than 10m rows. When I want to search with the query show here, it is very slow.

CREATE TABLE dbo.table_name
(
    Col1 int NOT NULL,
    Col2 int NULL,
    Col3 char(2) NULL,
    Col4 char(15) NULL,
    Col5 varchar(8) NULL,
    Col6 varchar(12) NULL,
    Col7 varchar(8) NULL,
    Col8 int NULL,
    Col9 int NULL,
    Col10 varchar(16) NOT NULL
) ON PRIMARY

SELECT
    colum3   column4 
FROM
    tablename 
WHERE
    column3   column4 = @variable

What index method should I use to speed up this query? (of type varchar in two columns)

CodePudding user response：

Maybe you could consider a computed column and indexing that.

ALTER TABLE dbo.tablename ADD concat_column AS
  CONCAT(column1   column2);

CREATE INDEX ix_concat_column ON dbo.tablename(concat_column)
  INCLUDE ...

Not sure an index on the separate columns would be of much help, since it would still have to perform the concat on every pair (think 'Rhy' 'thm' is the same as 'R' 'hythm').

CodePudding user response：

There's no nice way to do this - but you have a few options.

The problem is that the query has to concatenate col3 and col4 for every row in the database and compare it to your variable. The concatenation means it won't use an index. The time to do that will increase linearly with the number of rows.

Firstly, if you create indexes on column3 and on column4, and change the query to be

select concat(col3, col4) 
from table_name 
where (
   col3 like left(@variable, 3)
   or col4 like left(@variable, 3)
and concat(col3, col4) = @variable

you should get a decent improvement in speed. That's because the first where clause can use an index, and should reduce the total number of rows on which the query has to concatenate col3 and col4. I've suggested the first 3 characters of @variable - adjust that to whatever works.

The second option is to create a view which concatenates column3 and column4 into a new column, and create an index on that column. That should be incredibly fast.

CodePudding user response：

The model suggests that Col3 and Col4 values are always 2 and 15 char in lenght.

If this is the case, you could add an index including both column and using them seperatly in the where clause.

It will remains to figure out how Null values should be handled.


CREATE INDEX idx_myindex ON dbo.tablename(Col1, Col2);
GO

SELECT
    Concat(colum3, column4) 
FROM
    tablename 
WHERE
    column3 = left(@variable,2) and (column4 = right(@variable,15)

Solution handling Null values based on the same assumptions.


SELECT
    Concat(colum3, column4) 
FROM
    tablename 
WHERE
    ( Len(@variable) = 17 AND 
      column3 = left(@variable,2) and (column4 = right(@variable,15) )
    OR
    ( Len(@variable) = 2 AND
      column3 = @variable AND column4 is null )
    OR
    ( Len(@variable) = 15 AND
      column3 is null AND column4 = @variable )