SQL Server Performance

Grouping large data

Discussion in 'General Developer Questions' started by mita, Jun 6, 2006.

  1. mita New Member

    I have got 3 huge tables
    here is the sample data

    table 1
    Companyname id year volume_for_jan vol_for_feb.....vol_dec
    abc 1 2000 34 333 ......555
    2 2000 33 22 666

    table 2
    Companyname id year volume_for_jan vol_for_feb.....vol_dec
    rrr 44 2001 55 66.............888
    24 2001 22 35 454

    each table has almost 800000 rows

    I need to generate a report out of these tables which should be as follows

    Company name Id Year Vol_for_jan Vol_for_feb.........
    abc 1 2000 34 33
    2 2000 33 22
    rrr 44 2001 55 66

    when i try to run the report in business intelligence ,it takes forever to run and doesnt even produce the report at the end...

    what shall i do????
  2. Madhivanan Moderator

    Did you use indexes in the table?
    Do you want to combine the results from two tables?


    Failing to plan is Planning to fail
  3. dineshasanka Moderator

    Check the indexing of your tables or use Index Tunning wizard

  4. sivaraman New Member

  5. mmarovic Active Member

    Can you post the long running query code? What is "business intelligence" you mentioned? Some kind of olap or sql client?
  6. FrankKalis Moderator

  7. Madhivanan Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br />I guess "business intelligence" is a report generator like Business Objects, Reporting Services or similar.<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Yes It sounds Crystal Reports [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  8. cmdr_skywalker New Member

    BI software creates cubes, an aggregate form of the measures. And usually, they have scheduled refresh run (except for real time). The problem might be indexes/config or how you implement it.

    May the Almighty God bless us all!
  9. mita New Member

    well my problem is i dont have a primary key on my tables..and if i need to insert an auto number field sql doesnt let me do that...
    wat shall i do???
    i really need to solve this problem..

    pls pls help guys
  10. mmarovic Active Member

    quote:Originally posted by mita

    well my problem is i dont have a primary key on my tables..and if i need to insert an auto number field sql doesnt let me do that...
    wat shall i do???
    i really need to solve this problem..

    pls pls help guys
    Now I understand even less what is the problem you want to be solved. First you said you have long running query, now the problem is that you can't insert autonumber field.
    I asked if it is possible to post the long running query but you haven't answered.

    I am afraid we can't help you unless you are much more specific with your request. Based on your first post it looks like that you need union from two tables that will return more then 1 500 000 rows. Such query would take a lot of time and no optimization is possible. I guess you have some kind of criteria you haven't mentioned yet.

    In your second post you are talking about inserting "autonumber field" and I really don't understand how it is related to your first post.

    Please clarify what the problem is.
  11. mita New Member

    well the reason i talked about inserting an auto number filed was to create a primary key on the table and then create a clustured index on that column..

    i want a report in a drill down sort manner where in the first grouping level is the company name,then next level is id,then next level is year and then the volumes(vol_jan,vol_feb etc)

    i have to export the final report in excel because we send the excel reports to our clients..

    so i am not very sure wheather i have to use the business intelligence or not..because the whole purpose of running a query is just to generate a report ...so wat shall i do now??

    In all my tables,Id field is the common one. as in it has got common values in all the tables..

    i hope i am a bit clear this time??
    My apologies if i confused u because i have been really trying hard to figure out the way to solve this problem
  12. mmarovic Active Member


    I am afraid your question is more about sql server 2005 and business inteligence studio and I have no experience with them.

    I have some restricted experience with previous version of analysis services but I am far, far from expert in that area. So, I'll just suggest something that you might have already tried and it is probably obvious to you. My guess that the problem can be solved by defining cube on top of view that is union of yearly tables.

    Maybe you would recieve more useful answers if you post the question in analysis services forum.
  13. cmdr_skywalker New Member

    What SQL Server version are you using? Most of the time, primary key should be defined in the table. It helps also on performance but lets go back to your request: the report.

    If you have a BI software, you can probably use its capability to generate the dimensions and cubes. For instance, in business object, you have to generate the universe, drag and drop the fields from the universe and run the report. The report can then be exported to Excel. Similar steps with Crystal report and Cognos.

    If you want to old fashion way using Excel and Analysis services, you have to create the dimensions and cubes, and run to generate the aggregates. Once you have done that, you can connect the excel through SQL olap provider and use the Excel pivot table capability to drag-and drop columns. Check the BOL for more information.

    If you are using Report services, see the web for examples on generating cubes/reports.

    May the Almighty God bless us all!
  14. mita New Member

    thanks for your help..
    well i am using sql 2005 only..but i ve got no idea how to use the universe or cubes...
    can you give me a quick direction or send me some links so that i can move onto the right path??
  15. cmdr_skywalker New Member

  16. mita New Member

    Hi thanks for the link.. it did happen to be very useful for me
    but i have not been able to completely design a cube with it...
    i created a new analysis project and then i added my two tables in the data source view.. but when i try to run the cube wizard, it asks to create the fact table which idont know how to do...
    how shall i solve my problem with the analysis services???????????

Share This Page