SQL Server Performance Forum – Threads Archive

SP Tuning

Hi All, I havent really done too much performance tuning so far in my time as a DBA – i have really just been spending my time trying to get things that work regardless of performance…until now [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] I have written the below procedure, and was hoping to get some tips on general things that might improve it, and specific things i should look at in execution plans, etc. Anyway, here it is…Any tips would be hugely appreciated! Ben <pre id="code">CREATE PROCEDURE Run_TrainingLoadTrainingTreeByTerminal @TerminalId_ NVARCHAR (<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> AS DECLARE @BookTreeTable TABLE (BookName NVARCHAR(100), BookDisplayName NVARCHAR(100), BookVersion INT, BookDescription NVARCHAR(500), ChapterName NVARCHAR(100), ChapterDisplayName NVARCHAR(100), ChapterVersion INT, ChapterOrder INT, ChapterDescription NVARCHAR(500), ScreenName NVARCHAR(100), ScreenDisplayName NVARCHAR(100), ScreenVersion INT, ScreenOrder INT, BackgroundImage NVARCHAR(50), BackgroundImageX INT, BackgroundImageY INT, TopicName NVARCHAR(100), TopicDisplayName NVARCHAR(100), TopicVersion INT, TopicOrder INT, MinimumTime INT, DocumentText NVARCHAR(3500), AccumulatedTimeCurrentVersion INT, AccumulatedTimeMajorVersion INT) INSERT INTO @BookTreeTable (BookName,BookDisplayName, BookVersion, BookDescription, ChapterName, ChapterDisplayName, ChapterVersion, ChapterOrder, ChapterDescription, ScreenName, ScreenDisplayName, ScreenVersion, ScreenOrder, BackgroundImage, BackgroundImageX, BackgroundImageY, TopicName, TopicDisplayName, TopicVersion, TopicOrder, MinimumTime, DocumentText, AccumulatedTimeCurrentVersion, AccumulatedTimeMajorVersion) SELECT dbo.tbl_TrainingBook.BookName, dbo.tbl_TrainingBookVersion.BookDisplayName, dbo.tbl_TrainingBookVersion.BookVersion, dbo.tbl_TrainingBookVersion.BookDescription, dbo.tbl_TrainingBookChapter.ChapterName, dbo.tbl_TrainingChapterVersion.ChapterDisplayName, dbo.tbl_TrainingBookChapter.ChapterVersion, dbo.tbl_TrainingBookChapter.ChapterOrder, dbo.tbl_TrainingChapterVersion.ChapterDescription, dbo.tbl_TrainingScreenTopic.ScreenName, dbo.tbl_TrainingScreenVersion.ScreenDisplayName, dbo.tbl_TrainingChapterScreen.ScreenVersion, dbo.tbl_TrainingChapterScreen.ScreenOrder, dbo.tbl_TrainingScreenVersion.BackgroundImage, dbo.tbl_TrainingScreenVersion.BackgroundImageX, dbo.tbl_TrainingScreenVersion.BackgroundImageY, dbo.tbl_TrainingTopicVersion.TopicName, dbo.tbl_TrainingTopicVersion.TopicDisplayName, dbo.tbl_TrainingScreenTopic.TopicVersion, dbo.tbl_TrainingScreenTopic.TopicOrder, dbo.tbl_TrainingTopicVersion.MinimumTime, tbl_TrainingTopicVersion.DocumentText, AccumulatedTimeCurrentVersion.AccumulatedTimeCurrentVersion, TerminalAccumulatedTimeMajorVersion.AccumulatedTimeMajorVersion FROM dbo.tbl_TrainingBook INNER JOIN dbo.tbl_TrainingBookVersion ON dbo.tbl_TrainingBook.BookName = dbo.tbl_TrainingBookVersion.BookName AND dbo.tbl_TrainingBook.CurrentVersionNo = dbo.tbl_TrainingBookVersion.BookVersion INNER JOIN dbo.tbl_TrainingBookChapter ON dbo.tbl_TrainingBookVersion.BookName = dbo.tbl_TrainingBookChapter.BookName AND dbo.tbl_TrainingBookVersion.BookVersion = dbo.tbl_TrainingBookChapter.BookVersion INNER JOIN dbo.tbl_TrainingChapterVersion ON dbo.tbl_TrainingBookChapter.ChapterName = dbo.tbl_TrainingChapterVersion.ChapterName AND dbo.tbl_TrainingBookChapter.ChapterVersion = dbo.tbl_TrainingChapterVersion.ChapterVersion INNER JOIN dbo.tbl_TrainingChapterScreen ON dbo.tbl_TrainingChapterVersion.ChapterName = dbo.tbl_TrainingChapterScreen.ChapterName AND dbo.tbl_TrainingChapterVersion.ChapterVersion = dbo.tbl_TrainingChapterScreen.ChapterVersion INNER JOIN dbo.tbl_TrainingChapter ON dbo.tbl_TrainingChapterVersion.ChapterName = dbo.tbl_TrainingChapter.ChapterName INNER JOIN dbo.tbl_TrainingScreenVersion ON dbo.tbl_TrainingChapterScreen.ScreenName = dbo.tbl_TrainingScreenVersion.ScreenName AND dbo.tbl_TrainingChapterScreen.ScreenVersion = dbo.tbl_TrainingScreenVersion.ScreenVersion INNER JOIN dbo.tbl_TrainingScreenTopic ON dbo.tbl_TrainingScreenVersion.ScreenName = dbo.tbl_TrainingScreenTopic.ScreenName AND dbo.tbl_TrainingScreenVersion.ScreenVersion = dbo.tbl_TrainingScreenTopic.ScreenVersion INNER JOIN dbo.tbl_TrainingScreen ON dbo.tbl_TrainingScreenVersion.ScreenName = dbo.tbl_TrainingScreen.ScreenName INNER JOIN dbo.tbl_TrainingTopicVersion ON dbo.tbl_TrainingScreenTopic.TopicName = dbo.tbl_TrainingTopicVersion.TopicName AND dbo.tbl_TrainingScreenTopic.TopicVersion = dbo.tbl_TrainingTopicVersion.TopicVersion INNER JOIN dbo.tbl_TrainingTopic ON dbo.tbl_TrainingTopicVersion.TopicName = dbo.tbl_TrainingTopic.TopicName LEFT OUTER JOIN (SELECT TopicName, TopicVersion, SUM(AccumulatedTime) AS AccumulatedTimeCurrentVersion FROM dbo.tbl_TrainingTerminalSessionTopicProgress WHERE (TerminalId = @TerminalId_) GROUP BY TopicName, TopicVersion) AccumulatedTimeCurrentVersion ON dbo.tbl_TrainingScreenTopic.TopicName = AccumulatedTimeCurrentVersion.TopicName COLLATE Latin1_General_CI_AS AND dbo.tbl_TrainingScreenTopic.TopicVersion = AccumulatedTimeCurrentVersion.TopicVersion LEFT OUTER JOIN (SELECT TopicName, TopicVersion / 1000000 AS MajorVersion, SUM(AccumulatedTime) AS AccumulatedTimeMajorVersion FROM dbo.tbl_TrainingTerminalSessionTopicProgress WHERE (TerminalId = @TerminalId_) GROUP BY TopicName, TopicVersion / 1000000) TerminalAccumulatedTimeMajorVersion ON dbo.tbl_TrainingScreenTopic.TopicName = TerminalAccumulatedTimeMajorVersion.TopicName COLLATE Latin1_General_CI_AS AND dbo.tbl_TrainingScreenTopic.TopicVersion / 1000000 = TerminalAccumulatedTimeMajorVersion.MajorVersion WHERE (dbo.tbl_TrainingBook.Disable = 0) AND (dbo.tbl_TrainingChapter.Disable = 0) AND (dbo.tbl_TrainingScreen.Disable = 0) AND (dbo.tbl_TrainingTopic.Disable = 0) AND (dbo.tbl_TrainingBookVersion.Disable = 0) AND (dbo.tbl_TrainingChapterVersion.Disable = 0) AND (dbo.tbl_TrainingScreenVersion.Disable = 0) AND (dbo.tbl_TrainingTopicVersion.Disable = 0) AND (dbo.tbl_TrainingBookChapter.CurrentVersion = 1) AND (dbo.tbl_TrainingChapte rScreen.CurrentVersion = 1) AND (dbo.tbl_TrainingScreenTopic.CurrentVersion = 1) –Current Books SELECT DISTINCT BookName, BookDisplayName, BookVersion, BookDescription FROM @BookTreeTable –Current Chapters SELECT DISTINCT BookName, BookVersion, ChapterName, ChapterDisplayName, ChapterVersion, ChapterOrder, ChapterDescription FROM @BookTreeTable –Current Screens SELECT DISTINCT BookName, BookVersion, ChapterName, ChapterVersion, ScreenName, ScreenDisplayName, ScreenVersion, ScreenOrder, BackgroundImage, BackgroundImageX, BackgroundImageY FROM @BookTreeTable –Current Topics SELECT DISTINCT BookName, BookVersion, ChapterName, ChapterVersion, ScreenName, ScreenVersion, TopicName, TopicDisplayName, TopicVersion, TopicOrder, MinimumTime, ISNULL(AccumulatedTimeCurrentVersion, 0) AS AccumulatedTimeCurrentVersion, ISNULL(AccumulatedTimeMajorVersion, 0) AS AccumulatedTimeMajorVersion FROM @BookTreeTable –Current Events SELECT DISTINCT BTT.BookName, BTT.BookVersion, BTT.ChapterName, BTT.ChapterVersion, BTT.ScreenName, BTT.ScreenVersion, BTT.TopicName, BTT.TopicVersion, EventName, EventOrder FROM tbl_TrainingTopicEvent TTE INNER JOIN @BookTreeTable BTT ON TTE.TopicName = BTT.TopicName AND TTE.TopicVersion = BTT.TopicVersion WHERE TTE.Disable = 0 –Current Highlights SELECT DISTINCT BTT.BookName, BTT.BookVersion, BTT.ChapterName, BTT.ChapterVersion, BTT.ScreenName, BTT.ScreenVersion, TTEH.TopicName, TTEH.TopicVersion, TTEH.EventName, TTEH.EventOrder, TTEH.HighlightWidth, TTEH.HighlightHeight, TTEH.HighlightX, TTEH.HighlightY FROM tbl_TrainingTopicEvent TTE INNER JOIN @BookTreeTable BTT ON TTE.TopicName = BTT.TopicName AND TTE.TopicVersion = BTT.TopicVersion INNER JOIN tbl_TrainingTopicEventHighlight TTEH ON TTE.TopicName = TTEH.TopicName AND TTE.TopicVersion = TTEH.TopicVersion AND TTE.EventName = TTEH.EventName AND TTE.EventOrder = TTEH.EventOrder WHERE TTE.Disable = 0 AND TTEH.Disable = 0 –Current Callouts SELECT DISTINCT BTT.BookName, BTT.BookVersion, BTT.ChapterName, BTT.ChapterVersion, BTT.ScreenName, BTT.ScreenVersion, TTEC.TopicName, TTEC.TopicVersion, TTEC.EventName, TTEC.EventOrder, TTEC.CalloutText, TTEC.CalloutWidth, TTEC.CalloutHeight, TTEC.CalloutX, TTEC.CalloutY, TTEC.InstructionType, TTEC.DonglePosition FROM tbl_TrainingTopicEvent TTE INNER JOIN @BookTreeTable BTT ON TTE.TopicName = BTT.TopicName AND TTE.TopicVersion = BTT.TopicVersion INNER JOIN tbl_TrainingTopicEventCallout TTEC ON TTE.TopicName = TTEC.TopicName AND TTE.TopicVersion = TTEC.TopicVersion AND TTE.EventName = TTEC.EventName AND TTE.EventOrder = TTEC.EventOrder WHERE TTE.Disable = 0 AND TTEC.Disable = 0 –Current Images SELECT DISTINCT BTT.BookName, BTT.BookVersion, BTT.ChapterName, BTT.ChapterVersion, BTT.ScreenName, BTT.ScreenVersion, TTEDI.TopicName, TTEDI.TopicVersion, TTEDI.EventName, TTEDI.EventOrder, TTEDI.ImagePath, TTEDI.ImageX, TTEDI.ImageY FROM tbl_TrainingTopicEvent TTE INNER JOIN @BookTreeTable BTT ON TTE.TopicName = BTT.TopicName AND TTE.TopicVersion = BTT.TopicVersion INNER JOIN tbl_TrainingTopicEventDemonstrationImage TTEDI ON TTE.TopicName = TTEDI.TopicName AND TTE.TopicVersion = TTEDI.TopicVersion AND TTE.EventName = TTEDI.EventName AND TTE.EventOrder = TTEDI.EventOrder WHERE TTE.Disable = 0 AND TTEDI.Disable = 0 GO </pre id="code">

Do you really need nvarchar can’t go with varchat

quote:Originally posted by benwilson Hi All, (BookName NVARCHAR(100),
BookDisplayName NVARCHAR(100),
BookVersion INT,
BookDescription NVARCHAR(500),
ChapterName NVARCHAR(100),
ChapterDisplayName NVARCHAR(100),
ChapterVersion INT,
ChapterOrder INT,
ChapterDescription NVARCHAR(500),
ScreenName NVARCHAR(100),
ScreenDisplayName NVARCHAR(100),
ScreenVersion INT,
ScreenOrder INT,
BackgroundImage NVARCHAR(50),
TopicName NVARCHAR(100),
TopicDisplayName NVARCHAR(100),
TopicVersion INT,
TopicOrder INT,
MinimumTime INT,
DocumentText NVARCHAR(3500),
AccumulatedTimeCurrentVersion INT,
AccumulatedTimeMajorVersion INT) [/code]

we use nvarchars as standard throught our system, so yes, i think i need to stick with them here…would this really improve performance? if so, is this because of the size of the variables that would be created are smaller with varchars?

Use char/varchar columns instead of nchar/nvarchar if you do not need to store unicode data.
So, you can reduce the table’s size, this can improve performance of your queries and some maintenance tasks (such as backup, restore and so on).

quote:Originally posted by benwilson we use nvarchars as standard throught our system, so yes, i think i need to stick with them here…would this really improve performance? if so, is this because of the size of the variables that would be created are smaller with varchars?

I don’t like the concept you applied. Better build selects from source tables directly instead of making one big multi-join select and then filter results from there. Also separate selects in their own stored procedures.

ok…if i was to do it as a number of separate selects rather than the big one, and also split it up into a number of stored procs, i would then have to pass around a whole lot of table variables- would this be more efficient?

Here is version 2 of the procedure using smaller selects…if anything, i would say it is slightly slower than the first code i posted (the last 3 selects for highlights, callouts and images seems to be the main bottleneck). Once again, any tips would be greatly appreciated!: <pre id="code">CREATE PROCEDURE Run_TrainingLoadTrainingTreeByTerminal @TerminalId_ NVARCHAR (<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> AS DECLARE @BookTable TABLE (BookName NVARCHAR(100), BookDisplayName NVARCHAR(100), BookVersion INT, BookDescription NVARCHAR(500), BookOrder INT) DECLARE @ChapterTable TABLE (BookName NVARCHAR(100), BookVersion INT, ChapterName NVARCHAR(100), ChapterDisplayName NVARCHAR(100), ChapterVersion INT, ChapterOrder INT, ChapterDescription NVARCHAR(500)) DECLARE @ScreenTable TABLE (ChapterName NVARCHAR(100), ChapterVersion INT, ScreenName NVARCHAR(100), ScreenDisplayName NVARCHAR(100), ScreenVersion INT, ScreenOrder INT, BackgroundImage NVARCHAR(50), BackgroundImageX INT, BackgroundImageY INT) DECLARE @TopicTable TABLE (ScreenName NVARCHAR(100), ScreenVersion INT, TopicName NVARCHAR(100), TopicDisplayName NVARCHAR(100), TopicVersion INT, TopicOrder INT, MinimumTime INT, DocumentText NVARCHAR(3500), AccumulatedTimeCurrentVersion INT, AccumulatedTimeMajorVersion INT) INSERT INTO @BookTable (BookName, BookDisplayName, BookVersion, BookDescription, BookOrder) SELECT dbo.tbl_TrainingBook.BookName, dbo.tbl_TrainingBookVersion.BookDisplayName, dbo.tbl_TrainingBookVersion.BookVersion, dbo.tbl_TrainingBookVersion.BookDescription, dbo.tbl_TrainingBookVersion.BookOrder FROM dbo.tbl_TrainingBook INNER JOIN dbo.tbl_TrainingBookVersion ON dbo.tbl_TrainingBook.BookName = dbo.tbl_TrainingBookVersion.BookName AND dbo.tbl_TrainingBook.CurrentVersionNo = dbo.tbl_TrainingBookVersion.BookVersion WHERE (dbo.tbl_TrainingBook.Disable = 0) AND (dbo.tbl_TrainingBookVersion.Disable = 0) INSERT INTO @ChapterTable (BookName, BookVersion, ChapterName, ChapterDisplayName, ChapterVersion, ChapterOrder, ChapterDescription) SELECT BT.BookName, BT.BookVersion, dbo.tbl_TrainingChapterVersion.ChapterName, dbo.tbl_TrainingChapterVersion.ChapterDisplayName, dbo.tbl_TrainingChapterVersion.ChapterVersion, dbo.tbl_TrainingBookChapter.ChapterOrder, dbo.tbl_TrainingChapterVersion.ChapterDescription FROM dbo.tbl_TrainingBookChapter INNER JOIN dbo.tbl_TrainingChapterVersion ON dbo.tbl_TrainingBookChapter.ChapterName = dbo.tbl_TrainingChapterVersion.ChapterName AND dbo.tbl_TrainingBookChapter.ChapterVersion = dbo.tbl_TrainingChapterVersion.ChapterVersion INNER JOIN dbo.tbl_TrainingChapter ON dbo.tbl_TrainingChapterVersion.ChapterName = dbo.tbl_TrainingChapter.ChapterName INNER JOIN @BookTable BT ON BT.BookName = tbl_TrainingBookChapter.BookName AND BT.BookVersion = tbl_TrainingBookChapter.BookVersion WHERE (dbo.tbl_TrainingChapter.Disable = 0) AND (dbo.tbl_TrainingChapterVersion.Disable = 0) AND (dbo.tbl_TrainingBookChapter.CurrentVersion = 1) INSERT INTO @ScreenTable (ChapterName, ChapterVersion, ScreenName, ScreenDisplayName, ScreenVersion, ScreenOrder, BackgroundImage, BackgroundImageX, BackgroundImageY) SELECT CT.ChapterName, CT.ChapterVersion, dbo.tbl_TrainingScreenVersion.ScreenName, dbo.tbl_TrainingScreenVersion.ScreenDisplayName, dbo.tbl_TrainingScreenVersion.ScreenVersion, dbo.tbl_TrainingChapterScreen.ScreenOrder, dbo.tbl_TrainingScreenVersion.BackgroundImage, dbo.tbl_TrainingScreenVersion.BackgroundImageX, dbo.tbl_TrainingScreenVersion.BackgroundImageY FROM dbo.tbl_TrainingChapterScreen INNER JOIN dbo.tbl_TrainingScreenVersion ON dbo.tbl_TrainingChapterScreen.ScreenName = dbo.tbl_TrainingScreenVersion.ScreenName AND dbo.tbl_TrainingChapterScreen.ScreenVersion = dbo.tbl_TrainingScreenVersion.ScreenVersion INNER JOIN dbo.tbl_TrainingScreen ON dbo.tbl_TrainingScreenVersion.ScreenName = dbo.tbl_TrainingScreen.ScreenName INNER JOIN @ChapterTable CT ON CT.ChapterName = tbl_TrainingChapterScreen.ChapterName AND CT.ChapterVersion = tbl_TrainingChapterScreen.ChapterVersion WHERE (dbo.tbl_TrainingChapterScreen.CurrentVersion = 1) AND (dbo.tbl_TrainingScreenVersion.Disable = 0) AND (dbo.tbl_TrainingScreen.Disable = 0) INSERT INTO @TopicTable (ScreenName, ScreenVersion, TopicName, TopicDisplayName, TopicVersion, TopicOrder, MinimumTime, DocumentText, AccumulatedTimeCurrentVersion, AccumulatedTimeMajorVersion) SELECT ST.ScreenName, ST.ScreenVersion, dbo.tbl_TrainingTopicVersion.TopicName, dbo.tbl_TrainingTopicVersion.TopicDisplayName, dbo.tbl_TrainingTopicVersion.TopicVersion, dbo.tbl_TrainingScreenTopic.TopicOrder, dbo.tbl_TrainingTopicVersion.MinimumTime, dbo.tbl_TrainingTopicVersion.DocumentText, AccumulatedTimeCurrentVersion.AccumulatedTimeCurrentVersion, TerminalAccumulatedTimeMajorVersion.AccumulatedTimeMajorVersion FROM dbo.tbl_TrainingScreenTopic INNER JOIN dbo.tbl_TrainingTopicVersion ON dbo.tbl_TrainingScreenTopic.TopicName = dbo.tbl_TrainingTopicVersion.TopicName AND dbo.tbl_TrainingScreenTopic.TopicVersion = dbo.tbl_TrainingTopicVersion.TopicVersion INNER JOIN dbo.tbl_TrainingTopic ON dbo.tbl_TrainingTopicVersion.TopicName = dbo.tbl_TrainingTopic.TopicName LEFT OUTER JOIN (SELECT TopicName, TopicVersion / 1000000 AS MajorVersion, SUM(AccumulatedTime) AS AccumulatedTimeMajorVersion FROM dbo.tbl_TrainingTerminalSessionTopicProgress WHERE (TerminalId = @TerminalId_) GROUP BY TopicName, TopicVersion / 1000000) TerminalAccumulatedTimeMajorVersion ON dbo.tbl_TrainingTopicVersion.TopicName = TerminalAccumulatedTimeMajorVersion.TopicName COLLATE Latin1_General_CI_AS AND dbo.tbl_TrainingTopicVersion.TopicVersion / 1000000 = TerminalAccumulatedTimeMajorVersion.MajorVersion LEFT OUTER JOIN (SELECT TopicName, TopicVersion, SUM(AccumulatedTime) AS AccumulatedTimeCurrentVersion FROM dbo.tbl_TrainingTerminalSessionTopicProgress WHERE (TerminalId = @TerminalId_) GROUP BY TopicName, TopicVersion) AccumulatedTimeCurrentVersion ON dbo.tbl_TrainingTopicVersion.TopicName = AccumulatedTimeCurrentVersion.TopicName COLLATE Latin1_General_CI_AS AND dbo.tbl_TrainingTopicVersion.TopicVersion = AccumulatedTimeCurrentVersion.TopicVersion INNER JOIN @ScreenTable ST ON ST.ScreenName = tbl_TrainingScreenTopic.ScreenName AND ST.ScreenVersion = tbl_TrainingScreenTopic.ScreenVersion –Current Books SELECT DISTINCT BookName, BookDisplayName, BookVersion, BookDescription, BookOrder FROM @BookTable ORDER BY BookOrder –Current Chapters SELECT DISTINCT BookName, BookVersion, ChapterName, ChapterDisplayName, ChapterVersion, ChapterOrder, ChapterDescription FROM @ChapterTable ORDER BY ChapterOrder –Current Screens SELECT DISTINCT CT.BookName, CT.BookVersion, ST.ChapterName, ST.ChapterVersion, ST.ScreenName, ST.ScreenDisplayName, ST.ScreenVersion, ST.ScreenOrder, ST.BackgroundImage, ST.BackgroundImageX, ST.BackgroundImageY FROM @ScreenTable ST INNER JOIN @ChapterTable CT ON ST.ChapterName = CT.ChapterName AND ST.ChapterVersion = CT.ChapterVersion ORDER BY ScreenOrder –Current Topics SELECT DISTINCT CT.BookName, CT.BookVersion, CT.ChapterName, CT.ChapterVersion, ST.ScreenName, ST.ScreenVersion, TT.TopicName, TT.TopicDisplayName, TT.TopicVersion, TT.TopicOrder, TT.MinimumTime, ISNULL(AccumulatedTimeCurrentVersion, 0) AS AccumulatedTimeCurrentVersion, ISNULL(AccumulatedTimeMajorVersion, 0) AS AccumulatedTimeMajorVersion FROM @TopicTable TT INNER JOIN @ScreenTable ST ON TT.ScreenName = ST.ScreenName AND TT.ScreenVersion = ST.ScreenVersion INNER JOIN @ChapterTable CT ON CT.ChapterName = ST.ChapterName AND CT.ChapterVersion = ST.ChapterVersion ORDER BY TopicOrder –Current Events SELECT DISTINCT CT.BookName, CT.BookVersion, CT.ChapterName, CT.ChapterVersion, ST.ScreenName, ST.ScreenVersion, TT.TopicName, TT.TopicVersion, TTE.EventName, TTE.EventOrder FROM @TopicTable TT INNER JOIN @ScreenTable ST ON TT.ScreenName = ST.ScreenName AND TT.ScreenVersion = ST.ScreenVersion INNER JOIN @ChapterTable CT ON CT.ChapterName = ST.ChapterName AND CT.ChapterVersion = ST.ChapterVersion INNER JOIN tbl_TrainingTopicEvent TTE ON TTE.TopicName = TT.TopicName AND TTE.TopicVersion = TT.TopicVersion WHERE TTE.Disable = 0 ORDER BY EventOrder –Current Highlights SELECT DISTINCT CT.BookName, CT.BookVersion, CT.ChapterName, CT.ChapterVersion, ST.ScreenName, ST.ScreenVersion, TT.TopicName, TT.TopicVersion, TTE.EventName, TTE.EventOrder, TTEH.HighlightWidth, TTEH.HighlightHeight, TTEH.HighlightX, TTEH.HighlightY FROM @TopicTable TT INNER JOIN @ScreenTable ST ON TT.ScreenName = ST.ScreenName AND TT.ScreenVersion = ST.ScreenVersion INNER JOIN @ChapterTable CT ON CT.ChapterName = ST.ChapterName AND CT.ChapterVersion = ST.ChapterVersion INNER JOIN tbl_TrainingTopicEvent TTE ON TTE.TopicName = TT.TopicName AND TTE.TopicVersion = TT.TopicVersion INNER JOIN tbl_TrainingTopicEventHighlight TTEH ON TTE.TopicName = TTEH.TopicName AND TTE.TopicVersion = TTEH.TopicVersion AND TTE.EventName = TTEH.EventName AND TTE.EventOrder = TTEH.EventOrder WHERE TTE.Disable = 0 AND TTEH.Disable = 0 ORDER BY TTE.EventOrder –Current Callouts SELECT DISTINCT CT.BookName, CT.BookVersion, CT.ChapterName, CT.ChapterVersion, ST.ScreenName, ST.ScreenVersion, TT.TopicName, TT.TopicVersion, TTE.EventName, TTE.EventOrder, TTEC.CalloutText, TTEC.CalloutWidth, TTEC.CalloutHeight, TTEC.CalloutX, TTEC.CalloutY, TTEC.InstructionType, TTEC.DonglePosition FROM @TopicTable TT INNER JOIN @ScreenTable ST ON TT.ScreenName = ST.ScreenName AND TT.ScreenVersion = ST.ScreenVersion INNER JOIN @ChapterTable CT ON CT.ChapterName = ST.ChapterName AND CT.ChapterVersion = ST.ChapterVersion INNER JOIN tbl_TrainingTopicEvent TTE ON TTE.TopicName = TT.TopicName AND TTE.TopicVersion = TT.TopicVersion INNER JOIN tbl_TrainingTopicEventCallout TTEC ON TTE.TopicName = TTEC.TopicName AND TTE.TopicVersion = TTEC.TopicVersion AND TTE.EventName = TTEC.EventName AND TTE.EventOrder = TTEC.EventOrder WHERE TTE.Disable = 0 AND TTEC.Disable = 0 ORDER BY TTE.EventOrder –Current Images SELECT DISTINCT CT.BookName, CT.BookVersion, CT.ChapterName, CT.ChapterVersion, ST.ScreenName, ST.ScreenVersion, TT.TopicName, TT.TopicVersion, TTE.EventName, TTE.EventOrder, TTEDI.ImagePath, TTEDI.ImageX, TTEDI.ImageY FROM @TopicTable TT INNER JOIN @ScreenTable ST ON TT.ScreenName = ST.ScreenName AND TT.ScreenVersion = ST.ScreenVersion INNER JOIN @ChapterTable CT ON CT.ChapterName = ST.ChapterName AND CT.ChapterVersion = ST.ChapterVersion INNER JOIN tbl_TrainingTopicEvent TTE ON TTE.TopicName = TT.TopicName AND TTE.TopicVersion = TT.TopicVersion INNER JOIN tbl_TrainingTopicEventDemonstrationImage TTEDI ON TTE.TopicName = TTEDI.TopicName AND TTE.TopicVersion = TTEDI.TopicVersion AND TTE.EventName = TTEDI.EventName AND TTE.EventOrder = TTEDI.EventOrder WHERE TTE.Disable = 0 AND TTEDI.Disable = 0 ORDER BY TTE.EventOrder GO </pre id="code">

What I mean is to split this proc into separate stored procedures that will return selects directly from source tables instead of using table variables and filter later from them or join them. It is not absolutely necessary to remove all of table variables… More when i analyze your code.

Please try to define clustered primary keys on your table variables and let us know if performance improved.

Here is an example of approach I had in mind:<pre id="code">CREATE PROCEDURE Run_TrainingLoadTrainingTreeByTerminalCurrentImages @TerminalId_ NVARCHAR (<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> AS –Current Images SELECT DISTINCT tb.BookName, tbCh.BookVersion, tbCh.ChapterName, tbCh.ChapterVersion, tcScr.ScreenName, tcScr.ScreenVersion, tsTopic.TopicName, tsTopic.TopicVersion, TTE.EventName, TTE.EventOrder, TTEDI.ImagePath, TTEDI.ImageX, TTEDI.ImageY FROM dbo.tbl_TrainingScreenTopicas tsTopic JOIN dbo.tbl_TrainingTopicVersion as ttVerON tsTopic.TopicName = ttVer.TopicName AND tsTopic.TopicVersion = ttVer.TopicVersion JOIN dbo.tbl_TrainingTopic as tTopicON ttVer.TopicName = tTopic.TopicName LEFT OUTER JOIN (SELECT TopicName, TopicVersion / 1000000AS MajorVersion, SUM(AccumulatedTime) AS AccumulatedTimeMajorVersion, FROM dbo.tbl_TrainingTerminalSessionTopicProgress WHERE TerminalId = @TerminalId_ GROUP BY TopicName, TopicVersion / 1000000 ) as tatmv ON ttVer.TopicName = tatmv.TopicName COLLATE Latin1_General_CI_AS AND ttVer.TopicVersion / 1000000 = tatmv.MajorVersion LEFT OUTER JOIN (SELECT TopicName, TopicVersion, SUM(AccumulatedTime) AS AccumulatedTimeCurrentVersion FROM dbo.tbl_TrainingTerminalSessionTopicProgress WHERE TerminalId = @TerminalId_ GROUP BY TopicName, TopicVersion ) as atcv ON ttVer.TopicName = atcv.TopicName COLLATE Latin1_General_CI_AS AND ttVer.TopicVersion = atcv.TopicVersion join dbo.tbl_TrainingChapterScreenas tcScron tcScr.ScreenName = tsTopic.ScreenName and tcScr.ScreenVersion = tsTopic.ScreenVersion JOIN dbo.tbl_TrainingScreenVersionas tsVerON tcScr.ScreenName = tsVer.ScreenName AND tcScr.ScreenVersion = tsVer.ScreenVersion JOIN dbo.tbl_TrainingScreen as tScrON tsVer.ScreenName = tScr.ScreenName joindbo.tbl_TrainingBookChapteras tbChon tbCh.ChapterName = tcScr.ChapterName AND tbCh.ChapterVersion = tcScr.ChapterVersion JOIN dbo.tbl_TrainingChapterVersionas tcVerON tbCh.ChapterName = tcVer.ChapterName AND tbCh.ChapterVersion = tcVer.ChapterVersion JOIN dbo.tbl_TrainingChapter as tChON tcVer.ChapterName = tCh.ChapterName JOIN dbo.tbl_TrainingBookas tbon tb.BookName = tbCh.BookName joindbo.tbl_TrainingBookVersion as tbVerON tbCh.BookVersion = tbVer.BookVersion and tb.BookName = tbVer.BookName and tb.CurrentVersionNo = tbVer.BookVersion JOIN dbo.tbl_TrainingTopicEvent as TTE ON TTE.TopicName = ttVer.TopicName AND TTE.TopicVersion = ttVer.TopicVersion JOIN dbo.tbl_TrainingTopicEventDemonstrationImage as TTEDI ON TTE.TopicName = TTEDI.TopicName AND TTE.TopicVersion = TTEDI.TopicVersion AND TTE.EventName = TTEDI.EventName AND TTE.EventOrder = TTEDI.EventOrder WHERE TTE.Disable = 0 AND TTEDI.Disable = 0 and (tCh.Disable = 0) AND (tcVer.Disable = 0) AND (tbCh.CurrentVersion = 1) and (tb.Disable = 0) and (tbVer.Disable = 0) and (tcScr.CurrentVersion = 1) AND (tScr.Disable = 0) and (tsVer.Disable = 0) AND (tsTopic.CurrentVersion = 1) AND (ttVer.Disable = 0) AND (tTopic.Disable = 0) ORDER BY TTE.EventOrder GO</pre id="code">After re-writting the code like that you can try to optimize code, like to see what disable = 0 conditions can be ommited and joins removed.

]]>