Solved

VB.net sqlCmd.ExecuteReader is very slow

Posted on 2009-05-15
4
2,055 Views
Last Modified: 2013-11-26
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?
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'

Open in new window

execution-time.JPG
0
Comment
Question by:lali_murray
4 Comments
 
LVL 41

Expert Comment

by:ralmada
ID: 24403352
Two things:
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 
0
 
LVL 4

Accepted Solution

by:
TMarkham1 earned 500 total points
ID: 24404324
This advice is likely to make you grimmace... but I honestly think it is the best solution for you.

I believe your biggest performance issue stems from the parsing of the SELECT statement, which as far as I can tell, is going to occur on every execution of the statement. This isn't giving SQL Server a chance to help you, because to SQL Server it's as if the SELECT statement is new everytime it sees it, so it has to parse it each and everytime.

If you could somehow write a stored procedure that accepts various parameters, then within that stored procedure you execute predefined static SELECT statements based upon the values of the parameters, you'd be much better off.

As it stands now, It appears you are highly optimized for generating dynamic SELECT statements, and while that is fantastic in terms of extensibility, in the long run it's hurting you as far as performance goes.

But before jumping in with both feet on my suggestion, try a small test first. Create a stored procedure with a single SELECT statement in the format you would expect it to be in, then call that stored procedure from your function above. Just be sure to change the CommandType to StoredProcedure.
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 24404651
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.
0
 

Author Closing Comment

by:lali_murray
ID: 31581905
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!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Location of Dynamics AX Service accounts in SQL 3 16
SQL Log size 3 18
Groupbox Control ? 2 18
Casting is giving error in sql server 3 9
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question