a better way? - SQL Server Forums

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

SQL Server Development (2000)

a better way?

Author

Topic

PeterG
Posting Yak Master

156 Posts

Posted - 2003-05-09 : 13:36:11

I have the following SP and I'm wondering if there's a better, more efficient way of rewriting this (especially the aggregate function part).

SELECT T.txtAccountNum,
T.txtCompanyNum, T.dtePostDate, T.dteCycleDate, T.dteTransacDate, T.txtMerchantName, T.txtSourceCurr, T.txtBillingCurr, T.curForeignAmnt, T.txtSic, T.curTransacAmnt,
T.txtTransacCode, T.txtMerchantCity, T.txtMerchantProv, T.txtMemoFlag, T.lngParent, T.txtCostCenter, T.curOrigAmnt, T.curGst, T.lngSourceId, T.curPst, T.blnForeignEx, T.curNetAmnt,
T.blnSent, T.blnMatched, T.txtComments,
M_Total = (Select SUM(A.curTransacAmnt) from Transactions A where (A.txtMemoFlag = '$')
AND (A.txtAccountNum = '0000000000083642') AND (A.dteCycleDate = '01/01/1963') )
FROM Transactions T
WHERE (T.txtAccountNum = '0000000000083642') AND
(T.dteCycleDate = '01/01/1963')
order by T.dteTransacDate, T.lngParent, T.lngIndex

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-09 : 13:48:10

Well, I have a couple of questions...if you have multiple transaction rows then the sum amount will be the same for every row...sort of like having parental information on the children rows. The row represents the parts of the sum, but your seeing the sum of all of the parts, which is kind of misrepresenting the data.

Also you have txtMemoFlag = '$'. What happend to a row that doesn't have that flag? Again anothe misrepresntation of the data.

What are you trying to do?

Brett

8-)

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-05-09 : 13:52:06

If you run this query frequently, you might consider moving the correlated subquery (the aggregate part) out of the query and into a view. The view would have 3 columns: T.AccountNum,T.dteCycleDate, and SUM(T.CurTransacAmnt ) AS CurTransacAmnt GROUP BY T.AccountNum,T.DteCycleDate, then you could join the main body of the query on the view on the account, and your WHERE clause will not have to be repeated twice.
This idea is similar to a tally table. A tally table is a good idea when there is very large amounts of data. If your table isn't huge, a view might work well enough.

Sarah Berger MCSD

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-10 : 10:48:31

Try this:

SELECT T.txtAccountNum,
..etc..., b.M_Total
FROM Transactions T
INNER JOIN
(Select txtAccountNum, dteCycleDate, SUM(A.curTransacAmnt) as M_Total from Transactions A
where A.txtMemoFlag = '$'
GROUP BY txtAccountNum, dteCycleDate ) B
ON T.txtAccountNum = b.txtAccountNum AND
T.dteCycleDate = b.dteCycleDate
WHERE (T.txtAccountNum = '0000000000083642') AND
(T.dteCycleDate = '01/01/1963')
order by T.dteTransacDate, T.lngParent, T.lngIndex

PLEASE GET AWAY FROM EMBEDDING DATATYPES IN COLUMN NAMES! just makes your code harder to read, longer, etc. I see no advantage of doing this in SQL Server ... just my opininon of course, but doing that sort of thing went out WAY back in the 90's !!

This is very close to what Sarah suggested, just not using a VIEW.

Test it out. I usually paste two select statements in the QA one after another, then run them or analyze them, and the QA gives a nice comparision of the two, and which one takes longer to run and all that. very cool way to compare variuos ways of selecting data. You'll see either they both take 50% of the time, or a lot of times one will take 25% and the other 75%, etc -- giving you a clue as to which is faster.

- Jeff

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-10 : 10:51:47

quote:

if you have multiple transaction rows then the sum amount will be the same for every row...sort of like having parental information on the children rows

Brett -- this happens all the time, and is a good technique. Look at this and see if it makes sense:

select customer, value
from
Data

Now look at this:

select customer, value,
1.0 * value /
(select sum(value) from data) as PctOfTotal
from
Data

It's the only way using SQL to get percentages of totals or things like that in your data -- every row must have the same grand total field in it.

Hope that makes sense.

- Jeff

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-05-11 : 10:31:58

I once needed percentages for each row in the resultset based on a grand total, but rather than repeat the grand total query as a subquery for each row (it's not a correlated subquery) with a very long WHERE clause, I declared two variables at the top of the batch, initialized them first with the grand totals, and then divided the amounts by those two variables to get the percentages. Is this generally a good idea?

Also, prefixing a field with its type is a very good Front-End idea, it's hell to maintain code that isn't prefixed, but I agree with Jeff that it doesn't really make sense in SQL Server. You can use descriptive enough names to understand what each field holds.

Sarah Berger MCSD

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-11 : 19:33:30

Sarah -- that is definitely a good idea, to get the total first and put in a variable and then use that.

that only works when there is 1 grand total per query, of course. And, you must be able to declare a variable and assign it as well -- which isn't always possible (i.e., when you can only use a single SELECT)

However, when you need a percentage of total sales per product for each customer, for example, then some sort of subquery or another is the way to go.

I usually do it like this:

SELECT a.customer, a.product, a.ProductSales, 1.0 * a.ProductSales / b.CustSales as ProductPct
FROM
(SELECT customer, product, sum(sales) as ProductSales FROM Data GROUP BY customer, product) a
INNER JOIN
(SELECT customer, sum(sales) as CustSales FROM Data GROUP BY customer) b
ON
a.customer = b.customer

In that example, you wouldn't be able to assign a variable. And it still executes very efficiently.

Of course, watch out for dividing by zero !

- Jeff

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-05-11 : 23:26:52

SET ARITHABORT OFF
SET ARITHIGNORE ON
SET ANSI_WARNINGS_OFF

My homemade remedy to division by zero errors...

Sarah Berger MCSD

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-12 : 10:14:27

Jeff,

Thanks. Great thread (again).

Sarah: I don't know what the prevailing opinion is, but I personally don't like to mess with settings unless I have to...Just means I have to remeber what I did (not a good thing for me).

Unless of course you set them back on exit.

Brett

8-)

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-05-12 : 22:06:48

Of course I always set the settings back at the end of the proc/batch. I got this whole ritual with the NOCOUNT- before I type a functional line, I do the SET NOCOUNT ON/OFF set, and then sandwich all other statements in between. ADO has fits when NOCOUNT is off. And CONCAT_NULL_YIELDS_NULL is a staple, too. Gotta set it OFF to get reasonable results.

These options aren't to be used lightly, but they're invaluable when you are expecting a high possibility of division by zero problems.

Sarah Berger MCSD

MaverickUK
Yak Posting Veteran

89 Posts

Posted - 2003-05-13 : 08:47:25

Ain't that the truth, in my early SQL Stored Procedure/ADO days, not knowing about the SET NOCOUNT ON issue was the bain of my problems!

Learn the hard way and never forget ;)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources