Re: performance of Insert with Partitioned View

From: BP Margolin (bpmargo@attglobal.net)
Date: 04/24/03 

From: "BP Margolin" <bpmargo@attglobal.net>
Date: Thu, 24 Apr 2003 10:48:07 -0400

OK, now that we know that you have a correctly defined partitioned view, could you provide details as to why you think performance is bad. What exactly does that mean ... is it that you want SQL Server to do the INSERT operation in 5 seconds and you are not getting that level of performance, or is it taking 36 hours to complete the operation?

As a first guess, if you have clustered indexes on the individual tables, does adding an ORDER BY clause to the SELECT * FROM Temp so that the data is being added in the sequence of the clustered indexes improve performance?

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.

"TNL" <le.nhan@freenet.de> wrote in message news:eA484ehCDHA.1156@TK2MSFTNGP12.phx.gbl...
> OK, my Partitioned View ist correct, with "UNION ALL", I wrot only
> incorrect.
> What is with the performance?
>
>
> "BP Margolin" <bpmargo@attglobal.net> schrieb im Newsbeitrag
> news:eh0EpEhCDHA.3040@TK2MSFTNGP11.phx.gbl...
> TNL,
>
> As documented in the SQL Server Books Online, partitioned views require
> UNION ALL's. Using just UNION does not create a partitioned view.
>
> -------------------------------------------
> BP Margolin
> Please reply only to the newsgroups.
> When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
> can be cut and pasted into Query Analyzer is appreciated.
>
> "TNL" <le.nhan@freenet.de> wrote in message
> news:ed#AssgCDHA.33548@TK2MSFTNGP10.phx.gbl...
> > I am trying to insert new Records in different Database on different
> Server
> > with a Partitioned View, but the perfomance is very bad.
> >
> > The partitioned View: MyPVIEW
> > SELECT * FROM server1.myDB.myTable
> > UNION
> > SELECT * FROM server2.myDB.myTable
> > UNION
> > SELECT * FROM server3.myDB.myTable
> >
> > The Job:
> > Insert into MyPVIEW
> > SELECT * FROM Temp
> >
> > Temp is a Table on the local server, it hat about 300.000 records.
> >
> > How can I improve the performance?
> >
> >
>
>

Relevant Pages

  • Union of two tables in different databases from within a VB6 application
    ... Since the tables are in different databases ... ... The Union in Query analyzer looks like this: ... Select PartNum, Manu, Available from YYYY2.dbo.PART2 where PartNum like ...
    (microsoft.public.sqlserver.programming)
  • Re: performance of Insert with Partitioned View
    ... OK, my Partitioned View ist correct, with "UNION ALL", I wrot only ... As documented in the SQL Server Books Online, ... > The partitioned View: MyPVIEW ... > Temp is a Table on the local server, ...
    (microsoft.public.sqlserver.security)
  • Re: Simple merging question...
    ... > UNION ... how do I merge two sql tables using query analyzer? ... >> This seems like it'd be so simple, yet I keep generating errors. ... >> What command or function should I be looking for? ...
    (microsoft.public.sqlserver.programming)
  • Re: ordering an int column
    ... Run it in Query Analyzer ... -- not ordered as int ... union all ... Prev by Date: ...
    (comp.databases.ms-sqlserver)
  • Error in SQL Statement
    ... I am using MS Query Analyzer 8.0. ... SELECT qti.CUSTNMBR,sumas amnt ... union all ... Prev by Date: ...
    (microsoft.public.data.ado)