Hi
I have a for each loop which steps through an ado recordset (approx. 5,000 rows), this passes two variables to an SQL statement which populates a second recordset (normally 8 to 10 rows). I use the second recordset in a dataflow task which was a simple Script which returns approximately 30 rows for inclusion in my destination table. The package runs for a while OK, although the loop appears to execute slowly, then I get the below message constantly repeated in the debug window.
[DTS.Pipeline] Information: The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 174 buffers were considered and 174 were locked. Either not enough memory is available to the pipeline because not enough is installed, other processes are using it, or too many buffers are locked.
I have 2gb of virtual memory on my machine, and the recordsets are relatively small. Have I missed a seting some where?My guess is your problem is where you're creating 5,000 8-10 row recordsets. Instead of creating those recordsets from a SQL statement and consuming them in a Data Flow script, can you not execute the SQL statement in an OLE DB Source in your Data Flow?
Maybe you simplified your scenario for the sake of explanation, but if you can do the above, I wonder if you can't find a way to move that 5,000 iteration loop into the same SQL Statement as a set-based operation.
|||
Thanks for the reply, the 5000 row record set has two fields one integer the other nchar(5). The second recordset gets re-used in the loop so this will only have 10 rows max at a time and has 4 fields. Unfortunately I cannot change the Loop to SQL, I do not have any knowledge of set based operations, is this similar to a loop?
Regards
ADG
|||What SP level are you running? I looks like there was some memory related issues in pre SP versions of the products.|||How is the second recordset being reused? Do you mean that you are repopulated the same variable with a new recordset?
Also, are you explicitly closing the recrodset you are using?
|||Thanks for the reply.
Inside my Foreach loop container I have an Execute SQL task which uses the two variables from the Recordset being scrolled through, and populates my second recordset. Do I need to explicitly close the recordset inside the loop? If so how should the recordset be closed, is this a Script task?
|||
ADG wrote:
Thanks for the reply.
Inside my Foreach loop container I have an Execute SQL task which uses the two variables from the Recordset being scrolled through, and populates my second recordset. Do I need to explicitly close the recordset inside the loop? If so how should the recordset be closed, is this a Script task?
The outer recordset (A) probably isn't the issue - it's the second one (B) that I am talking about. You are using the second one in a data flow, right? Are you using a source script component to read recordset B? If so, call the recordset.Close() method (if you are using ADODB) or the DataTable.Dispose() method if you are using ADO.NET) after the loop to output the rows completes.
|||Many thanks,
I will change the script component and give it a try. I am new to SQL Server / SSIS so it looks like II have made another beginners error.
|||I have tried to close the recordset but the close method does not appear to be valid in the main code,( I tried Me.Variables.MyRecordSet2.close). Do I need to override one of the Class methods? The code I used is modified from one of Jamie Thomsons blogs and is as below:
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Public Overrides Sub CreateNewOutputRows()
'
Dim olead As New Data.OleDb.OleDbDataAdapter'Define an ADO.Net data adapter
Dim dt As New Data.DataTable'Define an ADO.Net DataTable
Dim row As System.Data.DataRow'Define an ADO.Net DataRow
Dim lastSI, SI As Int32
Dim strU(50) As String
Dim strU2(50) As String
Dim dblFactor(50) As Double
Dim x, y, j, k As Int32
Dim isFirst As Boolean
Dim strLComb, strComb As String
olead.Fill(dt, Me.Variables.MyRecordSet2)'Populate our DataTable from the adapter
lastSI = 0
strLComb = " "
SI = 0
x = 0
isFirst = True
For Each row In dt.Rows'Iterate over the rows in the table
strComb = row("SI").ToString & row("U").ToString
If strComb <> strLComb Then
If isFirst Then
isFirst = False
Else
For y = 1 To (x - 1)
For k = 2 To x
With OutputBuffer
.AddRow()
.Calculated = True
.Factor = dblFactor(k) / dblFactor(y)
.SI = SI
.U = strU2(y)
.U2 = strU2(k)
End With
Next
Next
End If
SI = CType(row("SI"), Integer)
strLComb = row("SI").ToString & row("U").ToString
x = 1
strU(x) = row("U").ToString
strU2(x) = row("U2").ToString
dblFactor(x) = CType(row("Factor"), Double)
Else
x = x + 1
strU(x) = row("U").ToString
strU2(x) = row("U2").ToString
dblFactor(x) = CType(row("Factor"), Double)
End If
Next
For y = 1 To (x - 1)
For k = 2 To x
With OutputBuffer
.AddRow()
.Calculated = True
.Factor = dblFactor(k) / dblFactor(y)
.SI = SI
.U = strU2(y)
.U2 = strU2(k)
End With
Next
Next
dt.Dispose()
End Sub
End Class
|||Thanks for showing us your code. I don't see why you need the recordsets at all. It looks like you could just as easily execute the query that creates the recordset in an OLE DB Source component and use this code in a custom transformation component. Instead of the rows coming into this code via Recordset, they come in via the component's input. This is what I suggested on 4/2.
|||Since you are using ADO.NET, you need to call the Dispose() method. However, Jay is correct in saying that the operation you are trying to perform would be much more efficient if it was not done in a script component.|||
Thanks again,
Can you point me in the right direction with the custom transformation. I have created the OLE DB which will return between 2 and 10 rows, I need to store this in an array to process. I assume that each row will pass into the script via Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) . How do I catch all the rows then perform the output?
|||
Thanks again,
Can you point me in the right direction with the custom transformation. I have created the OLE DB which will return between 2 and 10 rows, I need to store this in an array to process. I assume that each row will pass into the script via Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) . How do I catch all the rows then perform the output?
Take a look at this topic in books online: "Creating an Asynchronous Transformation with the Script Component"
Since your are not simply passing each row through, you'll need to use an asynchronous transform.
|||If you have your rows coming from an OLE DB Source, then you would change your script source to an asynchronous script transformation and do your processing on the incoming rows instead of shredding the recordset. Biggest difference is that you no longer have a FOR loop to iterate the rows. Instead ProcessInputRows gets called multiple times. Therefore some of your variables needed to be made global. Also, the rows you output after seeing all the input rows had to be moved to another method that gets called when the input rows are finished. I rearranged your code to demonstrate. Hopefully I didn't change the logic any.
Code Snippet
Dim lastSI As Int32 = 0
Dim SI As Int32 = 0
Dim strU(50) As String
Dim strU2(50) As String
Dim dblFactor(50) As Double
Dim x As Int32 = 0
Dim isFirst As Boolean = True
Dim strLComb As String = " "
Public Overrides Sub FinishOutputs()
Dim y, k As Int32
For y = 1 To (x - 1)
For k = 2 To x
With OutputBuffer
.AddRow()
.Calculated = True
.Factor = dblFactor(k) / dblFactor(y)
.SI = SI
.U = strU2(y)
.U2 = strU2(k)
End With
Next
Next
End Sub
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
'
' Add your code here
'
Dim strComb As String
Dim y, k As Int32
strComb = Row.SI & Row.U
If strComb <> strLComb Then
If isFirst Then
isFirst = False
Else
For y = 1 To (x - 1)
For k = 2 To x
With OutputBuffer
.AddRow()
.Calculated = True
.Factor = dblFactor(k) / dblFactor(y)
.SI = SI
.U = strU2(y)
.U2 = strU2(k)
End With
Next
Next
End If
SI = CType(Row.SI, Integer)
strLComb = Row.SI & Row.U
x = 1
strU(x) = Row.U
strU2(x) = Row.U2
dblFactor(x) = CType(Row.Factor, Double)
Else
x = x + 1
strU(x) = Row.U
strU2(x) = Row.U2
dblFactor(x) = CType(Row.Factor, Double)
End If
End Sub
|||
Many thanks for the post. I have incorporated some of the above into my final package
However, I have gone back to using a for each loop to generate a OLE DB source, which feeds an asynchronous script transformation . The above works but does not create all the records that I needed. The process of adding rows to the table in turn creates more possible row combinations. My memory issue was eventually resolved by installing SP2. My package now works, but is slow. Below is an exapmle of my data problem ( data for two SKU's as in the original source, and as in my final table )
Becomes
The project takes about 30 minutes to run with 21,500 input rows and has 135,900 final rows. I guess if this process could be done with SQL it would be far more efficient, but I cannot see how this can be done when each row added can gerate more possible combinations. If anyone out there can see away of performing the above transformation with pure SQL I would be grateful for your ideas.
No comments:
Post a Comment