sqlserver Query time taking

Question

I am executing below query. It takes 80 seconds for just 17 records.

can any body tell me reason if knows. I have already tried with Indexes.

SELECT DISTINCT t.i_UserID,
                u.vch_LoginName,
                t.vch_PreviousEmailAddress AS 'vch_EmailAddress',
                u.vch_DisplayName,
                t.d_TransactionDate AS 'd_DateAdded',
                'Old' AS 'vch_RecordStatus'
           FROM tblEmailTransaction t
     INNER JOIN tblUser u 
             ON t.i_UserID = u.i_UserID
          WHERE t.vch_PreviousEmailAddress LIKE '%kala%'

Post your Actual Execution Plan and we'll be able to see what's taking the time. — Rich Benner, May 04 '16 at 10:23
`WHERE t.vch_PreviousEmailAddress Like '%kala%'` **[Non-SARGable condition](http://stackoverflow.com/a/799604/5070879)** — Lukasz Szozda, May 04 '16 at 10:24

score 1 · Answer 1 · answered May 04 '16 at 10:42

Change collation for vch_PreviousEmailAddress column on Latin1_General_100_BIN2

Create covered index:

CREATE NONCLUSTERED INDEX ix
ON dbo.tblEmailTransaction (vch_PreviousEmailAddress)
    INCLUDE (i_UserID, d_TransactionDate)
GO

And have fun with this query:

SELECT t.i_UserID,
       u.vch_LoginName,
       t.vch_PreviousEmailAddress AS vch_EmailAddress,
       u.vch_DisplayName,
       t.d_TransactionDate AS d_DateAdded,
       'Old' AS vch_RecordStatus
FROM (
    SELECT DISTINCT i_UserID,
                    vch_PreviousEmailAddress,
                    d_TransactionDate
    FROM dbo.tblEmailTransaction
    WHERE vch_PreviousEmailAddress LIKE '%kala%' COLLATE Latin1_General_100_BIN2
) t
JOIN dbo.tblUser u ON t.i_UserID = u.i_UserID

score 0 · Answer 2 · answered May 04 '16 at 10:53

One other thing, which I find useful in solving problems like this:

Try running the following script. It will tell you which indexes you could ask to your SQL Server database, which would make the most (positive) improvement.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 100 
  ROUND(s.avg_total_user_cost * s.avg_user_impact * (s.user_seeks + s.user_scans),0) AS 'Total Cost',
  s.avg_user_impact,
  d.statement AS 'Table name',
  d.equality_columns, 
  d.inequality_columns,
  d.included_columns,
  'CREATE INDEX [IndexName] ON ' + d.statement + ' ( ' 
     + case when (d.equality_columns IS NULL OR d.inequality_columns IS NULL)
       then ISNULL(d.equality_columns, '') + ISNULL(d.inequality_columns, '')
       else ISNULL(d.equality_columns, '') + ', ' + ISNULL(d.inequality_columns, '')
     end + ' ) '
     + CASE WHEN d.included_columns IS NULL THEN '' ELSE 'INCLUDE ( ' + d.included_columns + ' )' end AS 'CREATE INDEX command'
FROM sys.dm_db_missing_index_groups g,
     sys.dm_db_missing_index_group_stats s, 
     sys.dm_db_missing_index_details d 
WHERE d.database_id = DB_ID() 
AND s.group_handle = g.index_group_handle 
AND d.index_handle = g.index_handle
ORDER BY [Total Cost] DESC

The right-hand column displays the CREATE INDEX command which you'd need to run, to create that index.

This one of those lifesaver scripts, which I run on our in-house databases once ever so often.

But yes, in your example, this is just likely to tell you that you need an index on the vch_PreviousEmailAddress field in your tblEmailTransaction table.

score 0 · Answer 3 · edited Jan 26 '17 at 09:03

The probable bottleneck are 2:

Missing Index on tblEmailTransaction.i_UserID: Check if the table has the index
Missing Index on tblUser.i_UserID: Check if the table has the index
Like Statement: Like statement is know to be not good in performance, as Devart suggested, try to specify collection in this way:
```
 WHERE vch_PreviousEmailAddress LIKE '%kala%' COLLATE Latin1_General_100_BIN2
```

To have a better view on your query, You have to run this command with your query:

SET IO STATISTICS ON

It will write all the IO Access that the query does and the we can see what happen.

Just a final question ? How many rows contains the two tables?

Ciao

sqlserver Query time taking

3 Answers3