|
|
You're in the
sql server data warehouse
group:SQL Server memory ballooning during mass import
sql server data warehouse:
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
[quoted text, click to view]
"Mark Rae" <mark@mark-N-O-S-P-A-M-rae.co.uk> wrote in message news:eF$F65qFFHA.2144@TK2MSFTNGP09.phx.gbl... > > 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... -- Adam Machanic SQL Server MVP http://www.sqljunkies.com/weblog/amachanic --
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. [quoted text, click to view] "Mark Rae" <mark@mark-N-O-S-P-A-M-rae.co.uk> wrote in message news:eF$F65qFFHA.2144@TK2MSFTNGP09.phx.gbl... > 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 > >
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 [quoted text, click to view] "Mark Rae" <mark@mark-N-O-S-P-A-M-rae.co.uk> wrote in message news:eF$F65qFFHA.2144@TK2MSFTNGP09.phx.gbl... > 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 > >
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |