Tuning an SP

Question

Post reply

Tuning an SP

Alan Brownlow

Old Hand

Points: 393
More actions
November 23, 2006 at 9:06 am

#173608

Hi,

I have been asked to tune a SP for a colleaugue, so far I have got it from 30 seconds down to 4 with some well placed Clustered and Non clustered Indexes and some changes to the SP. but I feel that it is still not quick enough, I will place the code below but a bit of background info. The SP is to query a db that holds all our calls logged for the last X years and enables people to search based on a number of factors mainly free text.

The code

The main SP is as follows

ALTER PROCEDURE [dbo].[AJB_TSD_sp_sel_rec_InquiryCountBySearchCriteria_FullText]

-- Add the parameters for the stored procedure here

@Platform_Id integer,

@Products nvarchar(56) = '0',  /*Wildcard*/

@Version nvarchar(56) = '%',  /*Wildcard*/

@ProductArea nvarchar(512) = 'All', /*Wildcard*/

@LogDateFrom datetime = NULL,  /*Wildcard*/

@RDBMS nvarchar(56) = '%',   /*Wildcard*/

@RDBMSVersion nvarchar(56) = '%', /*Wildcard*/

@OS nvarchar(56) = '%',    /*Wildcard*/

@OSVersion nvarchar(56) = '%',  /*Wildcard*/

@Text nvarchar(256)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

Declare @Count as int;

    -- Insert statements for procedure here

Select

  @Count = count(*)

From

  TSD_tb_Inquiries As TSDs

--Left Join

-- (Select Distinct Inquiry_Id From

--  TSD_tb_DiaryEntries Where CONTAINS (*, @Text))

-- As DE On TSDs.Inquiry_Id = DE.Inquiry_Id

Where

  Platform_Id = @Platform_Id

  and (Product_Id In (Select Item Collate SQL_Latin1_General_CP1_CI_AS From TSD_udf_SplitList(@Products))

  or Product_Id In (Case When @Products = '0' Then Product_Id Else NULL End))

  and Type like '%-' + @Version + '%'

  and (ProductArea In (Select Item Collate SQL_Latin1_General_CP1_CI_AS From TSD_udf_SplitList(@ProductArea))

  or ProductArea In (Case When @ProductArea = 'All' Then TSDs.ProductArea Else NULL End))

  and ServerOS like '%' + @os + '%'

  and ServerOSVersion like '%' + @OSVersion + '%'

  and DatabaseType like '%' + @RDBMS + '%'

  and DatabaseVersion like '%' + @RDBMSVersion + '%'

  and CONTAINS (TSDs.*, @Text) -- or DE.Inquiry_Id Is Not Null)

If (@Count > 300)

Begin

  Set @Count = 300

End

Select @Count

END

The UDF that is used above is as follows....

ALTER FUNCTION [dbo].[TSD_udf_SplitList]

(

@List varchar(500)

)

RETURNS

@ParsedList table

(

Item nvarchar(128)

)

AS

BEGIN

DECLARE @ID nvarchar(128), @Pos int

SET @List = LTRIM(RTRIM(@List))+ ','

SET @Pos = CHARINDEX(',', @List, 1)

IF REPLACE(@List, ',', '') <> ''

BEGIN

  WHILE @Pos > 0

  BEGIN

   SET @ID = LTRIM(RTRIM(LEFT(@List, @Pos - 1)))

   IF @ID <> ''

   BEGIN

    INSERT INTO @ParsedList (Item)

    VALUES (@ID) --Use Appropriate conversion

   END

   SET @List = RIGHT(@List, LEN(@List) - @Pos)

   SET @Pos = CHARINDEX(',', @List, 1)

  END

END

RETURN

END

Any ideas how to speed this up a bit,

Thanks

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

bledu SSChampion Points: 12335 More actions · Answer 1

bledu

SSChampion

Points: 12335

November 23, 2006 at 9:57 am

#673715

I would think 4s is good enough considering that you are filtering on free text.

Is there a business rule that wants it any faster?

Everything you can imagine is real.

Alan Brownlow Old Hand Points: 393 More actions · Answer 2

I gues your right re 4s, but its one of those things that you hope to have covered everything and tried every avenue to tighten things up, and maybe learn something new or a new approach to problem.

I guess another way of doing this is a seperate SP for each of the choices a user can make thereby stripping out any unneeded params based on the users choice.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Ninja's_RGR'us

SSC Guru

Points: 294069

November 23, 2006 at 10:05 am

#673721

One way would be to use either dynamic sql or create a single sp for each combinaison of parameters possible. Then optimize them all separatly. I would do that ONLY IF a businesss requirement forced me into it .

AndrewMurphy SSCertifiable Points: 5586 More actions · Answer 4

1. have a look at the execution plan, and try to see why indices aren't being fully used.

2. your LIKE statements....won't use indices...especially with leading %

3. qualify your tables with "dbo."

4. why the need for the "collation" clauses? this may stop indices being used.

5. there are better ways to resolve optional parameters...(on the lines of)

declare @code integer

declare @name char(20)

Set @code = 10

set @name = 'mycust'

select * from dbo.customer

where (code = @code or @code is null)

or (name = @name or @name is null)

6. cross-check why there seems to be a mismatch between your variable lengths...esp going into/coming out of the UDF. this may affect the quality of the results, never mind it's performance.

...remember datatypes should match in size and type to help indices to be used....

7....I presume that ARE indices on the underlying table...posting the DDL would help!

8. you didn't put the current SP speed in context of the underlying database...how big...2mb, 2gb....how fast is the server? are you experiencing SP_recompiles. how busy is the system? how often is this called...once a day, once every 10 seconds?

more info => more solutions

Alan Brownlow Old Hand Points: 393 More actions · Answer 5

Andrew,

Thanks for the pointers but still no further below is more info

Execution Plan below (not sure how this will look)

1 1 Select @Count = count(*) From dbo.CODA_TSD_tb_Inquiries As TSDs --Left Join -- (Select Distinct Inquiry_Id From -- CODA_TSD_tb_DiaryEntries Where CONTAINS (*, @Text)) -- As DE On TSDs.Inquiry_Id = DE.Inquiry_Id Where Platform_Id = @Platform_Id and (Product_Id In (Select Item /*Collate SQL_Latin1_General_CP1_CI_AS*/ From CODA_TSD_udf_SplitList(@Products)) or Product_Id In (Case When @Products = '0' Then Product_Id Else NULL End)) and Type like '%-' + @Version + '%' and (ProductArea In (Select Item Collate SQL_Latin1_General_CP1_CI_AS From CODA_TSD_udf_SplitList(@ProductArea)) or ProductArea In (Case When @ProductArea = 'All' Then TSDs.ProductArea Else NULL End)) and ServerOS like '%' + @os + '%' and ServerOSVersion like '%' + @OSVersion + '%' and DatabaseType like '%' + @RDBMS + '%' and DatabaseVersion like '%' + @RDBMSVersion + '%' and CONTAINS (TSDs.*, @Text) -- or DE.Inquiry_Id Is Not Null) 3 1 0 NULL NULL NULL NULL 1 NULL NULL NULL 20.69226 NULL NULL SELECT 0 NULL

1 1 |--Sequence 3 2 1 Sequence Sequence NULL NULL 1 0 3E-06 11 20.69226 [Expr1010] NULL PLAN_ROW 0 1

0 1 |--Table-valued function(OBJECT[CODA_TSD].[dbo].[CODA_TSD_udf_SplitList])) 3 3 2 Table-valued function Table-valued function OBJECT[CODA_TSD].[dbo].[CODA_TSD_udf_SplitList]) NULL 1 0 1.157E-06 9 1.157E-06 NULL NULL PLAN_ROW 0 1

0 1 |--Table-valued function(OBJECT[CODA_TSD].[dbo].[CODA_TSD_udf_SplitList])) 3 5 2 Table-valued function Table-valued function OBJECT[CODA_TSD].[dbo].[CODA_TSD_udf_SplitList]) NULL 1 0 1.157E-06 9 1.157E-06 NULL NULL PLAN_ROW 0 1

0 0 |--Compute Scalar(DEFINE[Expr1010]=CONVERT_IMPLICIT(int,[globalagg1046],0))) 3 8 2 Compute Scalar Compute Scalar DEFINE[Expr1010]=CONVERT_IMPLICIT(int,[globalagg1046],0)) [Expr1010]=CONVERT_IMPLICIT(int,[globalagg1046],0) 1 0 1E-07 11 20.69225 [Expr1010] NULL PLAN_ROW 0 1

1 1 |--Stream Aggregate(DEFINE[globalagg1046]=SUM([partialagg1045]))) 3 9 8 Stream Aggregate Aggregate NULL [globalagg1046]=SUM([partialagg1045]) 1 0 1.7E-06 15 20.69225 [globalagg1046] NULL PLAN_ROW 0 1

1 1 |--Stream Aggregate(DEFINE[partialagg1045]=Count(*))) 3 10 9 Stream Aggregate Aggregate NULL [partialagg1045]=Count(*) 2 0 0.0020357 15 20.66375 [partialagg1045] NULL PLAN_ROW 0 1

5005 1 |--Hash Match(Inner Join, HASH[Full-text Search Engine].)=([TSDs].[Inquiry_Id])) 3 11 10 Hash Match Inner Join HASH[Full-text Search Engine].)=([TSDs].[Inquiry_Id]) NULL 6784 0 0.2330947 9 20.66171 NULL NULL PLAN_ROW 0 1

6784 1 |--Remote Scan(OBJECTCONTAINS)) 3 12 11 Remote Scan Remote Scan OBJECTCONTAINS) NULL 6784 0 0.2561333 11 0.2561333 [Full-text Search Engine]. NULL PLAN_ROW 0 1

94594 1 |--Nested Loops(Left Semi Join, OUTER REFERENCES[TSDs].[ProductArea])) 3 13 11 Nested Loops Left Semi Join OUTER REFERENCES[TSDs].[ProductArea]) NULL 77410.34 0 0.179764 11 20.00648 [TSDs].[Inquiry_Id] NULL PLAN_ROW 0 1

94805 1 |--Nested Loops(Left Semi Join, OUTER REFERENCES[TSDs].[Product_Id])) 3 14 13 Nested Loops Left Semi Join OUTER REFERENCES[TSDs].[Product_Id]) NULL 86011.49 0 0.1997378 523 19.65641 [TSDs].[Inquiry_Id], [TSDs].[ProductArea] NULL PLAN_ROW 0 1

95733 1 | |--Clustered Index Scan(OBJECT[CODA_TSD].[dbo].[CODA_TSD_tb_Inquiries].[PK_CODA_TSD_tb_Inquiries] AS [TSDs]), WHERE[CODA_TSD].[dbo].[CODA_TSD_tb_Inquiries].[Platform_Id] as [TSDs].[Platform_Id]=[@Platform_Id] AND CONVERT_IMPLICIT(nvarchar(124),[CODA_TSD].[dbo].[CODA_TSD_tb_Inquiries].[Type] as [TSDs].[Type],0) like CONVERT_IMPLICIT(nvarchar(59),(N'%-'+[@Version])+N'%',0) AND CONVERT_IMPLICIT(nchar(64),[CODA_TSD].[dbo].[CODA_TSD_tb_Inquiries].[ServerOS] as [TSDs].[ServerOS],0) like CONVERT_IMPLICIT(nvarchar(58),(N'%'+[@OS])+N'%',0) AND CONVERT_IMPLICIT(nchar(64),[CODA_TSD].[dbo].[CODA_TSD_tb_Inquiries].[ServerOSVersion] as [TSDs].[ServerOSVersion],0) like CONVERT_IMPLICIT(nvarchar(58),(N'%'+[@OSVersion])+N'%',0) AND CONVERT_IMPLICIT(nchar(64),[CODA_TSD].[dbo].[CODA_TSD_tb_Inquiries].[DatabaseType] as [TSDs].[DatabaseType],0) like CONVERT_IMPLICIT(nvarchar(58),(N'%'+[@RDBMS])+N'%',0) AND CONVERT_IMPLICIT(nchar(64),[CODA_TSD].[dbo].[CODA_TSD_tb_Inquiries].[DatabaseVersion] as [TSDs].[DatabaseVersion],0) like CONVERT_IMPLICIT(nvarchar(58),(N'%'+[@RDBMSVersion])+N'%',0))) 3 16 14 Clustered Index Scan Clustered Index Scan OBJECT[CODA_TSD].[dbo].[CODA_TSD_tb_Inquiries].[PK_CODA_TSD_tb_Inquiries] AS [TSDs]), WHERE[CODA_TSD].[dbo].[CODA_TSD_tb_Inquiries].[Platform_Id] as [TSDs].[Platform_Id]=[@Platform_Id] AND CONVERT_IMPLICIT(nvarchar(124),[CODA_TSD].[dbo].[CODA_TSD_tb_Inquiries].[Type] as [TSDs].[Type],0) like CONVERT_IMPLICIT(nvarchar(59),(N'%-'+[@Version])+N'%',0) AND CONVERT_IMPLICIT(nchar(64),[CODA_TSD].[dbo].[CODA_TSD_tb_Inquiries].[ServerOS] as [TSDs].[ServerOS],0) like CONVERT_IMPLICIT(nvarchar(58),(N'%'+[@OS])+N'%',0) AND CONVERT_IMPLICIT(nchar(64),[CODA_TSD].[dbo].[CODA_TSD_tb_Inquiries].[ServerOSVersion] as [TSDs].[ServerOSVersion],0) like CONVERT_IMPLICIT(nvarchar(58),(N'%'+[@OSVersion])+N'%',0) AND CONVERT_IMPLICIT(nchar(64),[CODA_TSD].[dbo].[CODA_TSD_tb_Inquiries].[DatabaseType] as [TSDs].[DatabaseType],0) like CONVERT_IMPLICIT(nvarchar(58),(N'%'+[@RDBMS])+N'%',0) AND CONVERT_IMPLICIT(nchar(64),[CODA_TSD].[dbo].[CODA_TSD_tb_Inquiries].[DatabaseVersion] as [TSDs].[DatabaseVersion],0) like CONVERT_IMPLICIT(nvarchar(58),(N'%'+[@RDBMSVersion])+N'%',0)) [TSDs].[Inquiry_Id], [TSDs].[ProductArea], [TSDs].[Product_Id] 95568.32 18.90979 0.0745738 813 18.98437 [TSDs].[Inquiry_Id], [TSDs].[ProductArea], [TSDs].[Product_Id] NULL PLAN_ROW 0 1

94805 95733 | |--Concatenation 3 18 14 Concatenation Concatenation NULL NULL 1 0 2E-07 9 0.1892254 NULL NULL PLAN_ROW 0 95568.32

0 95733 | |--Table Scan(OBJECT[CODA_TSD].[dbo].[CODA_TSD_udf_SplitList]), WHERE[CODA_TSD].[dbo].[CODA_TSD_tb_Inquiries].[Product_Id] as [TSDs].[Product_Id]=CONVERT_IMPLICIT(int,[CODA_TSD].[dbo].[CODA_TSD_udf_SplitList].[Item],0))) 3 20 18 Table Scan Table Scan OBJECT[CODA_TSD].[dbo].[CODA_TSD_udf_SplitList]), WHERE[CODA_TSD].[dbo].[CODA_TSD_tb_Inquiries].[Product_Id] as [TSDs].[Product_Id]=CONVERT_IMPLICIT(int,[CODA_TSD].[dbo].[CODA_TSD_udf_SplitList].[Item],0)) NULL 1 0.0032035 7.96E-05 139 0.1084072 NULL NULL PLAN_ROW 0 95568.32

94805 95733 | |--Filter(WHERESTARTUP EXPR([CODA_TSD].[dbo].[CODA_TSD_tb_Inquiries].[Product_Id] as [TSDs].[Product_Id]=CASE WHEN [@Products]=N'0' THEN [CODA_TSD].[dbo].[CODA_TSD_tb_Inquiries].[Product_Id] as [TSDs].[Product_Id] ELSE NULL END))) 3 22 18 Filter Filter WHERESTARTUP EXPR([CODA_TSD].[dbo].[CODA_TSD_tb_Inquiries].[Product_Id] as [TSDs].[Product_Id]=CASE WHEN [@Products]=N'0' THEN [CODA_TSD].[dbo].[CODA_TSD_tb_Inquiries].[Product_Id] as [TSDs].[Product_Id] ELSE NULL END)) NULL 1 0 9.8E-07 9 0.1892254 NULL NULL PLAN_ROW 0 95568.32

94805 94805 | |--Constant Scan 3 23 22 Constant Scan Constant Scan NULL NULL 1 0 1.157E-06 9 0.09556848 NULL NULL PLAN_ROW 0 95568.32

94594 94805 |--Concatenation 3 25 13 Concatenation Concatenation NULL NULL 1 0 2E-07 9 0.1703029 NULL NULL PLAN_ROW 0 86011.49

1 94805 |--Table Scan(OBJECT[CODA_TSD].[dbo].[CODA_TSD_udf_SplitList]), WHERECONVERT_IMPLICIT(nchar(512),[CODA_TSD].[dbo].[CODA_TSD_tb_Inquiries].[ProductArea] as [TSDs].[ProductArea],0)=CONVERT(nvarchar(128),[CODA_TSD].[dbo].[CODA_TSD_udf_SplitList].[Item],0))) 3 27 25 Table Scan Table Scan OBJECT[CODA_TSD].[dbo].[CODA_TSD_udf_SplitList]), WHERECONVERT_IMPLICIT(nchar(512),[CODA_TSD].[dbo].[CODA_TSD_tb_Inquiries].[ProductArea] as [TSDs].[ProductArea],0)=CONVERT(nvarchar(128),[CODA_TSD].[dbo].[CODA_TSD_udf_SplitList].[Item],0)) NULL 1 0.0032035 7.96E-05 139 0.09789464 NULL NULL PLAN_ROW 0 86011.49

94593 94804 |--Filter(WHERESTARTUP EXPR([CODA_TSD].[dbo].[CODA_TSD_tb_Inquiries].[ProductArea] as [TSDs].[ProductArea]=CASE WHEN [@ProductArea]=N'All' THEN [CODA_TSD].[dbo].[CODA_TSD_tb_Inquiries].[ProductArea] as [TSDs].[ProductArea] ELSE NULL END))) 3 29 25 Filter Filter WHERESTARTUP EXPR([CODA_TSD].[dbo].[CODA_TSD_tb_Inquiries].[ProductArea] as [TSDs].[ProductArea]=CASE WHEN [@ProductArea]=N'All' THEN [CODA_TSD].[dbo].[CODA_TSD_tb_Inquiries].[ProductArea] as [TSDs].[ProductArea] ELSE NULL END)) NULL 1 0 9.8E-07 9 0.1703029 NULL NULL PLAN_ROW 0 86011.49

94593 94593 |--Constant Scan 3 30 29 Constant Scan Constant Scan NULL NULL 1 0 1.157E-06 9 0.09556848 NULL NULL PLAN_ROW 0 95568.32

I have currently removed the indices as looking back they do not seem to be used probably due to the wildcard usage as you pointed out. I have tried to remove the CI scan (row12) as it could be a ‘quick win’ with such a high subtree costbut looking at the values passed I cannot see a way round this..

OBJECT[CODA_TSD].[dbo].[CODA_TSD_tb_Inquiries].[PK_CODA_TSD_tb_Inquiries] AS [TSDs]), WHERE[CODA_TSD].[dbo].[CODA_TSD_tb_Inquiries].[Platform_Id] as [TSDs].[Platform_Id]=[@Platform_Id] AND CONVERT_IMPLICIT(nvarchar(124),[CODA_TSD].[dbo].[CODA_TSD_tb_Inquiries].[Type] as [TSDs].[Type],0) like CONVERT_IMPLICIT(nvarchar(59),(N'%-'+[@Version])+N'%',0) AND CONVERT_IMPLICIT(nchar(64),[CODA_TSD].[dbo].[CODA_TSD_tb_Inquiries].[ServerOS] as [TSDs].[ServerOS],0) like CONVERT_IMPLICIT(nvarchar(58),(N'%'+[@OS])+N'%',0) AND CONVERT_IMPLICIT(nchar(64),[CODA_TSD].[dbo].[CODA_TSD_tb_Inquiries].[ServerOSVersion] as [TSDs].[ServerOSVersion],0) like CONVERT_IMPLICIT(nvarchar(58),(N'%'+[@OSVersion])+N'%',0) AND CONVERT_IMPLICIT(nchar(64),[CODA_TSD].[dbo].[CODA_TSD_tb_Inquiries].[DatabaseType] as [TSDs].[DatabaseType],0) like CONVERT_IMPLICIT(nvarchar(58),(N'%'+[@RDBMS])+N'%',0) AND CONVERT_IMPLICIT(nchar(64),[CODA_TSD].[dbo].[CODA_TSD_tb_Inquiries].[DatabaseVersion] as [TSDs].[DatabaseVersion],0) like CONVERT_IMPLICIT(nvarchar(58),(N'%'+[@RDBMSVersion])+N'%',0))

The defined value for that row is [TSDs].[Inquiry_Id], [TSDs].[ProductArea], [TSDs].[Product_Id] which with the use of wildcards wont be used with an index.

The collation was used as the DB contains foreign languages but again after removing this section it was the same speed

DDL for the table in use

USE [TSD]

GO

/****** Object: Table [dbo].[CODA_TSD_tb_Inquiries] Script Date: 11/24/2006 11:43:10 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[TSD_tb_Inquiries](

[Inquiry_Id] [int] NOT NULL,

[State] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Status] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Priority] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Type] [varchar](124) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Category1] [varchar](124) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Category2] [varchar](124) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[LoggedDate] [datetime] NULL,

[ClosedDate] [datetime] NULL,

[ClosedBy] [varchar](64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Account_Id] [int] NULL,

[AccountName] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Licence] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[AccountContact_Id] [int] NULL,

[ContactFirstname] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[ContactLastname] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Request_Id] [int] NULL,

[OwnerRep] [varchar](64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[SolutionRating] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[CalObject] [varchar](64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[AppsOrTech] [char](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[InquiryText] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[ErrorMsgText] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[ProductArea] [char](512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[GeneralKeywords] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_coda_knowledge_general_keyword] DEFAULT ('coda'),

[CallAudit] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[DatabaseType] [char](64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[DatabaseVersion] [char](64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[ServerOS] [char](64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[ServerOSVersion] [char](64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[SolutionRatingSubcat] [char](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[SolutionText] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Language_Id] [int] NULL,

[LanguageDesc] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Platform_Id] [int] NULL,

[Product_Id] [int] NULL,

[ProductDesc] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Version_Id] [int] NULL,

[Version] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[VersionDisplayOrder] [int] NULL,

[Build_Id] [int] NULL,

[Build] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[ProductArea_Id] [int] NULL,

[UpdatedDate] [datetime] NULL,

[FullInquiryText] [text] COLLATE Latin1_General_CI_AS NULL,

CONSTRAINT [PK_CODA_TSD_tb_Inquiries] PRIMARY KEY CLUSTERED

(

[Inquiry_Id] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

/****** Object: Statistic [_dta_stat_1077578877_1_36] Script Date: 11/24/2006 11:43:10 ******/

CREATE STATISTICS [_dta_stat_1077578877_1_36] ON [dbo].[ TSD_tb_Inquiries]([Inquiry_Id], [Product_Id])

GO

/****** Object: Statistic [_dta_stat_1077578877_1_36_35_24_5_27_30_29] Script Date: 11/24/2006 11:43:10 ******/

CREATE STATISTICS [_dta_stat_1077578877_1_36_35_24_5_27_30_29] ON [dbo].[ TSD_tb_Inquiries]([Inquiry_Id], [Product_Id], [Platform_Id], [ProductArea], [Type], [DatabaseType], [ServerOSVersion], [ServerOS])

GO

/****** Object: Statistic [_dta_stat_1077578877_24_1_35_5_29_30_27_28] Script Date: 11/24/2006 11:43:10 ******/

CREATE STATISTICS [_dta_stat_1077578877_24_1_35_5_29_30_27_28] ON [dbo].[TSD_tb_Inquiries]([ProductArea], [Inquiry_Id], [Platform_Id], [Type], [ServerOS], [ServerOSVersion], [DatabaseType], [DatabaseVersion])

GO

/****** Object: Statistic [_dta_stat_1077578877_27_1_35_5_29] Script Date: 11/24/2006 11:43:10 ******/

CREATE STATISTICS [_dta_stat_1077578877_27_1_35_5_29] ON [dbo].[ TSD_tb_Inquiries]([DatabaseType], [Inquiry_Id], [Platform_Id], [Type], [ServerOS])

GO

/****** Object: Statistic [_dta_stat_1077578877_28_1_35_5_29_30] Script Date: 11/24/2006 11:43:10 ******/

CREATE STATISTICS [_dta_stat_1077578877_28_1_35_5_29_30] ON [dbo].[ TSD_tb_Inquiries]([DatabaseVersion], [Inquiry_Id], [Platform_Id], [Type], [ServerOS], [ServerOSVersion])

