Database Design for 100GB DB

Question

Database Design for 100GB DB

CrazyMan

SSChampion

Points: 12560
More actions
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

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

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

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 1

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)?

CrazyMan SSChampion Points: 12560 More actions · Answer 2

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

Loner SSC-Insane Points: 21279 More actions · Answer 3

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?

CrazyMan SSChampion Points: 12560 More actions · Answer 4

CrazyMan

SSChampion

Points: 12560

May 22, 2008 at 9:39 am

#819214

Does this make a difference on the performance??

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 5

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 Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

steveb. SSC-Forever Points: 46733 More actions · Answer 6

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

CrazyMan SSChampion Points: 12560 More actions · Answer 7

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

steveb. SSC-Forever Points: 46733 More actions · Answer 8

Okay makes sense.

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

Robinson SSCrazy Points: 2194 More actions · Answer 9

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?

Robert-378556 SSCertifiable Points: 5917 More actions · Answer 10

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.

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 11

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)

CrazyMan SSChampion Points: 12560 More actions · Answer 12

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

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 13

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)

CrazyMan SSChampion Points: 12560 More actions · Answer 14

CrazyMan

SSChampion

Points: 12560

May 27, 2008 at 7:10 am

#820539

yes jeff, its the Ph column