Post reply

clustered indexes on materialized views

  • February 26, 2004 at 12:00 pm

    #161162

    Greetings,

    I've been given the unfortunate task of taking a view that is used for gathering information to spit into a report and making it 'faster'.  The base sql 'before' the where clause is about a page long selecting roughly 60 fields with 8 left outer and 3 inner joins across VIEWS.  Ugly as hell, unfortunately it works.

    So, executing the view takes about 12 seconds.  I went thru and for all of the joined views I materialized them to tables, created a clustered index on the table.  I did it this way because the views are nested within views sometimes 3-4 deep.  Not sure how to fix THAT issue but handling the one I think I can atm.

    select * into tempCWB from rptComputationsWithBuildings

    Create clustered index [tempCWB_Indx] on tempCWB (intMasterAcctID,MBL)

    I created 11 temp tables, all indexed and now the view returns a result in '1' second.  Cool.

    Here is my next problem.  The temp tables need to be populated right before the report is printed so the information is fresh (and yes it does change frequently).  Every time I created a new index as I was doing these in order to see the performance gains, I had to run the view twice to see the gain. It seemed like the index needed to be built the first time and then once it was it just ran great.  Dropping the temp table drops the index so how do I build the index BEFORE I use the view the uses the index?

    Also, while I am calling them 'temp tables' in my example they are real tables just prefixed 'temp'.  Are there any benifits to using # or ## in building these out?  regardless they are going to be dropped after the report is ran.

    Thanks,

    Chris

     

  • February 26, 2004 at 12:24 pm

    #495971

    maybe a stored proc can give the solution ?

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • February 26, 2004 at 3:55 pm

    #496032

    yep , I will go the sp route and maybe computed columns if at all possible!


    * Noel

  • February 27, 2004 at 2:17 am

    #496111

    Just on a matter of speed, you could try replacing your temp tables with table variables as these are handled in memory so there aren't any expensive writing to disk operations.

    For example :

    declare @temptable Table(tID int primary key identity(1,1),

                                        otherfield varchar(20))

    But you must use aliases if you use it in a join otherwise sql complains

    select

     *

    from                                    

     @temptable t Join othertable on t.tID = othertable.tID

  • February 27, 2004 at 6:49 am

    #496174

    well the problem is that the reports are being created in Crystal Reports from a VB.NET application... long story short, my 'Crystal Guru' said that the reports cannot be fed from a stored procedure but only from a view or table.

    Are there any limitations to table variables?  Some of these tables are 20 columns with at least 60k records.

    Thanks,

    Chris

     

  • February 27, 2004 at 9:18 am

    #496235

    Tell your Crystal GURU that you can use stored procedures I do it all the time  

    I am going to guess that he has to go to the

    Report Options and select Show --> stored procedures and Crystal will do the rest


    * Noel

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

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