indexes on my sproc - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

indexes on my sproc

Author

Topic

PGG123
Yak Posting Veteran

55 Posts

Posted - 2004-07-02 : 12:08:13

Here's my sproc:
SET NOCOUNT ON
SELECT DISTINCT T.txtAccountNum, T.dtePostDate, T.dteCycleDate, T.dteTransacDate, T.txtMerchantName, T.txtSourceCurr,
T.txtBillingCurr, T.txtRefNum, T.txtSic, T.SICGroupID, T.curForeignAmnt, T.curTransacAmnt, T.txtTransacCode, T.txtMerchantCity,
T.txtMerchantProv, T.txtMerchantCountry, T.txtMemoFlag, T.txtCostCenter, T.lngParent, T.curOrigAmnt, T.curGst, T.curPst,
T.curNetAmnt, T.txtUseDef1, T.txtComments, T.txtCompanyNum, T.txtInternetNum, T.txtAirChunk, T.txtPurchaseID, T.lngIndex

FROM dbo.Transactions T

INNER JOIN dbo.Account A ON T.txtAccountNum = A.txtAccountNum

WHERE T.txtAccountNum IN
(
(SELECT DISTINCT A.txtAccountNum
FROM dbo.ApprovalGroup As AG
INNER JOIN dbo.UserGroups AS UG
ON AG.txtInternetNum = UG.txtInternetNum
AND AG.intViewGroup = UG.lngGroupNum
INNER JOIN dbo.Account AS A ON UG.txtAccountNum = A.txtAccountNum
AND UG.txtInternetNum = A.txtInternetCompNum
WHERE (AG.intViewGroup = @user)
AND (AG.txtInternetNum = @icn))
)

AND T.dteCycleDate = @cycle AND T.txtInternetNum = @icn
--AND A.txtCompanyNum = @comp
--ORDER BY T.txtAccountNum, lngUniqueID
ORDER BY T.txtAccountNum, T.lngIndex
SET NOCOUNT OFF

What indexes should I have to make this perform faster? Thanks.

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-02 : 12:20:10

First, what are the size of the columns?

Second, you're accessing account twice...

I would remove it from the IN and make the IN an inner join

What are the indexes you have now...and why distinct?

Brett

8-)

EDIT: PS. YOu don't index sprocs

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-07-02 : 12:27:23

Derived table may help.

SELECT DISTINCT T.txtAccountNum, T.dtePostDate, T.dteCycleDate, T.dteTransacDate, T.txtMerchantName, T.txtSourceCurr, 
T.txtBillingCurr, T.txtRefNum, T.txtSic, T.SICGroupID, T.curForeignAmnt, T.curTransacAmnt, T.txtTransacCode, T.txtMerchantCity, 
T.txtMerchantProv, T.txtMerchantCountry, T.txtMemoFlag, T.txtCostCenter, T.lngParent, T.curOrigAmnt, T.curGst, T.curPst, 
T.curNetAmnt, T.txtUseDef1, T.txtComments, T.txtCompanyNum, T.txtInternetNum, T.txtAirChunk, T.txtPurchaseID, T.lngIndex

FROM dbo.Transactions T

INNER JOIN dbo.Account A ON T.txtAccountNum = A.txtAccountNum

INNER JOIN

--WHERE T.txtAccountNum IN
(
	(SELECT 		A.txtAccountNum
	FROM 		dbo.ApprovalGroup As AG  INNER JOIN dbo.UserGroups AS UG 
			  ON AG.txtInternetNum = UG.txtInternetNum 
			  AND AG.intViewGroup = UG.lngGroupNum 
			INNER JOIN dbo.Account AS A 
			  ON UG.txtAccountNum = A.txtAccountNum
			  AND UG.txtInternetNum = A.txtInternetCompNum 

	WHERE 		(AG.intViewGroup = @user) 
	AND 		(AG.txtInternetNum = @icn))
) x

ON x.txtAccountNum = A.txtAccountNum

WHERE T.dteCycleDate = @cycle AND T.txtInternetNum = @icn
--AND A.txtCompanyNum = @comp
--ORDER BY T.txtAccountNum, lngUniqueID
ORDER BY T.txtAccountNum, T.lngIndex

I also think that subquery may be able to be coded into the main query somehow, but without having tables and data to test I can't say for sure.

Index on the join fields for sure.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources