Huge result set using many joins and grouping 1

[Joiner]

Hi.
I have been given a stored procedure request that would return about 100,000 records at a time. The record length would be approximately 1,500 bytes and the criteria includes about 17 left joins and an aggregate function which would group about 60 fields. The programmer wants to take this entire result set and bind it to a data grid on the client side. My issue at this point is speed. The programmer wants this to be almost instantaneous. Is this realistic? Does anyone else have experience returning such large and complicated result sets? Any tricks on how to make the return time as speedy as possible? I have indexed and optimized the tables - the Optimizer had no further suggestions.
Thank you in advance.

 

[fluteplr]

Not only is it not realistic, IMHO it is stupid.

1. What on earth is the enduser going do with a grid with 100,000 records in it. Spend the next three weeks scrolling though it?

2. There are physical limits to how fast you can move that much data, much less create it in the first place. You are talking about over 1 meg of data (if my math is correct).

3. I am not sure what you mean by group 60 fields? Do you mean the group by clause has 60 fields in it, or you are aggregating 60 fields per group?

My suggestion (only slightly tounge in cheek), get a new programmer.

You (he/she) whatever, needs to design a better user interface, one where the user narrows down the number of records they are viewing at any one time.

If you can describle what the actual purpose of this is supposed to be, I would be happy to make some suggestions.

 

[Andel]

-- that would return about 100,000 records at a time.
-- record length would be approximately 1,500
-- criteria includes about 17 left joins
-- aggregate function which would group about 60 fields.

I don't think it's realistic.
If after the GROUP BY clause you still have 100,000 records returned?




Andel
andel@barroga.net

http://andyb.8m.com

 

[Andel]

Good suggestion fluteplr
Andel
andel@barroga.net

http://andyb.8m.com

 

[Joiner]

Thank you! Both of your comments are very helpful, in addition to supporting my case.