locked
multiple table problem RRS feed

  • Question

  • Hi,

    below is my current design.

    In one of my database so many tables and procs one part I feel problematic as below.

    there are around 25 history tables since 1990, all having same definition , one for each year with huge data, all the tables consume approx. 100 GB data. every year one table is being added.

    problem is, when I try to search anything from all the tables I use a view which runs very slow. 

    Below is view designed like.

    create view

    as

    select C1,C2,C3,C4....... from T1990

    Union ALL

    select C1,C2,C3,C4....... from T1991

    Union ALL

    select C1,C2,C3,C4....... from T1992

    ....

    ....

    GO

    I definitely feel its a design problem..

    don't what could be the reason to keep a separate table for each year .. but they could be merged or could be some better design, please suggest.


    SQL Server DBA

    Thursday, January 14, 2016 4:57 AM

Answers

  • Not necessarily, you have to have indexes to qualify your queries. As a matter of fact it is imperative to build correct indexes in such scenarios. At the end, these indexes will be bound to individual tables so they are not duplicated in any way.

    Good Luck!
    Please Mark This As Answer if it solved your issue.
    Please Vote This As Helpful if it helps to solve your issue

    Thursday, January 14, 2016 7:46 AM

All replies

  • http://sqlblog.com/blogs/maria_zakourdaev/archive/2012/02/28/unfairly-forgotten-partitioning-views-can-help-us-make-our-partitioning-tables-design-better.aspx

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, January 14, 2016 6:12 AM
  • Queries on view having multiple tables using UNION ALL can get immensely benefited by placing suitable indexes for your target queries. It is important to know whether your queries are covered by relevant indexes or not. You can post some sample queries that your application runs and also give us details about indexes on these tables to see whether more relevant indexes could be placed


    Good Luck!
    Please Mark This As Answer if it solved your issue.
    Please Vote This As Helpful if it helps to solve your issue

    Thursday, January 14, 2016 7:12 AM
  • keeping same index on all 25 tables, don't you think its a design problem?

    same for future table as well..


    SQL Server DBA

    Thursday, January 14, 2016 7:16 AM
  • In that case, why not using 1 table but partition the data by year? or even can go smaller then by quarter, it will improve your queries with more recent data and don't have so much joins when it comes to historical data.

    Thursday, January 14, 2016 7:44 AM
  • Not necessarily, you have to have indexes to qualify your queries. As a matter of fact it is imperative to build correct indexes in such scenarios. At the end, these indexes will be bound to individual tables so they are not duplicated in any way.

    Good Luck!
    Please Mark This As Answer if it solved your issue.
    Please Vote This As Helpful if it helps to solve your issue

    Thursday, January 14, 2016 7:46 AM