SQL Select statement vs sp AND literals vs variables

SQL Select statement vs sp AND literals vs variables

Post by Yan Y » Thu, 24 Dec 1998 04:00:00



Hi,

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
SELECT
   ...
FROM B, A

AND A.COMPANY_CODE = B.COMPANY_CODE
...

Any help is greatly appreciated.

Thanks,

Yan.