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