December 2, 2010 at 8:16 am
Hi all,
please find the below code . Any suggestions on improving the performance of the t-sql batch is highly appreciated.
BEGIN
declare @SearchText varchar(100)
declare @ArticleTypeIDs varchar(20)
declare @TopicIDs varchar(20)
declare @RegionIDs varchar(15)
declare @DateFrom smalldatetime
declare @DateTo smalldatetime
declare @DateOn smalldatetime
declare @MagazineIssueID int
declare @Skip int
declare @Take int
declare @TotalRecords int
set @SearchText = 'google'
set @ArticleTypeIDs = null
set @TopicIDs = null
set @RegionIDs = null
set @DateFrom = null
set @DateTo = null
set @DateOn = null
set @MagazineIssueID = 11
set @Skip = 0
set @Take = 10
set @TotalRecords = null
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @table_Results TABLE(ID int, RowNumber int)
DECLARE @SQL varchar(max)
SET @SQL = '
SELECT Article_ID AS [ID], ROW_NUMBER() OVER (order by Article_StartDate DESC) AS RowNumber
FROM Articles
WHERE Article_Approved = 1
AND ISNULL(Article_EndDate, getDate()) >= getDate()
AND cast(Floor(cast(Article_StartDate AS float)) AS datetime) <= getDate()'
IF (@SearchText IS NOT NULL) AND (@SearchText <> '')
BEGIN
SET @SQL = @SQL + '
AND CONTAINS(Article_BodyRaw, ''' + @SearchText + ''')'
END
-- do dates
IF NOT @DateOn IS NULL
BEGIN
SET @SQL = @SQL + '
AND cast(Floor(cast(Article_StartDate AS float)) AS datetime) = ''' + CAST(@DateOn AS varchar) + ''''
END
ELSE
BEGIN
IF (NOT @DateFrom IS NULL) AND (NOT @DateTo IS NULL)
BEGIN -- if we have a from and to date, we use between
SET @SQL = @SQL + '
AND cast(Floor(cast(Article_StartDate AS float)) AS datetime) BETWEEN ''' + CAST(@DateFrom AS varchar) + ''' AND ''' + CAST(@DateTo AS varchar) + ''''
END
ELSE -- we add them individually
BEGIN
IF NOT @DateFrom IS NULL
BEGIN
SET @SQL = @SQL + '
AND cast(Floor(cast(Article_StartDate AS float)) AS datetime) >= ''' + CAST(@DateFrom AS varchar) + ''''
END
IF NOT @DateTo IS NULL
BEGIN
SET @SQL = @SQL + '
AND cast(Floor(cast(Article_StartDate AS float)) AS datetime) <= ''' + CAST(@DateTo AS varchar) + ''''
END
END
END
-- article types
IF (@ArticleTypeIDs IS NOT NULL) AND (@ArticleTypeIDs <> '')
BEGIN
SET @SQL = @SQL + '
AND ArticleType_ID IN (SELECT item FROM fnSplit(''' + @ArticleTypeIDs + ''', '',''))'
END
-- my attempt at topics
IF (@TopicIDs IS NOT NULL) AND (@TopicIDs <> '')
BEGIN
SET @SQL = @SQL + '
AND Article_ID IN (SELECT a.Article_ID
FROM Articles a INNER JOIN X_ArticleTopic xat ON a.Article_ID = xat.Article_ID
WHERE xat.Topic_ID IN (SELECT item FROM fnSplit(''' + @TopicIDs + ''', '','')))'
END
-- my attempt at regions
IF (@RegionIDs IS NOT NULL) AND (@RegionIDs <> '')
BEGIN
SET @SQL = @SQL + '
AND Article_ID IN (SELECT a.Article_ID
FROM Articles a INNER JOIN X_ArticleRegion xar ON a.Article_ID = xar.Article_ID
WHERE xar.Region_ID IN (SELECT item FROM fnSplit(''' + @RegionIDs + ''', '','')))'
END
-- and now limit it to a magazine issue if appropriate
if (@MagazineIssueID is not null) and @MagazineIssueID > 0
begin
SET @SQL = @SQL + '
and Article_ID in (select Article_ID
from dbo.X_ArticleMagazineIssue
where MagazineIssue_ID = ' + cast(@MagazineIssueID as varchar) + ')'
end
--SELECT @SQL
INSERT INTO @table_Results
EXEC (@SQL);
end
December 2, 2010 at 8:41 am
Why are you doing all those conversions on the date column? Can you not just leave them as datetime? I'd guess that's what's killing performance. In order to be sure, we need table and index DDL and execution plans from you, please.
John
December 2, 2010 at 8:52 am
please refer to the Gails article...we will be able to help a little better
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
How to Post Performance Problems
December 2, 2010 at 8:53 am
I'm seeing nested inline views - that is usually a performance killer.
Would you mind in posting cardinalities as well as available indexes? please include distinct keys counts for each index.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.December 2, 2010 at 9:11 am
Hi all,
Thanks for your reply.
I actually have one more query to include in the t-sql batch i posted.
and the query is
SELECT @TotalRecords = COUNT(ID) FROM @table_Results
SELECT a.Article_ID, a.ArticleType_ID, a.Source_ID, a.Article_Approved,
a.Article_StartDate, a.Article_EndDate, a.Article_Image, a.Article_Headline,
a.Article_Standfirst, s.Source_Name, at.ArticleType_Name
FROM @table_Results temp INNER JOIN Articles a ON temp.ID = a.Article_ID INNER JOIN
LKUP_Sources s ON a.Source_ID = s.Source_ID INNER JOIN
LKUP_ArticleTypes at ON a.ArticleType_ID = at.ArticleType_ID
WHERE temp.RowNumber BETWEEN @Skip AND (@Skip + @Take)
ORDER BY temp.RowNumber ASC
Please find the execution plan attached.
and here are the indexes on the article table.
1)
USE [TotalTelecom]
GO
/****** Object: Index [IX_a_articletypeid] Script Date: 12/02/2010 16:10:17 ******/
CREATE NONCLUSTERED INDEX [IX_a_articletypeid] ON [dbo].[Articles]
(
[ArticleType_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
2)
USE [TotalTelecom]
GO
/****** Object: Index [IX_a_userid] Script Date: 12/02/2010 16:10:41 ******/
CREATE NONCLUSTERED INDEX [IX_a_userid] ON [dbo].[Articles]
(
[User_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
3)
USE [TotalTelecom]
GO
/****** Object: Index [PK_Articles] Script Date: 12/02/2010 16:10:57 ******/
ALTER TABLE [dbo].[Articles] ADD CONSTRAINT [PK_Articles] PRIMARY KEY CLUSTERED
(
[Article_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Hope these details will help you understand where its all going wrong.
Thnaks in advance
December 2, 2010 at 11:20 am
so... no answers at all to our questions? 😀
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.December 2, 2010 at 11:34 am
I had a look at the Execution plan you posted.
It looks pretty straight forward.
If 50% of the time it takes is sorting 2 (two) rows in a table variable I I don't think the overall performance would be an issue.
The dynamic SQL you build might be a different story though...
Please post the result of SELECT @SQL from your first post together with the actual execution plan when running that query.
That might give us another hint or two.
December 2, 2010 at 12:16 pm
agree this plan is not your problem.. if you captured a trace or set statistics time on you would see that this is very fast
December 9, 2010 at 6:08 am
Deepathy,
The link to "How to post performance problems" is great. In looking at everything you've posted there are two things I don't know, 1) How long the batch takes to run and 2) what are your expectations.
A posting like this is like going to the doctor's office and saying, "Doctor, fix me." Well, of course he can't fix you if he doesn't know what's wrong. Where do you hurt? When did it start? How often does the pain last?.....etc.
Performance, like pain is subjective. What's fast for you may be slow for me.
So, here's what I'd do if I were you. I would take each step of that batch process and cut it up into little pieces. Run each piece individually. Find your bottleneck and focus on that. If that's not possible, throw in some debugging statements, along with some timing calculations. I'm all for execution plans and what not, but end customers don't care about execution plans they care about the time something takes to run.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply