Post reply

Performance related query

  • July 16, 2012 at 9:24 am

    #261211

    I am creating a massive view with around 250 columns. Let me call it consolidated_View

    This view is created by joining 11 views on a single key which is an int.

    I need to be sending data in this view to few business users on daily basis

    A select * from the consolidated_View takes around 6 hrs to populate data which is tooo much. To reduce the time I created a job that physicalizes the 6 views into tables which were taking longest when run individually.

    Now the consolidated_View takes 5 hours which is also a lot. I would love to physicalize the consolidated_View but that would take too much space which I don’t have. The consolidated_View

    generates around 4546764 rows daily

    Any suggestions what I can do to just be able to get the data on daily basis without taking so long

    Please note I am not physicalizes all the views or teh consolidated view to be able to save space.

  • July 16, 2012 at 9:30 am

    #1513154

    You are sending 4.5 million rows of data with over 200 columns to users on a daily basis? They must have an amazing capability to visualize that in their heads... Something does not seem right here. Is this data summarized? What are they doing with that data, because without summarizing it, it would be impossible to get any business use out of it. Also, why views? What makes a view, in this case, more useful than a stored procedure or a query?

    Jared
    CE - Microsoft

  • July 16, 2012 at 9:35 am

    #1513156

    Add to that you are joining 11 views together to form another view. How many tables do each of the views access? Do this views access some of the same tables? Do any of the views you are joining together use views themselves?

  • July 16, 2012 at 9:38 am

    #1513158

    Thanks for showing interest in my question

    Yeah i know that is a lot of data to go through. The 11 views all have sumarized data.

    I created 11 smaller views since I had to create the consolidated view in the end which would use these 11 views. A query would have been just too long.

    I am create a view for the consolidated data becase a query joining the 11 views will also take the same amout of time.

  • July 16, 2012 at 9:41 am

    #1513163

    All the 11 views use 3 - 4 tables and yes 1 - 2 views also . These are common for all the 11 views.

    I know the data is huge and i cant do anything about it.

    I would have considered writing a job to retrieve rows from this view at night of during off business hours but another problem is that i do not send these results directly. There is a run team that does a select from my consolidated view daily and sends this data.

  • July 16, 2012 at 6:09 pm

    #1513368

    you have 3 levels of views, (the giant over view, the 11 sub views which are made up of both tables and views) and you are returning over 4.5 million rows of data?? i would love to know what the internal row count is, it must be massive. my suggestion is to rewrite the massive giant overview to be dealing with the singular tables and get rid of any views. that may help the performance a little but with so many views there is not going to be much we can do for you in a few forum posts.

    For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

    For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

    Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
    Jeff Moden's Cross tab and Pivots Part 1[/url]
    Jeff Moden's Cross tab and Pivots Part 2[/url]

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply