• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 227
  • Last Modified:

Very poor performance for Data Adapter vs Query in Query Analyzer

I use the sample code in a vb.net web app.  It takes 6-10 seconds to return data from SQL server.   If I do the same query in query analyzer the results come back in 15 milleseconds.  

I have tried this query in .Net using the four combos of .Net 1.1 and 2.0 and SQL Server 2000 and 2005.  I makes no diff.   I have heard that this is a .net issue, and an hoping somebody know a workaround for it.

Here is my sample code.

        Dim conn As New SqlConnection("workstation id=PAMS-PAT2;packet size=4096;user id=sa;data source=PAMS-WEB3;persist security info=True;initial catalog=Interchange;password=xxxxx")
           
        Dim intYearID As Integer = CInt(Request.Form("YearId"))
        Dim dsModel As New DataSet
        Dim cmd As SqlCommand = New SqlCommand("Exec getManu " + intYearID.ToString, conn)
        Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
        da.Fill(dsModel)


Thanks in advance.
Pat
0
pamsauto
Asked:
pamsauto
  • 10
  • 8
  • 2
1 Solution
 
Arthur_WoodCommented:
the biggest part of the performance problem will come from opening the Connection itself, which is an 'expensive' action.  SQL Server Query Analyzer, being a Native SQL Server application, does not pay the same 'connection price', since the connection is already established when Query Analyzer first opens.  What you are seeing with QA is the true execution time of the Query, and what you are seeing from your app, is the time it takes to create and open the Database Connection, from an outside program.

AW
0
 
Arthur_WoodCommented:
the best you could do would be to open the connection, as a GLOBAL object, and then use the already open connection, in your code to create the DataAdapter and use the DA to  fill the DataSet.

AW
0
 
pamsautoAuthor Commented:
OK, makes sense.   So I use the same code for a different page and a different SP, and It returns the results very fast.  The code for the page is the same except for the ds name and the sp.   The SP also executes in QA super quick.   I do not need a dataset for the end results, just the data as I will not be going back to the DB for any updates, deletes, etc.

So somebody must have made a work around for this, any help out there?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Bob LearnedCommented:
Use a DataReader:

 Dim conn As New SqlConnection("workstation id=PAMS-PAT2;packet size=4096;user id=sa;data source=PAMS-WEB3;persist security info=True;initial catalog=Interchange;password=xxxxx")
           
        Dim intYearID As Integer = CInt(Request.Form("YearId"))
        Dim cmd As New SqlCommand("Exec getManu " + intYearID.ToString, conn)
        conn.Open()
        Dim reader As SqlDataReader = cmd.ExecuteReader()
        While reader.Read()
        End While
        reader.Close()

Bob
0
 
pamsautoAuthor Commented:
Well, a little faster, but not much.   Still way too long of query times.  I tried replaceing the SP with just straight SQL in the SQLCommand and it halfed the execution times.  

It is strange too, if I watch CPU usage in .NET with using th SP it spikes one CPU 100% for 4 seconds.  If I use the SQL statement it puts 8 CPUS at 50 percent for 2.5 seconds.  If I use QA and the SP you can't even see a ripple in the CPU graphs.

The machine is a no wimp either, it is a quad x64 dual core 3.6 ghz so somehting just isnt right.

Any other ideas that might help some?
0
 
Bob LearnedCommented:
Is this any faster:

Dim cmd As New SqlCommand("getManu " + intYearID.ToString, conn)
cmd.CommandType = CommandType.StoredProcedure

Bob
0
 
pamsautoAuthor Commented:
Bob-

Good idea, but it performs the sames as "Exec getManu" and not delclaring it as a SP.

Pat
0
 
Bob LearnedCommented:
Cool b-)  I wasn't sure that it went through the same path, since I don't use the EXEC prefix.

Bob
0
 
Bob LearnedCommented:
What does the stored procedure look like (if you have the liberty of showing)?

Bob
0
 
pamsautoAuthor Commented:
ALTER PROCEDURE dbo.getModel
  @YearID int,
  @Manu varchar(50)
AS
BEGIN
  DECLARE @year int
  SET @year = (SELECT TOP 1 LongYear FROM [year] WHERE id=@yearID)
  IF @year IS NOT NULL
    SELECT DISTINCT M.LongModelName FROM ICModel M
      WHERE M.ManufactuerName=@Manu AND
        EXISTS(SELECT TOP 1 1 FROM ICIndex71_1 I
                      WHERE M.LongModelName=I.ICModelID AND
                            I.FirstYear<=@year AND
                            I.LastYear>=@year
                   )
                   order by M.longModelName
END

I started this thread with the qrong code too!    Not that much is diff.  I am pasting the current code in here.

Partial Class getmodel
    Inherits System.Web.UI.Page
        Dim conn As New SqlConnection("workstation id=PAMS-PAT2;packet size=4096;user id=sa;data source=PAMS-WEB3;persist security info=True;initial catalog=Interchange;password=xxxxxx")


    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Dim intYearID As Integer = CInt(Request.Form("YearId"))
        Dim strManu As String = Request.Form("Manufacturer")

            intYearID = 30
            strManu = "Chrysler"
        Dim intCount As Integer
            Dim rowModel As DataRow

            Dim dsModel As New DataSet
            'Dim cmd As SqlCommand = New SqlCommand("declare @Year int set @Year=( select longyear from year where id='" + intYearID.ToString + "') SELECT DISTINCT ICModel.LongModelName  FROM ICIndex71_1 INNER JOIN ICModel ON ICIndex71_1.ICModelID = ICModel.LongModelName where ICIndex71_1.firstyear<=@Year and ICIndex71_1.lastyear>=@Year and ICModel.ManufactuerName='" + strManu + "'", conn)
            'Dim cmd As SqlCommand = New SqlCommand("Exec getModel " + intYearID.ToString + ",'" + strManu + "'", conn)
            Dim cmd As SqlCommand = New SqlCommand("getModel", conn)
            cmd.CommandType = CommandType.StoredProcedure
            Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
            da.SelectCommand.Parameters.Add("@YearID", SqlDbType.Int)
            da.SelectCommand.Parameters.Add("@Manu", SqlDbType.VarChar)
            da.SelectCommand.Parameters("@YearID").Value = intYearID
            da.SelectCommand.Parameters("@Manu").Value = strManu
            da.Fill(dsModel)

            For Each rowModel In dsModel.Tables(0).Rows
                Response.Write("mn" & intCount & "=" & (rowModel("LongModelName")) & "&")
                intCount += 1
            Next
            Response.Write("entries=" & CStr(intCount) & "&")


            ''Dim SQLstring As String = "DECLARE @year int SET @year = (SELECT TOP 1 LongYear FROM [year] WHERE id=" + intYearID.ToString + ") IF @year IS NOT NULL SELECT DISTINCT M.LongModelName FROM ICModel M WHERE M.ManufactuerName='" + strManu + "' AND EXISTS(SELECT TOP 1 1 FROM ICIndex71_1 I WHERE M.LongModelName=I.ICModelID AND I.FirstYear<=@year AND I.LastYear>=@year) order by M.longModelName"
            ''Dim cmd As New SqlCommand(SQLstring, conn)

            ''conn.Open()
            ''Dim reader As SqlDataReader = cmd.ExecuteReader()
            ''While reader.Read()
            ''    Response.Write("mn" & intCount & "=" & (reader.Item("LongModelName")) & "&")
            ''    intCount += 1
            ''End While
            ''reader.Close()
            ''Response.Write("entries=" & CStr(intCount) & "&")




        End Sub


Pat
0
 
Bob LearnedCommented:
What table indexes (including type) do you have on the ICModel and ICIndex71_1?

Bob
0
 
pamsautoAuthor Commented:
On the ICModel table
     the LongModelName (Non-Unique, Non Clustered) is the only indexed column

On the ICIndex71_1 table
     ICIndex71_1_FirstYear(Non-Unique, Non Clustered)
     ICIndex71_1_HollModelName(Non-Unique, Non Clustered)
     ICIndex71_1_ICModelID(Non-Unique, Non Clustered)
     ICIndex71_1_LastYear(Non-Unique, Non Clustered)
     ICIndex71_1_PartCode(Non-Unique, Non Clustered)
     ICIndex71_1(Clustered)

Pat
0
 
Bob LearnedCommented:
Can you create a test procedure, using this SQL:

SELECT DISTINCT M.LongModelName
    FROM ICModel M
    INNER JOIN ICIndex71_1 I ON M.LongModelName=I.ICModelID AND
                @year BETWEEN I.FirstYear AND I.LastYear
    WHERE M.ManufactuerName=@Manu
    ORDER BY M.longModelName

I would be curious if this was more optimized, vs. the EXISTS.

Bob
0
 
Bob LearnedCommented:
Also, try SET NOCOUNT ON.

Bob
0
 
Bob LearnedCommented:
Also, try this:

   dsModel.EnableConstraints = False

Bob
0
 
pamsautoAuthor Commented:
Folks-


Much progress!    dsModel.EnableConstraints = False makes the SP run on all processors so it is faster, but still not what I need.

Bob's Optmization makes this whole thing happen in a second or less!   I still do not undserstand why QA can do it so fast and with no CPU utlization and .Net can't, but I have the results I needed.

Thanks for you help.

Pat
0
 
pamsautoAuthor Commented:
Sorry, I meant the learned One's Opzimation!
0
 
Bob LearnedCommented:
Ok, I realize that you meant optimization ;)

Bob
0
 
pamsautoAuthor Commented:
Hehe

Why can't keyboards have built in spell checkers!
0
 
Bob LearnedCommented:
Now, there's an idea ;)

Bob
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 10
  • 8
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now