CODEWITHSUNDEEP
sql-serverquery-optimization
Jared Wadsworth
Jared Wadsworth

Reputation: 847

SQL Server optimize query for millions of rows

I have a query that I run, but it takes hours to complete. It runs on a table with over 5 million rows. I've tried messing with different portions to tune it up, but have limited training in SQL and no training in SQL optimization. Any help to make this even a little faster would be greatly appriciated.

The query:

UPDATE Work_Orders SET status_ref = (SELECT id FROM Status s WHERE s.status LIKE 'Invalid Loan Number') 
WHERE id IN ( SELECT w.id FROM Work_Orders w LEFT JOIN (SELECT * FROM Contract_Code_Ref c WHERE c.contract_code = 'AAA' ) a 
ON w.file_name LIKE a.prior_servicer_loan_number + '%' 
WHERE a.id IS NULL AND w.work_order_ref = (SELECT id FROM Work_Order_Lookup l WHERE l.work_order = '1234'));

the table structures:

Work_Orders:
id              bigint  
work_order_ref  int 
path            varchar(300)    
file_name       varchar(150)    
file_size       bigint  
loan_number     varchar(15) Null
scan_date       date        Null
doctype         varchar(50) Null
status_ref      tinyint 
last_updated    timestamp   


Work_Order_Lookup:
id          int 
work_order  varchar(6)  


Status:
id       int
status   varchar(30)


Contract_Code_Ref:
id  int Unchecked
contract_code               varchar(3)  
prior_servicer_loan_number  varchar(15) 
current_loan_number         varchar(10) 
last_updated                timestamp

I'm using SQL server 2014 express.

Thanks in advance!

Upvotes: 0

Views: 184

Answers (2)

Bruce Dunwiddie
Bruce Dunwiddie

Reputation: 2908

UPDATE w
SET status_ref = (SELECT id FROM Status s WHERE s.status LIKE 'Invalid Loan Number') 
FROM
Work_Orders w
WHERE 
NOT EXISTS
(
SELECT *
FROM
Contract_Code_Ref c
WHERE
c.contract_code = 'AAA'
-- this is probably the issue
AND w.file_name LIKE a.prior_servicer_loan_number + '%'
)
AND w.work_order_ref = (SELECT id FROM Work_Order_Lookup l WHERE l.work_order = '1234')
AND status_ref <> (SELECT id FROM Status s WHERE s.status LIKE 'Invalid Loan Number');

Put an index on Work_Order_Lookup.work_order, one on Work_Orders.work_order_ref, and one on Contract_Code_Ref.contract_code.

Upvotes: 1

thomasw_lrd
thomasw_lrd

Reputation: 546

Do you have any idexes on your tables?

The main problem I see is all the sub queries. Sub queries are not a great way to go.

Do you have to update every single row everytime? You can make the update statement faster by putting a where clause on there. A few examples of your data might help get an answer faster.

Upvotes: 0

Related Questions