Couple of things that can help to speed it up....

Declare the GETDATE() as a variable and call it out... Helps with processing time.

Add "WITH (NOLOCK)" to each and every table in your join statments. If you can call out an actual index this can help.

Change this line

LEFT JOIN (SELECT * FROM ORDER_DEAL) od1 ON TITLE.title_no = od1.order_no AND TITLE.company_id = od1.company_id

To

LEFT JOIN ORDER_DEAL AS od1 WITH (NOLOCK) ON TITLE.title_no = od1.order_no AND TITLE.company_id = od1.company_id

Move "OR" query to last part of where clause

AND (TITLE.sub_no = @sub_no OR @sub_no = 0 ) -- all subscrober (0) or one subscriber

New code can be improved

AND TITLE.title_code NOT BETWEEN 123 and 134 -- SAFE products

AND TITLE.title_code NOT BETWEEN 135 and 138 -- SAFE Products *** this was the new code ***

No need for second line just make it

AND TITLE.title_code NOT BETWEEN 123 and 138 -- SAFE products

Is this a large table

(SELECT * FROM ORDER_DEAL WHERE product_id = 'P' AND product_sub_code = 123)

Because you could think about writing this to a temp table and then referencing it in the actual query