lazy spool
-
I have a query that has a very bad performance. This is caused by a sum function in the query list. In the explain plan window I see a 'Lazy spool' action which consumes 77% of the total cost. Here temporary data is written to the tempdb row by row (I read an article that's how the lazy spool works). My questions are:
1 is it possible force the optimizer to keep the data (this is a part of the query data) in cache?
2 why the optimize chooses to write the rows 1 by 1 to the tempdb?
3 Is it possible to add hints or something like that to force the optimizer to change its behavior?
Any help will be appriciated. If you need additional information, please let me know.
Thanks in advance and kind regards,
Bert
-
Hi Bert,
I have to admint I know the SQL 2000 optimizer a lot better than '50, but here's a couple thoughts. First off, I generally recommend to keep from giving hints to SQL Server. I prefer to "massage" the query to do what I want it to do, instead. One thing that might be interesting to try is using covering indexes or the new feature in '05 called "included columns".
My last thought is about forcing the caching of the data. While you may be able to "beat" SQL in the performance game sometimes. You may globally impact the performance of your server in exchange for better local performance of your individual query. That being said, I believe you should be able to pin a table in memory to get what you want.
Hope that helps,
Eric
P.S. The more info you give us, the better our answers!
-
Eric, thanks for your reply. I will hand over your suggestions to our sql dba. This afternoon I did some more research on the query and I found that 2 conditions in the where clause caused the problem. At the moment these 2 conditions are not realy necessary, because they are always true. This will change the next couple of weeks when the dwh gets filled, so the dba's have a few more days to come with the solution.
At the moment the query performs well: from over 25 minutes to about 20 seconds now. This is acceptable.
Again: thanks for your suggestions and have a nice weekend.
Bert
-
Hereby I send the query causing all the performance troubles. When I leave out the first SUM(CASE ... attribute, the query has finished after 6 seconds. With the sum attribute the query takes over 30 minutes to complete. I am totally lost, so is the dba.
The query:
SELECT
--SUM(CASE WHEN afg_per_totalen_begroting.periode = 1
--THEN afg_per_totalen_begroting.beginsaldo + afg_per_totalen_begroting.periode_mutatie
--ELSE afg_per_totalen_begroting.periode_mutatie END)
--,
FLTEST.dbo.gl_grootboekrek_plat.nivo2_rek_oms,
FLTEST.dbo.gl_grootboekrek_plat.nivo3_rek_oms,
FLTEST.dbo.gl_grootboekrek_plat.nivo4_rek_oms,
afg_perioden.period_year,
FLTEST.dbo.gl_geldstromen.geldstroom,
FLTEST.dbo.gl_grootboekrek_plat.nivo1_rek_oms,
FLTEST.dbo.gl_grootboekrek_plat.nivo0_rek_oms,
FLTEST.dbo.gl_grootboekrek_plat.nivo5_rek_oms,
FLTEST.dbo.gl_grootboekrek_plat.nivo6_rek_oms,
FLTEST.dbo.gl_maatschappij_plat.nivo1_mts_oms,
FLTEST.dbo.gl_maatschappij_plat.nivo2_mts_oms,
FLTEST.dbo.gl_maatschappij_plat.nivo3_mts_oms,
FLTEST.dbo.gl_maatschappij_plat.nivo4_mts_oms,
FLTEST.dbo.gl_maatschappij_plat.nivo5_mts_oms,
FLTEST.dbo.gl_maatschappij_plat.nivo6_mts_oms
FROM
FLTEST.dbo.gl_grootboekrek_plat,
( select FLTEST.dbo.gl_perioden.period_num
,FLTEST.dbo.gl_perioden.period_year
,FLTEST.dbo.gl_perioden.quarter_num
,FLTEST.dbo.gl_perioden.period_type
,FLTEST.dbo.gl_perioden.period_set_name
,FLTEST.dbo.gl_perioden.period_name
,FLTEST.dbo.gl_perioden.start_date
,FLTEST.dbo.gl_perioden.end_date
from FLTEST.dbo.gl_perioden
where
FLTEST.dbo.gl_perioden.period_set_name = 'ORG_Kalender') afg_perioden,
FLTEST.dbo.gl_geldstromen,
FLTEST.dbo.gl_maatschappij_plat,
( SELECT
FLTEST.dbo.gl_periode_totalen.code_combination_id
,FLTEST.dbo.gl_periode_totalen.set_of_books_id
,FLTEST.dbo.gl_periode_totalen.valuta_code
,FLTEST.dbo.gl_periode_totalen.boekjaar
,FLTEST.dbo.gl_periode_totalen.periode
,FLTEST.dbo.gl_periode_totalen.saldosoort
,FLTEST.dbo.gl_periode_totalen.budget_version_id
,FLTEST.dbo.gl_periode_totalen.budget_type
,FLTEST.dbo.gl_periode_totalen.budget_name
,FLTEST.dbo.gl_periode_totalen.encumbrance_type_id
,FLTEST.dbo.gl_periode_totalen.type_verplichting
,FLTEST.dbo.gl_periode_totalen.beginsaldo
,FLTEST.dbo.gl_periode_totalen.periode_mutatie
FROM
FLTEST.dbo.gl_periode_totalen
WHERE
FLTEST.dbo.gl_periode_totalen.saldosoort = 'Budget'
AND FLTEST.dbo.gl_periode_totalen.budget_type = 'standard'
AND FLTEST.dbo.gl_periode_totalen.budget_name like '%BEGROTING%'
) afg_per_totalen_begroting,
FLTEST.dbo.gl_rekening_combinaties,
FLTEST.dbo.gl_grootboekrekeningen,
FLTEST.dbo.gl_maatschappijen
WHERE
( FLTEST.dbo.gl_rekening_combinaties.maatschappij=FLTEST.dbo.gl_maatschappijen.maatschappij )
AND ( FLTEST.dbo.gl_rekening_combinaties.grootboekrekening=FLTEST.dbo.gl_grootboekrekeningen.grootboekrekening )
AND ( FLTEST.dbo.gl_rekening_combinaties.geldstroom=FLTEST.dbo.gl_geldstromen.geldstroom )
AND ( FLTEST.dbo.gl_grootboekrek_plat.grootboekrekening=FLTEST.dbo.gl_grootboekrekeningen.grootboekrekening )
AND ( afg_per_totalen_begroting.code_combination_id=*FLTEST.dbo.gl_rekening_combinaties.code_combination_id )
AND ( FLTEST.dbo.gl_maatschappijen.maatschappij=FLTEST.dbo.gl_maatschappij_plat.maatschappij )
AND ( afg_perioden.period_num*=afg_per_totalen_begroting.periode
and afg_perioden.period_year*=afg_per_totalen_begroting.boekjaar )
AND (
afg_perioden.period_year IN ('2007')
AND FLTEST.dbo.gl_grootboekrek_plat.nivo0_rek_oms IN ('Lasten', 'Baten', 'Exploitatieresultaat', 'Financiele baten en lasten')
)
GROUP BY
FLTEST.dbo.gl_grootboekrek_plat.nivo2_rek_oms,
FLTEST.dbo.gl_grootboekrek_plat.nivo3_rek_oms,
FLTEST.dbo.gl_grootboekrek_plat.nivo4_rek_oms,
afg_perioden.period_year,
FLTEST.dbo.gl_geldstromen.geldstroom,
FLTEST.dbo.gl_grootboekrek_plat.nivo1_rek_oms,
FLTEST.dbo.gl_grootboekrek_plat.nivo0_rek_oms,
FLTEST.dbo.gl_grootboekrek_plat.nivo5_rek_oms,
FLTEST.dbo.gl_grootboekrek_plat.nivo6_rek_oms,
FLTEST.dbo.gl_maatschappij_plat.nivo1_mts_oms,
FLTEST.dbo.gl_maatschappij_plat.nivo2_mts_oms,
FLTEST.dbo.gl_maatschappij_plat.nivo3_mts_oms,
FLTEST.dbo.gl_maatschappij_plat.nivo4_mts_oms,
FLTEST.dbo.gl_maatschappij_plat.nivo5_mts_oms,
FLTEST.dbo.gl_maatschappij_plat.nivo6_mts_oms
Creating views did not improve the performance. Hopefully anyone reading this problem has the solution and wants to share it.
Kind regards,
Bert
-
Bert,
Lazy Spool are usually indications of lack of indexes or large operations. There are several things you can do.
First can you use ANSI joins instead of "old" notation
this helps the optimizer understand a bit better what you want.
Second:
The fact that you are using several non-sarg searches make me believe that one of those tables is large. so you should try to find a different way of doing FLTEST.dbo.gl_periode_totalen.budget_name like '%BEGROTING%'
and change
afg_perioden.period_year IN ('2007') for
afg_perioden.period_year ='2007' -- Is that supposed to be a string and not a number?
Third. You may probably be able to materialize the calculation on an *idexed view*
( are you using EE ? )
* Noel
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply