Iniciar sesión
 
InicioBibliotecaAprendizajeDescargaSoporteComunidadForos
Recursos para Profesionales de TI >
> 
> 
Linked Server Alternatives

Answered Linked Server Alternatives

  • martes, 05 de julio de 2011 16:56
     
      Tiene código
    Inicie sesión para votar
    0

    I inherited a set of stored procedures that populate a data mart in SQL Server 2005.  The linked server goes to an Oracle database.  The stored procedures are now taking up too many resources.  One stored procedure alone is pulling a little of 92 million records.  It is time to contemplate an alternative. 

    I've been comparing a BulkCopy vs an SSIS package.  Does anyone have any experience regarding the performance comparisons?

    Just to add, the stored procedures truncate a table, disables indexes, uses an OPENQUERY to insert the records, then rebuilds indexes:

    EXEC('TRUNCATE TABLE ' + @BASE_OBJECT);	

EXEC('ALTER INDEX [IDXREF1] ON ' + @BASE_OBJECT + ' DISABLE');

INSERT INTO REZSVC
	SELECT * FROM OPENQUERY(OC1, 
	'SELECT FIELD1,
	FIELD2,
	FIELD3,
	FIELD4,
	FIELD5,
	FIELD6
	FROM OC1.REZSVC')

EXEC('ALTER INDEX [IDXREF1] ON ' + @BASE_OBJECT + ' REBUILD');

     

    Please Vote &/or "Mark As Answer" if this post is helpful to you. Thanks and happy coding :D
     

Todas las respuestas

  • sábado, 09 de julio de 2011 9:24
     
     Respondida
    Inicie sesión para votar
    0

    The speed of inserting data in SQL Server is wholly dependent on how many writes occur to the transaction log. These writes occur in two different modes, Minimal logging and Full logging. Minimal logging directly to the data page then writes only a pointer to the datapage in the transaction log, while Full logging writes the content of all the rows to the transaction log prior to inserting them into the data page. 

    SSIS also import data using bulk import mechanism but it would be make difference when you change  recovery model to simple(minimum logging).

     

    hope this helps you

    http://uk.linkedin.com/in/ramjaddu
    • Marcado como respuesta polymorphic martes, 15 de noviembre de 2011 18:06
    •  
     
  • miércoles, 23 de mayo de 2012 21:47
     
     
    Inicie sesión para votar
    0

    Dude,

    This is absolute non-sense: "Minimal logging directly to the data page then writes only a pointer to the datapage in the transaction log"

    Refer to this article by Paul Randal to understand how logging works in SQL Server:

    http://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx

     
  • martes, 29 de mayo de 2012 15:31
     
     
    Inicie sesión para votar
    0

    A bigger concern is probably the integrity of the data.

    SSIS will probably use different drivers and I've seen this cause issues - SSIS usually gets it more correct than a linked server though.

    Also consider extracting from Oracle as a separate task to the insert into sql server. You could save as a raw file in ssis. Might be best though to extract on the Oracle server to a file copy to the sql server then bulk insert or ssis in - that means that you don't need the two simultaneous connections - and if sql server is delayed the file will already be there waiting - and you can process multiple files if you get behind (if this is an incremental load).