Calculations in the SQL Server

Hi All,
I am converting database from Access 2000 to SQL Server 2000.
I have very long calculations written in Access.
eg.
A+B as C, C+D as E, E + F as G and so on...
In SQL I created views, and they refer to each other. From my query
with calculation I created 5 views, where view 2 use calculations from
view 1, view 3 from view 2 and 1... and view 5 use view 1 to view 4. I
use those views in Stored Procedures with sum, average and LAST which
doesn't exist in SQL. (So that is another view)
All of that works but very slow.
Is there any better way to handle this kind of calulations?
Thank you
Schapopa
Deeply nested views can be slow. It might be better to simply create a
single view with all of the calcs you need...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"schapopa" <areklubinski@.hotmail.com> wrote in message
news:1112614187.922629.116440@.z14g2000cwz.googlegr oups.com...
> Hi All,
> I am converting database from Access 2000 to SQL Server 2000.
> I have very long calculations written in Access.
> eg.
> A+B as C, C+D as E, E + F as G and so on...
> In SQL I created views, and they refer to each other. From my query
> with calculation I created 5 views, where view 2 use calculations from
> view 1, view 3 from view 2 and 1... and view 5 use view 1 to view 4. I
> use those views in Stored Procedures with sum, average and LAST which
> doesn't exist in SQL. (So that is another view)
> All of that works but very slow.
> Is there any better way to handle this kind of calulations?
> Thank you
> Schapopa
>
|||Is there any other way. Those calculation are really big, and often
contain divisions. (where I am checking if value = 0) I would say that
it is impossible to create one view from those 5 views.
Wayne Snyder wrote:
> Deeply nested views can be slow. It might be better to simply create
a[vbcol=seagreen]
> single view with all of the calcs you need...
from[vbcol=seagreen]
4. I[vbcol=seagreen]
which[vbcol=seagreen]
|||On 4 Apr 2005 07:32:33 -0700, schapopa wrote:

>Is there any other way. Those calculation are really big, and often
>contain divisions. (where I am checking if value = 0) I would say that
>it is impossible to create one view from those 5 views.
Hi schapopa,
I'm not sure if it helps in your case, but you could consider using
derived tables. Something like this:
SELECT Expr1, Expr2, Expr1 + Expr2 AS Expr3
FROM (SELECT Col1 - Col2 AS Expr1, Col3 * Col4 AS Expr2, Col7
FROM SomeTable
WHERE Col5 > Col6) AS x
WHERE Expr1 <> Expr2
AND Expr2 + Col7 > 500
You can even nest them. The example below ussees two levels of nesting,
but you can add more.
SELECT Expr1, Expr2, Expr3,
COALESCE(Expr3 / NULLIF(Expr1 - Expr2, 0), 0) AS Expr4
FROM (SELECT Expr1, Expr2, Expr1 + Expr2 AS Expr3
FROM (SELECT Col1 - Col2 AS Expr1, Col3 * Col4 AS Expr2, Col7
FROM SomeTable
WHERE Col5 > Col6) AS x
WHERE Expr1 <> Expr2
AND Expr2 + Col7 > 500) AS y
WHERE Expr3 = 18
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Look at using scalar user-defined functions.
Without seeing the calculations you are trying to perform, you should still
be able to build a base set of functions that you can nest. Then, create
one VIEW that puts them all together.
Sincerely,
Anthony Thomas

"schapopa" <areklubinski@.hotmail.com> wrote in message
news:1112625153.408751.17040@.g14g2000cwa.googlegro ups.com...
Is there any other way. Those calculation are really big, and often
contain divisions. (where I am checking if value = 0) I would say that
it is impossible to create one view from those 5 views.
Wayne Snyder wrote:
> Deeply nested views can be slow. It might be better to simply create
a[vbcol=seagreen]
> single view with all of the calcs you need...
from[vbcol=seagreen]
4. I[vbcol=seagreen]
which[vbcol=seagreen]