best practice for loading large files into SQL Server database

=?Utf-8?B?UGhpbCBKb2huc29u?=

Hello,

I am working on a peice of code that takes a file and loads it into a SQL
Server DB.

When I load large files (over 70Mb) I get a system out of memory exception.
My machine has 2G RAM

I think the problem is that a file stream pointing to the file on the file
system is loaded into a memory stream one byte at a time. Does anybody have
any pointers on doing this type of thing and how to do this so that the file
is actually streamed from the file system right into the SQL Server DB?

Any advice or links to articles on how to do this best etc would be really
appreciated.

Thanks in advance for any help

--
Regards,

Phil Johnson (MCAD)

Nov 23 '07 #1

Subscribe Reply

6668

Marc Gravell

Well, what is the file and what code are you using to look at it?

To insert lots of rows (i.e. a huge CSV) you should be using
SqlBulkCopy with a streaming IDataReader; I recommend the one here:
http://www.codeproject.com/cs/database/CsvReader.asp

To insert a single huge BLOB, you must use chunking techniques - i.e.
you need to allocate a buffer (8040 bytes is optimal for SQL Server, I
believe) and read buffer-fulls of info, and pass this down to the db
in chunks, using the appropriate BLOB SQL commands [the commands
change between SQL 2000 with "image", and SQL 2005 with
"varbinary(max) "].

If you try and load the entire BLOB into memory (as a huge byte[]),
yes it will fail.

If you try and load the entire file into memory (as a DOM, DataTable,
etc), yes it will fail.

Marc

Nov 23 '07 #2

Marc Gravell

I found some of my old BLOB examples...

stream=>databas e
http://groups.google.co.uk/group/mic...4e7e3782e59a93

database=>strea m
http://groups.google.co.uk/group/mic...d173f1db2951f1

Marc

Nov 23 '07 #3

=?Utf-8?B?UGhpbCBKb2huc29u?=

Thanks Marc,

It is indeed a BLOB that I am loading, not a csv.

Those links look ideal.

Thanks again.
--
Regards,

Phil Johnson (MCAD)
"Marc Gravell" wrote:

I found some of my old BLOB examples...

stream=>databas e
http://groups.google.co.uk/group/mic...4e7e3782e59a93

database=>strea m
http://groups.google.co.uk/group/mic...d173f1db2951f1

Marc

Nov 23 '07 #4

Marc Gravell

No problem. If you need fewer round trips when saving, I believe that
any multiple of 8040 will work; likewise, if you are using SQL Server
2005 you should definitely use the varbinary(max) and associated
commands (instead of UPDATETEXT) - but the "read" stays the same.

For info, SQL Server 2008 has a new file-stream data type / attribute
(or something!). I haven't played with it yet, but that may also be
useful for file-based scenarios. YMMV.

Marc

Nov 23 '07 #5

Similar topics

4951

Best way to install a database at a remote site

by: great_googley_moogley | last post by:

Greetings, I am in the process of installing a SQL database at a customer location. I have determined that there are 3 ways to do this, and I wanted to know which is the best of the 3. 1 Install From Script. In this method I create the database and its objects in scripts that are run via osql utility on the SQL server machine. For loading any initial data that I need in the database I also run bcp commands.

Microsoft SQL Server

9273

Object test = new Object() <-- Java, best way in C++

by: DrUg13 | last post by:

In java, this seems so easy. You need a new object Object test = new Object() gives me exactly what I want. could someone please help me understand the different ways to do the same thing in C++. I find my self sometimes, trying Object app = Object(); Object *app = Object(); Object app = new Object();

C / C++

136

9460

Javascript Best Practices Document v1.0

by: Matt Kruse | last post by:

http://www.JavascriptToolbox.com/bestpractices/ I started writing this up as a guide for some people who were looking for general tips on how to do things the 'right way' with Javascript. Their code was littered with document.all and eval, for example, and I wanted to create a practical list of best practices that they could easily put to use. The above URL is version 1.0 (draft) that resulted. IMO, it is not a replacement for the FAQ,...

Javascript

3238

Menu ascx - use database? Best practice?

by: Sandy | last post by:

Hello - I have a book that illustrates pulling menu items from a Sql Server table into an ascx via a stored procedure. Is this something that is done in the real world? I do like the effect when viewing it in the source code, however, because it doesn't show the complete path to the file. (I'm talking about Internet Explorer click View, click source code.)

ASP.NET

793

Bytea/Large Objects/external files best practices

by: David Helgason | last post by:

I think those best practices threads are a treat to follow (might even consider archiving some of them in a sort of best-practices faq), so here's one more. In coding an game asset server I want to keep a large number of file revisions of varying sizes (1Kb-50Mb) inside the database. Naturally I want to avoid having to allocate whole buffers of 50Mb too often.

PostgreSQL Database

2631

Best practices for migrating a development database to a releasedatabase

by: Collin Peters | last post by:

I have searched the Internet... but haven't found much relating to this. I am wondering on what the best practices are for migrating a developmemnt database to a release database. Here is the simplest example of my situation (real world would be more complex). Say you have two versions of your application. A release version and a development version. After a month of developing you are ready to release a new version. There have...

PostgreSQL Database

5916

what is best practice to populate large combobox?

by: Rich | last post by:

Hello, I have a search application to search data in tables in a database (3 sql server tables). I populate 2 comboboxes with with data from each table. One combobox will contain unique CompanyID's. The second combobox will contain unique memberID's. Each of the tables that I have to search contain a CompanyID and a memberID field, and these fields are not unique in the respective tables. Like CompanyID, MemberID

Visual Basic .NET

2820

FOR ALL - Best Practices for navigating within code? -plus - Add-In's that help?

by: Rex | last post by:

Hi All - I have a question that I think MIGHT be of interest to a number of us developers. I am somewhat new to VIsual Studio 2005 but not new to VB. I am looking for ideas about quick and efficient navigating within Visual Studio 2005. Let's say your project (or solution) has dozens of forms and hundreds or even thousands of routines. Two Questions: 1) BUILT-IN to Visual Studio 2005. What ideas do you have to quickly

Visual Basic .NET

10829

Best practices for moving large amounts of data using WCF ...

by: =?Utf-8?B?TW9iaWxlTWFu?= | last post by:

Hello everyone: I am looking for everyone's thoughts on moving large amounts (actually, not very large, but large enough that I'm throwing exceptions using the default configurations). We're doing a proof-of-concept on WCF whereby we have a Windows form client and a Server. Our server is a middle-tier that interfaces with our SQL 05 database server.

.NET Framework

9673

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...

General

9522

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...

Windows Server

10217

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...

Online Marketing

10167

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...

Windows Server

9046

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...

Career Advice

7544

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...

Microsoft Access / VBA

6784

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...

C# / C Sharp

3730

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP

2922

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General