Going after repeat offenders in SP

Issue is a batch processing SP that does multiple inserts (6 tables)
This statement contains a "repeat offending" NOT IN ( ) clause.
-- start code
SELECT
@.BatchID, MA.Merchant_Account_ID, 0, 0, @.Batch_Date, 0, 0
FROM Merchant_Account MA
WHERE Merchant_Account_ID NOT IN (SELECT El_Account_ID FROM
Operating_Account_Summary WHERE Transaction_Batch_ID=@.BatchID)
ORDER BY Merchant_Account_ID
Would it be better off to do that selection one time into a #T1 and
reference that all the needed times, finally dropping it at the end? Of
course I'd create it outside of the transaction and drop it likewise.
Table has 1/2 million rows now and growing @. 10,000 per w.
TIAIs it that the rows generated by this SELECT are to be inserted into
multiple tables? If so, it would make sense to populate a temp table and
then use it. I'm curious as to why these same rows have to be inserted into
multiple tables. I sit possible to use one table and reference it?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"_Stephen" <srussell@.electracash.com> wrote in message
news:uIH3csTmGHA.492@.TK2MSFTNGP05.phx.gbl...
Issue is a batch processing SP that does multiple inserts (6 tables)
This statement contains a "repeat offending" NOT IN ( ) clause.
-- start code
SELECT
@.BatchID, MA.Merchant_Account_ID, 0, 0, @.Batch_Date, 0, 0
FROM Merchant_Account MA
WHERE Merchant_Account_ID NOT IN (SELECT El_Account_ID FROM
Operating_Account_Summary WHERE Transaction_Batch_ID=@.BatchID)
ORDER BY Merchant_Account_ID
Would it be better off to do that selection one time into a #T1 and
reference that all the needed times, finally dropping it at the end? Of
course I'd create it outside of the transaction and drop it likewise.
Table has 1/2 million rows now and growing @. 10,000 per w.
TIA|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:e4con5TmGHA.4512@.TK2MSFTNGP04.phx.gbl...
> Is it that the rows generated by this SELECT are to be inserted into
> multiple tables? If so, it would make sense to populate a temp table and
> then use it. I'm curious as to why these same rows have to be inserted
> into
> multiple tables. I sit possible to use one table and reference it?
Thanks for the reply.
It will insert different data table depending, but it's initial where is the
key not in (select key from other table)
So I will refactor this and other sp's that follow the same sense of
nonsense. Actually it's a refactor of an app that only needed to do this
once, but the new and better data layout needs the same call a few more
times.