SQL Server memory ballooning during mass import

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mark Rae - 19 Feb 2005 18:11 GMT

Hi,

Apologies if this is not the correct newsgroup - I've also posted this
message to the microsoft.public.dotnet.framework.adonet newsgroup...

As part of a datawarehoue application, I have a Windows service which
iterates through a mySQL DataReader (using the CoreLab native .NET provider)
and pumps the records into SQL Server 2000. There can be upwards of 300,000
rows in the mySQL DataReader. Each record in the mySQL DataReader may
represent a new record in the SQL Server database or an update of an
existing record.

The service runs quite happily consuming just under 30Mb RAM, though it does
use a fair bit of CPU, which is not surprising.

The problem (if it even is a problem...) is that the sqlserver.exe process
is ballooning out of all proportion during the part of the process where it
does the 300,000 or so database writes, almost like its caching them, or
holding them in a transaction before committing them...

However, doing a SELECT COUNT(*) on the table that the records are being
pumped into clearly shows that they're going in one by one.

Can anyone see anything glaringly obvious that I've missed from the
following code which might cause this...?

While objMySQLDR.Read ' mySQLDataReader
objSQLDS = New DataSet() ' SQL Server DataSet
objSQLDA = New SqlDataAdapter("SELECT * FROM tbl_av_content_download
WHERE ttmms_acct_id = " & pintTTMMSAcctID.ToString & "
AND tbl_cms_download_download_id = " & objMySQLDR(1).ToString,
objSQLConnection)
objSQLCommandBuilder = New SqlCommandBuilder(objSQLDA)
objSQLDA.Fill(objSQLDS, "tbl_av_content_download")
If objSQLDS.Tables(0).Rows.Count = 0 Then ' adding new row to SQL
Server
objImportRow = objSQLDS.Tables(0).NewRow ' create a blank row
For intRow As Integer = 0 to objMySQLDR.FieldCount - 1
objImportRow.Item(intRow) = objMySQLDR(intRow) ' sync the fields
Next
objSQLDS.Tables(0).Rows.Add(objImportRow) ' add the new Row to the
DataSet
Else ' updating existing row in SQL Server
objImportRow = objSQLDS.Tables(0).Rows(0) ' use the existing row
objImportRow.BeginEdit ' set it into Edit mode
For intRow As Integer = 0 to objMySQLDR.FieldCount - 1
objImportRow.Item(intRow) = objMySQLDR(intRow) ' sync the fields
Next
objImportRow.EndEdit ' set it out of Edit mode
End If
objSQLDA.Update(objSQLDS.Tables("tbl_av_content_download")) ' update the
Row object
objImportRow.AcceptChanges ' write the Row back to SQL Server
objSQLCommandBuilder.Dispose
objSQLDA.Dispose
objSQLDS.Dispose
objImportRow = Nothing
objMySQLDS.Clear
objMySQLDS.Dispose
End while
objMySQLDR.Close

Any assistance gratefully received.

Mark Rae

Reply to this Message

Adam Machanic - 21 Feb 2005 18:12 GMT

> The problem (if it even is a problem...) is that the sqlserver.exe process
> is ballooning out of all proportion during the part of the process where it
> does the 300,000 or so database writes, almost like its caching them, or
> holding them in a transaction before committing them...

Mark,

It is caching them. That's the way SQL Server works -- every data page read
from or being written to is loaded into memory and then aged out. This is
an optimization, as in many cases it will be common that a page recently
written to might be read from again sooner than a page that has not been
recently written to (from a use case perspective, I think you'd find that in
most apps it's more common to request recent data than older data).

I wouldn't call this a problem; but if you feel that SQL Server is using too
much memory you can configure it to use less using the sp_configure 'max
server memory' setting. You can look up syntax in Books Online for that...

Signature

Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--

Reply to this Message

JohnnyAppleseed - 21 Feb 2005 21:04 GMT

Rather than inserting from your DataReader back into SQL Server:

1. Perform the entire ETL using DTS
or
2. Export the contents of your DataReader to a tab delimited text file
and then bulk copy the file into SQL Server.

Also, if this is an occasional maintenance task, then schedule off hours and
temporarily configure the database for single user / dbo use only. This will
minimize page locking.

> Hi,
>
[quoted text clipped - 61 lines]
>
> Mark Rae

Reply to this Message

Vladimir Chtepa - 22 Feb 2005 23:10 GMT

Hi Mark,

Your coud seems pretty expensive.
I would reccomend your to use sqlCommand or that contains script for insert
or update.

for sample:

update YourTabe
....
where ....
if @@rowcount
insert into YourTable values(...

so you could achieve considerably better performance.
Using of prepared statment and parameters brings your yet more performance

Thanks,
Vladimir Chtepa

> Hi,
>
[quoted text clipped - 61 lines]
>
> Mark Rae

Reply to this Message