GO

/****** Object: Statistic [_dta_stat_1077578877_29_1_35] Script Date: 11/24/2006 11:43:10 ******/

CREATE STATISTICS [_dta_stat_1077578877_29_1_35] ON [dbo].[ TSD_tb_Inquiries]([ServerOS], [Inquiry_Id], [Platform_Id])

GO

/****** Object: Statistic [_dta_stat_1077578877_30_1_35_5] Script Date: 11/24/2006 11:43:10 ******/

CREATE STATISTICS [_dta_stat_1077578877_30_1_35_5] ON [dbo].[ TSD_tb_Inquiries]([ServerOSVersion], [Inquiry_Id], [Platform_Id], [Type])

GO

/****** Object: Statistic [_dta_stat_1077578877_35_5_29_30_27_28_36_24] Script Date: 11/24/2006 11:43:10 ******/

CREATE STATISTICS [_dta_stat_1077578877_35_5_29_30_27_28_36_24] ON [dbo].[TSD_tb_Inquiries]([Platform_Id], [Type], [ServerOS], [ServerOSVersion], [DatabaseType], [DatabaseVersion], [Product_Id], [ProductArea])

GO

/****** Object: Statistic [_dta_stat_1077578877_36_35_1_37_5_24] Script Date: 11/24/2006 11:43:10 ******/

CREATE STATISTICS [_dta_stat_1077578877_36_35_1_37_5_24] ON [dbo].[ TSD_tb_Inquiries]([Product_Id], [Platform_Id], [Inquiry_Id], [ProductDesc], [Type], [ProductArea])

GO

/****** Object: Statistic [_dta_stat_1077578877_37_1_36] Script Date: 11/24/2006 11:43:10 ******/

CREATE STATISTICS [_dta_stat_1077578877_37_1_36] ON [dbo].[ TSD_tb_Inquiries]([ProductDesc], [Inquiry_Id], [Product_Id])

GO

/****** Object: Statistic [_dta_stat_1077578877_5_1_35_29_30_27_28_36] Script Date: 11/24/2006 11:43:10 ******/

CREATE STATISTICS [_dta_stat_1077578877_5_1_35_29_30_27_28_36] ON [dbo].[ TSD_tb_Inquiries]([Type], [Inquiry_Id], [Platform_Id], [ServerOS], [ServerOSVersion], [DatabaseType], [DatabaseVersion], [Product_Id])

.

The table is as follows

TSD_tb_Inquiries rows 135446 Total space 1022256KB Data space 1010840 KB Index space 10680 KB Unused space 736 KB

AndrewMurphy SSCertifiable Points: 5586 More actions · Answer 6

you have a lot of clustered-index scans....which I think are worse than tablescans....you want index seeks...for real performance. only way to do that is to rework the wildcards....

using the likes of my suggestion #5

going by the number of records, you should be doing better than 4 secs...the wildcards are killing you.

try a simple sample query...with your style of wildcard...and try to re-work it with my suggestion....there should be a noticable improvement.

only other thin I can think of...ie to

a) move the textimage onto a different fileset...on a different drive...

and b) re-work the underlying drives supporting the server...to have different drives for tempdb, os files, log files and data files....if you're working with a tractor, don't expect it to be faster than a F1/NASCAR car.

sqldba-294117 Hall of Fame Points: 3486 More actions · Answer 7

most or all of your columns are nvarchar so Clustered Index is not recommended. NON-CLUSTERED index is only good on those columns which as uniqueue value of more than 95%. for e.g. if your column is going to have like 'yes' or 'no' value then is no uniqueue value.

try to create full text search catalog on that table & see if that helps.

thx

Alan Brownlow Old Hand Points: 393 More actions · Answer 8

Alan Brownlow

Old Hand

Points: 393

November 27, 2006 at 3:04 am

#673996

Thanks for the suggestion, I think we are going to start again from the tables upwards

AndrewMurphy SSCertifiable Points: 5586 More actions · Answer 9

"...nvarchar so Clustered Index is not recommended"

Whether or not the col is nvarchar or not is irrelevent for the Clustered Index...it's the cardinality of the column that affects is suitability...ie the 2nd part of your arguement. the datatype and cardinality of the column are (in this case) not related.

By the way....there is already a FTS catalog on the table.....see the CONTAINS clause incorporated in the user query.