Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We need to do some reporting every night on our SQL Server 2008 R2. Calculating the reports takes several hours. In order to shorten the time we precalculate a table. This table is created based on JOINining 12 quite big (tens of milions row) tables.

The calculation of this aggregation table took until few days ago cca 4 hours. Our DBA than split this big join into 3 smaller joins (each joining 4 tables). The temporary result is saved into a temporary table every time, which is used in the next join.

The result of the DBA enhancement is, that the aggregation table is calculated in 15 minutes. I wondered how is that possible. DBA told me that it is because the number of data the server must process is smaller. In other words, that in the big original join the server has to work with more data than in summed smaller joins. However, I would presume that optimizer would take care of doing it efficiently with the original big join, splitting the joins on its own and sending only the number of columns needed to next joins.

The other thing he has done is that he created an index on one of the temporary tables. However, once again I would think that the optimizer will create the appropriate hash tables if needed and altogether better optimize the computation.

I talked about this with our DBA, but he was himself uncertain about what cased the improvement in processing time. He just mentioned, that he would not blame the server as it can be overwhelming to compute such big data and that it is possible that the optimizer has hard time to predict the best execution plan ... . This I understand, but I would like to have more defining answer as to exactly why.

So, the questions are:

  1. What could possibly cause the big improvement?

  2. Is it a standard procedure to split big joins into smaller?

  3. Is the amount of data which server has to process really smaller in case of multiple smaller joins?

Here is the original query:

    Insert Into FinalResult_Base
SELECT       
    TC.TestCampaignContainerId,
    TC.CategoryId As TestCampaignCategoryId,
    TC.Grade,
    TC.TestCampaignId,    
    T.TestSetId
    ,TL.TestId
    ,TSK.CategoryId
    ,TT.[TestletId]
    ,TL.SectionNo
    ,TL.Difficulty
    ,TestletName = Char(65+TL.SectionNo) + CONVERT(varchar(4),6 - TL.Difficulty) 
    ,TQ.[QuestionId]
    ,TS.StudentId
    ,TS.ClassId
    ,RA.SubjectId
    ,TQ.[QuestionPoints] 
    ,GoodAnswer  = Case When TQ.[QuestionPoints] Is null Then 0
                      When TQ.[QuestionPoints] > 0 Then 1 
                      Else 0 End
    ,WrongAnswer = Case When TQ.[QuestionPoints] = 0 Then 1 
                      When TQ.[QuestionPoints] Is null Then 1
                     Else 0 End
    ,NoAnswer    = Case When TQ.[QuestionPoints] Is null Then 1 Else 0 End
    ,TS.Redizo
    ,TT.ViewCount
    ,TT.SpentTime
    ,TQ.[Position]  
    ,RA.SpecialNeeds        
    ,[Version] = 1 
    ,TestAdaptationId = TA.Id
    ,TaskId = TSK.TaskId
    ,TaskPosition = TT.Position
    ,QuestionRate = Q.Rate
    ,TestQuestionId = TQ.Guid
    ,AnswerType = TT.TestletAnswerTypeId
FROM 
    [TestQuestion] TQ WITH (NOLOCK)
    Join [TestTask] TT WITH (NOLOCK)            On TT.Guid = TQ.TestTaskId
    Join [Question] Q WITH (NOLOCK)         On TQ.QuestionId =  Q.QuestionId
    Join [Testlet] TL WITH (NOLOCK)         On TT.TestletId  = TL.Guid 
    Join [Test]     T WITH (NOLOCK)         On TL.TestId     =  T.Guid
    Join [TestSet] TS WITH (NOLOCK)         On T.TestSetId   = TS.Guid 
    Join [RoleAssignment] RA WITH (NOLOCK)  On TS.StudentId  = RA.PersonId And RA.RoleId = 1
    Join [Task] TSK WITH (NOLOCK)       On TSK.TaskId = TT.TaskId
    Join [Category] C WITH (NOLOCK)     On C.CategoryId = TSK.CategoryId
    Join [TimeWindow] TW WITH (NOLOCK)      On TW.Id = TS.TimeWindowId 
    Join [TestAdaptation] TA WITH (NOLOCK)  On TA.Id = TW.TestAdaptationId
    Join [TestCampaign] TC WITH (NOLOCK)        On TC.TestCampaignId = TA.TestCampaignId 
WHERE
    T.TestTypeId = 1    -- eliminuji ankety 
    And t.ProcessedOn is not null -- ne vsechny, jen dokoncene
    And TL.ShownOn is not null
    And TS.Redizo not in (999999999, 111111119)
END;

The new splitted joins after DBA great work:

    SELECT       
    TC.TestCampaignContainerId,
    TC.CategoryId As TestCampaignCategoryId,
    TC.Grade,
    TC.TestCampaignId,    
    T.TestSetId
    ,TL.TestId
    ,TL.SectionNo
    ,TL.Difficulty
    ,TestletName = Char(65+TL.SectionNo) + CONVERT(varchar(4),6 - TL.Difficulty) -- prevod na A5, B4, B5 ...
    ,TS.StudentId
    ,TS.ClassId
    ,TS.Redizo
    ,[Version] = 1 -- ? 
    ,TestAdaptationId = TA.Id
    ,TL.Guid AS TLGuid
    ,TS.TimeWindowId
INTO
    [#FinalResult_Base_1]
FROM 
    [TestSet] [TS] WITH (NOLOCK)
    JOIN [Test] [T] WITH (NOLOCK) 
        ON [T].[TestSetId] = [TS].[Guid] AND [TS].[Redizo] NOT IN (999999999, 111111119) AND [T].[TestTypeId] = 1 AND [T].[ProcessedOn] IS NOT NULL
    JOIN [Testlet] [TL] WITH (NOLOCK)
        ON [TL].[TestId] = [T].[Guid] AND [TL].[ShownOn] IS NOT NULL
    JOIN [TimeWindow] [TW] WITH (NOLOCK)
        ON [TW].[Id] = [TS].[TimeWindowId] AND [TW].[IsActive] = 1
    JOIN [TestAdaptation] [TA] WITH (NOLOCK)
        ON [TA].[Id] = [TW].[TestAdaptationId] AND [TA].[IsActive] = 1
    JOIN [TestCampaign] [TC] WITH (NOLOCK)
        ON [TC].[TestCampaignId] = [TA].[TestCampaignId] AND [TC].[IsActive] = 1
    JOIN [TestCampaignContainer] [TCC] WITH (NOLOCK)
        ON [TCC].[TestCampaignContainerId] = [TC].[TestCampaignContainerId] AND [TCC].[IsActive] = 1
    ;

 SELECT       
    FR1.TestCampaignContainerId,
    FR1.TestCampaignCategoryId,
    FR1.Grade,
    FR1.TestCampaignId,    
    FR1.TestSetId
    ,FR1.TestId
    ,TSK.CategoryId AS [TaskCategoryId]
    ,TT.[TestletId]
    ,FR1.SectionNo
    ,FR1.Difficulty
    ,TestletName = Char(65+FR1.SectionNo) + CONVERT(varchar(4),6 - FR1.Difficulty) -- prevod na A5, B4, B5 ...
    ,FR1.StudentId
    ,FR1.ClassId
    ,FR1.Redizo
    ,TT.ViewCount
    ,TT.SpentTime
    ,[Version] = 1 -- ? 
    ,FR1.TestAdaptationId
    ,TaskId = TSK.TaskId
    ,TaskPosition = TT.Position
    ,AnswerType = TT.TestletAnswerTypeId
    ,TT.Guid AS TTGuid

INTO
    [#FinalResult_Base_2]
FROM 
    #FinalResult_Base_1 FR1
    JOIN [TestTask] [TT] WITH (NOLOCK)
        ON [TT].[TestletId] = [FR1].[TLGuid] 
    JOIN [Task] [TSK] WITH (NOLOCK)
        ON [TSK].[TaskId] = [TT].[TaskId] AND [TSK].[IsActive] = 1
    JOIN [Category] [C] WITH (NOLOCK)
        ON [C].[CategoryId] = [TSK].[CategoryId]AND [C].[IsActive] = 1
    ;    

DROP TABLE [#FinalResult_Base_1]

CREATE NONCLUSTERED INDEX [#IX_FR_Student_Class]
ON [dbo].[#FinalResult_Base_2] ([StudentId],[ClassId])
INCLUDE ([TTGuid])

SELECT       
    FR2.TestCampaignContainerId,
    FR2.TestCampaignCategoryId,
    FR2.Grade,
    FR2.TestCampaignId,    
    FR2.TestSetId
    ,FR2.TestId
    ,FR2.[TaskCategoryId]
    ,FR2.[TestletId]
    ,FR2.SectionNo
    ,FR2.Difficulty
    ,FR2.TestletName
    ,TQ.[QuestionId]
    ,FR2.StudentId
    ,FR2.ClassId
    ,RA.SubjectId
    ,TQ.[QuestionPoints] -- 1+ good, 0 wrong, null no answer
    ,GoodAnswer  = Case When TQ.[QuestionPoints] Is null Then 0
                      When TQ.[QuestionPoints] > 0 Then 1 -- cookie
                      Else 0 End
    ,WrongAnswer = Case When TQ.[QuestionPoints] = 0 Then 1 
                      When TQ.[QuestionPoints] Is null Then 1
                     Else 0 End
    ,NoAnswer    = Case When TQ.[QuestionPoints] Is null Then 1 Else 0 End
    ,FR2.Redizo
    ,FR2.ViewCount
    ,FR2.SpentTime
    ,TQ.[Position] AS [QuestionPosition]  
    ,RA.SpecialNeeds -- identifikace SVP        
    ,[Version] = 1 -- ? 
    ,FR2.TestAdaptationId
    ,FR2.TaskId
    ,FR2.TaskPosition
    ,QuestionRate = Q.Rate
    ,TestQuestionId = TQ.Guid
    ,FR2.AnswerType
INTO
    [#FinalResult_Base]
FROM 
    [#FinalResult_Base_2] FR2
    JOIN [TestQuestion] [TQ] WITH (NOLOCK)
        ON [TQ].[TestTaskId] = [FR2].[TTGuid]
    JOIN [Question] [Q] WITH (NOLOCK)
        ON [Q].[QuestionId] = [TQ].[QuestionId] AND [Q].[IsActive] = 1

    JOIN [RoleAssignment] [RA] WITH (NOLOCK)
        ON [RA].[PersonId] = [FR2].[StudentId]
        AND [RA].[ClassId] = [FR2].[ClassId] AND [RA].[IsActive] = 1 AND [RA].[RoleId] = 1

    drop table #FinalResult_Base_2;

    truncate table [dbo].[FinalResult_Base];
    insert into [dbo].[FinalResult_Base] select * from #FinalResult_Base;

    drop table #FinalResult_Base;
share|improve this question
2  
A word of warning - WITH (NOLOCK) Is evil - can result in bad data coming back. I suggest trying WITH (ROWCOMMITTED). –  TomTom Jun 2 '13 at 4:27
    
@TomTom Did you mean READCOMMITTED? I've never seen ROWCOMMITTED before. –  ypercube Jun 3 '13 at 21:46
2  
WITH(NOLOCK) is not evil. It's just not the magic bullet that people seem to think it is. Like most things in SQL Server and software development in general it has its place. –  Zane Jun 3 '13 at 21:46
1  
Yes, but given that NOLOCK may produce warnings in the log and - more important - return WRONG DATA, I consider it evil. It is pretty much only usable on tables GUARANTEED not to change in the primary key and selected keys while the query runs. And yes, I meand READCOMMMITED, sorry. –  TomTom Jun 3 '13 at 22:08

3 Answers 3

1 Reduction of 'search space', coupled with better statistics for the intermediate/late joins.

I've had to deal with 90-table joins (mickey mouse design) where the Query Processor refused to even create a plan. Breaking such a join into 10 subjoins of 9 tables each, dramatically brought down the complexity of each join, which grows exponentially with each additional table. Plus the Query Optimiser now treats them as 10 plans, spending (potentially) more time overall (Paul White may even have metrics!).

The intermediate result tables will now have fresh statistics of their own, thus joining much better compared to the statistics of a deep tree that become skewed early on and end up as Science Fiction soon afterwards.

Plus you can force the most selective joins first, cutting down the data volumes moving up the tree. If you can estimate the selectivity of your predicates much better than the Optimiser, why not force the join order. Might be worth searching for "Bushy Plans".

2 It should be considered in my view, if efficiency and performance are important

3 Not necessarily, but it could be if the most selective joins are executed early on

share|improve this answer
1  
+1 Thanks. Especially for the description of your experience. Very much true in saying this "If you can estimate the selectivity of your predicates much better than the Optimiser, why not force the join order." –  Ondra Peterka Jun 2 '13 at 9:58
1  
It is a very valid question actually. The 90-table join could be coerced to produce a plan just by using the 'Force Order' option. It didn't matter that the order was probably random and suboptimal, just reducing the search space was enough to help the Optimiser create a plan within a couple of seconds (without the hint it would time out after 20 seconds). –  John Alan Jun 2 '13 at 10:06
  1. SQLServer optimizer usually does a good job. However, its goal is not to generate the best possible plan, but to find the plan which is good enough quickly. For a particular query with many joins it may cause very poor performance. Good indication of such case is a big difference between estimated and actual number of rows in actual execution plan. Also, I'm pretty sure that execution plan for the initial query will show many 'nested loops join' which is slower than 'merge join'. The latter requires both inputs to be sorted using the same key, which is expensive, and usually optimizer discards such an option. Storing results in temporary table and adding proper indexes as you did results -my guess- in choosing better algorithm for further joins (side note - you follow best practices by populating temp table first, and adding indexes after) . In addition, SQLServer generates and keeps statistics for temporary tables which as well helps choosing proper index.
  2. I cannot say there is a standard about using temporary tables when number of joins is greater than some fixed number, but it's definitely an option which can improve performance. That doesn't happen to often, but I had similar problems (and similar solution) couple times. Alternatively, you can try figuring out the best execution plan yourself, store and force re-using it, but it will take enormous amount of time (no 100% guaranteed you will succeed). Another side note - in case if resultset which stored in temporary table is relatively small (say about 10k records) table variable performs better than temp table.
  3. I hate saying 'that depends', but it's probably my answer to your third question. Optimizer has to give results fast; you don't want it to spend hours trying to figure out the best plan; each join adds extra work, and sometimes the optimizer 'gets confused'.
share|improve this answer
1  
+1 thanks for the confirmation and explanation. What you have written makes sense. –  Ondra Peterka Jun 1 '13 at 19:02

Well, let me start by saying that you work on small data - 10ns of millions are not large. The last DWH projet I had had 400 million rows added to the fact table. PER DAY. Storage for 5 years.

The problem is hardware, partially. As large joins may use a LOT of temporary space and there is only so much RAM, the moment you overflow into disc things get a lot slower. As such, it may make sense to split the work into smaller parts simply because while SQL lives in a world of sets, and does not care about size, the server you run on is not infinite. I am quite used to get out of space errors in a 64gb tempdb during some operations.

Otherwise, as long as the staitsics are in order, the query optimizer is not overwhelmed. It does not really care how large the table is - it works by statistics that really do not grow. THAT SAID: If you really have a LARGE table (double digit billion number of rows) then they may be a little coarse.

There is also a matter of locking - unless you program that nicely the large join may lock the table for hours. I am doing 200gb copy operations at the moment, and I am splitting them into smllerparty by a business key (effectively looping) which keeps the locks a lot shorter.

At the end, we work with limited hardware.

share|improve this answer
    
+1 thanks for your answer. There is good point in saying it depends on HW. We have just 32 GB of RAM, which is not sufficient probably. –  Ondra Peterka Jun 1 '13 at 19:05
1  
I am a bit frustrated every time I read answers like that - even a few dozen of million rows create CPU load on our database server for hours. Maybe the number of dimensions is high, but 30 dimensions seems not a too big number. I think the very high number of rows you can process are from a simple model. Even worse: The whole data fits into RAM. And still takes hours. –  flaschenpost Jun 1 '13 at 19:52
    
30 dimensions is a LOT - are you sure the model is properly optimized into a star? Some mistakes for example that cost CPU - on the OP query is using GUID's as primary keys (uniqueidentifier). I love them too - as unique index, the primary key is a ID field, makes the whole comparison faster and the index more nawwox (4 or 8 bytes, not 18). Tricks like that save a TON of CPU. –  TomTom Jun 2 '13 at 4:27

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.