SQL Server execution duration difference

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • alexbf
    #1

    SQL Server execution duration difference

    Hello,

    I have a stored procedure in SQL Server 2000.

    When I execute it through my .NET application (OleDbCommand), I can
    see (in SQL Profiler) that it takes 423 ms to complete.

    If I run the same exact query in Query Analyzer, it takes 216 ms (in
    SQL Profiler).

    There's no other activity on that server.

    Is there an explanation for that time difference?


    Thanks,
    Alex

    Tags: None
    • =?ISO-8859-1?Q?G=F6ran_Andersson?=
      #2
      Re: SQL Server execution duration difference

      alexbf wrote:
      Hello,
      >
      I have a stored procedure in SQL Server 2000.
      >
      When I execute it through my .NET application (OleDbCommand), I can
      see (in SQL Profiler) that it takes 423 ms to complete.
      >
      If I run the same exact query in Query Analyzer, it takes 216 ms (in
      SQL Profiler).
      >
      There's no other activity on that server.
      >
      Is there an explanation for that time difference?
      >
      >
      Thanks,
      Alex
      >
      The SQL server caches things like execution plans, query results and
      function results, so execution times depends also on what the server has
      been doing in the past.

      To say anything more specific, I would have to know what the stored
      procedure is doing.

      --
      Göran Andersson
      _____
      guffa.com
      http://www.guffa.com
      Göran Anderssons privata hemsida.

        Comment

        • alexbf
          #3
          Re: SQL Server execution duration difference

          Hello Goran,

          it's quite a simple stored procedure.

          - One SELECT INTO that feeds a temp table
          - Another select to return the results, fitered with a call to a user
          defined function in the WHERE.

          I don't think it's the stored proc itself because I execute the exact
          same thing through my application or with Query Analyzer.

          Thanks,
          Alex





          On Aug 2, 2:02 pm, Göran Andersson <gu...@guffa.co mwrote:
          alexbf wrote:
          Hello,
          >
          I have a stored procedure in SQL Server 2000.
          >
          When I execute it through my .NET application (OleDbCommand), I can
          see (in SQL Profiler) that it takes 423 ms to complete.
          >
          If I run the same exact query in Query Analyzer, it takes 216 ms (in
          SQL Profiler).
          >
          There's no other activity on that server.
          >
          Is there an explanation for that time difference?
          >
          Thanks,
          Alex
          >
          The SQL server caches things like execution plans, query results and
          function results, so execution times depends also on what the server has
          been doing in the past.
          >
          To say anything more specific, I would have to know what the stored
          procedure is doing.
          >
          --
          Göran Andersson
          _____http://www.guffa.com- Hide quoted text -
          >
          - Show quoted text -

            Comment

            • Mr. Arnold
              #4
              Re: SQL Server execution duration difference


              "alexbf" <alexbf@gmail.c omwrote in message
              news:1186076482 .994910.177870@ d30g2000prg.goo glegroups.com.. .
              Hello,
              >
              I have a stored procedure in SQL Server 2000.
              >
              When I execute it through my .NET application (OleDbCommand), I can
              see (in SQL Profiler) that it takes 423 ms to complete.
              >
              If I run the same exact query in Query Analyzer, it takes 216 ms (in
              SQL Profiler).
              >
              There's no other activity on that server.
              >
              Is there an explanation for that time difference?
              In using the Stored Procedure with QA, I believe QA is talking directly to
              SQL Server and is a faster means.

              When using OleDb and running that SP, OleDb is not directly talking to SQL
              Server and is a slower means.

              <copied from link>

              One of the reasons the SQL .NET Data Provider has gotten so much hype (which
              is why all the samples use it), is how it has been optimized. The SQL
              Managed Provider talks directly to SQL Server without using OLEDB (the
              benefits of using products from the same company). Microsoft claims that the
              speed of moving data between SQL Server and your ASP.NET application can
              increase as much as 300% or more using the SQL Managed Provider because of
              this direct communication.

              <end>

              http://dotnetjunkies.com/Tutorial/926E56FB-3E8B-4A9B-A872-E5D7C89364A1.dcik


              When I was taking my .Net training back in 2004, it was explained that for
              speed considerations to avoid OleDb if you were concerned about speed in
              accessing SQL Server.

              I am sure you can find more articles out on Google about this.

                Comment

                • =?ISO-8859-1?Q?G=F6ran_Andersson?=
                  #5
                  Re: SQL Server execution duration difference

                  alexbf wrote:
                  Hello Goran,
                  >
                  it's quite a simple stored procedure.
                  >
                  - One SELECT INTO that feeds a temp table
                  - Another select to return the results, fitered with a call to a user
                  defined function in the WHERE.
                  >
                  I don't think it's the stored proc itself because I execute the exact
                  same thing through my application or with Query Analyzer.
                  >
                  Thanks,
                  Alex
                  >
                  So, what is it that makes it take more than a few milliseconds to run?
                  Is there a lot of data in the table that you have to go through? Does
                  the final result contain a lot of data? Is it the user defined function
                  that takes time?

                  Why is there a need for a temporary table at all? Can't you just filter
                  the data that are selected from the table? The table can have an index
                  on the field that you are filtering on, while the temporary table
                  doesn't. That can make filtering on the table much faster than filtering
                  on the temporary table.

                  --
                  Göran Andersson
                  _____
                  guffa.com
                  http://www.guffa.com
                  Göran Anderssons privata hemsida.

                    Comment

                    • alexbf
                      #6
                      Re: SQL Server execution duration difference

                      Hello Goran,

                      yes I could do my stored procedure differently to optimize it.. but I
                      really want to know why there's such a difference in time between
                      executing the stored procedure through .NET and through Query Analyzer

                      The data volume is about 100 rows and a few columns... so I doubt it's
                      an issue.

                      Alex

                      On Aug 4, 12:33 pm, Göran Andersson <gu...@guffa.co mwrote:
                      alexbf wrote:
                      Hello Goran,
                      >
                      it's quite a simple stored procedure.
                      >
                      - One SELECT INTO that feeds a temp table
                      - Another select to return the results, fitered with a call to a user
                      defined function in the WHERE.
                      >
                      I don't think it's the stored proc itself because I execute the exact
                      same thing through my application or with Query Analyzer.
                      >
                      Thanks,
                      Alex
                      >
                      So, what is it that makes it take more than a few milliseconds to run?
                      Is there a lot of data in the table that you have to go through? Does
                      the final result contain a lot of data? Is it the user defined function
                      that takes time?
                      >
                      Why is there a need for a temporary table at all? Can't you just filter
                      the data that are selected from the table? The table can have an index
                      on the field that you are filtering on, while the temporary table
                      doesn't. That can make filtering on the table much faster than filtering
                      on the temporary table.
                      >
                      --
                      Göran Andersson
                      _____http://www.guffa.com

                        Comment

                        • =?ISO-8859-1?Q?G=F6ran_Andersson?=
                          #7
                          Re: SQL Server execution duration difference

                          alexbf wrote:
                          Hello Goran,
                          >
                          yes I could do my stored procedure differently to optimize it.. but I
                          really want to know why there's such a difference in time between
                          executing the stored procedure through .NET and through Query Analyzer
                          >
                          The data volume is about 100 rows and a few columns... so I doubt it's
                          an issue.
                          >
                          Alex
                          >
                          Have you verified that there is a consistent time difference? Do you
                          include opening and closing the connection when you check the time in
                          the code?

                          --
                          Göran Andersson
                          _____
                          guffa.com
                          http://www.guffa.com
                          Göran Anderssons privata hemsida.

                            Comment

                            • Mr. Arnold
                              #8
                              Re: SQL Server execution duration difference

                              I am trying to figure out what you two are talking about now that doesn't
                              make any sense about the speed of something running through SQL Server QA,
                              which is in direct contact with SQL Server, as opposed to something that's
                              running from a program that using OleDb

                              <from a post to me in another NG about the speed>
                              One of the reasons the SQL .NET Data Provider has gotten so much hype
                              (which
                              is why all the samples use it), is how it has been optimized. The SQL
                              Managed Provider talks directly to SQL Server without using OLEDB (the
                              benefits of using products from the same company). Microsoft claims that
                              the
                              speed of moving data between SQL Server and your ASP.NET application can
                              increase as much as 300% or more using the SQL Managed Provider because of
                              this direct communication.
                              I posted the results of a little test here back in June:

                              SQL Client (2.0): INSERT 20000 rows: 9,109375 seconds
                              SQL Client (2.0): 50 SELECT 20000 rows: 1,75 seconds
                              OLE DB (2000): INSERT 20000 rows: 20 seconds
                              OLE DB (2000): 50 SELECT 20000 rows: 62,140625 seconds
                              ODBC (2000): INSERT 20000 rows: 16,65625 seconds
                              ODBC (2000): 50 SELECT 20000 rows: 47,8125 seconds
                              OLE DB (2005): INSERT 20000 rows: 17,28125 seconds
                              OLE DB (2005): 50 SELECT 20000 rows: 61,484375 seconds
                              ODBC (2005): INSERT 20000 rows: 13,96875 seconds
                              ODBC (2005): 50 SELECT 20000 rows: 47,734375 seconds

                              <end>

                              Anything coming from OLE DB or ODBC is going to be slower because those
                              solutions are not in direct contact with SQL Server, as opposed to SQL
                              Client or something running within SQL Server QA with those solutions being
                              in direct contact with SQL Server.

                                Comment

                                Previous template Next
                                Working...