Sequel multiplication

The Mighty Dubs · 09-12-2005 3:06pm #1

Hi
Currently putting together a sequel server db backend to a vb asp site. Now I want to make a multiplication of two numeric fields and put them into a third field i.e ListPrice * Quantity = TotalPrice. All fields are numeric. Can this be done in the "critera" section of the TotalPrice section.

this is my current recordset that im using

SELECT *
FROM dbo.QryByAccount
WHERE BrandName LIKE '%rsSearch%'
ORDER BY CartStatus

******************************

And this is the query script that im using

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[QryByAccount]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[QryByAccount]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE VIEW dbo.QryByAccount
AS
SELECT dbo.tblCart.CartID, dbo.tblCart.Username, dbo.tblCart.OrderID, dbo.tblProducts.ListPrice, dbo.tblProducts.LeadTime, dbo.tblProducts.ProdLgPic,
dbo.tblProducts.ProdName, dbo.tblCart.ProdID, dbo.tblCart.DatePlaced, dbo.tblCart.CartStatus, dbo.tblBrands.BrandName, dbo.tblBrands.BrandLogo,
dbo.tblProducts.OfferEndDate, dbo.tblProducts.MOQ, dbo.tblProducts.Brand, dbo.tblCart.PO, dbo.tblCart.Quantity, dbo.tblCart.TotalPrice
FROM dbo.tblCart INNER JOIN
dbo.tblProducts ON dbo.tblCart.ProdID = dbo.tblProducts.ProdID INNER JOIN
dbo.tblBrands ON dbo.tblProducts.Brand = dbo.tblBrands.BrandID
WHERE (dbo.tblCart.DatePlaced >= CONVERT(DATETIME, '2005-01-01 00:00:00', 102)) AND (dbo.tblCart.CartStatus <> 'Delivered')

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

colm_c · 09-12-2005 3:22pm

Wouldn't it just be easier to do it in ASP?

var1 * var2

(or whatever the asp is)

Mutant_Fruit · 09-12-2005 5:56pm

colm_c wrote:

Wouldn't it just be easier to do it in ASP?

var1 * var2

(or whatever the asp is)

Damn right! You want to store as little info in the database as possible. So all you store is VAR1 and VAR2. You then have a query to pick out those values, and in your ASP code (or whatever) you write result = var1*var2;

Don't save that result in your database! Its pointless.

Seth Brundle · 09-12-2005 10:10pm

Mutant_Fruit wrote:

Don't save that result in your database! Its pointless.

Not only is it pointless but it is also unnecessarily increasing the workload on the database server.

The Mighty Dubs · 12-12-2005 3:11pm

Your gonna have to help me out here guys..Im not quite the programmer i used to be...if you get my drift...

Not to big on ASP...can you help me out here?

Seth Brundle · 12-12-2005 3:23pm

Yes. Refresh your ASP skills:-
www.w3schools.com/asp
www.manastungare.com/publications/asp

amen · 13-12-2005 1:49pm

everyone is correct but as he is using a view then I always take the opinion that the view should provide everything to the user and should require no further action. In this case I would have added a column, TotalPrice in the result set that is ListPrice * Quantity. Then ASP would just display.

What would you do if there were taxes/discounts involved ? bring these back and apply in ASP ?

The Mighty Dubs · 13-12-2005 5:10pm

Still no luck...

Getting this error...

Error Type:
Microsoft VBScript compilation (0x800A03EA)
Syntax error
/pfizer/resumebybrand.asp, line 443, column 7
vbproc(<%= (rsQryProdCart.Fields.Item("ListPrice").Value),)

^

Here is the ASP im trying get working

<Head>
<%
sub vbproc(ListPrice,Quantity)
response.write(ListPrice*Quantity)
end sub
%>

<%vbproc(<%= ((rsQryProdCart.Fields.Item("ListPrice").Value),) %>,<%=(rsQryProdCart.Fields.Item("Quantity").Value)%>)%>

Sneaky_Russian · 13-12-2005 11:35pm

why not just return it from the stored proc/sql

select myfield1, myfield2, (listprice * quantity) [TotalPrice]
from QryByAccount

Mutant_Fruit · 14-12-2005 7:46am

Sneaky_Russian wrote:

why not just return it from the stored proc/sql

select myfield1, myfield2, (listprice * quantity) [TotalPrice]
from QryByAccount

Because i'm so used to the old mantra now that i can't go against it :P

"Try to never store static results in SQL, try to make as few calls as possible to the server, try and make the SQL server do as little as possible to give you the required results".

Multiplication can be easily done in PHP, so don't make the SQL Server multiply it for you. Inner joining half a dozen tables on three dozen conditions can't be easily done in code (it can be done though :P) so you're best doing that in the SQL Server.

amen · 14-12-2005 2:47pm

ok I think you are bit confused

<%vbproc(<%= ((rsQryProdCart.Fields.Item("ListPrice").Value),) %>,<%=(rsQryProdCart.Fields.Item("Quantity").Value)%>)%>

should be

<%vbproc(((rsQryProdCart.Fields.Item("ListPrice").Value),),(rsQryProdCart.Fields.Item("Quantity").Value))%>

but I still think this is the wrong way to to
you are getting back a recordset and as you process each row you call a function to multiple two columns in the result set and then display.
would be much easier to have this already done in sql or at least have a pre display rule (business layer) that does this for you so the asp just does the display(client) which it what you really be doing anyway

Sneaky_Russian · 15-12-2005 12:04pm

Because i'm so used to the old mantra now that i can't go against it :P

ah sure, no hope for you so :rolleyes:

bonkey · 15-12-2005 1:23pm

I'm reading all this "don't precalculate....are you mad", and all that I'm thinking is that you guys need to work with something approaching a VLDB and/or with any sort of data-warehousing.

There are times when it makes a *huge* amount of sense to pre-calculate....you calculate once on INSERT, once on an UPDATE, and *never* on retrieval. When working with large volumes of data, large numbers of users, high query-frequency, or have ultra-high performance demands....pre-calculation is sometimes the only way to go.

Mutant Fruit wrote:

Because i'm so used to the old mantra now that i can't go against it :P

"Try to never store static results in SQL, try to make as few calls as possible to the server, try and make the SQL server do as little as possible to give you the required results".

As a matter of interest....where did this 'old mantra' come from? The one I learned was that relational databases are orders of magnitude more efficient at performing set-based operations (e.g. the multiplication of hte same pair of fields for every record) than procedural-based code processing the resultant recordset.

The only case I can see for doing it post-query is in a situation where you're performing the calculation on the client, and can thus minimise the hit to other users. With a web-server as your database-client, you cannot gain this advantage, unless you perform the muiltiplication right at the end with some client-side script in your ASP. If you're gonna do it server-side, do it on the database server (most probably at query-time, but as mentioned above, there are situations where this is not the optimal) rather than on the web-server.

These are, as always, generalities. If your DB server is hammered from serving multiple apps, but your web-server is ticking over with cycles to spare....sure....my logic doesn't hold. But for me, thats generally a case for arguing for hardware restructuring, not application redesign.

Getting back to the original question....all things being equal, I'd have the calculation performed in the View definition.

jc

amen · 15-12-2005 1:56pm

which is what I said ages ago.
I would be interest to know of those who suggested doing in ASP how many work as DBAs or at least use databases extensively in their jobs?

Mutant_Fruit · 15-12-2005 5:30pm

Well, i've worked on three different big databases. The ones i've been working on have information being updated very frequently (every 30 seconds readings were being taken in). So in the case of that database, precalculating was a waste, as more often than not you'd be precalculating values that would never get looked at, as the operator would only really be checking the database if an error were to occur or the end of the phase.

I suppose thats where i got the "don't precalculate and store static data" from. Then again, if you were in the situation where you had data that was updated infrequently, but accessed very often, you would be better off storing the precalculated data in the database.

In the second case, storing the "static" data (precalculated results) would actually result in making the database do less work (albeit with a slightly larger DB size), but in the first case, it would make the database do a lot of unneeded work. More often than not, minimising database CPU usage is much more preferable.

It really depends on that situation. Which is why the matra says "try"

Its not an iron law.

jd13 · 17-12-2005 10:11pm

The Mighty Dubs wrote:

Hi
Currently putting together a sequel server db backend to a vb asp site. Now I want to make a multiplication of two numeric fields and put them into a third field i.e ListPrice * Quantity = TotalPrice. All fields are numeric. Can this be done in the "critera" section of the TotalPrice section.

Hi

don't mean to be pedantic but unless your are talking about this sequel then its SQL.

BTW if you are creating a view anyway, can't see adding another field creating too much extra overhead, unless you've got a db with a couple of million of records or an already overworked db server.

Sequel multiplication

Comments