Re: Newbie question; Why is my procedure slow when a parameter is used?
From: Tom Moreau (tom_at_dont.spam.me.cips.ca)
Date: 03/12/04
- Next message: JT Lovell: "Re: Matching on a subquery with multiple columns"
- Previous message: Scott Morris: "Re: SQL Server Community Survey"
- In reply to: Paul: "RE: Newbie question; Why is my procedure slow when a parameter is used?"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 12 Mar 2004 16:10:12 -0500
Just looking at your code, I see that there is an implicit conversion to an
INNER JOIN, since your WHERE clause refers to columns in the unpreserved
table - dbo.gltrans. If you truly want rows where the WHERE condition is
true, then change the RIGHT JOIN to a JOIN. However, if you want rows from
the preserved table plus rows from the unpreserved table - if any - change
your code as shown below:
SELECT dbo.gltrans.tr_d_narrative, dbo.gltrans.tr_vouch_no,
dbo.dltransd.dtd_line, dbo.dltransd.dtd_item,
dbo.dltransd.dtd_text, dbo.dltransd.dtd_ext_desc,
dbo.dltransd.dtd_qty, dbo.dltransd.dtd_price,
dbo.dltransd.dtd_line_total, dbo.dltransd.dtd_vat_code,
dbo.dltransd.dtd_vat_amount, dbo.gltrans.tr_batch
FROM dbo.gltrans JOIN
dbo.dltransd ON
dbo.gltrans.tr_vouch_no = dbo.dltransd.dtd_voucher
AND dbo.gltrans.tr_d_narrative = @Enter_Invoice_Number /* '90086670' */
--
Tom
---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Paul" <anonymous@discussions.microsoft.com> wrote in message
news:DB7EE5B4-5B34-4DB3-951A-B8FDC14CCCE0@microsoft.com...
Thanks Dragan, forcing the index has worked and it is now running at an
acceptable speed.
New version of the procedure below:
Alter Procedure prInvDet
(@Enter_Invoice_Number varchar(30))
As
SELECT dbo.gltrans.tr_d_narrative, dbo.gltrans.tr_vouch_no,
dbo.dltransd.dtd_line, dbo.dltransd.dtd_item,
dbo.dltransd.dtd_text, dbo.dltransd.dtd_ext_desc,
dbo.dltransd.dtd_qty, dbo.dltransd.dtd_price,
dbo.dltransd.dtd_line_total, dbo.dltransd.dtd_vat_code,
dbo.dltransd.dtd_vat_amount, dbo.gltrans.tr_batch
FROM dbo.gltrans WITH (INDEX(IX_GLTRANS_INV) ) right outer JOIN
dbo.dltransd ON
dbo.gltrans.tr_vouch_no = dbo.dltransd.dtd_voucher
WHERE dbo.gltrans.tr_d_narrative = @Enter_Invoice_Number /* '90086670' */
set nocount on
/* set nocount on */
RETURN
Many thanks to all who replied,
Paul.
- Next message: JT Lovell: "Re: Matching on a subquery with multiple columns"
- Previous message: Scott Morris: "Re: SQL Server Community Survey"
- In reply to: Paul: "RE: Newbie question; Why is my procedure slow when a parameter is used?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
