September 12, 2007 at 7:21 am
Hi Experts,
I need you help to optimize this query. Because right now it is taking 2 hours to complete the execution.
Some experts from Sqlservercentral wrote that performance is depends on 1% in memory, 2% in server and 97% in code.
I know it i have to remove RBAR concept from the below query. But i am not able to remove RBAR concept.
Can any experts give me some suggestion and comments about the code and please give me some idea to avoid RBAR logic in the code ?
-------------------------------------------------------------------------
create Procedure dbo.CPSCalc
(
@CompanyInstit int = NULL
)
AS
Begin
create table #Tranches
(
Seq int identity,
CompanyInstitNbr int,
ValueDate datetime,
OutstUsdAmt money,
ShareQty float,
CPS money
)
IF @CompanyInstit <> NULL
BEGIN
insert into #Tranches
SELECT distinct T.CompanyInstitNbr,ValueDate,
CONVERT (money, 0), CONVERT (float, 0),
CONVERT (float, 0)
FROM Tranche T,AcctEntry A
WHERE T.CompanyInstitNbr = A.CompanyInstitNbr
and T.ProjectId = A.ProjectId
and T.TrancheNbr = A.TrancheNbr
and HoldingNbr IS NOT NULL -- null HoldingNbr means no shares purchased.
and T.CompanyInstitNbr = @CompanyInstit
ORDER BY T.CompanyInstitNbr,ValueDate
END
ELSE
BEGIN
insert into #Tranches
SELECT distinct T.CompanyInstitNbr,ValueDate,
CONVERT (money, 0), CONVERT (float, 0),
CONVERT (float, 0)
FROM Tranche T,AcctEntry A
WHERE T.CompanyInstitNbr = A.CompanyInstitNbr
and T.ProjectId = A.ProjectId
and T.TrancheNbr = A.TrancheNbr
and HoldingNbr IS NOT NULL -- null HoldingNbr means no shares purchased.
ORDER BY T.CompanyInstitNbr,ValueDate
END
DECLARE @Seq int,
@CompanyInstitNbr int,
@ValueDate Datetime,
@OutstUsdAmt money, /* Outstanding Amt. */
@ShareQty float
/* Let's scroll through all unprocessed tranches. */
SET NOCOUNT ON
SELECT @Seq = 0
WHILE 1 = 1
BEGIN
SELECT @CompanyInstitNbr = CompanyInstitNbr,@Seq = Seq, @ValueDate =ValueDate
FROM #Tranches
WHERE Seq = (SELECT MIN (Seq)
FROM #Tranches
WHERE Seq > @Seq)
IF @@ROWCOUNT = 0
BREAK
print 'Records Up...'
print @Seq
SELECT @OutstUsdAmt = SUM (AcctEntryUsdAmt)
FROM AcctEntry A, GlRollup G
WHERE BalRuleNbr = '010'
AND A.AcctNbr = G.AcctNbr
AND A.SubAcctNbr = G.SubAcctNbr
AND A.ValueDate <= @ValueDate
AND A.CompanyInstitNbr = @CompanyInstitNbr
SELECT @ShareQty = SUM (AcctEntryQty)
FROM AcctEntry A, GlRollup G
WHERE BalRuleNbr = '075'
AND A.AcctNbr = G.AcctNbr
AND A.SubAcctNbr = G.SubAcctNbr
AND A.ValueDate <= @ValueDate
AND A.CompanyInstitNbr = @CompanyInstitNbr
UPDATE #Tranches
SET OutstUsdAmt = isnull(@OutstUsdAmt,0), ShareQty = isnull(@ShareQty,0)
WHERE Seq = @Seq
END
/* Now let's calc @ the company level. */
SELECT CompanyInstitNbr,ValueDate,
SUM (OutstUsdAmt) OutstUsdAmt,
SUM (ShareQty) ShareQty,
CONVERT (money, 0) CPS
INTO #Companies
FROM #Tranches
GROUP BY CompanyInstitNbr,ValueDate
UPDATE #Companies
SET CPS = OutstUsdAmt / ShareQty
WHERE ShareQty <> 0
select * into dbo.CPS_All
from #Companies
WHERE ShareQty <> 0
END
-------------------------------------------------------------------------
Regards
Karthik
karthik
September 12, 2007 at 7:29 am
I have a better idea. Why not use this as a learning experience for yourself?
At a glance, I see a single RBAR issue in your query. Can you tell us which single part of the query works in an RBAR fashion, and what purpose that portion of the query serves?
September 12, 2007 at 8:23 am
Try using the execution plan to see where your code is slowest or most resource intensive. then use modularise your code. by this i mean use views, user defined functions and try to stay away from cursors and temporary tables. this has always worked for me...
there is a new coding paradime emerging, with the advent of dlinq the trend will be towards moving away from putting business logic into store procedures.
even without this in mind i recommonded you leave as much business logic out of stored procs as possible as it slows the performance of the sql server and makes maintenance a pain in the butt.
you'll find that using this approch is also more scalable and at the end of the day your procs run faster.
i hope this helps
September 12, 2007 at 8:32 am
OK, if you look at the procedure, it's pretty much getting two values for each companyId: the sum(moneyamount) and the sum(number of shares)
you can get the same information from a subquery for each of the two values i think.
try this query and tell me if it's close to producing the results you were looking for:
SELECT distinct
Tranche.CompanyInstitNbr,
Tranche.ValueDate,
OutstUsdAmt.AcctEntryUsdAmt AS OutstUsdAmt,
ShareQty.AcctEntryQty AS ShareQty,
CASE WHEN ISNULL(ShareQty.AcctEntryQty,0) = 0 THEN 0 --CHECK FOR DIVISION BY ZERO
ELSE OutstUsdAmt.AcctEntryUsdAmt / ShareQty.AcctEntryQty
END AS CPS
FROM Tranche
INNER JOIN AcctEntry ON Tranche.CompanyInstitNbr = AcctEntry.CompanyInstitNbr
LEFT OUTER JOIN ( --USED TO GET THE OutstUsdAmt FOR ALL COMPANIES?
SELECT
CompanyInstitNbr,
SUM (AcctEntryUsdAmt) AS AcctEntryUsdAmt,
AcctNbr,
SubAcctNbr
FROM GlRollup
WHERE BalRuleNbr = '010'
GROUP BY
CompanyInstitNbr,
AcctNbr,
SubAcctNbr
) OutstUsdAmt ON AcctEntry.CompanyInstitNbr = OutstUsdAmt.CompanyInstitNbr
AND AcctEntry.AcctNbr = OutstUsdAmt.AcctNbr
AND AcctEntry.SubAcctNbr = OutstUsdAmt.SubAcctNbr
LEFT OUTER JOIN ( --USED TO GET THE ShareQty FOR ALL COMPANIES?
SELECT
CompanyInstitNbr,
SUM (AcctEntryQty) AS AcctEntryQty,
AcctNbr,
SubAcctNbr
FROM GlRollup
WHERE BalRuleNbr = '075'
GROUP BY
CompanyInstitNbr,
AcctNbr,
SubAcctNbr) ShareQty ON AcctEntry.CompanyInstitNbr = ShareQty.CompanyInstitNbr
AND AcctEntry.AcctNbr = ShareQty.AcctNbr
AND AcctEntry.SubAcctNbr = ShareQty.SubAcctNbr
WHERE Tranche.ProjectId = AcctEntry.ProjectId
and Tranche.TrancheNbr = AcctEntry.TrancheNbr
and Tranche.HoldingNbr IS NOT NULL -- null HoldingNbr means no shares purchased.
Lowell
September 12, 2007 at 9:49 am
In this query value date calculation is very important.why ?
InstitNbr VAlueDate Amount
232 07-sep-2007 5000
232 08-sep-2007 6000
232 09-sep-2007 1000
Then we need to calculate
232 - 07-sep-2007 amount value is 5000
232 - 08-sep-2007 amount value is 11000 ( previous date current value + current date amount value )
232 - 09-sep-2007 amount value is 12000 ( all previous date amount value + current date amount value)
Regards
Karthik
karthik
September 12, 2007 at 9:51 am
David,
----------------------------------------------------------------
SELECT @CompanyInstitNbr = CompanyInstitNbr,@Seq = Seq, @ValueDate =ValueDate
FROM #Tranches
WHERE Seq = (SELECT MIN (Seq)
FROM #Tranches
WHERE Seq > @Seq)
-----------------------------------------------------------------
This part is using RBAR logic. Am i correct ? if not please correct me.
Regards
Karthik
karthik
September 12, 2007 at 10:05 am
That's pretty close, karthikeyan. At the very least, the following code is RBAR in nature, as you are looping through #Tranches a row at a time for all of the statements in that loop.
SELECT @Seq = 0
WHILE 1 = 1
BEGIN
SELECT @CompanyInstitNbr = CompanyInstitNbr,@Seq = Seq, @ValueDate =ValueDate
FROM #Tranches
WHERE Seq = (SELECT MIN (Seq)
FROM #Tranches
WHERE Seq > @Seq)
IF @@ROWCOUNT = 0
BREAK
print 'Records Up...'
print @Seq
SELECT @OutstUsdAmt = SUM (AcctEntryUsdAmt)
FROM AcctEntry A, GlRollup G
WHERE BalRuleNbr = '010'
AND A.AcctNbr = G.AcctNbr
AND A.SubAcctNbr = G.SubAcctNbr
AND A.ValueDate <= @ValueDate
AND A.CompanyInstitNbr = @CompanyInstitNbr
SELECT @ShareQty = SUM (AcctEntryQty)
FROM AcctEntry A, GlRollup G
WHERE BalRuleNbr = '075'
AND A.AcctNbr = G.AcctNbr
AND A.SubAcctNbr = G.SubAcctNbr
AND A.ValueDate <= @ValueDate
AND A.CompanyInstitNbr = @CompanyInstitNbr
UPDATE #Tranches
SET OutstUsdAmt = isnull(@OutstUsdAmt,0), ShareQty = isnull(@ShareQty,0)
WHERE Seq = @Seq
END
I'd recommend you try out Lowell's code, and if it accomplishes what you want, try and figure out how it works. Also, while I'm not ready to jump on board DLinq as the next big thing quite yet (and that's a topic for a different thread), Chris is quite right that the execution plan is what you should be using in this process. I'd recommend that if Lowell's process works for what you want, you take your execution plan, the execution plan from his method, and compare the two, using BOL as a reference as you look over each object in the plan.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply