Post reply

Database Design for 100GB DB

  • May 22, 2008 at 8:14 am

    #191365

    Hi All

    I have currently inherited a SQL server which has been used for reports, it has 2 years worth of data on a DB, with Months as tables, datas are retrived Via an SP which does a Union All on all the tables for a Customer number.

    It takes nearly 30 minutes to retrive a users data for 2 years. is there any way to reduce this time, which is the best pratice to do this.

    Can any one recommand

    Cheers

  • May 22, 2008 at 8:36 am

    #819161

    Index things? Rewrite the query? Both are possible ways to speed things up, but without more details, can't really give more advice.

    What's the query and the table DDL (and indexes)?

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • May 22, 2008 at 8:44 am

    #819177

    Hi Steve

    Thanks for the reply, the Query is so simple,

    select col1,col2,col3 from table1 where user=@userid

    Union all

    select col1,col2,col3 from table1 where user=@userid

    and all the tables have been indexed on User

    And if this helps, DB is on a RAID 5 Lun,

    Cheers

  • May 22, 2008 at 9:34 am

    #819211

    select col1,col2,col3 from table1 where user=@userid

    Union all

    select col1,col2,col3 from table1 where user=@userid

    Why do you need to use 'UNION ALL' , it seems to me the select statement is exactly the same?

  • May 22, 2008 at 9:39 am

    #819214

    Does this make a difference on the performance??

  • May 22, 2008 at 10:35 am

    #819245

    You have an index, but is it being used properly? What do the execution plans look like? What's the selectivity of the data in that index, meanin, how unique is it?

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • May 22, 2008 at 10:48 am

    #819255

    CrazyMan (5/22/2008)

    Does this make a difference on the performance??

    You are doing the exact same thing twice , unless there is some reason for this , then it will affect performance

  • May 22, 2008 at 11:03 am

    #819263

    Sorry for the code

    Its

    select --- From table 1 where user=@userid

    union all

    Select --- From table 2 where user=@userid

    --

    --

    --

    --

    Union all

    Select --- from table 12 where user=@userid

  • May 22, 2008 at 11:12 am

    #819268

    Okay makes sense.

    Post the DDL of the table and im sure someone can help you out..

  • May 23, 2008 at 10:53 am

    #819816

    Is it possible to further qualify the query and modify the stored procedure accordingly?

    In other words, is there a reason you need to pull 2 full years worth of data for every query that runs?

  • May 23, 2008 at 11:40 pm

    #819969

    There are at least 2 possible problems.

    1. each select may not have required indexes

    2. it's forced to create a temp table

    Union all does not create temp table and if #1 is not a problem, it takes nothing more than a single table select, unless you force it to create the temp table by using order by.

  • May 24, 2008 at 9:44 am

    #820003

    CrazyMan,

    I have a similar situation at work... Just to be sure... you're talking about 24 tables, correct?

    Just to be sure, you ARE using 2005, correct?

    How many (order of magnitude) rows are in each table?

    Also, would you post the table CREATE statement of one of the tables, please... be sure to include the code for the Primary key and any indexes you may have on the table.

    Also, once you get the data for a single user, what are you doing with it? It may make a difference.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • May 27, 2008 at 5:45 am

    #820472

    Thanks Jeff

    I am Using nearly 48 tables(4 years worth of tables) and yes its SQL 2005 (64 Bit),

    Each table contains nearly 5 to 6 million records,

    CREATE TABLE [dbo].[Ou0510](

    [msgID] [bigint] NOT NULL,

    [ph] [char](15) NULL,

    [msg] [varchar](240) NULL,

    [dat] [datetime] NOT NULL,

    [aaa] [smallint] NOT NULL,

    [bbb] [tinyint] NOT NULL,

    [ccc] [datetime] NULL,

    [dddd] [varchar](38) NULL,

    [eeee] [tinyint] NOT NULL,

    [Yy] [tinyint] NULL,

    [Mm] [tinyint] NULL,

    [Dd] [tinyint] NULL,

    [Hh] [tinyint] NULL,

    [cd] [bit] NOT NULL,

    [rt] [tinyint] NOT NULL,

    [rs] [tinyint] NULL,

    [oi] [tinyint] NOT NULL,

    [iMsgid] [bigint] NULL,

    [sr] [smallint] NOT NULL,

    [kd] [smallint] NOT NULL,

    [bo] [bigint] NULL,

    CONSTRAINT [PK_Outqueue0510] PRIMARY KEY CLUSTERED

    (

    [msgID] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = ON, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]

    ) ON [PRIMARY]

    I had tried to create a view on each table and used the View, which took me nearly 20 minutes to return a result of 5000 Rows for a particular user, and i search via Ph, which is indexed when i can see with SP_helpindex,

    Is the the normal time to retrive records from the tables, how much time does it take to retrive data from the above mentioned structure, in other words how do i calculate the correct time to retrive the records and then compare that with the actual SP time??

    Thanks in Advance

  • May 27, 2008 at 6:35 am

    #820508

    Almost the same size as the tables I deal with at work...

    Tell me... what identifies a "user"... the PH column?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • May 27, 2008 at 7:10 am

    #820539

    yes jeff, its the Ph column

Viewing 15 posts - 1 through 15 (of 16 total)

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