Re: performance of Insert with Partitioned View

From: Andrew J. Kelly (ajkellynospam@attbi.com)
Date: 04/24/03 

From: "Andrew J. Kelly" <ajkellynospam@attbi.com>
Date: Thu, 24 Apr 2003 08:09:41 -0400

I don't know if it is directly related to the DPV or not but inserting 300K
rows at a time can be pretty slow if the hardware is not configured
properly. Try inserting them in smaller batches of say 10K at a time. 

-- 
Andrew J. Kelly
SQL Server MVP
"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

  • Re: performance of Insert with Partitioned View
    ... As documented in the SQL Server Books Online, partitioned views require UNION ALL's. ... > The partitioned View: MyPVIEW ... > Temp is a Table on the local server, ...
    (microsoft.public.sqlserver.security)
  • 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: faster way to write tables?
    ... What I've found to be the fastest way of inserting hundreds/thousands of records into a table which might already contain millions of rows, using ADO in Access/SQLserver is to use RecordSets and insert via a temporary table ... Create a new temp table which exactly matches the structure of the target table... ... For inserting millions of rows (into an Access database from SQLserver) I still haven't found anything which performs as well as... ... According to a random database creation log that I'm looking at, Access can insert 3 million rows in just over 70 seconds ...
    (borland.public.delphi.database.ado)
  • Re: Row Order
    ... but that doesn't guarantee they will be located in the ... IDENTITY property, then can I assume that the IDENTITY property will ... Remember this is a TEMP table I'm ... >> I'm inserting rows into a temp table via a SELECT statement that has an ...
    (microsoft.public.sqlserver.programming)
  • Help with a list variable
    ... then looping through the list and inserting the values into ... the temp table, then selecting all the records from teh temp table. ... DECLARE @startingPosition int ...
    (comp.databases.ms-sqlserver)