February 27, 2008 at 2:47 am
Hi guys!
This problem I have in Sql Server 2005 might sound implausible...but it happens, nevertheless.
The thing is that I have this query with several derived tables in it. And, when I join them with INNER JOIN the query drags, and completes after 2 minutes, with a result of 12 rows. When I use LEFT JOIN between main derived tables (I call them main because they contain a couple of derived tables inside, which are joined with an INNER JOIN...and that works fine), the query completes immediately, 0 secs with the same result of 12 rows.
Now, this would't give me such a headache if the exact same query to the letter, with INNER joins, didn't work perfectly in SQL Server 2000. But it does!
So my conclusion should be that INNER joins between derived tables in Sql Server 2005 is toublesome, while in Sql Server 2000 it works fine. But I don't think that should be the conclusion one makes.
So, if anyone has any info on this, I would sure appreciate it!
Thanks,
Srdjan.
February 27, 2008 at 2:51 am
What does the plans look like?
Especially look for differences when it 'drags' and when it does not.
February 27, 2008 at 3:29 am
Thanks for your qick response.
Well, query plans do differ....and the one that uses INNER joins even uses temp tables to "optimize rewinds" ?! And there is a significant difference in "data flow" lines (I don't know the proper terminology) which imply that in INNER join query a lot of data is being returned, while the LEFT join query returns far less data, according to the query plan.
Now, I'm not very familiar with analyzing query plans, but I know how to read and what I see is this: it's, somehow, more complicated for SQLSRV 2005 to process INNER joins with derived tables than with LEFT joins. I am struggling to understand this. Especially because I optimized that query over a period of time, tested it a thousand times....it's not something I just fumbled up. And logic demands that inner joins work faster since they exclude non-identical values, or null values. At least in SQLSRV 2000 it seems to be the case.
If anything, I expected the query to run smoother on 2005 than 2000. That's why I thought that there might be an issue in 2005 regarding derived table joins.
Best regards.
February 27, 2008 at 5:30 am
Well, the total experience of a query isn't just the SQL alone.
The same query may behave differently (read: using different plans) due to differences in hardware, data, indexes, state of things as statistics, not to mention that the query engine and optimizer are indeed different things in 2000 and 2005, and so on...
Though the optimizer isn't perfect, there are times that it chooses a suboptimal plan.
But my point is that it's more than just the query itself that needs to 'be the same' in order to be sure that the circumstances are comparable.
So, do you have identical hardware, data, tables, indexes et al in this case when one 'drags' and the other goes like lightning, or are there differences? Since the plans seems to actually be different, there should be a reason to why.
/Kenneth
February 27, 2008 at 7:53 am
Both SQL 2000 and 2005 are installed on the same server machine.
The databases that I tested the query on have the same tables (same structure, indexes etc.), with different amounts of data, but not that different to pose a problem, I should think.
Now, there are a few of issues here, I think:
1. the query runs on 2000 with INNER joins between derived tables as smooth as silk (0-2 sec).
2. the query runs on 2005 with INNER joins between derived tables with a lag of 2-4 minutes.
3. the query runs on 2005 with LEFT joins between derived tables as smooth as silk (0-2 sec).
Point No 3 is a bit scary because it makes me to wonder if any records will pass through that shouldn't. So far none have, but that doesn't reassure me. Perhaps it's some weird bug on MS part...perhaps not. But it's two differents databases with the same structure on two instances of SQL Server, one 2000 (with SP 4) and the other 2005, on the same Windows 2003 server machine.
I do appreciate everything you pointed out so far, and I am grateful for the input.
I wondered if anyone else bumped into this kind of a problem, and had solved it successfully.
I hope I'm not making myself a nuissance.
Best regards,
Srdjan.
February 27, 2008 at 8:04 am
Have you rebuilt all of the indexes once you ported the DB over? Make sure your statistics are current and valid.
Also - like Kenneth pointed out, the optimizer sometimes acts a little differently, so just because an indexing scheme worked great on 2000 doesn't necessarily mean it's the one 2005 would prefer.
2005 likes derived tables just fine - you may just need to do a little arm-twisting for it to treat this particular one the "right way".
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 28, 2008 at 1:23 am
Ok, thanks guys...this is not the first derived table query that I've had this problem with. But, I'll keep trying and implement some of that "armtwisting" on it.
Thanks again to both Kenneth and you Matt.
February 28, 2008 at 2:35 am
Before you go down the route of armtwisting, you should follow Matt's instructions and rebuild indexes/update statistics.
February 28, 2008 at 7:15 am
I forgot to mention that tables that are used in the query were not ported from 2000 but created from a DDL script, and all indexes, keys, constraints also, so I didn't think rebuilding indexes was the way to go.
But as I am not very experienced in using SS 2005, I did it (although no index was fragmented beyond 2.2%), and I updated statistics for all the columns of the tables that are involved in the query, but it didn't yield any results.
The query still returns results (12 rows) after 2-4 minutes when using INNER joins between derived tables, and same results after 0-2 sec when using LEFT join between derived tables.
I guess LEFT join will have to do....
Thanks for your time guys.
February 28, 2008 at 9:22 am
Do you mind sharing what one of those queries look like?
Nothing beats looking at actual code 😉
(you may change names to protect the innocent ofc)
/Kenneth
February 28, 2008 at 9:30 am
Can you also post the execution plans from the 2005 servers (saved as .sqlplan files, zipped and attached to post please)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 28, 2008 at 9:32 am
Srdjan Svrdlan (2/28/2008)
I forgot to mention that tables that are used in the query were not ported from 2000 but created from a DDL script, and all indexes, keys, constraints also, so I didn't think rebuilding indexes was the way to go.But as I am not very experienced in using SS 2005, I did it (although no index was fragmented beyond 2.2%), and I updated statistics for all the columns of the tables that are involved in the query, but it didn't yield any results.
The query still returns results (12 rows) after 2-4 minutes when using INNER joins between derived tables, and same results after 0-2 sec when using LEFT join between derived tables.
I guess LEFT join will have to do....
Thanks for your time guys.
Have you taken a peek into the missing indexes DMV? sys.dm_db_missing_index_details for example?
What's the execution plan telling you?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 29, 2008 at 1:54 am
Ofcourse not Kenneth....but since the table names are in Serbian, I don't know if I should translate them to english?
Well here goes...if you need them translated for clarity, I will ablige.
DECLARE @cena_boda decimal(18,8), @br_dana_god INT, @datum_od VARCHAR(20), @datum_do VARCHAR(20), @god VARCHAR(10), @pom_datum DATETIME,
@pvn VARCHAR(10), @pvnkmt VARCHAR(10), @koef_cene decimal(18,2), @pom_datum_od DATETIME, @pom_datum_do DATETIME
SET @god = '2007'
SET @datum_od = '01/01/2007'
SET @datum_do = '31/12/2007'
SELECT @pvn=VREDNOST FROM NAK_SETUP WHERE KLJUC='VN_NAK'
SELECT @pvnkmt=VREDNOST FROM NAK_SETUP WHERE KLJUC='VN_KMT'
SELECT
OBVEZNIK_ID
, OBJEKAT_ID
, DATUM_DPO
, IZNOS_RATE_OBJEKTA
, @pvn
, @god
, RATA_ID
, SIFRA_MAX
, BROJ_RATA
FROM
(
SELECT
OO.DATUM_DPO AS DATUM_DPO
, OO.ID AS RATA_ID
, OO.NAK_OBVEZNIK_ID AS OBVEZNIK_ID
, OO.NAK_OBJEKAT_ID AS OBJEKAT_ID
, OO.ZADUZENJE_PERIODA
, CASEWHEN RIK.SIFRA = REK.SIFRA_MAX THEN
ZADUZENJE_PERIODA -
ROUND(ROUND(ZADUZENJE_PERIODA / AHA.BROJ_RATA, 2) * AHA.BROJ_RATA, 2) +
ROUND(ZADUZENJE_PERIODA / AHA.BROJ_RATA, 2)
ELSE ROUND(ZADUZENJE_PERIODA / AHA.BROJ_RATA, 2)
END AS IZNOS_RATE_OBJEKTA
, RIK.SIFRA AS SIFRA_RATE
, REK.SIFRA_MAX AS SIFRA_MAX
, AHA.BROJ_RATA
FROM
(
SELECT ZR.NAK_OBVEZNIK_ID, ZR.DATUM_DPO, ZR.ID, OO.NAK_OBJEKAT_ID AS NAK_OBJEKAT_ID
, CASEWHEN OB.LICA_ID IS NOT NULL THEN 0
WHEN OB.PRAVNA_LICA_ID IS NOT NULL THEN 1
ELSE 2 END AS TIP_LICA
, OO.DATUM_OD, OO.DATUM_DO
, OO.ZADUZENJE_PERIODA
FROM NAK_ZADUZENJA_RATE ZR
INNER JOIN
(
SELECT
NAK_OBJEKAT_ID
, NAK_OBVEZNIK_ID
, DATUM_OD
, DATUM_DO
, IZNOS AS ZADUZENJE_PERIODA
FROM NAK_ZADUZENJA
WHERE VRSTA_ZADUZENJA = @pvn
AND GOD = @god
) OO ON ZR.NAK_OBVEZNIK_ID = OO.NAK_OBVEZNIK_ID
INNER JOIN NAK_OBVEZNIK OB ON OO.NAK_OBVEZNIK_ID = OB.ID
WHERE ZR.VN = @pvn
AND ZR.GOD = @god
) OO
INNER JOIN
(
SELECT CAST(SIFRA AS INT) AS SIFRA, TIP_RATE, DATUM_DO
FROM NAK_SETUP_RATE
WHERE GODINA=@god
) RIK ON RIK.TIP_RATE = OO.TIP_LICA
AND RIK.DATUM_DO = OO.DATUM_DPO
INNER JOIN
(
SELECT MAX(CAST(R.SIFRA AS INT)) AS SIFRA_MAX, AHA.ID, AHA.TIP_RATE
FROM
(
SELECT R.SIFRA, TIP_RATE
FROM NAK_SETUP_RATE R
WHERE GODINA = @god
) R
INNER JOIN
(
SELECT OHO.ID, ROK.SIFRA, ROK.DATUM_DO, ROK.TIP_RATE
FROM
(
SELECT SIFRA, TIP_RATE, DATUM_DO, GODINA
FROM NAK_SETUP_RATE
WHERE GODINA = @god
) ROK
INNER JOIN
(
SELECT ZR.ID, ZR.DATUM_DPO
, CASEWHEN OB.LICA_ID IS NOT NULL THEN 0
WHEN OB.PRAVNA_LICA_ID IS NOT NULL THEN 1
ELSE 2 END AS TIP_LICA
FROM NAK_ZADUZENJA_RATE ZR
INNER JOIN NAK_OBVEZNIK OB ON ZR.NAK_OBVEZNIK_ID = OB.ID
WHERE ZR.VN = @pvn
AND ZR.GOD = @god
) OHO ON ROK.TIP_RATE = OHO.TIP_LICA
) AHA ON AHA.SIFRA = R.SIFRA AND AHA.TIP_RATE = R.TIP_RATE
GROUP BY AHA.ID, AHA.TIP_RATE
) REK ON REK.TIP_RATE = RIK.TIP_RATE
AND REK.ID = OO.ID
INNER JOIN
(
SELECT OHO.ID, COUNT(OHO.ID) AS BROJ_RATA, ROK.TIP_RATE, ROK.GODINA AS GODINA
FROM
(
SELECT SIFRA, TIP_RATE, DATUM_DO, GODINA
FROM NAK_SETUP_RATE
WHERE GODINA = @god
) ROK
INNER JOIN
(
SELECT ZR.ID, ZR.DATUM_DPO
, CASEWHEN OB.LICA_ID IS NOT NULL THEN 0
WHEN OB.PRAVNA_LICA_ID IS NOT NULL THEN 1
ELSE 2 END AS TIP_LICA
FROM NAK_ZADUZENJA_RATE ZR
INNER JOIN NAK_OBVEZNIK OB ON ZR.NAK_OBVEZNIK_ID = OB.ID
WHERE ZR.VN = @pvn
AND ZR.GOD = @god
) OHO ON ROK.TIP_RATE = OHO.TIP_LICA
GROUP BY OHO.ID, ROK.TIP_RATE, ROK.GODINA
)AHA ON OO.ID = AHA.ID AND RIK.TIP_RATE = AHA.TIP_RATE AND AHA.GODINA = @god
) SVE
WHERE OBVEZNIK_ID = 1
ORDER BY OBVEZNIK_ID, OBJEKAT_ID, RATA_ID
Now, as I said before, this query works perfect in SS 2000, but it hangs for 2-4 mins in SS 2005 before returning the results.
But, funny enough, when I put LEFT joins between outer derived tables (except for the first derived table, which is a simple select), it runs smoothly on SS 2005 too.
If I can do anything to clarify, please let me know.
The goal of the query is to calculate periodical payments (the number of which and dates are defined in NAK_SETUP_RATE) for debters but divided by the objects that they have to pay for (so it's number of objects * number of rates), taking into account that simple division of the yearly sum for an object (IZNOS FROM NAK_ZADUZENJA) by the number of periodical payments gives us installments that are the same, except for the last one (example: 100.00 / 6 = 16.67; so the last one is 100.00 - (16.67 * 6) + 16.67, which gives us 16.65, and so we have 5 installments of 16.67 and one of 16.65, the sum of which is 100.00).
That's why the query has a couple of derived tables....one to deal with sums per object (OO), another to deal with total number of payment periods (RIK), another to signal if we've come to the last payment period for one object of a debter (REK), and another to give us the actual number of payment periods for one object of a debter (AHA).
Same names are used for inner derived tables in a couple of places, so the names a gave are for outer derived tables.
Well, hope this helps you help me 🙂
Thanks.
P.S. I edited the code a bit, removing the SELECT TOP 100 PERCENT and according ORDER BY.... nothing significant, as I tested to be sure
February 29, 2008 at 3:56 am
One thing I notice from that.
You've got a TOP 100% ... ORDER BY in a sub query. It's useless there. Order by only applies in the outer select. It probably isn't the cause of the slow down, but it is unnecessary.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 29, 2008 at 4:09 am
I totaly agree Gail... since the query was written from inside out, so to speak, it'a a leftover from the test phase....it's been rectified in production...but I saved the test script for easy debugging 😉
Edit: I inserted query plans in the attachment
Edit2: I inserted the RIGH query plans....sorry for the previous, wrong one...
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply