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.
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.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments