Stored Procedure Tuning...

Question

Stored Procedure Tuning...

RLee

SSC Veteran

Points: 241
More actions
April 10, 2006 at 12:12 pm

#112632

Hello,
I have a stored procedure that i've been using for the past 2 years... it
has started to slow down on some of the complex searches... But i'll provide
the tables and a clean version of the stored procedure for anyone who can
help... (clean showing procedure without commented stuff out)..
I'm running SQL Server 2000 at this time... On Windows 2000 Server (soon to be migrated to Windows 2003 Server).
To sum up, this stored procedure will read data from two identical tables,
one with 100k records, and the other with 600k records. The records are
sorted into a temp table, and then the records for page XX are extracted
based on how many records per page is requested. All paging happens server
side, this way only the data required is returned.
I also have 3 other tables that are used with these tables, but they aren't
used as much...
I do have fulltext set up on Items and PubItems tables...
When a search is done by ISBN on both, the time taken is between 100 and 500 MS...
When i do a search (say by title or author), the time taken is between 1000
MS and ... well, i've seen numbers up around 200000 MS or higher...
I'm not going to provide any data inserts as trying to run this against a
table with 5-100 records isn't going to show how slow my searches can be...
Example procedure runs:
1: exec sp_PagedItems4 'Both','I.ISBN=''9781111111113''','TitleIndex',1,15
1: This search was under 1 second, and returned the one record as expected.
2: exec sp_PagedItems4 'Both','I.TitleText Like
''%Garfield%''','TitleIndex',1,15
2: This search took 5 seconds, and returned the 15 records for page 1, but
overall count is 265.
3: exec sp_PagedItems4 'Both',
'(FREETEXT (I.TitleText, ''"books"'') OR FREETEXT (I.Subtitle, ''"books"'')
OR FREETEXT (I.AuthorText, ''"books"'') OR FREETEXT (I.IllusText,
''"books"'')) AND (FREETEXT (I.TitleText, ''"about"'') OR FREETEXT
(I.Subtitle, ''"about"'') OR FREETEXT (I.AuthorText, ''"about"'') OR
FREETEXT (I.IllusText, ''"about"'')) AND (FREETEXT (I.TitleText,
''"crafts"'') OR FREETEXT (I.Subtitle, ''"crafts"'') OR FREETEXT
(I.AuthorText, ''"crafts"'') OR FREETEXT (I.IllusText, ''"crafts"''))'
,'TitleIndex',1,15
3: This search took 1 minute and 45 seconds (when run from SQL Query
Analyzer), and returned ONE record from PubItems.
I am trying to get an execution plan, but in Query Analyzer, pressing CTRL-L, gives me an error (due to the temp table in this stored procedure)...
Any help is very much appreciated...
Thanks!
CREATE TABLE [dbo].[AwardType] (
[AwardCode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AwardDesc] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Awards] (
[ItemID] [int] NOT NULL ,
[AwardCode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ReviewType] (
[ReviewCode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[ReviewDesc] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Reviews] (
[ItemID] [int] NOT NULL ,
[ReviewCode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE Items
(
ID int IDENTITY(1,1) PRIMARY KEY,
ItemID int null,
PubSource nvarchar(255) default (''),
ISBN nvarchar(13) null,
Dewey nvarchar(20) null,
TitleIndex nvarchar(175) null,
AuthorIndex nvarchar(50) null,
PubYear nvarchar(30) null,
PubDate datetime null,
SeriesName nvarchar(150) null,
SeriesNumber int null
)
GO
CREATE TABLE PubItems
(
ID int IDENTITY(1,1) PRIMARY KEY,
ItemID int null,
PubSource nvarchar(255) default (''),
ISBN nvarchar(13) null,
Dewey nvarchar(20) null,
TitleIndex nvarchar(175) null,
AuthorIndex nvarchar(50) null,
PubYear nvarchar(30) null,
PubDate datetime null,
SeriesName nvarchar(150) null,
SeriesNumber int null
)
GO
/*
sp_PagedItems4 -
This procedure is the continuation of the original sp_PagedItems, which
would allow us to grab records from BOTH Items and PubItems, and
eliminate the Items in the PubItems Table which are in Items. The only
difference here is that we only return the minimal data fields (which
should
hopefully speed up the query).
*/
CREATE PROCEDURE sp_PagedItems4
(
@TableExt nvarchar(10),
@Where nvarchar(1500),
@SortColumn nvarchar(300),
@Page int,
@RecsPerPage int
)
WITH RECOMPILE
AS
DECLARE @Where2 nvarchar(1500)
DECLARE @Table nvarchar(200)
DECLARE @Table2 nvarchar(200)
DECLARE @Fields nvarchar(2000)
DECLARE @Fields2 nvarchar(2000)
DECLARE @FieldsInsert nvarchar(2000)
DECLARE @FieldsI nvarchar(2000)
DECLARE @query nvarchar(4000)
DECLARE @TotalRec int
DECLARE @AwardReview char(1)
Set @AwardReview = 'N'
Set @Where2 = @Where
Set @Where2 = REPLACE(@Where2,'I.','PI.')
--Fields, Blurb, SecBlurb, SetID, BestBlurb are ntext fields. These fields
need to be removed from the union, but put in after
--the data is done.
Set @Fields = 'I.ItemID, '''', I.ISBN, I.Dewey, I.TitleIndex, I.AuthorIndex,
I.PubYear, I.PubDate, I.SeriesName, I.SeriesNumber'
Set @Fields2 = 'PI.ItemID, PI.PubSource, PI.ISBN, PI.Dewey, PI.TitleIndex,
PI.AuthorIndex, PI.PubYear, PI.PubDate, PI.SeriesName, PI.SeriesNumber'
Set @Table = 'Items I'
Set @Table2 = 'PubItems PI'
Set @FieldsI = 'I.ItemID, I.PubSource, I.ISBN, I.Dewey, I.TitleIndex,
I.AuthorIndex, I.PubYear, I.PubDate, I.SeriesName, I.SeriesNumber'
--Check for "A", "R" or "AD" in the Where as we need to join the tables if
that's the case. (Items only)
if charindex('A.', @Where) > 0
begin
Set @Table = @Table + ' JOIN Awards A ON I.ItemID = A.ItemID '
Set @AwardReview = 'Y'
end
if charindex('R.', @Where) > 0
begin
Set @Table = @Table + ' JOIN Reviews R ON I.ItemID = R.ItemID '
Set @AwardReview = 'Y'
end
-- Do not return # rows inserted in temp table.
SET NOCOUNT ON
--Create Temp Table
CREATE TABLE #TempItems
(
ID int IDENTITY(1,1) PRIMARY KEY,
ItemID int null,
PubSource nvarchar(255) default (''),
ISBN nvarchar(13) null,
Dewey nvarchar(20) null,
TitleIndex nvarchar(175) null,
AuthorIndex nvarchar(50) null,
PubYear nvarchar(30) null,
PubDate datetime null,
SeriesName nvarchar(150) null,
SeriesNumber int null
)
-- Insert the rows from Items into the temp table.
if @AwardReview = 'N'
begin
--No Award/Review, let's union this...
if @tableext = 'Both' Or @TableExt = 'Items'
begin
SELECT @query = 'INSERT INTO #TempItems ' +
   ' (' + @FieldsI + ') ' +
   ' SELECT ' + @Fields +
   ' FROM ' + @Table +
   ' WHERE ' + @Where
end
if @tableext = 'Both'
begin
Select @query = @query + ' UNION ' +
   ' SELECT ' + @Fields2 +
   ' FROM ' + @Table2 +
   ' WHERE ' + @Where2
end
if @TableExt = 'PubItems'
begin
SELECT @query = 'INSERT INTO #TempItems ' +
   ' (' + @Fields2 + ') ' +
   ' SELECT ' + @Fields2 +
   ' FROM ' + @Table2 +
   ' WHERE ' + @Where2
end
if @SortColumn <> '' And Not isnull(@SortColumn,'') = ''
SELECT @query = @query + ' ORDER BY ' + @SortColumn
--Print @Query
exec sp_executesql @query
end
else
begin
--There is an award/review requested, so don't even look at pubitems
if @tableext = 'Both' Or @TableExt = 'Items'
begin
SELECT @query = 'INSERT INTO #TempItems ' +
   ' (' + @FieldsI + ') ' +
   ' SELECT ' + @Fields +
   ' FROM ' + @Table +
   ' WHERE ' + @Where
if @SortColumn <> '' And Not isnull(@SortColumn,'') = ''
   SELECT @query = @query + ' ORDER BY ' + @SortColumn
--print @query
exec sp_executesql @query
end
end
SET @TotalRec =
(SELECT COUNT(ID)
FROM #TempItems)
--Check if there are any records. If there are, return everything, else
return all zeros and
--no resultset.
if @TotalRec > 0
begin
DECLARE @MoreRecords int
DECLARE @FirstRec int
DECLARE @LastRec int
DECLARE @DispFirstRec int
DECLARE @DispLastRec int
/*Find out first and last record we want
If Page=0, then we want all records!
*/
if @Page = 0
begin
SELECT @FirstRec = 0
SELECT @LastRec = @TotalRec + 1
end
else
begin
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)
end
-- Now return the set of paged records, plus an indication if we
-- have more to return or not.
SET @MoreRecords =
(SELECT COUNT(ID)
FROM #TempItems TI
WHERE TI.ID >= @LastRec)
-- Check @FirstRec & @LastRec.
-- @FirstRec should always be +1 based on the record count.
Set @DispFirstRec = @FirstRec + 1
-- @LastRec should be +1 unless it is on the last page.
if @LastRec > @TotalRec
Set @DispLastRec = @TotalRec
else
Set @DispLastRec = @LastRec - 1
--First ResultSet (TotalRec, MoreRecords, FirstRec, LastRec
SELECT TotalRec = @TotalRec, MoreRecords = @MoreRecords, FirstRec =
@DispFirstRec, LastRec = @DispLastRec
--Second ResultSet (All Records)
SELECT ID, ItemID, PubSource, ISBN
FROM #TempItems
WHERE ID>@FirstRec AND ID<@LastRec
--exec sp_executesql @query
end
else
begin
--Empty ResultSet (Return all Zeros)
SELECT TotalRec = 0, MoreRecords = 0, FirstRec = 0, LastRec =0
SELECT @query
end
--Don't need the Drop table as its automatically done after this procedure
is done.
--DROP TABLE #TempItems
-- Turn NOCOUNT back OFF
SET NOCOUNT OFF
GO

Viewing 12 posts - 1 through 11 (of 11 total)

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

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 1

You should be able to return the actual query plan for the proc when it runs - but if you still have problems you can capture the plan in profiler.

Once you have the plan you should be able to find your bottleneck. The tables are pretty small , the temp table ops will cause the proc to recompile - you need to decide if this is a problem.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Bryant McClellan SSCarpal Tunnel Points: 4397 More actions · Answer 2

Since you are performing searches on fields that are not indexed (like title or author), I'd venture that you are getting table scans on a number of the queries. And since a table scan does just that, the performance would degrade further as the tables grow.

------------
Buy the ticket, take the ride. -- Hunter S. Thompson

RLee SSC Veteran Points: 241 More actions · Answer 3

Ok, i didn't know i could capture the plan in profiler.. i'll do that now..

Each table (items/pubitems) have about 80 fields, and not the

RLee SSC Veteran Points: 241 More actions · Answer 4

Well, the tables are indexed (full text indexed)... TitleIndex, AuthorIndex, SeriesName (and a few others that aren't listed above)..

But is it possible that i'm still getting a table scan? How do i tell?

RLee SSC Veteran Points: 241 More actions · Answer 5

Hi,

Here's a copy of one of the execution plans that may help figure out why this is taking so long...

Here's the Insert statement, from the plan.

INSERT INTO #TempItems (I.ItemID, I.PubSource, I.ISBN, I.Dewey, I.TitleIndex, I.AuthorIndex, I.PubYear, I.PubDate, I.SeriesName, I.SeriesNumber) SELECT I.ItemID, '', I.ISBN, I.Dewey, I.TitleIndex, I.AuthorIndex, I.PubYear, I.PubDate, I.SeriesName, I.SeriesNumber FROM Items I WHERE (FREETEXT (I.TitleText, '"al"') OR FREETEXT (I.Subtitle, '"al"') OR FREETEXT (I.AuthorText, '"al"') OR FREETEXT (I.IllusText, '"al"')) AND (FREETEXT (I.TitleText, '"capone"') OR FREETEXT (I.Subtitle, '"capone"') OR FREETEXT (I.AuthorText, '"capone"') OR FREETEXT (I.IllusText, '"capone"')) AND (FREETEXT (I.TitleText, '"does"') OR FREETEXT (I.Subtitle, '"does"') OR FREETEXT (I.AuthorText, '"does"') OR FREETEXT (I.IllusText, '"does"')) AND (FREETEXT (I.TitleText, '"my"') OR FREETEXT (I.Subtitle, '"my"') OR FREETEXT (I.AuthorText, '"my"') OR FREETEXT (I.IllusText, '"my"')) AND (FREETEXT (I.TitleText, '"shirts"') OR FREETEXT (I.Subtitle, '"shirts"') OR FREETEXT (I.AuthorText, '"shirts"') OR FREETEXT (I.IllusText, '"shirts"')) UNION SELECT PI.ItemID, PI.PubSource, PI.ISBN, PI.Dewey, PI.TitleIndex, PI.AuthorIndex, PI.PubYear, PI.PubDate, PI.SeriesName, PI.SeriesNumber FROM PubItems PI WHERE (FREETEXT (PI.TitleText, '"al"') OR FREETEXT (PI.Subtitle, '"al"') OR FREETEXT (PI.AuthorText, '"al"') OR FREETEXT (PI.IllusText, '"al"')) AND (FREETEXT (PI.TitleText, '"capone"') OR FREETEXT (PI.Subtitle, '"capone"') OR FREETEXT (PI.AuthorText, '"capone"') OR FREETEXT (PI.IllusText, '"capone"')) AND (FREETEXT (PI.TitleText, '"does"') OR FREETEXT (PI.Subtitle, '"does"') OR FREETEXT (PI.AuthorText, '"does"') OR FREETEXT (PI.IllusText, '"does"')) AND (FREETEXT (PI.TitleText, '"my"') OR FREETEXT (PI.Subtitle, '"my"') OR FREETEXT (PI.AuthorText, '"my"') OR FREETEXT (PI.IllusText, '"my"')) AND (FREETEXT (PI.TitleText, '"shirts"') OR FREETEXT (PI.Subtitle, '"shirts"') OR FREETEXT (PI.AuthorText, '"shirts"') OR FREETEXT (PI.IllusText, '"shirts"')) ORDER BY AuthorIndex ASC

Maybe something in here can help me figure out on how to make this quicker?

Thanks,

[Edit: Sorry, not sure how to remove smileys... = : ( and = : P ]

...Robin

Execution Tree

--------------

Clustered Index Insert(OBJECT[tempdb].[dbo].[#TempItems__________________________________________________________________________________________________________000100023F7C].[PK__#TempItems__54C73499]), SET[#TempItems].[SeriesNumber]=[Union1109], [#TempItems].[PubDate]=[Union1107], [#TempItems].[ItemID]=[Union1100], [#TempItems].[ID]=RaiseIfNull([Expr1110]), [#TempItems].[PubSource]=[Union1101], [#TempItems].[ISBN]=[Union1102], [#TempItems].[Dewey]=[Union1103], [#TempItems].[TitleIndex]=[Union1104], [#TempItems].[AuthorIndex]=[Union1105], [#TempItems].[PubYear]=[Union1106], [#TempItems].[SeriesName]=[Union1108]))

|--Top(ROWCOUNT est 0)

|--Compute Scalar(DEFINE[Expr1110]=getidentity(1406341216, 2, NULL)))

|--Sort(DISTINCT ORDER BY[Union1105] ASC, [Union1100] ASC, [Union1101] ASC, [Union1102] ASC, [Union1103] ASC, [Union1104] ASC, [Union1106] ASC, [Union1107] ASC, [Union1108] ASC, [Union1109] ASC))

|--Concatenation

|--Compute Scalar(DEFINE[Expr1042]=Convert(.[ISBN]), [Expr1043]=Convert(.[Dewey]), [Expr1044]=Convert(.[TitleIndex]), [Expr1045]=Convert(.[AuthorIndex]), [Expr1046]=Convert(.[PubYear]), [Expr1047]=Convert(.[PubDate]), [Expr1048]=Convert(.[SeriesName]), [Expr1049]=Convert(.[SeriesNumber])))

| |--Filter(WHERE(([Expr1129] OR [Expr1130]) OR [Expr1131]) OR [Expr1132]))

| |--Merge Join(Left Semi Join, MERGE.[ItemID])=([FULLTEXT:Items].), RESIDUAL.[ItemID]=[FULLTEXT:Items].) OR (([Expr1129] OR [Expr1130]) OR [Expr1131]))

| |--Merge Join(Left Semi Join, MERGE.[ItemID])=([FULLTEXT:Items].), RESIDUAL.[ItemID]=[FULLTEXT:Items].) OR ([Expr1129] OR [Expr1130]))

| | |--Merge Join(Left Semi Join, MERGE.[ItemID])=([FULLTEXT:Items].), RESIDUAL.[ItemID]=[FULLTEXT:Items].) OR [Expr1129])

| | | |--Merge Join(Left Semi Join, MERGE.[ItemID])=([FULLTEXT:Items].), RESIDUAL.[ItemID]=[FULLTEXT:Items].))

| | | | |--Filter(WHERE(([Expr1125] OR [Expr1126]) OR [Expr1127]) OR [Expr1128]))

| | | | | |--Merge Join(Left Semi Join, MERGE.[ItemID])=([FULLTEXT:Items].), RESIDUAL.[ItemID]=[FULLTEXT:Items].) OR (([Expr1125] OR [Expr1126]) OR [Expr1127]))

| | | | | |--Merge Join(Left Semi Join, MERGE.[ItemID])=([FULLTEXT:Items].), RESIDUAL.[ItemID]=[FULLTEXT:Items].) OR ([Expr1125] OR [Expr1126]))

| | | | | | |--Merge Join(Left Semi Join, MERGE.[ItemID])=([FULLTEXT:Items].), RESIDUAL.[ItemID]=[FULLTEXT:Items].) OR [Expr1125])

| | | | | | | |--Merge Join(Left Semi Join, MERGE.[ItemID])=([FULLTEXT:Items].), RESIDUAL.[ItemID]=[FULLTEXT:Items].))

| | | | | | | | |--Filter(WHERE(([Expr1121] OR [Expr1122]) OR [Expr1123]) OR [Expr1124]))

| | | | | | | | | |--Merge Join(Left Semi Join, MERGE.[ItemID])=([FULLTEXT:Items].), RESIDUAL.[ItemID]=[FULLTEXT:Items].) OR (([Expr1121] OR [Expr1122]) OR [Expr1123]))

| | | | | | | | | |--Nested Loops(Left Semi Join, WHERE[Expr1121] OR [Expr1122])OUTER REFERENCES.[ItemID]), DEFINE[Expr1123] = [PROBE VALUE]))

| | | | | | | | | | |--Merge Join(Left Semi Join, MERGE.[ItemID])=([FULLTEXT:Items].), RESIDUAL.[ItemID]=[FULLTEXT:Items].) OR [Expr1121])

| | | | | | | | | | | |--Merge Join(Left Semi Join, MERGE.[ItemID])=([FULLTEXT:Items].), RESIDUAL.[ItemID]=[FULLTEXT:Items].))

| | | | | | | | | | | | |--Filter(WHERE(([Expr1117] OR [Expr1118]) OR [Expr1119]) OR [Expr1120]))

| | | | | | | | | | | | | |--Merge Join(Left Semi Join, MERGE.[ItemID])=([FULLTEXT:Items].), RESIDUAL.[ItemID]=[FULLTEXT:Items].) OR (([Expr1117] OR [Expr1118]) OR [Expr1119]))

| | | | | | | | | | | | | |--Merge Join(Left Semi Join, MERGE.[ItemID])=([FULLTEXT:Items].), RESIDUAL.[ItemID]=[FULLTEXT:Items].) OR ([Expr1117] OR [Expr1118]))

| | | | | | | | | | | | | | |--Nested Loops(Left Semi Join, WHERE[Expr1117])OUTER REFERENCES.[ItemID]), DEFINE[Expr1118] = [PROBE VALUE]))

| | | | | | | | | | | | | | | |--Merge Join(Left Semi Join, MERGE.[ItemID])=([FULLTEXT:Items].), RESIDUAL.[ItemID]=[FULLTEXT:Items].))

| | | | | | | | | | | | | | | | |--Filter(WHERE(([Expr1113] OR [Expr1114]) OR [Expr1115]) OR [Expr1116]))

| | | | | | | | | | | | | | | | | |--Merge Join(Left Semi Join, MERGE.[ItemID])=([FULLTEXT:Items].), RESIDUAL.[ItemID]=[FULLTEXT:Items].) OR (([Expr1113] OR [Expr1114]) OR [Expr1115]))

| | | | | | | | | | | | | | | | | |--Merge Join(Left Semi Join, MERGE.[ItemID])=([FULLTEXT:Items].), RESIDUAL.[ItemID]=[FULLTEXT:Items].) OR ([Expr1113] OR [Expr1114]))

| | | | | | | | | | | | | | | | | | |--Nested Loops(Left Semi Join, WHERE[Expr1113])OUTER REFERENCES.[ItemID]), DEFINE[Expr1114] = [PROBE VALUE]))

| | | | | | | | | | | | | | | | | | | |--Nested Loops(Left Semi Join, OUTER REFERENCES.[ItemID]), DEFINE[Expr1113] = [PROBE VALUE]))

Clustered Index Scan(OBJECT[Catalog].[dbo].[Items].[PK_Items_ItemID] AS ), ORDERED FORWARD)

Filter(WHERE.[ItemID]=[FULLTEXT:Items].))

Table Spool

|--Remote Scan(OBJECTFREETEXT))

| | | | | | | | | | | | | | | | | | | |--Filter(WHERE.[ItemID]=[FULLTEXT:Items].))

Table Spool

Remote Scan(OBJECTFREETEXT))

| | | | | | | | | | | | | | | |--Filter(WHERE.[ItemID]=[FULLTEXT:Items].))

| | | | | | | | | | |--Filter(WHERE.[ItemID]=[FULLTEXT:Items].))

| | | |--Sort(ORDER BY[FULLTEXT:Items]. ASC))

| | | |--Remote Scan(OBJECTFREETEXT))

| | |--Sort(ORDER BY[FULLTEXT:Items]. ASC))

| | |--Remote Scan(OBJECTFREETEXT))

| |--Sort(ORDER BY[FULLTEXT:Items]. ASC))

| |--Remote Scan(OBJECTFREETEXT))

|--Compute Scalar(DEFINE[Expr1091]=Convert([PI].[PubSource]), [Expr1092]=Convert([PI].[ISBN]), [Expr1093]=Convert([PI].[Dewey]), [Expr1094]=Convert([PI].[TitleIndex]), [Expr1095]=Convert([PI].[AuthorIndex]), [Expr1096]=Convert([PI].[PubYear]), [Expr1097]=Convert([PI].[PubDate]), [Expr1098]=Convert([PI].[SeriesName]), [Expr1099]=Convert([PI].[SeriesNumber])))

|--Filter(WHERE(([Expr1149] OR [Expr1150]) OR [Expr1151]) OR [Expr1152]))

|--Merge Join(Left Semi Join, MERGE[PI].[ItemID])=([FULLTEXTubItems].), RESIDUAL[PI].[ItemID]=[FULLTEXTubItems].) OR (([Expr1149] OR [Expr1150]) OR [Expr1151]))

|--Merge Join(Left Semi Join, MERGE[PI].[ItemID])=([FULLTEXTubItems].), RESIDUAL[PI].[ItemID]=[FULLTEXTubItems].) OR ([Expr1149] OR [Expr1150]))

| |--Merge Join(Left Semi Join, MERGE[PI].[ItemID])=([FULLTEXTubItems].), RESIDUAL[PI].[ItemID]=[FULLTEXTubItems].) OR [Expr1149])

| | |--Merge Join(Left Semi Join, MERGE[PI].[ItemID])=([FULLTEXTubItems].), RESIDUAL[PI].[ItemID]=[FULLTEXTubItems].))

| | | |--Filter(WHERE(([Expr1145] OR [Expr1146]) OR [Expr1147]) OR [Expr1148]))

| | | | |--Merge Join(Left Semi Join, MERGE[PI].[ItemID])=([FULLTEXTubItems].), RESIDUAL[PI].[ItemID]=[FULLTEXTubItems].) OR (([Expr1145] OR [Expr1146]) OR [Expr1147]))

| | | | |--Merge Join(Left Semi Join, MERGE[PI].[ItemID])=([FULLTEXTubItems].), RESIDUAL[PI].[ItemID]=[FULLTEXTubItems].) OR ([Expr1145] OR [Expr1146]))

| | | | | |--Merge Join(Left Semi Join, MERGE[PI].[ItemID])=([FULLTEXTubItems].), RESIDUAL[PI].[ItemID]=[FULLTEXTubItems].) OR [Expr1145])

| | | | | | |--Merge Join(Left Semi Join, MERGE[PI].[ItemID])=([FULLTEXTubItems].), RESIDUAL[PI].[ItemID]=[FULLTEXTubItems].))

| | | | | | | |--Filter(WHERE(([Expr1141] OR [Expr1142]) OR [Expr1143]) OR [Expr1144]))

| | | | | | | | |--Merge Join(Left Semi Join, MERGE[PI].[ItemID])=([FULLTEXTubItems].), RESIDUAL[PI].[ItemID]=[FULLTEXTubItems].) OR (([Expr1141] OR [Expr1142]) OR [Expr1143]))

| | | | | | | | |--Merge Join(Left Semi Join, MERGE[PI].[ItemID])=([FULLTEXTubItems].), RESIDUAL[PI].[ItemID]=[FULLTEXTubItems].) OR ([Expr1141] OR [Expr1142]))

| | | | | | | | | |--Merge Join(Left Semi Join, MERGE[PI].[ItemID])=([FULLTEXTubItems].), RESIDUAL[PI].[ItemID]=[FULLTEXTubItems].) OR [Expr1141])

| | | | | | | | | | |--Merge Join(Left Semi Join, MERGE[PI].[ItemID])=([FULLTEXTubItems].), RESIDUAL[PI].[ItemID]=[FULLTEXTubItems].))

| | | | | | | | | | | |--Filter(WHERE(([Expr1137] OR [Expr1138]) OR [Expr1139]) OR [Expr1140]))

| | | | | | | | | | | | |--Merge Join(Left Semi Join, MERGE[PI].[ItemID])=([FULLTEXTubItems].), RESIDUAL[PI].[ItemID]=[FULLTEXTubItems].) OR (([Expr1137] OR [Expr1138]) OR [Expr1139]))

| | | | | | | | | | | | |--Merge Join(Left Semi Join, MERGE[PI].[ItemID])=([FULLTEXTubItems].), RESIDUAL[PI].[ItemID]=[FULLTEXTubItems].) OR ([Expr1137] OR [Expr1138]))

| | | | | | | | | | | | | |--Merge Join(Left Semi Join, MERGE[PI].[ItemID])=([FULLTEXTubItems].), RESIDUAL[PI].[ItemID]=[FULLTEXTubItems].) OR [Expr1137])

| | | | | | | | | | | | | | |--Merge Join(Left Semi Join, MERGE[PI].[ItemID])=([FULLTEXTubItems].), RESIDUAL[PI].[ItemID]=[FULLTEXTubItems].))

| | | | | | | | | | | | | | | |--Filter(WHERE(([Expr1133] OR [Expr1134]) OR [Expr1135]) OR [Expr1136]))

| | | | | | | | | | | | | | | | |--Merge Join(Left Semi Join, MERGE[PI].[ItemID])=([FULLTEXTubItems].), RESIDUAL[PI].[ItemID]=[FULLTEXTubItems].) OR (([Expr1133] OR [Expr1134]) OR [Expr1135]))

| | | | | | | | | | | | | | | | |--Merge Join(Left Semi Join, MERGE[PI].[ItemID])=([FULLTEXTubItems].), RESIDUAL[PI].[ItemID]=[FULLTEXTubItems].) OR ([Expr1133] OR [Expr1134]))

| | | | | | | | | | | | | | | | | |--Merge Join(Left Semi Join, MERGE[PI].[ItemID])=([FULLTEXTubItems].), RESIDUAL[PI].[ItemID]=[FULLTEXTubItems].) OR [Expr1133])

| | | | | | | | | | | | | | | | | | |--Merge Join(Left Semi Join, MERGE[PI].[ItemID])=([FULLTEXTubItems].), RESIDUAL[PI].[ItemID]=[FULLTEXTubItems].))

Clustered Index Scan(OBJECT[Catalog].[dbo].[PubItems].[PK_ItemsRHTemp] AS [PI]), ORDERED FORWARD)

Sort(ORDER BY[FULLTEXTubItems]. ASC))

Remote Scan(OBJECTFREETEXT))

Remote Scan(OBJECTFREETEXT))

| | | |--Sort(ORDER BY[FULLTEXTubItems]. ASC))

| | | |--Remote Scan(OBJECTFREETEXT))

| | |--Sort(ORDER BY[FULLTEXTubItems]. ASC))

| | |--Remote Scan(OBJECTFREETEXT))

| |--Sort(ORDER BY[FULLTEXTubItems]. ASC))

| |--Remote Scan(OBJECTFREETEXT))

|--Sort(ORDER BY[FULLTEXTubItems]. ASC))

|--Remote Scan(OBJECTFREETEXT))

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 6

Here's my 'short list' for tuning:

Round I

DBCC UPDATEUSAGE

UPDATE STATISTICS (with FULL scan) for all tables

exec sp_recompile for all tables

exec sp_refreshview for all views

Round II

DBCC DBREINDEX

UPDATE STATISTICS (with FULL scan) for all tables

exec sp_recompile for all tables

exec sp_refreshview for all views

Round III

Profiler

Query Hints

The reason I'm posting is because you do not mention any database/tabler/index maintenance is ocurring. If it is not, then I'd suggest stepping back a bit in the process and you'll probably find a bigger 'bang' !

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

RLee SSC Veteran Points: 241 More actions · Answer 7

Hi Rudy,

Thanks for that... i will look at going through that list and applying it...

I will mention that i have a database maintenance plan which does Optimization (reorganize data and index pages), remove unused space, Check Integrity (Done Daily) and Backup (Once a week).

I've set up Full text change tracking, and update index in background on those 2 tables (Items/PubItems).

So i'm still stuck on what can be done on trying to improve performance... mind you, i did run profiler to capture and did notice a major improvement (something that took 8 hours, took 1 hour instead)...

The main thing is that specific procedure (above) that keeps taking too much time...

I've added "WITH RECOMPILE"... but maybe i should remove that?

...Robin

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 8

I would not remove the 'WITH RECOMPILE' if this is a batch type proc. The 'WITH RECOMPILE' option on a SP allows it to make use of the updated statistics in order to come up with an optimal execution pan to access the data. However, if you run sp_recompile for all tables then the 'WITH RECOMPILE' is not needed. You also might want to chenge the 'sampling' percentage for your optimization as well (maybe a separate JOB for each table, Items/PubItems, to sample 100% ?). The more you sample the better your statistics, which in turn gives you a more optimal execution plan.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

RLee SSC Veteran Points: 241 More actions · Answer 9

Hi Rudy,

I did in fact have a separate Update statistics maintenance plan that runs daily at 10:30pm, which does a sampling of 100% of the database... I don't know if it made much of a difference though...

I only use "with recompile" in the above stored procedure... the rest of the stored procedures are generally instant (as there is only ONE select statement in there)...

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 10

Round III

Profiler - you're already there

Query Hints

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Bryant McClellan SSCarpal Tunnel Points: 4397 More actions · Answer 11

There are a couple of Clustered Index Scans mentioned but I don't see a table scan. You can turn the Clustered Index Scans into Seeks with some judicious references to the fields in those indexes. If, for example, the CI is on an identity field, check that that field > 0. Whether this will help measurably or not, I can't say.

Another thing to observe is the number of Remote Scans. It appears that these are references to the full text indexes. BOL doesn't distinguish but it appears that the full text indexes are considered remote objects. Anybody familiar with that?

I also see a lot of Where Exprxx OR Expryy OR Exprzz. Depending on the indexing scheme, ORs that cross a number of columns can result in less than optimal execution plans.

------------
Buy the ticket, take the ride. -- Hunter S. Thompson