Classical summation/optimization problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Classical summation/optimization problem

Author

Topic

Moussie
Starting Member

20 Posts

Posted - 2002-03-24 : 21:31:59

Hello!

What I have here is a classical summation/optimization
problem. The problem is below, in a simplified form.

We have a table of billings, T_billings. Every billing has a number, BillingID (plus lots of other irrelevant stuff).

Every billing contains lines from T_billed_lines.

Every line here has a sum, LineSum, a LineID, and,
if the line has been billed, a BillingID.

Now, I need a report (surprise!) that includes for every LineID:

BillingID - LineID - LineSum - BillingSum.

BillingSum is the sum of all the LineSums that belongs
to that lines BillingID. This is (for the sake of
the example) not saved in the T_billings.

--------

The obvious (and slow) way to do this is

SELECT T_billings.BillingID, T_billed_lines.*,
(SELECT SUM(LineSum)
From T_billed_lines tbl JOIN T_billings tb
on tbl.BillingID = tb.BillingID
WHERE tbl.BillingID = T_billings.BillingID)
as BillingSum

FROM T_billings INNER JOIN T_billing ON
T_billings.BillingID = T_billed_lines.BillingID

---------

So, we make a sum for every frigging LineID, although
it would've been enough to do it only for every BillingID.

The obvious solution is to precalculate the sums into
a temp. table and then look them up fast according to
BillingID.

BUT: I HAVE TO USE A VIEW!!! (Access reports blah blah)
So that is not an option...

So, my question is: Is there any clever SQL that will
do this without the SUM for every LineID ? I guess I can
base a view on an SP... but it would be cleaner to just
do one view. It works now, it is just so slow...

Any suggestions ?

-------
Don't use your head - and you'll walk alot

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-03-25 : 01:52:36

Hello! back, moussie

try this
select tb.billingid, tbl.*, a.billingsum
from t_billings tb
inner join t_billed_lines tbl on
tb.billingid = tbl.billingid
inner join
(select billingid, sum(linesum) as billingsum from t_billed_lines group by billingid) a
on tbl.billingid = a.billingid

This is slightly quicker, even though this looks almost the same (and produces the same results).

However, there are a few things you can consider....

1. your where clause is unnecessary as you have already specified this requirement in the join
2. strictly speaking you aren't limited to views in Access Reports - there is always a way.

Post back if you want more help

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Edited by - rrb on 03/25/2002 01:57:12

Moussie
Starting Member

20 Posts

Posted - 2002-03-25 : 20:07:27

>Hello! back, moussie
>
>try this
>select tb.billingid, tbl.*, a.billingsum
>from t_billings tb
>inner join t_billed_lines tbl on
>tb.billingid = tbl.billingid
>inner join
>(select billingid, sum(linesum) as billingsum from t_billed_lines
>group by billingid) a on tbl.billingid = a.billingid

That is true actually...nice! I'll use that one probably.

That is unless you know about a way to base a view on a SP,
that something like:
CREATE VIEW VIEWNAME
AS
SELECT * FROM (EXEC SP)

Is this possible (could not find anything in BOL)
If so, I could use some optimizations with precalculated temp.
tables.

>This is slightly quicker, even though this looks almost the same
>(and produces the same results).

>However, there are a few things you can consider....
>1. your where clause is unnecessary as you have already
>specified this requirement in the join

Oops...that is true actually.

>2. strictly speaking you aren't limited to views in Access Reports -
>there is always a way.

I know.
The point is that I have a report that needs to be called with
all kinds of parameters ("docmd.openreport reportname,,,, X where
X is the wherestring with different parameters/fields (not just
the values) every time...If I should do that with SPs I would have
to figure the parameters each time in the startup of the report...)
And it's not very flexible...

----------

Some people on another site
also pointed out that you can use COMPUTE BY (or even
ROLLUP), but they come in a format (multiple recordsets)
that is not very suitable for Access Reports...:)

Thanks for your time!

-------
Don't use your head - and you'll walk alot

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-03-25 : 20:14:59

actually, even so , you could still use the SP from an Access report.

However, before I suggest how, typically how many records are appearing on your report - i mean are we talking tens, hundreds, or hundreds of thousands...?

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Moussie
Starting Member

20 Posts

Posted - 2002-03-25 : 20:32:40

>actually, even so , you could still use the
>SP from an Access report.

>However, before I suggest how, typically how many
>records are appearing on your report - i mean are
>we talking tens, hundreds, or hundreds of thousands...?

Around 6000-7000 records in one report. Each line contains
something like 20 sums described above. (The reports
are around 300 pages...one of those nasty quarterly totals..:).

Still, by hand-joining the tables
and indexing all relevant fields
in 13-15 seconds (using my method, not yours, will take
some time to incorporate it). Since everything is done
on the server, once it has been run all the pages in
Access comes up instantly, that is, Access just shows
the pages, no processing is done on that side.

So it is all acceptable, I just wanted to see if there
was a way I had overlooked, to let the server rest a little :)
Also I like to optimize....

The other way (that would show the first page of the
report onto the screen faster, but every page would
take longer to load) would be to
let Access do the summing for you,
through Dsum. That is plain slow, and requires that
the query is run once for every call to Dsum (I've
looked at the traces). Also, the clients are not
so new, but the server is really fast.

The way I understand to use SPs in Access reports
are to set the RecordSource to EXEC xyz in the Open
event ???

Thanks again.

-------
Don't use your head - and you'll walk alot

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-03-25 : 20:43:32

Wow - that's one hell of a big report.

You say that you open the access report with a bunch of parameters using
docmd.OpenReport

You could write an sp which would take your WHERE CLAUSE parameter, and do the calc eg:

Public Sub GETREPORT_OnClick()
...
rst.Open "exec sp_DoMySum " & X,CnDB
docmd.OpenReport "reportname",,,,,,X
...
End Sub

1.are you using DAO3.6 or ADO?
2.are you familiar with Dynamic SQL?

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-03-25 : 21:19:40

Even if you just calculated ALL the sums in an sqp before calling the report, you'd probably still be better off....

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Moussie
Starting Member

20 Posts

Posted - 2002-03-25 : 21:26:21

>Wow - that's one hell of a big report.

Small reports are for breakfast :)

>You say that you open the access report with a bunch of parameters using
>docmd.OpenReport

>You could write an sp which would take your WHERE CLAUSE parameter, and do the calc eg:

>Public Sub GETREPORT_OnClick()
>...
>rst.Open "exec sp_DoMySum " & X,CnDB
>docmd.OpenReport "reportname",,,,,,X
>...
>End Sub[/b]

So here you are saving the results into a table, right,
and then you run the report on that precalculated table.
Now, X could be for instance
((Billingnum = 20 or billingnum = 21) AND paid < now())
or just
(Billingsum > 100 AND billingid > 0)
So if I understand you correctly, the SP will need to parse the
X for all kinds or ANDs and ORs AND on top of that, different
variables/parameters...or even EXISTS queries...whoa
(the report is run on all kinds of parameters, not the same set every time) Sounds less than trivial, at least for me :)

>1.are you using DAO3.6 or ADO?

ADO...but I still remember some DAO...:)

>2.are you familiar with Dynamic SQL?

EXEC @stringvar etc ? Havent used it so much yet,
but I have seen some articles here about it...
Time to learn the finer points I guess...:)

-------
Don't use your head - and you'll walk alot

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-03-25 : 21:36:33

(in order)....
quite

yes

yes

agreed

good

don't bother. If you can generate a 300 page report and the user only has to wait 15 seconds, I'd say you're doing fine.

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources