Post reply

Designing a View, Can this be tuned ??

  • August 30, 2006 at 5:39 am

    #62035

    Hi All,

     

    I currently have a view that refereces some tables, an example is

     

    Create view UVw_Test as

    SELECT * FROM MEDICSERVICEFEED20060825 UNION ALL

    SELECT * FROM MEDICSERVICEFEED20060826 UNION ALL

    SELECT * FROM MEDICSERVICEFEED20060827 UNION ALL

    SELECT * FROM MEDICSERVICEFEED20060828 UNION ALL

    SELECT * FROM MEDICSERVICEFEED20060829

     

    Queries against this view takes ages, each of the daily feed tables have around 5million records, does anyone have any idea of how this can be tuned.

    SQL server Ent Edition

     

    I thought of indexed views, but as you cannot have unions, so that’s no good.

    Kindest Regards,

    John Burchel (Trainee Developer)

  • August 30, 2006 at 7:23 am

    #657502

    Do you need all the details (columns) of each record?

    Can you find some clauses to restrict the number of records?

  • August 30, 2006 at 7:23 am

    #657501

    can you post a create table statement for one of these medicservicefeed tables?

    you can also use union hints

    select x,y,z from a union all

    select x,y,z from b

    option(CONCAT UNION)

    which will "attempt" to force a concat union. you need to be carefull with these though

    you might also check your plan for parallelism and consider using maxdop (sometimes unions self block when split).

    to be honest though - a union view of 25 million rows isn't going to be very fast

    MVDBA

  • August 30, 2006 at 7:34 am

    #657510

    I think you are looking for partinioning (tables or views).  Check out books online for more details.  I can't help much because I never had to use that feature.

Viewing 4 posts - 1 through 4 (of 4 total)

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