SQL Query takes lot of time
 

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

Re: SQL Query takes lot of time
 

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.

Re: SQL Query takes lot of time
 

thanks sknake,

I am attaching the execution plan

Re: SQL Query takes lot of time
 

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%

Re: SQL Query takes lot of time
 

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.