SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Slow Script
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

easy_goer
Starting Member

21 Posts

Posted - 10/01/2013 :  10:12:34  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 10/01/2013 :  10:38:37  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page

easy_goer
Starting Member

21 Posts

Posted - 10/03/2013 :  00:14:17  Show Profile  Reply with Quote
Got it. Thanks for the help!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000