0
select TableA.Sys_ID, TableA.State_Code, 
    TableA.Zip_Code, TableA.Birth_Date, TableA.Gender, 
    TableA.Source_code, TableB.Sys_ID
into #testtable
from DM.TableA
    inner join DM.TableB
    on TableA.Sys_ID = TableB.Sys_ID;

TableA has about 130 million records, while TableB has about 600 million records. I have aborted my query multiple times after 15 minutes, because I don't want to clog up access to the database across the company. Is there anything I can do to optimize my code for greater load speed, or is it simply a matter of time before the job completes?

blacksite
  • 12,086
  • 10
  • 64
  • 109
  • Any indexes on tables? on `Sys_ID` – Meet Aug 20 '15 at 13:49
  • Yes. There are 85 on TableA and 24 on TableB. How does this affect run speed? – blacksite Aug 20 '15 at 14:03
  • 15min or a little bit more seems normal for an Table join with 150mil and 600mil, altough it doesnt have much columns, still thats alot to process. Indexes are very important for performance, especially if you are joining such large tables, it is important to know which index type to use for every column you think it might be smart to use on. I dont see why do you worry about clogin up access, you are making a commit, in IQ you can always make an select, it doesnt matter how much people are making an select statement on the same table, you are just reading from the table, you didnt lock it. – theweeknd Aug 31 '15 at 10:59

2 Answers2

0

One of the tables at least will need an index on Sys_ID. If one does, then are statistics up to date? UPDATE INDEX STATISTICS if not. You can find out how off the statistics are with:

SELECT DATACHANGE(tab_name, null, null)
Abe Crabtree
  • 524
  • 3
  • 9
0

Please can you execute the below once to check if that helps.

select TableA.Sys_ID, TableA.State_Code, 
TableA.Zip_Code, TableA.Birth_Date, TableA.Gender, 
TableA.Source_code, TableB.Sys_ID
into #testtable
from DM.TableA
inner join DM.TableB
on TableA.Sys_ID = TableB.Sys_ID where 1=2;


insert into  #testtable select TableA.Sys_ID, TableA.State_Code, 
TableA.Zip_Code, TableA.Birth_Date, TableA.Gender, 
TableA.Source_code, TableB.Sys_ID from DM.TableA
inner join DM.TableB on TableA.Sys_ID = TableB.Sys_ID  order by            
TableA.Sys_ID

You can monitor the insertion of records into temporary table using MDA tables (monOpenobjectActivity ) to see how much records are inserted in how much time.

Although Select into's are faster than inserts. But you can try this option once.

Alternative try by :

select TableA.Sys_ID, TableA.State_Code, 
TableA.Zip_Code, TableA.Birth_Date, TableA.Gender, 
TableA.Source_code, TableB.Sys_ID
into #testtable
from DM.TableA
inner join DM.TableB
on TableA.Sys_ID = TableB.Sys_ID order by TableA.Sys_ID
Spage
  • 1