Results 1 to 13 of 13
  1. #1
    Join Date
    Jun 2007
    Location
    Dresden, Germany
    Posts
    31

    Unanswered: strange speed problem with view

    Hi!

    I have created 3 Views of 3 different tables. Each view consists of differnet WHERE clause value ranges and looks similar like this one...

    Code:
    CREATE VIEW REKO AS
    SELECT FALL, BETNR, VDI1, VDI2, VDI3, VDI6, VDI7 FROM [20_REKO]
    WHERE VDI1 BETWEEN 1 AND 12
    AND VDI2 BETWEEN 1 AND 4
    AND VDI3 BETWEEN 10 AND 91
    AND VDI6 BETWEEN 0 AND 9
    AND VDI7 BETWEEN 0 AND 99
    Only one view has additionally to the int and smallint ranges a WHERE clause for a nvarchar field...
    Code:
    (MAXDEFZ LIKE 'F[1-4]' OR MAXDEFZ LIKE 'H[1-4]' OR MAXDEFZ LIKE 'D[1-4]' OR MAXDEFZ LIKE 'R[A-C][0-4]' OR MAXDEFZ LIKE 'L[A-C][0-4]')
    Now the problem: If I do a joined select over these 3 views using again for example MAXDEFZ LIKE 'F[1-4]' the select needs 11 minutes!!! Without it's done in 3 seconds. Why that???


    11 minutes candidate
    Code:
    SELECT r.FALL, r.BETNR, r.VDI2, SUM(EES) AS sumEES
    FROM [FZG] f, [FZGA] a, [REKO] r
    WHERE f.FALL = a.FALL AND f.BETNR = a.BETNR AND f.FALL = r.FALL AND f.BETNR = r.BETNR
    AND MAXDEFZ LIKE 'F[1-4]'
    GROUP BY r.FALL, r.BETNR, r.VDI2
    3 seconds candidate
    Code:
    SELECT r.FALL, r.BETNR, r.VDI2, SUM(EES) AS sumEES
    FROM [FZG] f, [FZGA] a, [REKO] r
    WHERE f.FALL = a.FALL AND f.BETNR = a.BETNR AND f.FALL = r.FALL AND f.BETNR = r.BETNR
    GROUP BY r.FALL, r.BETNR, r.VDI2
    Thank you in advance!!!

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Not relevant, but you could shorten your where predicates.
    Code:
    (MAXDEFZ LIKE '[FHD][1-4]' OR MAXDEFZ LIKE '[RL][A-C][0-4]')
    Could you post the execution plans?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jun 2007
    Location
    Dresden, Germany
    Posts
    31
    Thank you for the hint! Should I post the execution plan as picture or xml? I just saw in the exec. plan that Table Scan for 02_FZG needs 124.890.648 lines with MAXDEFZ LIKE 'F[1-4] and without only 15.238 lines. Table 02_FZG has only 15.238 lines, that means with the first command sql server scans the table 8196 times!

  4. #4
    Join Date
    Jun 2007
    Location
    Dresden, Germany
    Posts
    31
    sorry double posting
    Last edited by sp00ky; 06-24-09 at 10:20.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    SHOWPLAN_XML is perfect.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jun 2007
    Location
    Dresden, Germany
    Posts
    31
    ok, here is the exec. plan for the 11 minutes candidate...
    Attached Files Attached Files

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    And the other please, for comparison.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jun 2007
    Location
    Dresden, Germany
    Posts
    31
    and the other one from the 3 seconds candidate...
    Attached Files Attached Files

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The problem is the optimiser is massively over estimating how selective that additional filter is. As such, it tries to join two dataset using a Nested Loops join.
    Try running this:
    Code:
    SELECT      r.FALL
            , r.BETNR
            , r.VDI2
            , sumEES    = SUM(EES)
    FROM    [FZG] AS f
    INNER HASH JOIN 
            [FZGA] AS a
    ON    f.FALL = a.FALL AND f.BETNR = a.BETNR 
    INNER JOIN
            [REKO] AS r
    ON    r.FALL  = f.FALL 
    AND r.BETNR = f.BETNR
    WHERE    MAXDEFZ LIKE 'F[1-4]'
    GROUP BY  r.FALL
            , r.BETNR
            , r.VDI2
    I haven't tested since I don't have DDL.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Jun 2007
    Location
    Dresden, Germany
    Posts
    31
    hmm nice it works... thank you!!!

    so it was only an optimizer problem? what does INNER HASH JOIN exactly do?

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    OK - this is your query once I had put it in to ISO syntax (i.e. used JOINS, not the WHERE clause to join tables). NOTE - absence of HASH, and as such will run slowly.
    Code:
    SELECT      r.FALL
            , r.BETNR
            , r.VDI2
            , sumEES    = SUM(EES)
    FROM    [FZG] AS f
    INNER JOIN 
            [FZGA] AS a
    ON    f.FALL = a.FALL AND f.BETNR = a.BETNR 
    INNER JOIN
            [REKO] AS r
    ON    r.FALL  = f.FALL 
    AND r.BETNR = f.BETNR
    WHERE    MAXDEFZ LIKE 'F[1-4]'
    GROUP BY  r.FALL
            , r.BETNR
            , r.VDI2
    If you look at the two plans, they differ because the slow plan uses a nested loops join (this is just the physical process the engine uses to join the tables). Nested loops work best with smaller data sets. You can see that the expected and actual row counts were way off. Viewing the good plan confirms that the nested loops are the problem, as the only real difference is that a Hash join is used instead.

    A Hash join is for joining large datasets that are not ordered in a complementary way. It was the optimal join in this case. The addition of the word HASH to the join is a hint (or more correctly, instruction) to the optimiser to use a Hash join there, not a Nested Loops.

    This is an awesome set of blog posts on the physical joins used in SQL Server:
    Craig Freedman's SQL Server Blog
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    BTW - 99.9% of the time the optimiser is better than you at guessing the optimal plan. It can also change its mind based on changing information. You rarely can. As such, very, very rarely use hints and review them periodically to check they are still optimal.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Jun 2007
    Location
    Dresden, Germany
    Posts
    31
    Thank you pootle flump, problem solved and I have learned much today!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •