Slow interaction with SQL Server

Question

Hi all,

I am in the process of debugging an application written in VB 2010, using a test SQL Server 2000 database. This application is simple enough: It polls a folder and if it finds a text data file it reads its contents and imports them into the SQL Server database. This new app is a Windows service that was authored to replace a functionally equivalent desktop app written in VB6.

The issue that I am having is that the VB6 runs circles around the .NET app. While the .NET app takes around 2 minutes to import the files the same app in VB6 takes around 15 seconds. I have tried various approaches, as detailed below.

I suspected that perhaps the TSQL statements were not optimized for the .NET environment. I tried tweaking them to see if there was any change in speed. There wasn't. I am not even sure if this strategy made sense since if anything at all, the TSQL should be optimized for the back end, not the client end.

I noticed that the VB6 app uses the following connection string:

"Driver={SQL Server};" & _
           "Server=Servename;" & _
           "Database=DBName;" & _
           "Uid=AppUsr;" & _
           "Pwd=UsrPwd"

The VB 2010 uses the following:

"Server=Servename;" & _
           "Database=DBName;" & _
           "Uid=AppUsr;" & _
           "Pwd=UsrPwd"

The VB 2010 connect string does not specify a provider. My connection variable is instantiated like so:

    Dim dbConx As New SqlConnection(strConnect)

I am using SqlClient. I thought that by changing the provider it would make some difference, but when I added the string Provider=.. and then Driver=... VB 2010 generated a runtime error indicating that the "Provider" or "Driver" keyword was not recognized.

I also wanted to test with ODBC by using system.data.odbc and made the necessary changes, but these turned out to be unwieldy because I use lots of parameterized queries in the form of:

select field1,field2, from MyTable where XYZ=@ProdId

During runtime I got the error message indicating that the variable @ProdId was not declared. After some research I found that ODBC uses the "?" char within the query text to denote a parameter.

Doing further research I found that DataReaders were faster than DataAdapters, so I modified the app to use the former. I saw no meaningful speed gains. If there was any gain, it was not enough to reduce the processing time to have it similar to that of the VB6 app.

I also thought that I was not handling my parameters correctly and investigated the parameter properties such as Scale and Precision, among others, but the info that I found mentioned that these parameters were for the benefit of Output parameters and were not strictly critical for input parameters.

I also tried using the Connection String Builder to specify other providers, but this does not have the faculty to do this. I was able to successfully build the connection string though.

So my question and plea for help is whether there is something (anything?) that I am missing to make this process go faster, at least as fast as its VB6 counterpart. Any orientation and feedback is welcomed. Thanks for your time and assistance. Regards, Saga

---

Insanity is the prelude to discovery

Answer 1

So my question and plea for help is whether there is something (anything?) that I am missing to make this process go faster, at least as fast as its VB6 counterpart. Any orientation and feedback is welcomed. Thanks for your time and assistance. Regards, Saga

It is best to use the .NET Framework provider for SQL Server (SqlClient) from managed VB code.  No provider need be specified in the connection string since the provider is implict due to the System.Data.SqlClient namespace.

Below is a simplified data reader example with a parameterized query:

        Dim dbConx As New SqlConnection("Data Source=Servername;Initial Catalog=DbName;User Id=AppUsr;Password=UsrPwd")
        Dim command As New SqlCommand("SELECT field1,field2 FROM dbo.MyTable WHERE XYZ=@ProdId;", dbConx)
        command.Parameters.Add("@ProdId", SqlDbType.Int).Value = 1
        dbConx.Open()
        Dim reader = command.ExecuteReader()
        While reader.Read()
            'your code here
        End While
        dbConx.Close()

Regarding performance, are you sure the slowness is in the data access rather than some area in app code?  How many records are you importing?  If you are just performing mass inserts, you might consider using the SqlBulkCopy class or an insert proc using a table-valued parameter.  Bulk insert methods like this are often an order of magnatude faster than individual inserts.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 2

Thank you for replying. The code that you list is very similar to what I am using. I just added a check to determine whether my "reader" variable is Nothing and another check to see if it has rows (reader.HasRows).

The app's major role is INSERTing or in some cases UPDATEing data. The SELECT queries bring back a minimum amount of data. For example, I have one TSQL that checks for the existence of a specific record so that it will know whether to update or insert it. If it is the code which is faulty I don't know where the problem could be. I can post some of the code I use, but it will have to be tomorrow, as I am home right now (theoretically) resting :-). Thanks again, Saga

---

Insanity is the prelude to discovery

Answer 3

Without seeing your application code, T-SQL, table structure etc, it’s impossible for anyone to assist you. What you’re basically doing is asking us to take a guess at what the problem is.  My best guess is that you’re using the wrong .Net objects to interact with the database.

---

When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer

Jeff Wharton
MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCSD, MCITP, MCDBA
Blog: Mr. Wharty's Ramblings
Twitter: @Mr_Wharty
MC ID: Microsoft Transcript

Answer 4

If you are using VS 2010 Premium or Ultimate, you can use performance profiling to identify the methods that take the most time so that you can focus your performance tuning efforts where it is needed.  Alternatively, you can instrument your code to gather method call timings and log the metrics to a file for analysis.

There are many ways to accomplish similar tasks in .NET and some are better than others from a performance perspective.  For example, using a StringBuilder instead of string concatenation will yield substantial performance improvement if you do a lot of string building and manipulation.  The same considerations apply to the higher level ADO.NET classes but I wouldn't jump to the conclusion that it is actuallly the data access that is slow until your timings indicate that.  At the end of the day, I would expect a VB.NET app with best practices to perform better than a functionally identical VB6 version.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 5

Again, thanks both for your replies.

I have VS Pro, but have access to Ultimate edition. I may have to upgrade.

I have not looked into the SQLBulkCopy class, but will do so.

As I mentioned in my last post, I needed to get to the office to access the code which I now include here. I hope this gives you all a better view of how I am doing the processes that I've mentioned.

Table structure:

CREATE TABLE [dbo].[TransRec] (
    [GROUPID] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [TRANSDATE] [datetime] NOT NULL ,
    [CUSTNUM] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [STARTTRANS] [datetime] NOT NULL ,
    [FINISHTRANS] [datetime] NOT NULL ,
    [CUSTVERIFY] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [GEOSECTIONID] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
) ON [PRIMARY]

Routine I use to open the DB connection. Once opened, I leave it closed.

   Dim strblder As New SqlConnectionStringBuilder()

   strblder.ApplicationName = "DotNET Test App"
   strblder.DataSource = "TestDB"
   strblder.InitialCatalog = "OpData"
   strblder.UserID = GetUserId()
   strblder.Password = GetSrvPwd()

   Dim ConnectStr as String = strblder.ConnectionString()

   Dim dbConx As New SqlConnection(ConnectStr)

   dbConx.Open()

   If dbConx.State = ConnectionState.Open Then

     dbConx.Close()

   end if

Routine that I use to insert table record.

    Dim SQLObj As SqlClient.SqlCommand    

    Dim strSQL As String = "set nocount on" & vbCrLf

    strSQL &= "if not exists (select * from TransRec with (nolock) where "

    'Four columns that make up the primary key.
    strSQL &= "GROUPID=@GROUP_ID and "
    strSQL &= "TRANSDATE=@TRANS_DATE and "
    strSQL &= "CUSTNUM=@CUST_ID and "
    strSQL &= "STARTTRANS=@START_TRANS)" & vbCrLf

    strSQL &= "begin" & vbCrLf

    strSQL = strSQL & "insert into TransRec (GROUPID,TRANSDATE,CUSTNUM,"
    strSQL = strSQL & "STARTTRANS,FINISHTRANS,CUSTVERIFY,GEOSECTIONID) values("

    strSQL = strSQL & "@GROUP_ID,"

    strSQL = strSQL & "@TRANS_DATE,"

    strSQL = strSQL & "@CUST_ID,"

    strSQL = strSQL & "@START_TRANS,"

    strSQL = strSQL & "@FINSH_TRANS,"

    strSQL = strSQL & "@CUST_VER,"

    strSQL = strSQL & "@GEOSEC_ID)" & vbCrLf

    strSQL = strSQL & "if @@Error=0" & vbCrLf
    strSQL = strSQL & "select 0 as RetVal" & vbCrLf
    strSQL = strSQL & "else" & vbCrLf
    strSQL = strSQL & "select 1 as RetVal" & vbCrLf

    strSQL &= "End" & vbCrLf
    strSQL &= "else" & vbCrLf
    strSQL &= "select 3 as RetVal" & vbCrLf


    SQLObj = New SqlCommand
    SQLObj.CommandText = strSQL

    'Note sGrpId, sCstId, sCV & sSectId declared as string, 
    '     dTrans, dTranStart, dTranFin declared as date
    '     All variables have been correctly initialized.
    SQLObj.Parameters.Add(New SqlParameter("@GROUP_ID", SqlDbType.NVarChar, 5)).Value = sGrpId
    SQLObj.Parameters.Add(New SqlParameter("@TRANS_DATE", SqlDbType.DateTime)).Value = dTrans
    SQLObj.Parameters.Add(New SqlParameter("@CUST_ID", SqlDbType.NVarChar, 10)).Value = sCstId
    SQLObj.Parameters.Add(New SqlParameter("@START_TRANS", SqlDbType.DateTime)).Value = 

dTranStart
    SQLObj.Parameters.Add(New SqlParameter("@FINSH_TRANS", SqlDbType.DateTime)).Value = dTranFin
    SQLObj.Parameters.Add(New SqlParameter("@CUST_VER", SqlDbType.NVarChar, 2)).Value = sCV
    SQLObj.Parameters.Add(New SqlParameter("@GEOSEC_ID", SqlDbType.NVarChar, 5)).Value = sSectId

    If dbConx.State = ConnectionState.Closed Then
      dbConx.Open()
    End If

    Dim blnNextRSExists As Boolean = True

    Dim dtrdr As SqlClient.SqlDataReader = SQLObj.ExecuteReader()

    If Not (dtrdr Is Nothing) Then

      Do While Not dtrdr.HasRows And blnNextRSExists
        blnNextRSExists = dtrdr.NextResult()
      Loop
      
      If dtrdr.HasRows Then

        While dtrdr.Read()
          iRetVal = dtrdr("RetVal")
        End While
      Else
        iRetVal = -1
      End If
    Else
      iRetVal = -2
    End If

    Return iRetVal

That's basically it. Thanks for your assistance. Saga

---

Insanity is the prelude to discovery

Answer 6

I did some research on the SQL Bulk Copy class and it looks promising. I have no problem reading the text data from the file, validating it and using it to build the required DataTable. What I am not sure of, and it was not obvious from the information that I read, is how does it handle duplicates. As can be seen from the code I posted the table in question has a primary key consisting of four columns.

What will happen if using the SQLBulkCopy class, I import the same text file twice? Will this class correctly handle dupes or will it generate an error because the same record was imported twice? Thanks, Saga

---

Insanity is the prelude to discovery

Answer 7

SqlBulkCoy inserts the entire batch in a single transatction according to the specified BatchSize property.  If any row in the batch fails (e.g. PK voilation) the entire batch is rolled back as an atomic unit of work.

There are 2 scenarios you should consider when using SqlBulkCopy when dups are possible.  One is intra-batch dups, where the same key occurs more than once in the same source batch.  The other is no dups in the batch but the same key already exists in the target table.  From a performance perspective, the best approach depends on the likelihood of either scenairio.

When the likelihood of intra-batch dups is high, consider inserting each batch into a staging table and then scrubbing the staging table to eliminate dups.  Then insert the de-duped rows into the target using an "INSERT INTO target..SELECT...FROM staging WHERE NOT EXISTS" query.  You can then report eliminated dups as needed.

In the case of unlikely intra-batch dups, you can optimistically insert into the target and fallback to the above mentioned method when a PK violation occurs.

I should mention that that the SqlBulkjCopy method has 3 overloads, DataTable, DataRow[] and IDataReader.  From a performance perspective, the IDataReader overload is fastest because DataTable and DataRow objects have more overhead.  See http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/b442fe97-06e7-4391-af1e-5dd6ec71ab89 for a C# example if the required IDataReader implementation.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 8

Again, thanks. You present some interesting alternatives that are worth looking into. I will consider the two scenarios of dupes that you mention. Regards, Saga

---

Insanity is the prelude to discovery

Answer 9

I see from your initial post that your are using SQL  Server 2000.  In SQL 2008 and later versions, table-valued-parameters are another optiion.  You can then pass the data as a TVP to a stored procedure that uses a MERGE statement, that INSERTs/UPDATEs data as needed and returns dupicates as desired. 

In my experience, passing a TVP as a DbType.Strucutred parameter that implements IEnumerable is an excellent alterative to SqlBulkCopy because you get the performance benifits of bulk insert (into the TVP temp table) with the flexibility of handlng dups as needed (e.g. MERGE statement WHEN MATCHED with an OUTPUT clause).

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 10

The biggest problem with TVP's is that you can't modify them. You need to drop/recreate. This causes another problem as you can’t drop a TVP if it’s being referenced by another object. Therefore, if you have a TVP which is being used in 20 stored procedures and you want to add/remove a column or modify a datatype in the TVP, you need to either:

1. Drop all the Stored Procedures, drop the TVP, recreate the TVP, and then recreate the stored procedures, or
2. Create a different TVP and then update the 20 stored procedures to reference this new TVP.

That to me is a little too much overhead for my liking.  You would be better off using XML and the MERGE statement.

I have a series of blog posts which discuss TVP's, XML and MERGE, and how to use them with .Net and Datasets. 

Part 1 – Sending Multiple Rows of Data to SQL Server 2008(R2)

Part 2 – OpenXML: What is it and how do I use it?

Part 3 – XQuery: Please don’t use OpenXML!

Part 4 – Table-Valued Parameters (TVP’s): Table what?

Part 5 – OpenXML and XQuery Optimisation Tips

---

When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer

Jeff Wharton
MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCSD, MCITP, MCDBA
Blog: Mr. Wharty's Ramblings
Twitter: @Mr_Wharty
MC ID: Microsoft Transcript

Answer 11

The biggest problem with TVP's is that you can't modify them. You need to drop/recreate. This causes another problem as you can’t drop a TVP if it’s being referenced by another object.

You bring up a good point about schema maintenance complexity with table types.  But I wouldn't go so far as to discourage SagaV9 from using TVPs for this merge scenario.  If the table type is used only to pass a rowset to a merge proc, the additional schema maintenance is minimal and, with IEnumerable, performs better than many other techniques.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 12

The biggest problem with TVP's is that you can't modify them. You need to drop/recreate. This causes another problem as you can’t drop a TVP if it’s being referenced by another object.

You bring up a good point about schema maintenance complexity with table types.  But I wouldn't go so far as to discourage SagaV9 from using TVPs for this merge scenario.  If the table type is used only to pass a rowset to a merge proc, the additional schema maintenance is minimal and, with IEnumerable, performs better than many other techniques.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Agree.

---

When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer

Jeff Wharton
MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCSD, MCITP, MCDBA
Blog: Mr. Wharty's Ramblings
Twitter: @Mr_Wharty
MC ID: Microsoft Transcript

Answer 13

Thanks again!

No worries, TVP looks like a feasible option and I would look into it further; however, the fact that this option is available only for version 2008(R2) concerns me. The solution should be compatible with versions 2000, 2005 and of course 2008R2.

Still, I am looking through the blog posts, great info Mr. Wharty!

It seems that I will have to go through some extra hoops to make my .NET app perform as well as the VB6 app which uses plain vanilla ADO 2.8 code to process the data without understanding why there is such a difference in speed between the two technologies.

Regards, Saga

---

Insanity is the prelude to discovery

Answer 14

Final thoughts.

I found the issue that is causing the delay in the data base operations: parameter sniffing (or lack thereof).

I removed the use of parameters in the test app and the speed was comparable to that of the VB6 app (although not consistently better).

My research and testing was not helped by the fact that the VB6 app was also not importing all the records that were in the data file due to a business rule that ignores record data older than 5 days and the test files that I was using were older than that. (I have now placed an order for a batch of "Bang Your Head Here" stickers".) This was causing the text file to import at least 5 seconds faster than it normally would have.

The major difference in import processing time was due to the way that SQL Server was creating the execution plan given TSQL with parameters, so the use of .NET vs VB6 was not the critical factor which I am glad. Again, thanks to those that participated in this thread with helpful suggestions. Regards, Saga

---

Insanity is the prelude to discovery