Slow Stored Procedure using ROWCOUNT

[anorthcote]

I have the following stored procedure

CREATE PROCEDURE Test

	@LocationID int,
	@StatusID int, 
	@Area int,
	@Contractor int,
	@EnteredBy	Varchar(20)
AS
BEGIN

		SET ROWCOUNT 30



		SELECT     dbo.Calls.LogID
FROM         dbo.Contractor INNER JOIN
                      dbo.ContractorOrder ON dbo.Contractor.ID = dbo.ContractorOrder.ContractorID RIGHT OUTER JOIN
                      dbo.CallStatus INNER JOIN
                      dbo.Calls ON dbo.CallStatus.ID = dbo.Calls.StatusID LEFT OUTER JOIN
                      dbo.Location ON dbo.Calls.LocationID = dbo.Location.ID LEFT OUTER JOIN
                      dbo.Area ON dbo.Location.AreaID = dbo.Area.ID ON dbo.ContractorOrder.OrderID = dbo.Calls.OrderID
where (@LocationID = 0  OR LocationID  = @LocationID) AND
      (@StatusID   = 0  OR StatusID    = @StatusID)   AND
      (@area       = 0  OR cast(substring(dbo.Area.Name,6,2) as int)  = @area) AND
      (@Contractor = 0  OR ContractorOrder.ContractorID = @Contractor) AND
      (@EnteredBy = ''  OR Calls.EnteredBy = @EnteredBy)

order by LogID Desc


end
GO

I run it in query analyzer using

exec test 0,0,0,0,''

this will return all records and works ok. If I substitute a value for any of the parameters the procedure will filter by the appropriate value. This, again, works fine for all but @Contractor.

If I put a value in for @Contractor the procedure goes from running in 1 second to over a minute. I have found that if I take out the ROWCOUNT statement it runs quickly again.

Why is the ROWCOUNT adding so much processing time to the procedure and only for @Contractor?

 

[anorthcote]

Worked it out!!!

I added a new index to ContractorOrder and it's processing time went down from 6 minutes 16 seconds to under 1 second!!

 

[Crowley16]

the reason why a rowcount clause reduces your processing time in the first place is because rowcount stops processing records once 30 are returned.

Therefore, it's likely when you put the @contractor in, the sproc is scanning the contractor table, which is big and not indexed well.

--------------------
Procrastinate Now!

 

[anorthcote]

I see you logic but the rowcount clause was actually incresing the processing time, that is why I was confused.

I have now learnt the importance of indexing

 

[Crowley16]

ahh, yeah, that's cos you've got the order by clause.

means the query has to read in all the values in the table to find out the biggest/smallest before it can order them...

--------------------
Procrastinate Now!