Custom Aggregate Causing Issues Sometimes

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » CLR Integration and Programming. » Custom Aggregate Causing Issues Sometimes

16 posts, Page 2 of 2

««1 2

Custom Aggregate Causing Issues Sometimes

Rate Topic

Display Mode

Topic Options

Author

Message

opc.three

Posted Tuesday, June 19, 2012 7:49 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:24 PM
Points: 6,826, Visits: 11,950

I am seeing some reports of this kind of issue with earlier builds of 2005. What build are you running?

SELECT  SERVERPROPERTY('Edition') AS Edition,
        SERVERPROPERTY('ProductVersion') AS ProductVersion,
        SERVERPROPERTY('ProductLevel') AS ProductLevel

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1318025

nick947

Posted Tuesday, June 19, 2012 7:51 AM

Grasshopper

Group: General Forum Members
Last Login: 2 days ago @ 11:54 AM
Points: 13, Visits: 328

Edition ProductVersion ProductLevel
Standard Edition 9.00.3215.00 SP2

Nick

Post #1318029

opc.three

Posted Tuesday, June 19, 2012 9:34 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:24 PM
Points: 6,826, Visits: 11,950

SP2 is very old. Is applying SP4+CU3+MS11-049 an option?

One more question about the data. How many rows are you calculating the Median over? Median being what it is, you have to store every value before you can calculate the answer meaning your object may be asking for a lot of memory and I have also seen reports of a high volume of memory allocations coming from SQLCLR becoming an issue at times. The Sort in Terminate is also going to be very expensive. I wrote a Median aggregate in SQLCLR not too long ago as a proof-of-concept (POC) and IIRC I used a sorted collection so the class sorted values into the collection as they were read. I am not sure how this compares to your technique in terms of performance, but I can share my code if you're interested. Mine never made it beyond the POC and I never really hammered it from a performance perspective. SQL Server may not be serializing your object, which is why rewriting that method had no affect one way or the other. Just some thoughts.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1318128

nick947

Posted Tuesday, June 19, 2012 10:01 AM

Grasshopper

Group: General Forum Members
Last Login: 2 days ago @ 11:54 AM
Points: 13, Visits: 328

I don't think my group will want to update the service pack. Since we would have to test all of our DBs and retest them when we upgrade.

For the number of rows, the underlying table has 270,107 values but I do an aggregate before that, so when I run the median function it is only being done on 426 rows. It does work just fine when I use it on a straight select of 500 runs I have never had a problem.

That's an interesting idea I will try to rewrite my accumulate function to insert into a sorted position so I won't use the List classes sort function.

Nick

Post #1318147

nick947

Posted Friday, November 09, 2012 2:24 PM

Grasshopper

Group: General Forum Members
Last Login: 2 days ago @ 11:54 AM
Points: 13, Visits: 328

Looks like it was the build of that server. I am currently testing my code on our new server (SQL Server 2012) and everything appears to be running great. Thanks for all the help.

Nick

Post #1383241

opc.three

Posted Friday, November 09, 2012 2:41 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:24 PM
Points: 6,826, Visits: 11,950

Good to know, you're welcome. Thanks for posting back that you found a path forward!

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1383250

« Prev Topic | Next Topic »

16 posts, Page 2 of 2

««1 2

Permissions