SQL Server 2008: Query performance using inner join

Question

Here I am getting a performance issue on inner join on huge data for my query as show below:

Example:

/* Creating table */

create table xyz
(
 colp1 nvarchar(10),
 colp2 nvarchar(10),
 coldt date,
 coltm datetime,
 coldr integer
);

/* Inserting records */ 

insert into xyz values('A','B','2014-08-02','10:00:00',50);
insert into xyz values('A','C','2014-08-02','11:08:08',120);
insert into xyz values('A','B','2014-08-02','11:08:55',160);
insert into xyz values('A','D','2014-08-03','09:00:15',180);
insert into xyz values('A','E','2014-08-04','11:00:10',600);
insert into xyz values('A','F','2014-08-04','11:05:50',320);
.
.
upto 50000

/* Query */

declare @testtable table(dt date,st time,et time)

insert into @testtable select coldt,coltm,DATEADD(ss,coldr,coltm) from xyz

select distinct colp1,colp2,coldt,
            coltm from xyz as x 
            inner join  
            @testtable  as t on convert(varchar,x.coltm,108) > t.st and 
            convert(varchar,x.coltm,108)< t.et;

Getting very much time to execute above query for huge data.

You have not set up any indexes on the join columns. This might help. — stakx - no longer contributing
– stakx - no longer contributing, Commented Sep 3, 2014 at 5:47
My guess is that your join conditions are non-sargable: convert(varchar,x.coltm,108) > t.st and convert(varchar,x.coltm,108)< t.et. It also converts the left side to varchar while comparing them to a time data type. — Felix Pamittan
– Felix Pamittan, Commented Sep 3, 2014 at 5:53

zhongxiao37 · Accepted Answer · 2014-09-03 05:58:58Z

1

Try to change your query to

create TABLE #testtable (dt date,st time,et time)

insert into #testtable select coldt,coltm,DATEADD(ss,coldr,coltm) from xyz

select distinct colp1,colp2,coldt,
        coltm 
INTO #tmp2
from xyz as x 
inner join  #testtable  as t on convert(varchar,x.coltm,108) > t.st and 
        convert(varchar,x.coltm,108)< t.et;

The problem here is using the table variables, instead of temp table. Here is the document for comparing table variable and temp table.

answered Sep 3, 2014 at 5:58

zhongxiao37

9878 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sarfaraz Makandar Over a year ago

This makes huge diffence! Thank you so much.

Joël Salamin · Accepted Answer · 2014-09-03 06:00:00Z

It's normal that you've performance issues, you're using a table variable with no indexes to manipulate important amount of data.

I don't understand the use of this variable to get your result, it's much more performant to directly use your xys table like that:

SELECT DISTINCT X.colp1
    ,X.colp2
    ,X.coldt
    ,X.coltm
FROM xyz X
INNER JOIN (SELECT Y.coldt AS [dt]
                 ,Y.coltm AS [st]
                 ,DATEADD(SS, Y.coldr, Y.coltm) AS [colet]) XT ON XT.st < CONVERT(VARCHAR, X.coltm, 108)
                                                                 AND XT.et > CONVERT(VARCHAR, X.coltm, 108)

By using this approach you take full advantage of your table indexes and it will be much more performant than using a table variable.

For sure this solution make sense only if you defined indexes on the table xys (if not, it's very important to think about that now).

Hope this will help you.

Collectives™ on Stack Overflow

SQL Server 2008: Query performance using inner join

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related