February 29, 2008 at 4:33 am
Cool.
Any chance of seeing the execution plans? 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 29, 2008 at 4:37 am
I updated the previous post....the plans are there
March 3, 2008 at 4:40 am
So did anyone have time to look at the execution plans?
March 3, 2008 at 6:05 am
Srdjan Svrdlan (3/3/2008)
So did anyone have time to look at the execution plans?
No, not yet, but playing with the code, it seems that this whole query could be "tamed" considerably first. There's some overkill with derived tables. What I suggest you do is this: where a derived table is used more than once in the query, prepare it first, before your main query, as a temp table, then substitute the derived table in your main query for the temp table. This will make the main query far easier to read, to such an extent that you will almost certainly be able to restructure the whole lot into something much simpler using straightforward joins. Run the query and check results each time you substitute a derived table for a temp table to ensure the results are correct. The purpose of doing this switching is to attempt to clean up the query, but in certain rare circumstances this approach can speed up a complex query too. In this case, I'd hunch that you will end up with a much simpler structure with perhaps two derived tables.
Here's a sample of what I mean - untested of course!
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 SIFRA, TIP_RATE, DATUM_DO, GODINA
INTO #ROK
FROM NAK_SETUP_RATE
WHERE GODINA = @god
-----------------------------------------------------------------
SELECT ZR.ID, ZR.DATUM_DPO,
CASE WHEN OB.LICA_ID IS NOT NULL THEN 0
WHEN OB.PRAVNA_LICA_ID IS NOT NULL THEN 1
ELSE 2 END AS TIP_LICA
INTO #OHO
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
------------------------------------------------------------------
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 #ROK
) 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 #ROK R
INNER JOIN
(
SELECT OHO.ID, ROK.SIFRA, ROK.DATUM_DO, ROK.TIP_RATE
FROM #ROK ROK
INNER JOIN #OHO 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 #ROK ROK
INNER JOIN #OHO 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
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 3, 2008 at 8:31 am
Hm..an interesting suggestion, to say the least! Now why didn't I think of that 🙂
Looking at the query you presented, I think it will work first time round, but I'll test it thoroughly, of course.
I'll try it out and post my observations.
March 3, 2008 at 8:57 am
Well that was quick testing, if I ever saw one.
And Chris, you'r hunch was on the dot - it works perfectly, just pasted into Management Studio 🙂
So no more trouble with inner joins in this one, and I got an 2 sec speed up over 67363 records
(5 sec instead of 7 sec).
So thank you for your time in analyzing the query, and all you other guys and gals who had time to converse on this subject.
I'll try narrowing it down a bit...towards fewer derived tables.
March 3, 2008 at 9:19 am
...and don't forget - the nice thing about temp tables is that they're TABLES (meaning - can be indexed, etc...). Sometimes building an index on a temp table is well worth the effort, especially if there's joining involved.
On the other hand - if you don't plan on ever indexing that stuff - you could also "build" your temp tables as CTE's. More stylistic that performance diff there though.
----------------------------------------------------------------------------------
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?
March 3, 2008 at 9:38 am
Nice work, Srdjan! And...good point as always, Matt.
It's not often that this technique actually speeds up a process; it's more useful as a tool for analysing and simplifying long, overcomplex queries which like this one have been built from the inside out.
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 3, 2008 at 10:07 am
Incredibly enough, this "tool" yields grater speeds on SS 2000!
Tested on 16632 rows on one database it yielded a 7 sec increase in speed (from 9 to 2 sec),
and on another database, on 152188 it yielded a 10 sec increase in speed (from 18 to 8 sec)!
I'll sure remember this!
Thanks again!
May 20, 2011 at 9:12 pm
this url might be helping you for INNER JOIN using sqlsrv. 🙂
May 23, 2011 at 8:54 am
You do realize that this thread is over three years old.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply