Joh wrote:
> Create View MedicalHistory
> as
> select * from MedicalHisotory_2001
> Union All
> select * from MedicalHisotory_2002
> Union All
> select * from MedicalHisotory_2003
> Union All
> select * from MedicalHisotory_2004
> Union All
> select * from MedicalHisotory_2005
>
> MedicalHisotory is also joining to another tables so that might cause
> slow down so can I create some indexing
> on the view ? please guide me.
>

"Joh" <joh@mailcity.com> wrote in message
news:eXRQdWdSFHA.748@TK2MSFTNGP10.phx.gbl...
> There was a MedicalHistory table in which contain 10 Billions of records
so
> I did partioning into five tables and
> insert the data according to year. Now the problem is that when I run the
> stored procedure from the table(Medical History) it took
> 5 seconds and when I run the stored procedure through the view which I
have
> created as follow it tooks 47 seconds.
>
> Can any one let me know after creating the following view what I have to
do
> for speed up the retrival.
>
>
> Create View MedicalHistory
> as
> select * from MedicalHisotory_2001
> Union All
> select * from MedicalHisotory_2002
> Union All
> select * from MedicalHisotory_2003
> Union All
> select * from MedicalHisotory_2004
> Union All
> select * from MedicalHisotory_2005
>
> MedicalHisotory is also joining to another tables so that might cause slow
> down so can I create some indexing
> on the view ? please guide me.
>
> thanks
>
>

"David Gugick" <davidg-nospam@imceda.com> wrote in message
news:e9Ug9hdSFHA.3156@TK2MSFTNGP15.phx.gbl...
> Joh wrote:
> > Create View MedicalHistory
> > as
> > select * from MedicalHisotory_2001
> > Union All
> > select * from MedicalHisotory_2002
> > Union All
> > select * from MedicalHisotory_2003
> > Union All
> > select * from MedicalHisotory_2004
> > Union All
> > select * from MedicalHisotory_2005
> >
> > MedicalHisotory is also joining to another tables so that might cause
> > slow down so can I create some indexing
> > on the view ? please guide me.
> >
>
> Do you you have the necessary indexes to perform the join operation? If
> not, you'll need to add it/them. Also, try and stay away from SELECT *
> syntax. Spell out the columns in the view. Personally, I would leave off
> the join and create another view on this view with the join in case you
> don't need access to the columns in the other table.
>
> If you need mroe help, post your DDL for the tables.
>
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>