February 13, 2007 at 3:57 pm
I have a sproc with a multitude of joins bringing back a unique assembly, packaging, routing, etc.. type of information. This is used for creating new Barcodes.
The last two days I have received calls about barcode printing timeout errors. I test the script (with appropriate variables populated) and the script runs in < 1 second. I run the stored procedure and the performance is > minute, same target values.
Looking through the execution plan I noticed both days the sproc was doing a right outer join where the script was running a left outer join. I used this descrepancy to determine which of the 8 table indexes to tweak. Which restored runtime to < 1 sec.
I went with tweaking indexes instead of default-populating the sproc variables. Is there different areas I should be looking at?
Looking for fresh ideas, and a possible explaination.
thanks
February 14, 2007 at 9:17 am
OUTER JOIN default to a null so the table position is fixed if the execution plan is showing right instead of left, then SQL Server 2005 thinks your table position in the ON clause was wrong. Index is not relevant because OUTER JOIN fixed table position does not leave room for taking advantage of indexes as INNER JOIN. You could look into using the AND operator to add more search conditions. Run the code and click on show execution cost so you know what to take out and adjust as needed. Hope this helps.
Kind regards,
Gift Peddie
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply