Urgen query problem

[xloop]

Very urgent problem..

I have a SP that is suddenly timing out;




CREATE PROCEDURE sp_SearchStock

@ClientName VarChar(50),
@ItemStyle VarChar(50),
@SerialNo VarChar(50),
@StatusID VarChar(5)

AS
DECLARE @ClientID int
SELECT @ClientID = (SELECT ClientID FROM tblClients WHERE ClientName = @ClientName)
SET NOCOUNT ON
SELECT dbo.tblItemStyles.ItemStyle, dbo.tblItemStyleStatus.StyleStatus,tblLocations.Location, dbo.tblStockDetails.SerialNo,
COUNT(dbo.tblStockDetails.StockID) AS Qty, dbo.tblRaiseItems.RaiseItem AS AssignedAgainst, Assigned =
CASE dbo.tblRaiseItems.RaiseItem
WHEN 'Client' THEN @ClientName
WHEN 'Project' THEN (SELECT ProjectName FROM tblProjects WHERE ProjectID = dbo.tblStockDetails.AssignedID)
WHEN 'Job' THEN CAST(dbo.tblStockDetails.AssignedID as VARCHAR(50))
WHEN 'Task' THEN (SELECT dbo.tblTaskNames.TaskName COLLATE Latin1_General_CI_AS AS TName FROM dbo.tblTasks LEFT OUTER JOIN dbo.tblTaskNames ON dbo.tblTasks.TaskID = dbo.tblTaskNames.TaskID WHERE (dbo.tblTasks.ID = dbo.tblStockDetails.AssignedID))
END

FROM dbo.tblStockDetails INNER JOIN
tblLocations ON tblStockDetails.CurrentLocID = tblLocations.LocationID INNER JOIN
dbo.tblRaiseItems ON dbo.tblStockDetails.AssignedAgainstID = dbo.tblRaiseItems.RaiseAgainstID INNER JOIN
dbo.tblItemStyles ON dbo.tblStockDetails.StyleID = dbo.tblItemStyles.StyleID INNER JOIN
dbo.tblItemStyleStatus ON dbo.tblStockDetails.StatusID = dbo.tblItemStyleStatus.StyleStatusID
GROUP BY dbo.tblStockDetails.ClientID, tblLocations.Location, dbo.tblStockDetails.SerialNo, dbo.tblStockDetails.StatusID, dbo.tblItemStyleStatus.StyleStatus, dbo.tblStockDetails.AssignedID, dbo.tblRaiseItems.RaiseItem, dbo.tblItemStyles.ItemStyle
HAVING (dbo.tblStockDetails.ClientID=@ClientID) AND (dbo.tblItemStyles.ItemStyle Like '%' + @ItemStyle + '%') AND (dbo.tblStockDetails.SerialNo Like '%' + @SerialNo +'%') AND (CAST (dbo.tblStockDetails.StatusID as VarChar(5)) Like @StatusID +'%')
ORDER BY tblItemStyles.ItemStyle

GO

Can this be speeded up at all???

tblStockDetails has 26,000 records, one clustered index.

I've recompiled, updated statistics..

Any ideas???

Many thanks
Rob

 

[TomKane]

A couple of things you should check:

maybe you're not licensed for enough connections something like that?

SQl's query profiler can give you some idea of what's going on in the system while you're trying to run your query - perhaps there are other queries/processes that are taking precedence over yours?

Maybe there is a resource issue - Is the query being blocked by another process? You can check this in enterprise manager using the current activity screen - If you want to know more about the current activity screen there i've got a web page for you:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_6ofn.asp

Has there been any change to the client/server setup? Are you connecting to your datbase using the server name or the ip address? Possibily a Winsserver issue?

If you're using the server name try using the ip address to see if there is any difference

On your PC is the IP address of your server in your hosts file? (assuming NT here)

I know it's a sort of "try it and see" answer, but these are things we've had to check as recently as Monday, so you never know....

Best of luck...

 

[tlbroadbent]

Change the HAVING clause to a WHERE and move it to the line before the GROUP BY clause. HAVING critereia are applied after the GROUP BY occurs. WHERE criteria will be applied before the grouping occurs. This will reduce the number of rows processed by the GROUP BY aggregation.

Is the tblStockDetails Clustered index on the ClientID column? ClientID should be indexed to get good performance from the query.

Do the other tables have indexes in the columns used to JOIN to other tables? The columns used in the JOIN statements should be indexed.

The tables used in the sub queries should be indexed on the columns used in the WHERE clause. For example, is tblProjects indexed on ProjectID?

Is the CAST function required on this code?

(CAST (sd.StatusID as VarChar(5)) Like @StatusID +'%')

Using CAST prevents SQL from using any indexes when searching. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.