CODEWITHSUNDEEP
vb.net
Nyk
Nyk

Reputation: 53

How to stream data from dataReader to SQL using sqlbulkcopy?

I have a requirement to create a desktop application to copy data from an oracle database to a SQL server database (the client explicitly wants this - ETL software, DTS, etc. are all out - it has to be the desktop *.exe)

As the data runs into the millions (often tens or hundreds of millions) of rows, I cannot use sqlbulkcopy(dataTable) as the size of the dataTable very quickly uses all available memory.

I'm using sqlbulkCopy(dataReader) but it is still consuming local memory.

How can I STREAM data from the reader to the sql table, without using massive amounts of local memory?

(skipping the try-catches, etc, just core code):

Dim dr As OleDb.OleDbDataReader
Dim connString As String = "Provider=MSDAORA.1;User ID=X;password=X;Data Source=X;Persist Security Info=False"
Dim cnn As OleDb.OleDbConnection = New OleDb.OleDbConnection(connString)
Dim cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand("SELECT * FROM Table", cnn)

cnn.open
dr = cmd.ExecuteReader

Dim sqlCnn As SqlClient.SqlConnection = New SqlClient.SqlConnection("Server=X;Database=X;Trusted_Connection=True;Connection Timeout=60")
sqlCnn.Open()
Dim bk As SqlClient.SqlBulkCopy = New SqlClient.SqlBulkCopy(sqlCnn)
bk.DestinationTableName = "TABLE_NAME"
bk.EnableStreaming = True
bk.BatchSize = 100
bk.BulkCopyTimeout = 0

bk.WriteToServer(dr)

Throws out of memory exception on local machine, regardless of batch size. Page file usage rises before dying. Expected the datareader to stream to sql server, hence not use much local memory

Upvotes: 1

Views: 2468

Answers (1)

Nyk
Nyk

Reputation: 53

Thanks to Jeroen for the idea to use Oracle Native Providers, rather than OLE.

"Official Oracle ODP.NET Managed Driver"

The data now streams quickly (~10,000 rows per second) and doesn't overly consume local memory, exactly what I was looking for.

New code (missing out try-catch, etc, just the code):

'Oracle connection using ODP.Net Managed Driver
Dim factory As Common.DbProviderFactory = Common.DbProviderFactories.GetFactory("Oracle.DataAccess.Client")
Dim dbconn As Common.DbConnection = factory.CreateConnection()
Dim connString As String = "User ID=X;password=X;Data Source=X;"

dbconn.ConnectionString = connString
dbconn.Open()

'Retrieval of data from oracle
Dim dbcommand = factory.CreateCommand
dbcommand.Connection = dbconn
dbcommand.CommandText = "SELECT * FROM Table"

Dim dr As Common.DbDataReader
dr = dbcommand.ExecuteReader

'Stream to SQL
Dim sqlCnn As SqlClient.SqlConnection = New 
SqlClient.SqlConnection("Server=X;Database=X;Trusted_Connection=True;Connection Timeout=60")
sqlCnn.Open()
Dim bk As SqlClient.SqlBulkCopy = New SqlClient.SqlBulkCopy(sqlCnn)
bk.DestinationTableName = "TABLE_NAME"
bk.EnableStreaming = True
bk.BatchSize = 5000
bk.BulkCopyTimeout = 0

bk.WriteToServer(dr)

Upvotes: 2

Related Questions