SEO

#1 (**permalink**) 02-27-2008, 05:08 PM

SQL SERVER 2K

We have 30 databases each for one business units.
Now I am consolidating few tables from 30 databases to one database for
Analysis purpose.

Now my tables in Central db have millions of records.
I need some tips to handle this huge tables for query purpose (already
applied with all Indexes).

Can I go with INDEXED VIEWS ??

Thx
Sh

#2 (**permalink**) 02-27-2008, 05:08 PM

Indexed views are only really good for aggregations and joining data from
mutiple tables, is this what you need to do??? Several of our tables are
over a million rows in our production environment and it all comes down to
indexing. Did you create new indexes designed for queries or are they the
existing indexes from the original tables?

"Shamim" <shamim.abdul@railamerica.com> wrote in message
news:#vojbnEYDHA.536@TK2MSFTNGP10.phx.gbl...
> SQL SERVER 2K
>
> We have 30 databases each for one business units.
> Now I am consolidating few tables from 30 databases to one database for
> Analysis purpose.
>
> Now my tables in Central db have millions of records.
> I need some tips to handle this huge tables for query purpose (already
> applied with all Indexes).
>
> Can I go with INDEXED VIEWS ??
>
> Thx
> Sh
>
>

#3 (**permalink**) 02-27-2008, 05:08 PM

I am sorry, I meant PARTITIONED VIEWS

Sh

"Kevin Brooks" <kbrooks@sagetelecom.net> wrote in message
news:OPszDvEYDHA.2204@TK2MSFTNGP12.phx.gbl...
> Indexed views are only really good for aggregations and joining data from
> mutiple tables, is this what you need to do??? Several of our tables are
> over a million rows in our production environment and it all comes down to
> indexing. Did you create new indexes designed for queries or are they the
> existing indexes from the original tables?
>
>
> "Shamim" <shamim.abdul@railamerica.com> wrote in message
> news:#vojbnEYDHA.536@TK2MSFTNGP10.phx.gbl...
> > SQL SERVER 2K
> >
> > We have 30 databases each for one business units.
> > Now I am consolidating few tables from 30 databases to one database for
> > Analysis purpose.
> >
> > Now my tables in Central db have millions of records.
> > I need some tips to handle this huge tables for query purpose (already
> > applied with all Indexes).
> >
> > Can I go with INDEXED VIEWS ??
> >
> > Thx
> > Sh
> >
> >
>
>

#4 (**permalink**) 02-27-2008, 05:08 PM

Partition views can help here. You will be able to keep table sizes down
and this can increase query times when done right. It sounds like you moved
multiple tables into one table. If this is the case you will need to break
them back out into smaller tables, add partitioning columns, modify Primary
Keys and build the view. That might be a hassle. I can query a 20million
record table and get a response back in under a second, I just did a query
on 250million and elasped time was 10sec(only one index on there, I know I
could get quicker

). Did you change indexes for reporting or not?

"Shamim" <shamim.abdul@railamerica.com> wrote in message
news:#dGAN7EYDHA.2200@TK2MSFTNGP09.phx.gbl...
> I am sorry, I meant PARTITIONED VIEWS
>
> Sh
>
> "Kevin Brooks" <kbrooks@sagetelecom.net> wrote in message
> news:OPszDvEYDHA.2204@TK2MSFTNGP12.phx.gbl...
> > Indexed views are only really good for aggregations and joining data
from
> > mutiple tables, is this what you need to do??? Several of our tables
are
> > over a million rows in our production environment and it all comes down
to
> > indexing. Did you create new indexes designed for queries or are they
the
> > existing indexes from the original tables?
> >
> >
> > "Shamim" <shamim.abdul@railamerica.com> wrote in message
> > news:#vojbnEYDHA.536@TK2MSFTNGP10.phx.gbl...
> > > SQL SERVER 2K
> > >
> > > We have 30 databases each for one business units.
> > > Now I am consolidating few tables from 30 databases to one database
for
> > > Analysis purpose.
> > >
> > > Now my tables in Central db have millions of records.
> > > I need some tips to handle this huge tables for query purpose (already
> > > applied with all Indexes).
> > >
> > > Can I go with INDEXED VIEWS ??
> > >
> > > Thx
> > > Sh
> > >
> > >
> >
> >
>
>

#5 (**permalink**) 02-27-2008, 05:08 PM

Thanks for the suggestions.

Sh

"Kevin Brooks" <kbrooks@sagetelecom.net> wrote in message
news:OitSnTFYDHA.2032@TK2MSFTNGP10.phx.gbl...
> Partition views can help here. You will be able to keep table sizes down
> and this can increase query times when done right. It sounds like you
moved
> multiple tables into one table. If this is the case you will need to
break
> them back out into smaller tables, add partitioning columns, modify
Primary
> Keys and build the view. That might be a hassle. I can query a 20million
> record table and get a response back in under a second, I just did a query
> on 250million and elasped time was 10sec(only one index on there, I know I
> could get quicker

). Did you change indexes for reporting or not?
>
>
> "Shamim" <shamim.abdul@railamerica.com> wrote in message
> news:#dGAN7EYDHA.2200@TK2MSFTNGP09.phx.gbl...
> > I am sorry, I meant PARTITIONED VIEWS
> >
> > Sh
> >
> > "Kevin Brooks" <kbrooks@sagetelecom.net> wrote in message
> > news:OPszDvEYDHA.2204@TK2MSFTNGP12.phx.gbl...
> > > Indexed views are only really good for aggregations and joining data
> from
> > > mutiple tables, is this what you need to do??? Several of our tables
> are
> > > over a million rows in our production environment and it all comes
down
> to
> > > indexing. Did you create new indexes designed for queries or are they
> the
> > > existing indexes from the original tables?
> > >
> > >
> > > "Shamim" <shamim.abdul@railamerica.com> wrote in message
> > > news:#vojbnEYDHA.536@TK2MSFTNGP10.phx.gbl...
> > > > SQL SERVER 2K
> > > >
> > > > We have 30 databases each for one business units.
> > > > Now I am consolidating few tables from 30 databases to one database
> for
> > > > Analysis purpose.
> > > >
> > > > Now my tables in Central db have millions of records.
> > > > I need some tips to handle this huge tables for query purpose
(already
> > > > applied with all Indexes).
> > > >
> > > > Can I go with INDEXED VIEWS ??
> > > >
> > > > Thx
> > > > Sh
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Central Database Design