Memory Problem doing INSERTs

I am having a problem with massive memory usage when running a series of
INSERT statement. This started in VB6 using ADO and Access, then I moved to
VB.NET using ADO, then to ADO.NET and then to SQL instead of Access. I have
scoured the search engines looking for a solution and can't seem to find one
if it's available.

Basically it appears that each time the routine runs a little more memory on
the workstation is chewed up. I can watch the pagefile usage grow in task
manager. This is about the 50th iteration I have tried. I have tried with
and without the opens and closes, I tried putting it in a transaction. The
workstation is XP Pro with all the patches.

Any ideas would be appreciated.

Public Sub Add(ByVal Number As Long, ByVal Group As Long, ByVal Subject As
String)
Dim intRecords As Integer

cnAdd = New SqlConnection(strConn)

cmdAdd = New SqlCommand
cmdAdd.Connection = cnAdd
cmdAdd.CommandText = "INSERT INTO ImageList values (" &
Number.ToString & ", " & Group.ToString & ", '" & Subject & "')"

Try
cnAdd.Open()
intRecords = cmdAdd.ExecuteNonQuery
cmdAdd.Dispose()
cnAdd.Close()
Catch ex As Exception
System.Diagnostics.Debug.WriteLine(ex.Message)
End Try

cnAdd.Dispose()
cnAdd = Nothing
End Sub

Series of inserts? Do you mean you have quite a bit of data to get into the
server and you are inserting one row at a time? If so, here are some
options:

1. Create an XML doc to insert and fire to a stored procedure that uses the
XML as a table and inserts into the proper table. One shot operation.
2. Create an updategram and fire at the database
3. Create a DataSet and load with the new values and fire Update() - this
still does each insert individuallly, but takes it out of your hands.
4. Use SQL bulk loading to load the objects into a temp table and then run a
routine to complete the inserts - this is esp. effective if you have minor
changes
5. Create a DTS package (SSIS in SQL 2005) that inserts the information.
This is most effective if you have massive shaping to the data for
inserting.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

*************************************************
Think outside of the box!
*************************************************
"DonS" <DonS@discussions.microsoft.com> wrote in message
news:0466319B-6C1A-4DB6-BDBB-CEF549C184D1@microsoft.com...
>I am having a problem with massive memory usage when running a series of
> INSERT statement. This started in VB6 using ADO and Access, then I moved
> to
> VB.NET using ADO, then to ADO.NET and then to SQL instead of Access. I
> have
> scoured the search engines looking for a solution and can't seem to find
> one
> if it's available.
>
> Basically it appears that each time the routine runs a little more memory
> on
> the workstation is chewed up. I can watch the pagefile usage grow in task
> manager. This is about the 50th iteration I have tried. I have tried
> with
> and without the opens and closes, I tried putting it in a transaction.
> The
> workstation is XP Pro with all the patches.
>
> Any ideas would be appreciated.
>
> Public Sub Add(ByVal Number As Long, ByVal Group As Long, ByVal Subject As
> String)
> Dim intRecords As Integer
>
> cnAdd = New SqlConnection(strConn)
>
> cmdAdd = New SqlCommand
> cmdAdd.Connection = cnAdd
> cmdAdd.CommandText = "INSERT INTO ImageList values (" &
> Number.ToString & ", " & Group.ToString & ", '" & Subject & "')"
>
> Try
> cnAdd.Open()
> intRecords = cmdAdd.ExecuteNonQuery
> cmdAdd.Dispose()
> cnAdd.Close()
> Catch ex As Exception
> System.Diagnostics.Debug.WriteLine(ex.Message)
> End Try
>
> cnAdd.Dispose()
> cnAdd = Nothing
> End Sub

Very interesting idea's... Thank you,

I had actually tried the XML way, but building the XML doc in memory was
taking too much memory. I could build it on disk I guess and then load it.

I am not sure what an Updategram is, but I'll do some research on that and
using Update as a bulk method.

One problem is that I am using the information at the same time it is
loading so I was hoping for more immediate availability of each line as it's
loaded.

I just can't figure out why Microsoft isn't releasing the memory (even if I
include the "dispose()" command. I have found similar problems posted in
various forums, but the answer always seems to be to end run the issue
instead of why the memory problem exists in the first place. Maybe I just
have to resign myself to doing the same.

Thanks for the hints.