question

Hi I have a SP to retrieve images from database tblSearchImages have over 2.25 million Records. My issue when execute SP using search string like "gentle man" or "Jackie Chan" then What is happening is that the first time a search string is introduced there is a delay before the data is returned. Then any time the same search string is entered the results return with no delay. I want to avoid those delay from retrieve images or Any suggestion on Tuning this SP? I have attached code of SP,Table, FULL Execution plan .......Quick reply appreciated ![alt text][1] SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[uspOpenGateConnectorSearch] ( @PageToDisplay INT = 1 , @ImagesPerPage INT = 100 , @AndSearchString VARCHAR(200) = '"Jackie Chan"' , @OrSearchString VARCHAR(200) = 'Yao' , @NotSearchString VARCHAR(200) = 'New York' , @StringDelimiter AS VARCHAR(1) = '|' , @StartDate VARCHAR(10) = '1900/01/01' , @EndDate VARCHAR(10) = '2015/01/01' , @CategoryID INT = 1 , @SortID INT = 4 , @Wild INT = 0 , @Format INT = 0 , @UserID INT = 6530 , @CustomerPK INT = 2506 , @UserTypeID AS INT = 2 , @CountryCode AS VARCHAR(3) = 'USA' , @IpAddress AS VARCHAR(15) = '102.123.123.241' , @UserAgent AS VARCHAR(1000) = 'SSMS' ) AS --SET NOCOUNT ON DECLARE @TotalRecordsFound INT DECLARE @IndexStart INT DECLARE @IndexEnd INT DECLARE @CompleteSearchString VARCHAR(600) DECLARE @FormatString AS VARCHAR(60) = '' DECLARE @StartDateInt AS INT DECLARE @EndDateInt AS INT DECLARE @SearchCount AS INT SELECT @StartDateInt = dbo.DateToInt(@StartDate) SELECT @EndDateInt = dbo.DateToInt(@EndDate) SELECT @AndSearchString = dbo.udfParseSearchString(@AndSearchString, RTRIM(@StringDelimiter), 2, @Wild) -- If OR Search string was passed in IF @OrSearchString <> '' BEGIN SELECT @OrSearchString = ' OR ' + dbo.udfParseSearchString(@OrSearchString, RTRIM(@StringDelimiter), 3, @Wild) END IF @NotSearchString <> '' BEGIN SELECT @NotSearchString = ' AND NOT ' + dbo.udfParseSearchString(@NotSearchString, RTRIM(@StringDelimiter), 4, @Wild) END --Combine the search strings SELECT @CompleteSearchString = @AndSearchString + @OrSearchString + @NotSearchString SELECT @CompleteSearchString = REPLACE(@CompleteSearchString, '_', ' ') -- Set up format refinement string (orientation of images) DECLARE @FormatString AS VARCHAR(60) = '' SELECT @FormatString = CASE @Format WHEN 1 THEN '1' WHEN 2 THEN '2' WHEN 3 THEN '3' ELSE '1, 2, 3' END SELECT @SearchCount = ( SELECT COUNT(*) FROM CONTAINSTABLE(DB_Lightbox_Images.dbo.tblSearchImages, ( CapKey ), @CompleteSearchString, 100000) ) -- Create first temp table to hold the IDs found by search with no restrictions. CREATE TABLE #TempTable1 ( ID INT IDENTITY(1, 1) , SearchImage_FK INT ) -- Create indexes CREATE CLUSTERED INDEX IDX_C_ID ON #TempTable1(ID) CREATE INDEX IDX_Temp_OCI_PK ON #TempTable1(SearchImage_FK) INSERT INTO #TempTable1(SearchImage_FK) SELECT fti.[Key] FROM CONTAINSTABLE(DB_Lightbox_Images.dbo.tblSearchImages, ( CapKey ), @CompleteSearchString, 5000) AS fti --Create second temp table. This one will hold the IDs of the found records --after any restrected records have been removed. CREATE TABLE #TempTable2 ( ID INT IDENTITY(1, 1) , SearchImage_FK INT ) --Create Indexes CREATE CLUSTERED INDEX IDX_C_ID ON #TempTable2(ID) CREATE INDEX IDX_Temp_OCI_PK ON #TempTable2(SearchImage_FK) INSERT INTO #TempTable2 ( SearchImage_FK ) SELECT TOP 2500 tsi.SearchImage_PK FROM DB_Lightbox_Images.dbo.tblSearchImages AS tsi INNER JOIN #TempTable1 AS tt ON tsi.SearchImage_PK = tt.SearchImage_FK WHERE tsi.AllowWeb = 1 AND tsi.Accept = 1 AND tsi.Orient IN ( SELECT [value] FROM dbo.utvfSplitDelimitedString(@FormatString, ',') ) AND tsi.FotogID NOT IN ( SELECT ugbc.StudioCode FROM dbo.utvfGetBlockedCollections(@CustomerPK, @UserTypeID, @CountryCode, @IpAddress) AS ugbc ) AND tsi.LBDImages_FK NOT IN ( SELECT ugfi.ImagePK FROM dbo.utvfGetFilteredImages(@CustomerPK) AS ugfi ) AND tsi.ArchiveDate BETWEEN @StartDateInt AND @EndDateInt ORDER BY tsi.movie DESC, CASE WHEN @SortID = 1 THEN tsi.ArchiveDate END DESC -- , CASE WHEN @SortID = 2 THEN tck.Caption --END -- , CASE WHEN @SortID = 3 THEN tck.Caption --END DESC , CASE WHEN @SortID = 4 THEN tsi.ArchiveDate END DESC , CASE WHEN @SortID = 5 THEN tsi.ArchiveDate END , CASE WHEN @SortID = 6 THEN tsi.ImageID END , CASE WHEN @SortID = 7 THEN tsi.ImageID END DESC --get the total returned records for the above query. SELECT @TotalRecordsFound = @@ROWCOUNT IF @TotalRecordsFound >= 2500 BEGIN SELECT @TotalRecordsFound = @SearchCount END --Set up the paging indexes from the images per page and the page to display SET @IndexStart = ( ( @ImagesPerPage * @PageToDisplay ) - @ImagesPerPage ) + 1 SET @IndexEnd = ( @ImagesPerPage * @PageToDisplay ) -- Get the data needed to be returned to the calling application SELECT tli.SearchImage_PK AS ImageID , tli.HRX , tli.HRY , dbo.IntToDate(tli.ArchiveDate) AS ArchiveDate1 , tli.CreateDate FROM #TempTable2 t INNER JOIN DB_Lightbox_Images.dbo.tblSearchImages AS tli ON t.SearchImage_FK = tli.SearchImage_PK WHERE ( t.ID >= @IndexStart ) AND ( t.ID <= @IndexEnd ) --Clean up the temp tables DROP TABLE #TempTable1 DROP TABLE #TempTable2 --Log the search into the tracking table EXEC dbo.uspPutSearchDetails @AndSearchString, @OrSearchString, @NotSearchString, @CategoryID, @SortID, @Wild, 1, @PageToDisplay, @ImagesPerPage, @TotalRecordsFound, @UserID, @CustomerPK, @UserTypeID, @CountryCode, @IpAddress, @UserAgent ---- Return the total records RETURN @TotalRecordsFound ----------------------------------------Code for table-------------------- USE [DB_Lightbox_Images] GO /****** Object: Table [dbo].[tblSearchImages] / SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[tblSearchImages]( [SearchImage_PK] [int] IDENTITY(1,1) NOT NULL, [LBDImages_FK] [int] NOT NULL, [CapKey] [varchar](max) COLLATE Latin1_General_CI_AI NOT NULL, [ImageID] [varchar](50) COLLATE Latin1_General_CI_AI NOT NULL, [Caption] [varchar](max) COLLATE Latin1_General_CI_AI NOT NULL, [Keyword] [varchar](max) COLLATE Latin1_General_CI_AI NOT NULL, [Company] [varchar](250) COLLATE Latin1_General_CI_AI NOT NULL CONSTRAINT [DF_tblSearchImages_Company] DEFAULT ((0)), [Thumbnail] [char](40) COLLATE Latin1_General_CI_AI NOT NULL, Preview] [char](40) COLLATE Latin1_General_CI_AI NOT NULL, [HighRes] [char](40) COLLATE Latin1_General_CI_AI NOT NULL, [FotogID] [varchar](50) COLLATE Latin1_General_CI_AI NOT NULL, [ArchiveDate] [float] NOT NULL, [CreateDate] [datetime] NOT NULL, [Accept] [bit] NOT NULL, [AllowWeb] [bit] NOT NULL, [Event] [bit] NOT NULL CONSTRAINT [DF_tblSearchImages_Event] DEFAULT ((0)), [HRX] [int] NOT NULL CONSTRAINT [DF_tblSearchImages_HRX] DEFAULT ((0)), [HRY] [int] NOT NULL CONSTRAINT [DF_tblSearchImages_HRY] DEFAULT ((0)), [StudioPK] [int] NOT NULL CONSTRAINT [DF_tblSearchImages_StudioPK] DEFAULT ((0)), [Color] [int] NOT NULL CONSTRAINT [DF_tblSearchImages_Color] DEFAULT ((0)), [Orient] AS (case when [HRX]>[HRY] then (1) when [HRY]>[HRX] then (2) else (0) end), [Movie] [bit] NOT NULL CONSTRAINT [DF_tblSearchImages_Movie] DEFAULT ((0)), CONSTRAINT [PK_tblSeachImages] PRIMARY KEY CLUSTERED ( [SearchImage_PK] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO set ansi_padding off ----------------------------------------------------------------------- [link text][1] [link text][2] [1]: /storage/temp/1501-fullexecplan.txt [2]: /storage/temp/1499-fullexecplan.txt