Tsql procedure to mutiple rows if it has duplicate id

Question

Tsql procedure to mutiple rows if it has duplicate id

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

SQL Kiwi (8/24/2012)
Luis Cazares (8/24/2012)
...the outer apply with EXP(SUM(LOG(value))) gave some unexpected results...
Try it with zero or negative numbers in the 'val' column...

Don't even want to. I've clearly said that is works for the picture given by OP.

There are more precise ways to calculate "aggregated" multiplication in SQL (you can try cast(exp(sum(log(my_field))) as int) to get rid off .0001s), but why T-SQL doesn't have such a simple in-build function?

There is a good reason for this: it's very rarely needed as it does overflow very quickly...

Did you do performance test on how many records? 100? 1000? 1000000?

Actually, if you look setup from OP you will realise that it may not be really necessary...

Can you see his example:

id val

1 2

1 3

1 4

2 5

2 6

3 7

The value is always increases even accross ID's...

In this case the maximum number of records per id will be 12 (if the val starts from 1)

as:

1*2*3*4*5*6*7*8*9*10*11*12*13 will overflow max bigint

Yes, there is a triangular join in my way, but it will work just fine for small data-sets.

If I would need to do the same for the large ones, I would write aggregate CLR, I think it will beat any of LOOP-ing solutions and may well win over "quirky" update.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 2

Luis Cazares (8/24/2012)
After several runs (with consistent results) that might not be enough to prove something real. I found out that the explicit RBAR (from Paul) beated the hidden RBAR from Eugene. It was running even faster than the quirky update method I used, but then I used an index which made it run five times faster with 5000+ rows.
As noted by Eugene, the quirky update method needs to be used carefully and tested before using it, but the outer apply with EXP(SUM(LOG(value))) gave some unexpected results as well (values with a variation of .0001).
Paul's cursor is well tunned and it works great even with a larger set of data.

Luis...

If you get the chance, I'd love to see your test harness for this.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 3

Eugene Elutin (8/24/2012)
Yes, there is a triangular join in my way, but it will work just fine for small data-sets.

I always have trouble justifying code like a Triangular Join based on a small number of rows because some poor slob will either miss such a note or not understand its meaning and use it on something big.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)