July 13, 2001 at 8:19 pm
What is the optimum number of columns that can be grouped by in SQL Server 2000 ?
July 13, 2001 at 11:25 pm
I'm afraid with the information you've provided, no one can give a valid answer. Can you give a more complete synopsis of what you're trying to do?
K. Brian Kelley
K. Brian Kelley
@kbriankelley
July 13, 2001 at 11:35 pm
Leads me to think about what is the max # of columns you can use in a group by and have it be usable...I rarely group by on more then 3-4 cols. Not answerable either - but anyone out there ever have a reason to use a lot more cols than that, say more than 10?
Andy
July 16, 2001 at 1:22 pm
I have a 17 million record table with 12 integer fields that I need to group by and 25 other fields that need to be summed. My question is - Can SQL Server 2000 handle this ? If so, any ideas to improve the query performance - like indexes etc...
Thanks
July 16, 2001 at 7:00 pm
I think you'd have to try it and see. I doubt any index is going to make a difference, unless the 17 million rows are a fairly small subset of the data. I think given the size and complexity you may want to look at OLAP as a better solution.
Andy
July 16, 2001 at 8:53 pm
Wow. Andy's suggestion is probably the best one. How often is the data being added to or updated? Also, is there a possibility of keeping some tables of "running totals" if it's an OLTP system?
K. Brian Kelley
K. Brian Kelley
@kbriankelley
July 16, 2001 at 10:37 pm
I concur that the OLAP solution is one good way. Indexes won't help you much with 17m records. I would also sugest pulling all totals you need on an enterval basis into another table to help shorten processing time on you app. Even if you can group some info into smaller tables that would help much. I have worked with large datasets like this and the more repeating data you can pull out or totals you can keep in other tables to be refed sepratly the better off you will be, but I was working in a datawarehouse/mining situation with data pulled nightly to build reports.
Wes
December 8, 2002 at 3:34 pm
quote:
I have a 17 million record table with 12 integer fields that I need to group by and 25 other fields that need to be summed. My question is - Can SQL Server 2000 handle this ? If so, any ideas to improve the query performance - like indexes etc...Thanks
Have you considered denormalizing your data - splitting it into separate tables based on your "group by" information?
How about indexed views on 2-3 of the columns that are widely used. I would also consider INDEXING all the columns that you use in the GROUP by clauses. if they're all the same, a composite index built of the columns will help response time.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply