ExecuteReader Blocks Inserts on a Table

Collapse
This topic is closed.
X
X
 
  • Time
  • Show
Clear All
new posts
  • Charles Law

    ExecuteReader Blocks Inserts on a Table

    I have a sproc that returns data from a table using a simple SELECT. There
    are quite a few rows returned, e.g. ~150,000.

    In my first application, I use a reader on the sproc and iterate through the
    rows, writing them out to a file. This takes about 5 minutes until I close
    the reader.

    Whilst this is going on, I have another application that is trying to insert
    rows into the table. Normally, the inserts happen straight away, but when
    the reader is open each insert takes a very long time to complete.

    I realise that this is not an unreasonable thing to happen, given that I am
    trying to write to the table whilst reading from it, but the rows being
    written will never be included in the WHERE clause in my select statement,
    and even if they were (which they won't), I wouldn't want them included in
    the selected rows.

    Is there a way to read rows so that inserts can still occur without
    blocking?

    I am using VB.NET in VS2005, and SQL Server 2005.

    TIA

    Charles


  • =?Utf-8?B?S2VycnkgTW9vcm1hbg==?=

    #2
    RE: ExecuteReader Blocks Inserts on a Table

    Charles,

    Are the 2 applications on the same machine or different machines?

    Is the database server on a different machine than the applications?

    Kerry Moorman


    "Charles Law" wrote:
    I have a sproc that returns data from a table using a simple SELECT. There
    are quite a few rows returned, e.g. ~150,000.
    >
    In my first application, I use a reader on the sproc and iterate through the
    rows, writing them out to a file. This takes about 5 minutes until I close
    the reader.
    >
    Whilst this is going on, I have another application that is trying to insert
    rows into the table. Normally, the inserts happen straight away, but when
    the reader is open each insert takes a very long time to complete.
    >
    I realise that this is not an unreasonable thing to happen, given that I am
    trying to write to the table whilst reading from it, but the rows being
    written will never be included in the WHERE clause in my select statement,
    and even if they were (which they won't), I wouldn't want them included in
    the selected rows.
    >
    Is there a way to read rows so that inserts can still occur without
    blocking?
    >
    I am using VB.NET in VS2005, and SQL Server 2005.
    >
    TIA
    >
    Charles
    >
    >
    >

    Comment

    • Cor Ligthert[MVP]

      #3
      Re: ExecuteReader Blocks Inserts on a Table

      Charles,

      You are by the way not using transaction locking, because then this is the
      normal behaviour.

      Cor

      "Charles Law" <blank@nowhere. comschreef in bericht
      news:Om8QPpa0IH A.6096@TK2MSFTN GP06.phx.gbl...
      >I have a sproc that returns data from a table using a simple SELECT. There
      >are quite a few rows returned, e.g. ~150,000.
      >
      In my first application, I use a reader on the sproc and iterate through
      the rows, writing them out to a file. This takes about 5 minutes until I
      close the reader.
      >
      Whilst this is going on, I have another application that is trying to
      insert rows into the table. Normally, the inserts happen straight away,
      but when the reader is open each insert takes a very long time to
      complete.
      >
      I realise that this is not an unreasonable thing to happen, given that I
      am trying to write to the table whilst reading from it, but the rows being
      written will never be included in the WHERE clause in my select statement,
      and even if they were (which they won't), I wouldn't want them included in
      the selected rows.
      >
      Is there a way to read rows so that inserts can still occur without
      blocking?
      >
      I am using VB.NET in VS2005, and SQL Server 2005.
      >
      TIA
      >
      Charles
      >
      >

      Comment

      • Jack Jackson

        #4
        Re: ExecuteReader Blocks Inserts on a Table

        On Thu, 19 Jun 2008 02:17:57 +0100, "Charles Law" <blank@nowhere. com>
        wrote:
        >I have a sproc that returns data from a table using a simple SELECT. There
        >are quite a few rows returned, e.g. ~150,000.
        >
        >In my first application, I use a reader on the sproc and iterate through the
        >rows, writing them out to a file. This takes about 5 minutes until I close
        >the reader.
        >
        >Whilst this is going on, I have another application that is trying to insert
        >rows into the table. Normally, the inserts happen straight away, but when
        >the reader is open each insert takes a very long time to complete.
        >
        >I realise that this is not an unreasonable thing to happen, given that I am
        >trying to write to the table whilst reading from it, but the rows being
        >written will never be included in the WHERE clause in my select statement,
        >and even if they were (which they won't), I wouldn't want them included in
        >the selected rows.
        >
        >Is there a way to read rows so that inserts can still occur without
        >blocking?
        >
        >I am using VB.NET in VS2005, and SQL Server 2005.
        >
        >TIA
        >
        >Charles
        >
        You might take a look at the Transact-SQL statement SET TRANSACTION
        ISOLATION LEVEL.

        Comment

        • Tibor Karaszi

          #5
          Re: ExecuteReader Blocks Inserts on a Table

          If the SELECT limits itself using a WHERE clause to rows which aren't in the inserted set, then you
          have a chance to have the SELECT not blocked by your INSERTs. But that also depends on what
          execution plan you get. If SQL Server drive the SELECT using an index which is on something that can
          be used to exclude the rows to INSERT then you shouldn't see this blocking. However, considering you
          return so many rows, you need to carefully evaluate your indexing strategy as well as your SELECT
          query to make this happen.

          Other options include Snapshot isolation and the READPAST optimizer hint. Those are well documented
          in Books Online.

          --
          Tibor Karaszi, SQL Server MVP




          "Charles Law" <blank@nowhere. comwrote in message news:Om8QPpa0IH A.6096@TK2MSFTN GP06.phx.gbl...
          >I have a sproc that returns data from a table using a simple SELECT. There are quite a few rows
          >returned, e.g. ~150,000.
          >
          In my first application, I use a reader on the sproc and iterate through the rows, writing them
          out to a file. This takes about 5 minutes until I close the reader.
          >
          Whilst this is going on, I have another application that is trying to insert rows into the table.
          Normally, the inserts happen straight away, but when the reader is open each insert takes a very
          long time to complete.
          >
          I realise that this is not an unreasonable thing to happen, given that I am trying to write to the
          table whilst reading from it, but the rows being written will never be included in the WHERE
          clause in my select statement, and even if they were (which they won't), I wouldn't want them
          included in the selected rows.
          >
          Is there a way to read rows so that inserts can still occur without blocking?
          >
          I am using VB.NET in VS2005, and SQL Server 2005.
          >
          TIA
          >
          Charles
          >
          >

          Comment

          • Charles Law

            #6
            Re: ExecuteReader Blocks Inserts on a Table

            Hi Kerry

            The two applications are on different machines, connected by a fairly slow
            link, which is why it takes so long to iterate through the rows returned by
            the reader. The database server is clustered on another machine, which is on
            a Gb link to the second application, but the first (reader) application is
            at the other end of the slow connection.

            I could run both on the same machine, but then I would have to transfer the
            resulting file over the slow link, and that would take longer in real time.

            Charles


            "Kerry Moorman" <KerryMoorman@d iscussions.micr osoft.comwrote in message
            news:6A4C73DD-8153-4A76-9DAE-288CE51305F4@mi crosoft.com...
            Charles,
            >
            Are the 2 applications on the same machine or different machines?
            >
            Is the database server on a different machine than the applications?
            >
            Kerry Moorman
            >
            >
            "Charles Law" wrote:
            >
            >I have a sproc that returns data from a table using a simple SELECT.
            >There
            >are quite a few rows returned, e.g. ~150,000.
            >>
            >In my first application, I use a reader on the sproc and iterate through
            >the
            >rows, writing them out to a file. This takes about 5 minutes until I
            >close
            >the reader.
            >>
            >Whilst this is going on, I have another application that is trying to
            >insert
            >rows into the table. Normally, the inserts happen straight away, but when
            >the reader is open each insert takes a very long time to complete.
            >>
            >I realise that this is not an unreasonable thing to happen, given that I
            >am
            >trying to write to the table whilst reading from it, but the rows being
            >written will never be included in the WHERE clause in my select
            >statement,
            >and even if they were (which they won't), I wouldn't want them included
            >in
            >the selected rows.
            >>
            >Is there a way to read rows so that inserts can still occur without
            >blocking?
            >>
            >I am using VB.NET in VS2005, and SQL Server 2005.
            >>
            >TIA
            >>
            >Charles
            >>
            >>
            >>

            Comment

            • Charles Law

              #7
              Re: ExecuteReader Blocks Inserts on a Table

              Hi Cor

              No, I'm not explicitly using transaction locking. I thought, therefore, that
              the reader would use row level locking, but if it is, it is still causing
              some kind of locking problem.

              Charles


              "Cor Ligthert[MVP]" <notmyfirstname @planet.nlwrote in message
              news:1E3A0A19-8E26-40DB-BBD7-3A9C52F77ADB@mi crosoft.com...
              Charles,
              >
              You are by the way not using transaction locking, because then this is the
              normal behaviour.
              >
              Cor
              >
              "Charles Law" <blank@nowhere. comschreef in bericht
              news:Om8QPpa0IH A.6096@TK2MSFTN GP06.phx.gbl...
              >>I have a sproc that returns data from a table using a simple SELECT. There
              >>are quite a few rows returned, e.g. ~150,000.
              >>
              >In my first application, I use a reader on the sproc and iterate through
              >the rows, writing them out to a file. This takes about 5 minutes until I
              >close the reader.
              >>
              >Whilst this is going on, I have another application that is trying to
              >insert rows into the table. Normally, the inserts happen straight away,
              >but when the reader is open each insert takes a very long time to
              >complete.
              >>
              >I realise that this is not an unreasonable thing to happen, given that I
              >am trying to write to the table whilst reading from it, but the rows
              >being written will never be included in the WHERE clause in my select
              >statement, and even if they were (which they won't), I wouldn't want them
              >included in the selected rows.
              >>
              >Is there a way to read rows so that inserts can still occur without
              >blocking?
              >>
              >I am using VB.NET in VS2005, and SQL Server 2005.
              >>
              >TIA
              >>
              >Charles
              >>
              >>
              >

              Comment

              • Charles Law

                #8
                Re: ExecuteReader Blocks Inserts on a Table

                Hi Jack

                I did read up about this, but it suggested that the default is row level
                locking, which seemed to be the one I'd want anyway, so I haven't attempted
                to change it. I have just looked again, and I see there is more to this than
                I first thought, so I will read up.

                Cheers

                Charles


                "Jack Jackson" <jjackson@cinno vations.netwrot e in message
                news:55sj549spe 8lfbpvh4q21humn 3rpkphfeb@4ax.c om...
                On Thu, 19 Jun 2008 02:17:57 +0100, "Charles Law" <blank@nowhere. com>
                wrote:
                >
                >>I have a sproc that returns data from a table using a simple SELECT. There
                >>are quite a few rows returned, e.g. ~150,000.
                >>
                >>In my first application, I use a reader on the sproc and iterate through
                >>the
                >>rows, writing them out to a file. This takes about 5 minutes until I close
                >>the reader.
                >>
                >>Whilst this is going on, I have another application that is trying to
                >>insert
                >>rows into the table. Normally, the inserts happen straight away, but when
                >>the reader is open each insert takes a very long time to complete.
                >>
                >>I realise that this is not an unreasonable thing to happen, given that I
                >>am
                >>trying to write to the table whilst reading from it, but the rows being
                >>written will never be included in the WHERE clause in my select statement,
                >>and even if they were (which they won't), I wouldn't want them included in
                >>the selected rows.
                >>
                >>Is there a way to read rows so that inserts can still occur without
                >>blocking?
                >>
                >>I am using VB.NET in VS2005, and SQL Server 2005.
                >>
                >>TIA
                >>
                >>Charles
                >>
                >
                You might take a look at the Transact-SQL statement SET TRANSACTION
                ISOLATION LEVEL.

                Comment

                • Charles Law

                  #9
                  Re: ExecuteReader Blocks Inserts on a Table

                  Hi Tibor

                  Thanks for the reply. I hadn't thought about the indexing issue. I will add
                  these to my reading list.

                  Cheers

                  Charles


                  "Tibor Karaszi" <tibor_please.n o.email_karaszi @hotmail.nomail .comwrote in
                  message news:FC918BA6-E607-4490-B7D7-33099DFE67B5@mi crosoft.com...
                  If the SELECT limits itself using a WHERE clause to rows which aren't in
                  the inserted set, then you have a chance to have the SELECT not blocked by
                  your INSERTs. But that also depends on what execution plan you get. If SQL
                  Server drive the SELECT using an index which is on something that can be
                  used to exclude the rows to INSERT then you shouldn't see this blocking.
                  However, considering you return so many rows, you need to carefully
                  evaluate your indexing strategy as well as your SELECT query to make this
                  happen.
                  >
                  Other options include Snapshot isolation and the READPAST optimizer hint.
                  Those are well documented in Books Online.
                  >
                  --
                  Tibor Karaszi, SQL Server MVP


                  >
                  >
                  "Charles Law" <blank@nowhere. comwrote in message
                  news:Om8QPpa0IH A.6096@TK2MSFTN GP06.phx.gbl...
                  >>I have a sproc that returns data from a table using a simple SELECT. There
                  >>are quite a few rows returned, e.g. ~150,000.
                  >>
                  >In my first application, I use a reader on the sproc and iterate through
                  >the rows, writing them out to a file. This takes about 5 minutes until I
                  >close the reader.
                  >>
                  >Whilst this is going on, I have another application that is trying to
                  >insert rows into the table. Normally, the inserts happen straight away,
                  >but when the reader is open each insert takes a very long time to
                  >complete.
                  >>
                  >I realise that this is not an unreasonable thing to happen, given that I
                  >am trying to write to the table whilst reading from it, but the rows
                  >being written will never be included in the WHERE clause in my select
                  >statement, and even if they were (which they won't), I wouldn't want them
                  >included in the selected rows.
                  >>
                  >Is there a way to read rows so that inserts can still occur without
                  >blocking?
                  >>
                  >I am using VB.NET in VS2005, and SQL Server 2005.
                  >>
                  >TIA
                  >>
                  >Charles
                  >>
                  >>
                  >

                  Comment

                  • Tibor Karaszi

                    #10
                    Re: ExecuteReader Blocks Inserts on a Table

                    I did read up about this, but it suggested that the default is row level locking,

                    It is not that easy. First, SQL Server decided itself whether to lock row, page, or table level.
                    Factors involved in this decision is selectivity of query and concurrent users. Also, something that
                    start up as row level can during execution escalate to table level.

                    But even with row level, you are not helped if SQL Server need to look at every row. How do SQL
                    Server know if a row satisfy your criteria without looking at it first? See my other post for
                    elaboration about this topic.

                    --
                    Tibor Karaszi, SQL Server MVP




                    "Charles Law" <blank@nowhere. comwrote in message news:uEAb7Te0IH A.2384@TK2MSFTN GP04.phx.gbl...
                    Hi Jack
                    >
                    I did read up about this, but it suggested that the default is row level locking, which seemed to
                    be the one I'd want anyway, so I haven't attempted to change it. I have just looked again, and I
                    see there is more to this than I first thought, so I will read up.
                    >
                    Cheers
                    >
                    Charles
                    >
                    >
                    "Jack Jackson" <jjackson@cinno vations.netwrot e in message
                    news:55sj549spe 8lfbpvh4q21humn 3rpkphfeb@4ax.c om...
                    >On Thu, 19 Jun 2008 02:17:57 +0100, "Charles Law" <blank@nowhere. com>
                    >wrote:
                    >>
                    >>>I have a sproc that returns data from a table using a simple SELECT. There
                    >>>are quite a few rows returned, e.g. ~150,000.
                    >>>
                    >>>In my first application, I use a reader on the sproc and iterate through the
                    >>>rows, writing them out to a file. This takes about 5 minutes until I close
                    >>>the reader.
                    >>>
                    >>>Whilst this is going on, I have another application that is trying to insert
                    >>>rows into the table. Normally, the inserts happen straight away, but when
                    >>>the reader is open each insert takes a very long time to complete.
                    >>>
                    >>>I realise that this is not an unreasonable thing to happen, given that I am
                    >>>trying to write to the table whilst reading from it, but the rows being
                    >>>written will never be included in the WHERE clause in my select statement,
                    >>>and even if they were (which they won't), I wouldn't want them included in
                    >>>the selected rows.
                    >>>
                    >>>Is there a way to read rows so that inserts can still occur without
                    >>>blocking?
                    >>>
                    >>>I am using VB.NET in VS2005, and SQL Server 2005.
                    >>>
                    >>>TIA
                    >>>
                    >>>Charles
                    >>>
                    >>
                    >You might take a look at the Transact-SQL statement SET TRANSACTION
                    >ISOLATION LEVEL.
                    >
                    >

                    Comment

                    • Charles Law

                      #11
                      Re: ExecuteReader Blocks Inserts on a Table

                      Hi Tibor

                      Yes, I was obviously viewing this too simplistically. I have a couple of
                      Kalen Delaney's books, and there are some good topics in them that prove how
                      naive I was.

                      Cheers.

                      Charles


                      "Tibor Karaszi" <tibor_please.n o.email_karaszi @hotmail.nomail .comwrote in
                      message news:8A813FA6-C9E8-4EF8-A9A0-992B030B38EA@mi crosoft.com...
                      >I did read up about this, but it suggested that the default is row level
                      >locking,
                      >
                      It is not that easy. First, SQL Server decided itself whether to lock row,
                      page, or table level. Factors involved in this decision is selectivity of
                      query and concurrent users. Also, something that start up as row level can
                      during execution escalate to table level.
                      >
                      But even with row level, you are not helped if SQL Server need to look at
                      every row. How do SQL Server know if a row satisfy your criteria without
                      looking at it first? See my other post for elaboration about this topic.
                      >
                      --
                      Tibor Karaszi, SQL Server MVP


                      >
                      >
                      "Charles Law" <blank@nowhere. comwrote in message
                      news:uEAb7Te0IH A.2384@TK2MSFTN GP04.phx.gbl...
                      >Hi Jack
                      >>
                      >I did read up about this, but it suggested that the default is row level
                      >locking, which seemed to be the one I'd want anyway, so I haven't
                      >attempted to change it. I have just looked again, and I see there is more
                      >to this than I first thought, so I will read up.
                      >>
                      >Cheers
                      >>
                      >Charles
                      >>
                      >>
                      >"Jack Jackson" <jjackson@cinno vations.netwrot e in message
                      >news:55sj549sp e8lfbpvh4q21hum n3rpkphfeb@4ax. com...
                      >>On Thu, 19 Jun 2008 02:17:57 +0100, "Charles Law" <blank@nowhere. com>
                      >>wrote:
                      >>>
                      >>>>I have a sproc that returns data from a table using a simple SELECT.
                      >>>>There
                      >>>>are quite a few rows returned, e.g. ~150,000.
                      >>>>
                      >>>>In my first application, I use a reader on the sproc and iterate through
                      >>>>the
                      >>>>rows, writing them out to a file. This takes about 5 minutes until I
                      >>>>close
                      >>>>the reader.
                      >>>>
                      >>>>Whilst this is going on, I have another application that is trying to
                      >>>>insert
                      >>>>rows into the table. Normally, the inserts happen straight away, but
                      >>>>when
                      >>>>the reader is open each insert takes a very long time to complete.
                      >>>>
                      >>>>I realise that this is not an unreasonable thing to happen, given that I
                      >>>>am
                      >>>>trying to write to the table whilst reading from it, but the rows being
                      >>>>written will never be included in the WHERE clause in my select
                      >>>>statement ,
                      >>>>and even if they were (which they won't), I wouldn't want them included
                      >>>>in
                      >>>>the selected rows.
                      >>>>
                      >>>>Is there a way to read rows so that inserts can still occur without
                      >>>>blocking?
                      >>>>
                      >>>>I am using VB.NET in VS2005, and SQL Server 2005.
                      >>>>
                      >>>>TIA
                      >>>>
                      >>>>Charles
                      >>>>
                      >>>
                      >>You might take a look at the Transact-SQL statement SET TRANSACTION
                      >>ISOLATION LEVEL.
                      >>
                      >>
                      >

                      Comment

                      • Tibor Karaszi

                        #12
                        Re: ExecuteReader Blocks Inserts on a Table

                        Yes, Kalen's books, along with Books Online and possibly some hints mentioned in this thread should
                        get you going on this. :-)

                        --
                        Tibor Karaszi, SQL Server MVP




                        "Charles Law" <blank@nowhere. comwrote in message news:ug$r3xe0IH A.2084@TK2MSFTN GP06.phx.gbl...
                        Hi Tibor
                        >
                        Yes, I was obviously viewing this too simplistically. I have a couple of Kalen Delaney's books,
                        and there are some good topics in them that prove how naive I was.
                        >
                        Cheers.
                        >
                        Charles
                        >
                        >
                        "Tibor Karaszi" <tibor_please.n o.email_karaszi @hotmail.nomail .comwrote in message
                        news:8A813FA6-C9E8-4EF8-A9A0-992B030B38EA@mi crosoft.com...
                        >>I did read up about this, but it suggested that the default is row level locking,
                        >>
                        >It is not that easy. First, SQL Server decided itself whether to lock row, page, or table level.
                        >Factors involved in this decision is selectivity of query and concurrent users. Also, something
                        >that start up as row level can during execution escalate to table level.
                        >>
                        >But even with row level, you are not helped if SQL Server need to look at every row. How do SQL
                        >Server know if a row satisfy your criteria without looking at it first? See my other post for
                        >elaboration about this topic.
                        >>
                        >--
                        >Tibor Karaszi, SQL Server MVP
                        >http://www.karaszi.com/sqlserver/default.asp
                        >http://sqlblog.com/blogs/tibor_karaszi
                        >>
                        >>
                        >"Charles Law" <blank@nowhere. comwrote in message news:uEAb7Te0IH A.2384@TK2MSFTN GP04.phx.gbl...
                        >>Hi Jack
                        >>>
                        >>I did read up about this, but it suggested that the default is row level locking, which seemed
                        >>to be the one I'd want anyway, so I haven't attempted to change it. I have just looked again,
                        >>and I see there is more to this than I first thought, so I will read up.
                        >>>
                        >>Cheers
                        >>>
                        >>Charles
                        >>>
                        >>>
                        >>"Jack Jackson" <jjackson@cinno vations.netwrot e in message
                        >>news:55sj549s pe8lfbpvh4q21hu mn3rpkphfeb@4ax .com...
                        >>>On Thu, 19 Jun 2008 02:17:57 +0100, "Charles Law" <blank@nowhere. com>
                        >>>wrote:
                        >>>>
                        >>>>>I have a sproc that returns data from a table using a simple SELECT. There
                        >>>>>are quite a few rows returned, e.g. ~150,000.
                        >>>>>
                        >>>>>In my first application, I use a reader on the sproc and iterate through the
                        >>>>>rows, writing them out to a file. This takes about 5 minutes until I close
                        >>>>>the reader.
                        >>>>>
                        >>>>>Whilst this is going on, I have another application that is trying to insert
                        >>>>>rows into the table. Normally, the inserts happen straight away, but when
                        >>>>>the reader is open each insert takes a very long time to complete.
                        >>>>>
                        >>>>>I realise that this is not an unreasonable thing to happen, given that I am
                        >>>>>trying to write to the table whilst reading from it, but the rows being
                        >>>>>written will never be included in the WHERE clause in my select statement,
                        >>>>>and even if they were (which they won't), I wouldn't want them included in
                        >>>>>the selected rows.
                        >>>>>
                        >>>>>Is there a way to read rows so that inserts can still occur without
                        >>>>>blocking ?
                        >>>>>
                        >>>>>I am using VB.NET in VS2005, and SQL Server 2005.
                        >>>>>
                        >>>>>TIA
                        >>>>>
                        >>>>>Charles
                        >>>>>
                        >>>>
                        >>>You might take a look at the Transact-SQL statement SET TRANSACTION
                        >>>ISOLATION LEVEL.
                        >>>
                        >>>
                        >>
                        >
                        >

                        Comment

                        • Andrew Morton

                          #13
                          Re: ExecuteReader Blocks Inserts on a Table

                          Charles Law wrote:
                          I have a sproc that returns data from a table using a simple SELECT.
                          There are quite a few rows returned, e.g. ~150,000.
                          >
                          In my first application, I use a reader on the sproc and iterate
                          through the rows, writing them out to a file. This takes about 5
                          minutes until I close the reader.
                          If you are doing

                          while moreRecords
                          begin
                          read record
                          write record to file
                          end

                          then maybe the writing to disk is the slow step, so how about

                          while moreRecords
                          begin
                          read record
                          append record to stringbuilder
                          end
                          write stringbuilder to file

                          ?

                          I take it getting the sp to write to a file is not an option?

                          Andrew


                          Comment

                          • Charles Law

                            #14
                            Re: ExecuteReader Blocks Inserts on a Table

                            Hi Andrew

                            Because of the speed of the connection, I think the extended times are
                            simply because of the time it takes to transfer that many records down the
                            wire. The file write is almost certainly not the slow bit.

                            If the sproc were to create the file then it would end up server-side, and I
                            need it client-side. Copying it would take much longer than the current 5
                            minutes.

                            Charles


                            "Andrew Morton" <akm@in-press.co.uk.inv alidwrote in message
                            news:6bupvpF3bs rauU1@mid.indiv idual.net...
                            Charles Law wrote:
                            >I have a sproc that returns data from a table using a simple SELECT.
                            >There are quite a few rows returned, e.g. ~150,000.
                            >>
                            >In my first application, I use a reader on the sproc and iterate
                            >through the rows, writing them out to a file. This takes about 5
                            >minutes until I close the reader.
                            >
                            If you are doing
                            >
                            while moreRecords
                            begin
                            read record
                            write record to file
                            end
                            >
                            then maybe the writing to disk is the slow step, so how about
                            >
                            while moreRecords
                            begin
                            read record
                            append record to stringbuilder
                            end
                            write stringbuilder to file
                            >
                            ?
                            >
                            I take it getting the sp to write to a file is not an option?
                            >
                            Andrew
                            >

                            Comment

                            • =?ISO-8859-1?Q?G=F6ran_Andersson?=

                              #15
                              Re: ExecuteReader Blocks Inserts on a Table

                              Charles Law wrote:
                              I have a sproc that returns data from a table using a simple SELECT. There
                              are quite a few rows returned, e.g. ~150,000.
                              >
                              In my first application, I use a reader on the sproc and iterate through the
                              rows, writing them out to a file. This takes about 5 minutes until I close
                              the reader.
                              >
                              Whilst this is going on, I have another application that is trying to insert
                              rows into the table. Normally, the inserts happen straight away, but when
                              the reader is open each insert takes a very long time to complete.
                              >
                              I realise that this is not an unreasonable thing to happen, given that I am
                              trying to write to the table whilst reading from it, but the rows being
                              written will never be included in the WHERE clause in my select statement,
                              and even if they were (which they won't), I wouldn't want them included in
                              the selected rows.
                              >
                              Is there a way to read rows so that inserts can still occur without
                              blocking?
                              >
                              I am using VB.NET in VS2005, and SQL Server 2005.
                              >
                              TIA
                              >
                              Charles
                              >
                              As already has been mentioned, there is different levels of locking. As
                              you are reading so many lines, the row locks will probably escalate into
                              page locks or a table lock to preserve resources. When that happens, it
                              will also lock other rows than the ones that you have selected.

                              You can specify (ROWLOCK) in your query. That should keep the database
                              from escalating the locks.

                              --
                              Göran Andersson
                              _____
                              Göran Anderssons privata hemsida.

                              Comment

                              Working...