CODEWITHSUNDEEP
sql-serveropenrowset
Paul
Paul

Reputation: 26680

Read large binary file in chunks in Microsoft SQL Server

Is there any functionality that allows reading binary file in chunks?

Currenty I am using the following query to read binary files:

SELECT
  image_data.BulkColumn
FROM
  OPENROWSET(BULK 'C:\bigfile', SINGLE_BLOB) as image_data

When size of the file is about 3 GB, the query takes about 50 seconds to execute.

When I am trying to extract a chunk from varbinary:

SELECT
  SUBSTRING(image_data.BulkColumn, 1, 15)
FROM
  OPENROWSET(BULK 'C:\bigfile', SINGLE_BLOB) as image_data

it takes about 28 seconds, no matter what the value of substring-offset is.

Are there any other ways to make the file-to-sql gateway in a more effective way? I don't want to store large binary data in the database.

Upvotes: 0

Views: 70

Answers (0)

Related Questions