Slow Script - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Slow Script

Author

Topic

easy_goer
Starting Member

21 Posts

Posted - 2013-10-01 : 10:12:34

Hello. I ran the following script that too 3 hours 25 minutes to insert about 40,000 rows. Is there any logic that I can put into this to speed it up? Thank you!

insert into T3
select T2.COL1, T2.COL2, T1.COL1, T1.COL2, T1.COL3
from T1
join T2 on T1.COL4 = T2.COL3
where T2.COL4 != 'data1'
and T1.COL1 is not NULL
and T1.COL5 is not NULL
and T1.COL6 = 'data2'
and CAST (T2.COL1 as VARCHAR)+'-'+CAST(T2.COL2 as VARCHAR)+'-'+CAST(T1.COL1 as VARCHAR)+'-'+CAST(T1.COL2 as VARCHAR) not in
(select CAST(COL1 as VARCHAR)+'-'+CAST(COL2 as VARCHAR)+'-'+CAST(COL3 as VARCHAR)+'-'+CAST(COL4 as VARCHAR) from T3)

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2013-10-01 : 10:38:37

depends which part is taking the time.
you could try this if the datatypes on T2 and T3 match

insert into T3
select T2.COL1, T2.COL2, T1.COL1, T1.COL2, T1.COL3
from T1
join T2 on T1.COL4 = T2.COL3
left join T3
on T2.COL1 = T3.COL1
and T2.COL2 = T3.COL2
and T2.COL3 = T3.COL3
and T2.COL4 = T3.COL4
where T2.COL4 != 'data1'
and T1.COL1 is not NULL
and T1.COL5 is not NULL
and T1.COL6 = 'data2'
AND t3.col1 IS NULL

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

easy_goer
Starting Member

21 Posts

Posted - 2013-10-03 : 00:14:17

Got it. Thanks for the help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources