Very Slow SP with TempTables

[CrystalDuck]

Well, I can't seem to figure out why this works this way, but I will try to explain.
I have data out of 4 different tables that I need to pull together. I have split them up into invidual temp tables, because when I try to pull it into one temp table it takes well over an hour. (And even then, I don't now if it would really work, because I keep stopping it to try something else.)

DROP TABLE #FUND_INFO
CREATE TABLE #FUND_INFO(
FUND_NO			int,
FUND_NAME		varchar(50),
CUSTOMER_ACCT_NO	char(10),
SHARE_PRICE		float,
SHARE_AMT		float,
DOLLAR_AMT		float,
SUPER_SHEET_DATE 	char(10),
TRADE_DATE		char(10),
TRAN_CODE		char(3),
TRAN_SUFFIX_CODE	char(3),
TRAN_DESC		varchar(40),
ACCT_FROM_TO_NO		char(10),
FUND_FROM_NO		int,
DEALER_NO		char(7),
ORDER_NO		int,
REP_ID			char(9)
)

DROP TABLE #REP_INFO
CREATE TABLE #REP_INFO(
REP_ID			char(9),
REP_NAME		char(105),
FIRST_NAME		char(35),
MIDDLE_NAME		char(35),
LAST_NAME		char(35))

DROP TABLE #DEALER_INFO
CREATE TABLE #DEALER_INFO(
DEALER_NO		char(7),
DEALER_NAME1		char(35),
DEALER_NAME2		char(35))

DROP TABLE #NONFINANCIAL_INFO
CREATE TABLE #NONFINANCIAL_INFO(
CUSTOMER_ACCT_NO	char(10),
FUND_NO			int,
REGISTR_LINE_1		varchar(35),
REGISTR_LINE_2		varchar(35),
REGISTR_LINE_3		varchar(35),
REGISTR_LINE_4		varchar(35),
REGISTR_LINE_5		varchar(35),	
REGISTR_LINE_6		varchar(35),
REGISTR_LINE_7		varchar(35))

		INSERT INTO #FUND_INFO
		SELECT 	
			A.FUND_NO,
			FM.FUND_NAME,
			A.CUSTOMER_ACCT_NO,
			A.SHARE_PRICE_AMT,
			sum(A.SHARE_TRAN_AMT),
			(sum(A.SHARE_TRAN_AMT)* A.SHARE_PRICE_AMT),
			convert(char(10),A.SUPER_SHEET_DATE,101),
			convert(char(10),A.TRADE_DATE,101),
			A.TRAN_CODE,
			A.TRAN_SUFFIX_CODE,
			TD.TRAN_DESC,
			A.ACCT_FROM_TO_NO,
			A.FUND_FROM_NO,
			A.DEALER_NO,
			A.ORDER_NO,
			A.REP_ID
						
		FROM	TA_ACTIVITY.dbo.TB_ACTIVITY A
			INNER JOIN TA_FUND.dbo.TB_FUND_MASTER FM
			ON A.FUND_NO = FM.FUND_NO
			RIGHT OUTER JOIN CORP_REF..TB_TRAN_CODE_DESC TD
			ON A.TRAN_CODE = TD.TRAN_CODE
			AND A.TRAN_SUFFIX_CODE = TD.TRAN_SUFFIX_CODE
	
		GROUP BY A.FUND_NO, 
			 FM.FUND_NAME,
			 A.CUSTOMER_ACCT_NO, 
			 A.SHARE_PRICE_AMT,
			 A.SUPER_SHEET_DATE,
			 A.TRADE_DATE,
			 A.TRAN_CODE,
			 A.TRAN_SUFFIX_CODE,
			 A.ACCT_FROM_TO_NO,
			 A.FUND_FROM_NO,
			 A.DEALER_NO,
			 A.ORDER_NO,
			 A.REP_ID,
			 TD.TRAN_DESC
		HAVING 	(sum(A.SHARE_TRAN_AMT)* A.SHARE_PRICE_AMT) >= 1000000.00
			OR (sum(A.SHARE_TRAN_AMT)* A.SHARE_PRICE_AMT) <= -1000000.00

		INSERT INTO #NONFINANCIAL_INFO
		SELECT 
			CUSTOMER_ACCT_NO, 
			FUND_NO,
			REGISTR_LINE_1,
			REGISTR_LINE_2,
			REGISTR_LINE_3,
			REGISTR_LINE_4,
			REGISTR_LINE_5,
			REGISTR_LINE_6,
			REGISTR_LINE_7
		FROM 	TA_ACCOUNT.dbo.TB_ACCT_NONFINANCIAL 
		WHERE 	CUSTOMER_ACCT_NO in (Select CUSTOMER_ACCT_NO FROM #FUND_INFO)
			AND FUND_NO in (Select FUND_NO FROM #FUND_INFO)
		
		INSERT INTO #DEALER_INFO
		SELECT 
			D.DEALER_NO,
			D.DEALER_NAME1,
			D.DEALER_NAME2
		FROM 	TA_DBR.dbo.TB_DEALER D	
		WHERE 	D.DEALER_NO in (SELECT DEALER_NO FROM #FUND_INFO)
		

		INSERT INTO #REP_INFO
		SELECT 
			R.REP_ID,
			(R.FIRST_NAME+' '+R.MIDDLE_NAME+' '+R.LAST_NAME) REP_NAME,
			R.FIRST_NAME,
			R.MIDDLE_NAME,
			R.LAST_NAME
		FROM 	TA_DBR.dbo.TB_REP R	
		WHERE 	R.REP_ID in (SELECT REP_ID FROM #FUND_INFO)

This code only takes 8 minutes to pull all the data I need into the SP...but I still need them all in one final results temp table.
So I tried this:

SELECT DISTINCT 
F.FUND_NO,
F.FUND_NAME,
F.CUSTOMER_ACCT_NO,
F.SHARE_PRICE,
F.SHARE_AMT,
F.DOLLAR_AMT,
F.SUPER_SHEET_DATE,
F.TRADE_DATE,
F.TRAN_CODE,
F.TRAN_SUFFIX_CODE,
F.TRAN_DESC,
F.ACCT_FROM_TO_NO,
F.FUND_FROM_NO,
F.DEALER_NO,
F.ORDER_NO,
F.REP_ID,
R.REP_NAME,
R.FIRST_NAME,
R.MIDDLE_NAME,
R.LAST_NAME,
D.DEALER_NO,
D.DEALER_NAME1,
D.DEALER_NAME2,
NF.CUSTOMER_ACCT_NO,
NF.FUND_NO,
NF.REGISTR_LINE_1,
NF.REGISTR_LINE_2,
NF.REGISTR_LINE_3,
NF.REGISTR_LINE_4,
NF.REGISTR_LINE_5,	
NF.REGISTR_LINE_6,
NF.REGISTR_LINE_7
 
FROM #FUND_INFO F 
RIGHT OUTER JOIN #REP_INFO R 
ON F.REP_ID = R.REP_ID
RIGHT OUTER JOIN #DEALER_INFO D 
ON F.DEALER_NO = D.DEALER_NO
RIGHT OUTER JOIN #NONFINANCIAL_INFO NF 
ON F.FUND_NO = NF.FUND_NO 
AND F.CUSTOMER_ACCT_NO = NF.CUSTOMER_ACCT_NO

But this still takes over an hour to run. What am I doing wrong? This seems so simple. Why would it take so long to run?

Any help is greatly appreciated.
Thank you!!
CrystalDuck

 

[mrdenny]

Create indexes on your temp tables of the columns that you are doing your join on. That should speed things up a little bit.

Each time you add a table to a join, the amound of overhead that needs to go into processing the data goes up. The more data the more overhead as well.

Indexes should help out.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief

 

[CrystalDuck]

Thank you for the quick response, MrDenny...I kinda figured that's what it would take...but I am very unfamiliar with creating indexes. I even went looking Indexes up on BOL...but don't have a good enough example to go by.

I know this makes me look "tres stupid" but any help on how to get started with that syntax would be greatly appreciated.

Thank you,
CrystalDuck

 

[DrSql]

Here are some samples, you need to execute after creating the table. The best practice is to create indexes on PK and filtered fields

[tt]
CREATE INDEX PK_FUND_NO_ind
ON #FUND_INFO (FUND_NO)
go

Go
CREATE INDEX PK_REP_ID_ind
ON #REP_INFO (REP_ID)
go
Go
CREATE INDEX PK_CUSTOMER_ACCT_NO_ind
ON #NONFINANCIAL_INFO (REP_ID)
go
Go
CREATE INDEX PK_FUND_NO_ind
ON #NONFINANCIAL_INFO (FUND_NO)
go
[/tt]

Dr.Sql
Good Luck.

 

[CrystalDuck]

Thank you so much...it's amazing the speed at which it goes now. *wow* Big difference!

Thank you both so much for your help.

Sincerely,
CrystalDuck

 

[mrdenny]

no problem.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief