I have a stored procedure that creates and populates temp tables, selects,
and groups records, returning desired recordset. When running sp on a small
dataset, it takes about 2 sec. On the large dataset, about 700,000 records
in the main table, it takes about 300 secs (5min) which is not acceptable.
To optimize a query, I have parsed the stored procedure into separate
select/insert statements. After all optimizations, using literals instead of
variables, I was able to cut a 5 min run to 2 seconds. Unfortunately, once I
plug my select/insert statements back to sp, or I change literal to
variable, I'm back to the 5 min.
query runs much faster
INSERT INTO #temp_trx
FROM B, A
AND A.COMPANY_CODE = B.COMPANY_CODE
Any help is greatly appreciated.