SQL Query takes lot of time

[rak4u]

Hi evrybody,

My sql SP takes around two to three minutes which is quite long time to execute
the SP select data from three tables publicity(29000 row) publicitysubject(29000 row) & lookuptable(50 rows)

all three tables have clustered index How can i reduce the time

USE [ReadWorthyPublication]
GO
/****** Object:  StoredProcedure [dbo].[RW_SP_GET_PUBLICITY_DETAIL]    Script Date: 06/29/2009 15:03:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




CREATE PROC [dbo].[RW_SP_GET_PUBLICITY_DETAIL]
	(
		@vParty_Name VARCHAR(200) = NULL,
		@iCategory_ID INT= NULL,
		@vCity VARCHAR(100) = NULL,
		@iCountry_ID INT = NULL,
		@iSubject_ID INT = NULL,
		@iPublicity_ID INT = NULL,
		@vAddress VARCHAR(200) = NULL,
		@bActive BIT = NULL

	)
AS
BEGIN
	BEGIN TRY
		SELECT  PT.Publicity_ID,
				PT.Party_Name,
				LT.Lookup_ID [Publicity_Category_ID],
				LT.Lookup_Item [Publicity_Category_Type],
				CT.Country_ID,
				CT.Country_Name,
				PT.Address,
				PT.City,
				PT.Phone,
				PT.Fax,
				PT.Active,
				PT.Web_Address,
				PT.Email,
				PST.Subject_ID
				
		FROM [dbo].[Publicity_Table] PT WITH (NOLOCK)
			INNER JOIN [dbo].[Country_Table] CT WITH (NOLOCK)
			ON PT.Country_ID = CT.Country_ID
				INNER JOIN [dbo].[Lookup_Table] LT WITH (NOLOCK)
				ON PT.Category_Type_ID = LT.Lookup_ID
					INNER JOIN [dbo].[PublicitySubject_Table] PST WITH (NOLOCK)
					ON PT.Publicity_ID = PST.Publicity_ID
		WHERE ((@vParty_Name IS NULL) OR (PT.Party_Name LIKE '%'+@vParty_Name+'%'))
			AND ((@iCategory_ID IS NULL) OR (PT.Category_Type_ID = @iCategory_ID))
			AND ((@vCity IS NULL) OR (PT.City LIKE '%' + @vCity + '%'))
			AND ((@iCountry_ID IS NULL) OR (PT.Country_ID = @iCountry_ID))
		
			AND ((@iSubject_ID IS NULL) OR (PST.Subject_ID = @iSubject_ID))
			AND ((@iPublicity_ID IS NULL) OR (PT.Publicity_ID = @iPublicity_ID))
			AND ((@vAddress IS NULL) OR (PT.Address LIKE '%' + @vAddress + '%'))
			AND ((@bActive IS NULL) OR (PT.Active = @bActive))
		Order By PT.Publicity_ID
			
				
	END TRY
	BEGIN CATCH
		EXEC dbo.RW_SP_GET_LOG_ERROR_INFO 'Expense_Table'
	END CATCH
END

[sknake]

Just because an index is clustered doesn't necessarily mean it is the best index to use. If you store a large amount of data in the tables, especially if you use varchar(max), text, or image data types, then you should probably create another nonclustered index on the joining identifiers. A clustered index will cover blob data and make the row seeking perform very slowly.

You should run the sprocs as TSQL and have the SQL Server Management Studio output an execution plan. CTLR+M = "Display actual execution plan" then save the execution plan, upload it, and i'll take a look at it.

[rak4u]

thanks sknake,

I am attaching the execution plan

[rak4u]

write some part of execution plan

1)Select cost 0%
2)Merge Join(inner join) cost 0%
3)Nested Loop(inner join) cost 11%
4)Sort 1%
5)Hash Match(inner join) 13%
6)Clustered index publicity cost 67%
7)clustered index publicitysubject 13%
8)clustered index lookup 0%
clustred index country 0%

[sknake]

Please ATTACH the execution plan as a file and also post the create table () statements for your tables with the indexes. This doesn't look like a complicated fix but I'm missing information.