lali_murray
asked on
VB.net sqlCmd.ExecuteReader is very slow
Hi!
I have an SSIS Script transformation where I use an SqlDataReader to perform a Select statement for every row I have in my package. In Sql server profiler, I can see the statement translate to an Exec sp_executesql command with parameters. The execution of this statement is very slow (200 ms) compared to the direct execution of the Select statement (20 ms). Since I have many records, the performance is catastrophic. Is there a better way to do this?
I have an SSIS Script transformation where I use an SqlDataReader to perform a Select statement for every row I have in my package. In Sql server profiler, I can see the statement translate to an Exec sp_executesql command with parameters. The execution of this statement is very slow (200 ms) compared to the direct execution of the Select statement (20 ms). Since I have many records, the performance is catastrophic. Is there a better way to do this?
Public Function UpdateFields(ByRef Table As String, ByRef Column As String, ByRef Condition As String, ByRef PkFieldArray As String(), ByRef PkFieldValueArray As String(), ByRef DerivedColumnsArray As Collections.ArrayList, ByRef SourceColumns_IsNull() As Boolean, ByRef SourceColumnsArray As Collections.ArrayList) As Boolean
Dim sqlCmd As New SqlClient.SqlCommand
Dim sqlConn As New SqlClient.SqlConnection(Variables.SQLConnectionString)
Dim SqlParam0 As New SqlClient.SqlParameter("@PkFieldValue0", SqlDbType.NVarChar)
Dim SqlParam1 As New SqlClient.SqlParameter("@PkFieldValue1", SqlDbType.Int)
Dim SqlParam2 As New SqlClient.SqlParameter("@PkFieldValue2", SqlDbType.NVarChar)
Dim SqlParam3 As New SqlClient.SqlParameter("@PkFieldValue3", SqlDbType.NVarChar)
Dim reader As SqlClient.SqlDataReader
Dim i As Integer = 0
sqlCmd.CommandText = ("SELECT" + Column + " FROM " + Table + Condition)
sqlCmd.Connection = sqlConn
sqlConn.Open()
sqlCmd.Parameters.Add(SqlParam0)
sqlCmd.Parameters("@PkFieldValue0").Value = PkFieldValueArray.GetValue(0)
sqlCmd.Parameters.Add(SqlParam1)
sqlCmd.Parameters("@PkFieldValue1").Value = CInt(PkFieldValueArray.GetValue(1))
sqlCmd.Parameters.Add(SqlParam2)
sqlCmd.Parameters("@PkFieldValue2").Value = PkFieldValueArray.GetValue(2)
sqlCmd.Parameters.Add(SqlParam3)
sqlCmd.Parameters("@PkFieldValue3").Value = PkFieldValueArray.GetValue(3)
reader = sqlCmd.ExecuteReader
If (reader.Read) Then
While i < reader.FieldCount
-------> Do processing here <----------
end while
end if
-----> Statement in SQL Server Profiler: exec sp_executesql N'SELECT Coalesce(ID,'''') AS ID, Coalesce([DATABASE],-9999) AS [DATABASE], Coalesce(VENDOR_ID,'''') AS VENDOR_ID, Coalesce(MFG_PART_ID,'''') AS MFG_PART_ID, Coalesce(STAGE_ID,'''') AS STAGE_ID, Coalesce(REVISION_ID,'''') AS REVISION_ID, Coalesce(DESCRIPTION_FR,'''') AS DESCRIPTION_FR, Coalesce(DESCRIPTION_EN,'''') AS DESCRIPTION_EN, Coalesce(WAREHOUSE_ID,'''') AS WAREHOUSE_ID, Coalesce(LOCATION_ID,'''') AS LOCATION_ID, Coalesce(MFG_NAME,'''') AS MFG_NAME, Coalesce(BUYER_USER_ID,'''') AS BUYER_USER_ID, Coalesce(CATEGORY,'''') AS CATEGORY, Coalesce(PRODUCT_CODE,'''') AS PRODUCT_CODE, Coalesce(UNIT_PRICE,-9999) AS UNIT_PRICE, Coalesce(UNIT_OF_MEASURE,'''') AS UNIT_OF_MEASURE, Coalesce(PART_TYPE,'''') AS PART_TYPE, Coalesce(ON_HAND_QTY,-9999) AS ON_HAND_QTY, Coalesce(DRAWING_NUMBER,'''') AS DRAWING_NUMBER, Coalesce(DRAWING_REV_NO,'''') AS DRAWING_REV_NO, Coalesce(PLANNER_USER_ID,'''') AS PLANNER_USER_ID, Coalesce(UNIT_COST,'''') AS UNIT_COST, Coalesce(LEADTIME,-9999) AS LEADTIME, Coalesce(ROHS,'''') AS ROHS, Coalesce(ROHS_NOTE,'''') AS ROHS_NOTE, Coalesce(OTHER_NOTE,'''') AS OTHER_NOTE, Coalesce(SALEABLE,-1) AS SALEABLE, Coalesce(PLM_ID,'''') AS PLM_ID FROM ONDT_PART_DEV WHERE ID = @PkFieldValue0 AND [DATABASE] = @PkFieldValue1 AND VENDOR_ID = @PkFieldValue2 AND MFG_PART_ID = @PkFieldValue3',N'@PkFieldValue0 nvarchar(8),@PkFieldValue1 int,@PkFieldValue2 nvarchar(6),@PkFieldValue3 nvarchar(8)',@PkFieldValue0=N'14BB0103',@PkFieldValue1=1,@PkFieldValue2=N'180240',@PkFieldValue3=N'A-DSTOPK'
----> Direct Select Statement: SELECT Coalesce(ID,'''') AS ID, Coalesce([DATABASE],-9999) AS [DATABASE], Coalesce(VENDOR_ID,'''') AS VENDOR_ID, Coalesce(MFG_PART_ID,'''') AS MFG_PART_ID, Coalesce(STAGE_ID,'''') AS STAGE_ID, Coalesce(REVISION_ID,'''') AS REVISION_ID, Coalesce(DESCRIPTION_FR,'''') AS DESCRIPTION_FR, Coalesce(DESCRIPTION_EN,'''') AS DESCRIPTION_EN, Coalesce(WAREHOUSE_ID,'''') AS WAREHOUSE_ID, Coalesce(LOCATION_ID,'''') AS LOCATION_ID, Coalesce(MFG_NAME,'''') AS MFG_NAME, Coalesce(BUYER_USER_ID,'''') AS BUYER_USER_ID, Coalesce(CATEGORY,'''') AS CATEGORY, Coalesce(PRODUCT_CODE,'''') AS PRODUCT_CODE, Coalesce(UNIT_PRICE,-9999) AS UNIT_PRICE, Coalesce(UNIT_OF_MEASURE,'''') AS UNIT_OF_MEASURE, Coalesce(PART_TYPE,'''') AS PART_TYPE, Coalesce(ON_HAND_QTY,-9999) AS ON_HAND_QTY, Coalesce(DRAWING_NUMBER,'''') AS DRAWING_NUMBER, Coalesce(DRAWING_REV_NO,'''') AS DRAWING_REV_NO, Coalesce(PLANNER_USER_ID,'''') AS PLANNER_USER_ID, Coalesce(UNIT_COST,'''') AS UNIT_COST, Coalesce(LEADTIME,-9999) AS LEADTIME, Coalesce(ROHS,'''') AS ROHS, Coalesce(ROHS_NOTE,'''') AS ROHS_NOTE, Coalesce(OTHER_NOTE,'''') AS OTHER_NOTE, Coalesce(SALEABLE,-1) AS SALEABLE, Coalesce(PLM_ID,'''') AS PLM_ID FROM ONDT_PART_DEV WHERE ID = '14BB0103' AND [DATABASE] = 1 AND VENDOR_ID = '180240' AND MFG_PART_ID = 'A-DSTOPK'
execution-time.JPG
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
One suggestion : You may wish to open the connection once at the start of your SSIS, save the connection in an object variable, then reuse the open connection in you script... It may not make much of a difference but its worth a try.
The other thing you could do is use a lookup component to cache most of the dataset in memory and look it up that way.
But I agree with TMarkham1 - it's difficult to have a design which is both flexible and efficient.
The other thing you could do is use a lookup component to cache most of the dataset in memory and look it up that way.
But I agree with TMarkham1 - it's difficult to have a design which is both flexible and efficient.
ASKER
I was away for the long week-end, I come back, create a SP and everything runs a lot smooter now. Thanks for the suggestion!
1) do you really need to use coalesce in all columns? Try dropping the and you will se an increase in performance.
2) Is the table indexed? Adding indexes to certain columns can improve your performance. In general, good candidates for indexes are:
- Primary key columns
- Foreign keys
- Columns on which you use the ORDER by and/or GROUP BY clauses
- Columns that you specify exactly in your WHERE clause.
Check this link for further details:
http://www.sqlteam.com/article/sql-server-indexes-the-basics