Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2009

    Unhappy Unanswered: Need help... need to create view and index on 1 huge table in sql server 2008

    Hello All,

    I am new to the community and new to SQL server. We have a database, within the database we got a huge table with 30-35 Million records/rows. We can not afford to get a DBA so I'm doing the work. I created U_ID as a Unique Cluster Index. I want to make the table run faster, and create a summary view from this. When I try to create a view on few fields and sum(amount), then do a group by to roll it up, after doing group by the view times out. Any suggestions? Please help. Any suggestions on improving speed of table? Best practices? Please help thanks.

    U_ID int
    field2 varchar(50)
    field3 varchar(50)
    field4 varchar(50)
    field5 varchar(50)
    field6 varchar(50)
    field7 varchar(50)
    field8 varchar(50)
    field9 int
    field10 varchar(50)
    field11 varchar(100)
    field12 int
    field13 date
    field14 date
    field15 varchar(50)
    field16 varchar(100)
    field17 varchar(50)
    field18 varchar(50)
    field19 varchar(100)
    field20 date
    field21 date
    field22 varchar(50)
    field23 money
    field24 varchar(50)
    field25 varchar(50)
    field26 varchar(50)
    field27 date
    field28 varchar(100)
    field29 varchar(150)
    field30 varchar(100)

  2. #2
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    Thirty five million rows isn't a lot for SQL Server running on appropriate hardware. A reasonbaly configured server will barely notice a table this size.

    Look at your column definitions and apply best practices to them. You'll probably need someone that has subject matter knowledge (understands the data) in order to do this, I don't know of any program or simple set of rules that will do this for you.

    Strongly consider applying indices to help with your aggregates. If you know that you want to group by field20 and field30, then put an index on them and it will help if the data changes fastest on the left (put the most selective columns first in your index).

    If your organization can recruit volunteers, you may be able to find a DBA that will help you for little or no money. You might be able to get a trainee from a local college to tackle this as an internship.

    As you've noticed, this problem is big enough that it could probably use someone with experience, although if you work at it a bit (I'd guess about 100 hours or so) you can develop that experience as you go. If you can post more detail either about your data itself (which will get you the best answers) or examples of the syntax you're trying to use (which will probably get decent answers), I'm sure that somone here will offer suggestions.


  3. #3
    Join Date
    Jun 2003
    Provided Answers: 1
    Dear Lord. Tell me those aren't really the column names?

    You can't "sum(amount)" if you have no amount column...

    And I'd disagree with Pat here on one thing. 35 million rows is a sizable table, when design and coding issues can start causing performance impacts.
    If it's not practically useful, then it's practically useless.

    blindman "sqlblindman"

  4. #4
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    I think we agree far more than disagree. Design, coding, etc all come into play for tables this size and I think that runs all through my response. As I said above
    Quote Originally Posted by Pat Phelan
    As you've noticed, this problem is big enough that it could probably use someone with experience
    With that said, I measure databases in tens of gigabytes, and you need to hit 25 or 30 of those (over a quarter terabyte) to qualify as big to me these days... Unless this is one table among many, this database wouldn't get much attention on most of our servers.


  5. #5
    Join Date
    Jan 2007
    Provided Answers: 10
    "My database is bigger than your database!"
    Home | Blog

  6. #6
    Join Date
    Jun 2007
    Ohio, USA
    Also look into tools like the Database Tuning Advisor. For newbie DBA's like myself, they do a good job at suggesting indexes and statistics you can add to increase performance. (Though sometimes it's a bit of overkill.)
    David Maxwell
    Data Integrity? Yeah, I've heard of that...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts