Loading ...
Sorry, an error occurred while loading the content.

RE: [SQLQueriesNoCode] Speeding Up a Summary Table Sproc

Expand Messages
  • John Warner
    Not sure I follow XMLFile s purpose but XML inside a cursor loop can be expensive John Warner ... much ... the ...
    Message 1 of 8 , Feb 23, 2011
    • 0 Attachment
      Not sure I follow XMLFile 's purpose but XML inside a cursor loop can be
      expensive

      John Warner


      > -----Original Message-----
      > From: SQLQueriesNoCode@yahoogroups.com
      > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Charles Carroll
      > Sent: Wednesday, February 23, 2011 1:34 PM
      > To: SQLQueriesNoCode@yahoogroups.com
      > Subject: [SQLQueriesNoCode] Speeding Up a Summary Table Sproc
      >
      > The SProc takes 3 minutes to do a half million records but I would like
      much
      > more speed.
      >
      >
      > Basically it pulls out data from a Table that has Values
      >
      >
      > Test Value 1
      > Test Value 2
      > Test Value 3
      > Test Value 1
      > Test Value 1
      > Test Value 3
      > Test Value 1
      >
      > and turns it into a summary table with count and first and last date and
      the
      > last row that fed that summary
      > Count First Last
      > RawID
      > Test Value 1 4 mm/dd/yy tttt mm/dd/yy ttt bigint
      > Test Value 2 1
      > Test Value 3 2
      >
      >
      > Table LogRaw
      > ==========================
      > LogRaw_ID bigint
      > OccuredWhen datetime2(7)
      > LogValue nvarchar(max,null)
      > XMLFile (XML(.),null)
      >
      > Indexes on LogRaw_ID of course
      >
      > Table LogDeDuped
      > ===========================
      > LogDeDuped_ID bigint
      > LogValue (nvarchar(max),not null)
      > OccuredLast datetime2(7)
      > OccuredFirst datetime2(7)
      > LogRaw_ID bigint
      > LogValueChecksum (int, not null)
      >
      > Indexes on LogDeDuped_ID and LogValueChecksum of course.
      >
      > I use the checksum to get past the nvarchar(max) not being indexable....
      >
      > The main long running Sproc:
      > ALTER PROCEDURE [dbo].[LogRawTransferDeduped]
      > AS
      > BEGIN
      > -- SET NOCOUNT ON added to prevent extra result sets from
      > -- interfering with SELECT statements.
      > SET NOCOUNT ON;
      >
      > DECLARE @HowManyRows bigint;
      > SET @HowManyRows =0;
      >
      > DECLARE @LogValue nvarchar(max);
      > DECLARE @LogValue_ID bigint;
      >
      > DECLARE @Deduped_ID bigint;
      >
      > DECLARE @Start_LogRaw_Id bigint;
      >
      > set @Start_LogRaw_ID=(select max(LogRaw_ID) from LogDeDuped);
      > If @Start_LogRaw_ID IS NULL
      > BEGIN
      > set @Start_LogRaw_ID=1
      > END
      >
      >
      >
      > DECLARE CursorRaw CURSOR FOR SELECT [LogRaw_ID],[LogValue]
      > from [LogRaw]
      > WHERE [LogRaw_ID]>@Start_LogRaw_ID;
      >
      > OPEN CursorRaw;
      >
      > FETCH NEXT FROM CursorRaw INTO @LogValue_ID,@LogValue;
      > PRINT 'Starting at LogRaw_ID=' + CAST (@Start_LogRaw_ID AS
      > nvarchar(max));
      > WHILE @@FETCH_STATUS = 0
      > BEGIN
      > SET @HowManyRows=@HowManyRows+1;
      > /*PRINT 'LogValue Before=' + @LogValue */
      > If @LogValue IS NULL
      > BEGIN
      > SET @LogValue=CAST((Select XMLFile from LogRaw where
      > LogRaw_ID=@LogValue_ID) AS nvarchar(max))
      > END
      > EXEC [dbo].[LogRawTransferRowDedupe]
      > @LogValue = @LogValue,
      > @LogRaw_ID = @LogValue_ID
      > FETCH NEXT FROM CursorRaw INTO @LogValue_ID,@LogValue;
      >
      > END
      > PRINT 'Processed ' + CAST (@HowManyRows AS nvarchar(max)) + '
      > rows';
      > CLOSE CursorRaw;
      > DEALLOCATE CursorRaw;
      > END
      >
      >
      >
      >
      > The Sproc it calls for each summary row:
      >
      > ALTER PROCEDURE [dbo].[LogRawTransferRowDeDupe]
      > @LogValue as nvarchar(max)
      > ,@LogRaw_ID as bigint
      > /*,@OccuredWhen as datetime2(7) */
      > AS
      > BEGIN
      > SET NOCOUNT ON;
      >
      > DECLARE @LogDeDuped_ID bigint;
      > DECLARE @LogValueChecksum int;
      >
      > SET @LogValueChecksum = CHECKSUM(@LogValue);
      >
      > /*select @LogDeDuped_ID=[LogDeDuped_ID] FROM [LogDeDuped]
      > where
      > rtrim(ltrim(@LogValue)) = rtrim(ltrim([LogValue])); */
      > select @LogDeDuped_ID=[LogDeDuped_ID] FROM [LogDeDuped]
      > where
      > @LogValueChecksum = [LogValueChecksum];
      > If @LogDeDuped_ID IS NULL
      > BEGIN
      > INSERT INTO [LogDeDuped]
      >
      ([LogValue],[OccuredLast],[Frequency],[OccuredFirst],[LogRaw_ID],[LogValue
      > Checksum])
      > VALUES (@LogValue,SYSDATETIME(),1,(select [OccuredWhen] from
      > [LogRaw] where
      > [LogRaw_ID]=@LogRaw_ID),@LogRaw_ID,CHECKSUM(@LogValue));
      > END
      > ELSE
      > BEGIN
      > UPDATE [LogDeDuped] SET [LogValue]=@LogValue,
      > [OccuredLast]= (select [OccuredWhen] from
      [LogRaw]
      > where [LogRaw_ID]=@LogRaw_ID),
      > [LogRaw_ID]=@LogRaw_ID,
      [Frequency]=[Frequency]+1,
      > [LogValueChecksum]=@LogValueChecksum
      > /* WHERE rtrim(ltrim(@LogValue)) =
      > rtrim(ltrim([LogValue])); */
      > where @LogValueChecksum = [LogValueChecksum];
      > END
      >
      > END
      >
      >
      > [Non-text portions of this message have been removed]
      >
      >
      >
      > ------------------------------------
      >
      > Yahoo! Groups Links
      >
      >
      >
    • Rob Richardson
      Can t you do this (or at least most of it) in a single statement using GROUP BY? In my database I ve got 49,000 coils in my inventory table. Each coil will be
      Message 2 of 8 , Feb 23, 2011
      • 0 Attachment
        Can't you do this (or at least most of it) in a single statement using
        GROUP BY?

        In my database I've got 49,000 coils in my inventory table. Each coil
        will be processed using one of a small set of cycles. Among the data
        for the coil is its width. The following query gives me all of the
        cycles in my inventory, along with number of coils processed using that
        cycle and the smallest and largest width coil that was processed using
        each cycle:

        select cycle, count(*), min(width), max(width) from inventory
        group by cycle

        This query works on a PostgreSQL database. You may have to tweak the
        syntax for yours. Also, I don't know what you mean by "the last row
        that fed the summary".

        I hope this helps!

        RobR
      • Charles Carroll
        Values are either simple or XMLFile Test 1 null Test 2 null null XMLFile null XMLFile probably about 1% of the values are XMLFile 99% of the
        Message 3 of 8 , Feb 23, 2011
        • 0 Attachment
          Values are either simple or XMLFile

          Test 1 null
          Test 2 null
          null XMLFile
          null XMLFile

          probably about 1% of the values are XMLFile 99% of the time XMLFile=null and
          the other value is used.

          So inside the LOOP if I see a NULL value I just fetch the XMLFile field for
          that row instead.

          I will run the cursor without fetching XMLFile and see how much faster it
          goes and it made no difference.

          I kind of thought the cursor only involves 2 field fetches i.e.
          CURSOR FOR SELECT [LogRaw_ID],[LogValue] from [LogRaw] WHERE
          [LogRaw_ID]>@Start_LogRaw_ID;

          But I spend 99% of my time in C# (and occasionally C++) and am rarely in SQL
          Server.

          On Wed, Feb 23, 2011 at 1:43 PM, John Warner <john@...> wrote:

          >
          >
          > Not sure I follow XMLFile 's purpose but XML inside a cursor loop can be
          > expensive
          >
          > John Warner
          >
          >
          > > -----Original Message-----
          > > From: SQLQueriesNoCode@yahoogroups.com
          > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Charles Carroll
          > > Sent: Wednesday, February 23, 2011 1:34 PM
          > > To: SQLQueriesNoCode@yahoogroups.com
          > > Subject: [SQLQueriesNoCode] Speeding Up a Summary Table Sproc
          > >
          > > The SProc takes 3 minutes to do a half million records but I would like
          > much
          > > more speed.
          > >
          > >
          > > Basically it pulls out data from a Table that has Values
          > >
          > >
          > > Test Value 1
          > > Test Value 2
          > > Test Value 3
          > > Test Value 1
          > > Test Value 1
          > > Test Value 3
          > > Test Value 1
          > >
          > > and turns it into a summary table with count and first and last date and
          > the
          > > last row that fed that summary
          > > Count First Last
          > > RawID
          > > Test Value 1 4 mm/dd/yy tttt mm/dd/yy ttt bigint
          > > Test Value 2 1
          > > Test Value 3 2
          > >
          > >
          > > Table LogRaw
          > > ==========================
          > > LogRaw_ID bigint
          > > OccuredWhen datetime2(7)
          > > LogValue nvarchar(max,null)
          > > XMLFile (XML(.),null)
          > >
          > > Indexes on LogRaw_ID of course
          > >
          > > Table LogDeDuped
          > > ===========================
          > > LogDeDuped_ID bigint
          > > LogValue (nvarchar(max),not null)
          > > OccuredLast datetime2(7)
          > > OccuredFirst datetime2(7)
          > > LogRaw_ID bigint
          > > LogValueChecksum (int, not null)
          > >
          > > Indexes on LogDeDuped_ID and LogValueChecksum of course.
          > >
          > > I use the checksum to get past the nvarchar(max) not being indexable....
          > >
          > > The main long running Sproc:
          > > ALTER PROCEDURE [dbo].[LogRawTransferDeduped]
          > > AS
          > > BEGIN
          > > -- SET NOCOUNT ON added to prevent extra result sets from
          > > -- interfering with SELECT statements.
          > > SET NOCOUNT ON;
          > >
          > > DECLARE @HowManyRows bigint;
          > > SET @HowManyRows =0;
          > >
          > > DECLARE @LogValue nvarchar(max);
          > > DECLARE @LogValue_ID bigint;
          > >
          > > DECLARE @Deduped_ID bigint;
          > >
          > > DECLARE @Start_LogRaw_Id bigint;
          > >
          > > set @Start_LogRaw_ID=(select max(LogRaw_ID) from LogDeDuped);
          > > If @Start_LogRaw_ID IS NULL
          > > BEGIN
          > > set @Start_LogRaw_ID=1
          > > END
          > >
          > >
          > >
          > > DECLARE CursorRaw CURSOR FOR SELECT [LogRaw_ID],[LogValue]
          > > from [LogRaw]
          > > WHERE [LogRaw_ID]>@Start_LogRaw_ID;
          > >
          > > OPEN CursorRaw;
          > >
          > > FETCH NEXT FROM CursorRaw INTO @LogValue_ID,@LogValue;
          > > PRINT 'Starting at LogRaw_ID=' + CAST (@Start_LogRaw_ID AS
          > > nvarchar(max));
          > > WHILE @@FETCH_STATUS = 0
          > > BEGIN
          > > SET @HowManyRows=@HowManyRows+1;
          > > /*PRINT 'LogValue Before=' + @LogValue */
          > > If @LogValue IS NULL
          > > BEGIN
          > > SET @LogValue=CAST((Select XMLFile from LogRaw where
          > > LogRaw_ID=@LogValue_ID) AS nvarchar(max))
          > > END
          > > EXEC [dbo].[LogRawTransferRowDedupe]
          > > @LogValue = @LogValue,
          > > @LogRaw_ID = @LogValue_ID
          > > FETCH NEXT FROM CursorRaw INTO @LogValue_ID,@LogValue;
          > >
          > > END
          > > PRINT 'Processed ' + CAST (@HowManyRows AS nvarchar(max)) + '
          > > rows';
          > > CLOSE CursorRaw;
          > > DEALLOCATE CursorRaw;
          > > END
          > >
          > >
          > >
          > >
          > > The Sproc it calls for each summary row:
          > >
          > > ALTER PROCEDURE [dbo].[LogRawTransferRowDeDupe]
          > > @LogValue as nvarchar(max)
          > > ,@LogRaw_ID as bigint
          > > /*,@OccuredWhen as datetime2(7) */
          > > AS
          > > BEGIN
          > > SET NOCOUNT ON;
          > >
          > > DECLARE @LogDeDuped_ID bigint;
          > > DECLARE @LogValueChecksum int;
          > >
          > > SET @LogValueChecksum = CHECKSUM(@LogValue);
          > >
          > > /*select @LogDeDuped_ID=[LogDeDuped_ID] FROM [LogDeDuped]
          > > where
          > > rtrim(ltrim(@LogValue)) = rtrim(ltrim([LogValue])); */
          > > select @LogDeDuped_ID=[LogDeDuped_ID] FROM [LogDeDuped]
          > > where
          > > @LogValueChecksum = [LogValueChecksum];
          > > If @LogDeDuped_ID IS NULL
          > > BEGIN
          > > INSERT INTO [LogDeDuped]
          > >
          > ([LogValue],[OccuredLast],[Frequency],[OccuredFirst],[LogRaw_ID],[LogValue
          > > Checksum])
          > > VALUES (@LogValue,SYSDATETIME(),1,(select [OccuredWhen] from
          > > [LogRaw] where
          > > [LogRaw_ID]=@LogRaw_ID),@LogRaw_ID,CHECKSUM(@LogValue));
          > > END
          > > ELSE
          > > BEGIN
          > > UPDATE [LogDeDuped] SET [LogValue]=@LogValue,
          > > [OccuredLast]= (select [OccuredWhen] from
          > [LogRaw]
          > > where [LogRaw_ID]=@LogRaw_ID),
          > > [LogRaw_ID]=@LogRaw_ID,
          > [Frequency]=[Frequency]+1,
          > > [LogValueChecksum]=@LogValueChecksum
          > > /* WHERE rtrim(ltrim(@LogValue)) =
          > > rtrim(ltrim([LogValue])); */
          > > where @LogValueChecksum = [LogValueChecksum];
          > > END
          > >
          > > END
          > >
          > >
          > > [Non-text portions of this message have been removed]
          > >
          > >
          > >
          > > ------------------------------------
          > >
          > > Yahoo! Groups Links
          > >
          > >
          > >
          >
          >
          >


          [Non-text portions of this message have been removed]
        • Charles Carroll
          select LogValue, count(*) as frequency, MIN(OccuredWhen) AS First, MAX(OccuredWhen) AS Last, MAX(LogRaw_ID) AS LogRaw_ID_Last from TestAutoStoreRPC.dbo.LogRaw
          Message 4 of 8 , Feb 23, 2011
          • 0 Attachment
            select LogValue, count(*) as frequency, MIN(OccuredWhen) AS First,
            MAX(OccuredWhen) AS Last, MAX(LogRaw_ID) AS LogRaw_ID_Last from
            TestAutoStoreRPC.dbo.LogRaw
            group by LogValue

            order by frequency desc

            works very well and fast was not sure if it would group by the varchar(max)

            Can I use coalesce or some null trick to use XMLFile for LogValue when
            LogValue is null?

            The GroupBy makes that unlikely although I could just process the XML
            differently...

            On Wed, Feb 23, 2011 at 1:57 PM, Rob Richardson
            <Rob.Richardson@...>wrote:

            >
            >
            > Can't you do this (or at least most of it) in a single statement using
            > GROUP BY?
            >
            > In my database I've got 49,000 coils in my inventory table. Each coil
            > will be processed using one of a small set of cycles. Among the data
            > for the coil is its width. The following query gives me all of the
            > cycles in my inventory, along with number of coils processed using that
            > cycle and the smallest and largest width coil that was processed using
            > each cycle:
            >
            > select cycle, count(*), min(width), max(width) from inventory
            > group by cycle
            >
            > This query works on a PostgreSQL database. You may have to tweak the
            > syntax for yours. Also, I don't know what you mean by "the last row
            > that fed the summary".
            >
            > I hope this helps!
            >
            > RobR
            >
            >


            [Non-text portions of this message have been removed]
          • John Warner
            Well, just a guess and my knee jerk reaction when I see XML within SQL procs. John Warner ... for ... it ... be
            Message 5 of 8 , Feb 23, 2011
            • 0 Attachment
              Well, just a guess and my knee jerk reaction when I see XML within SQL
              procs.

              John Warner


              > -----Original Message-----
              > From: SQLQueriesNoCode@yahoogroups.com
              > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Charles Carroll
              > Sent: Wednesday, February 23, 2011 1:58 PM
              > To: SQLQueriesNoCode@yahoogroups.com
              > Subject: Re: [SQLQueriesNoCode] Speeding Up a Summary Table Sproc
              >
              > Values are either simple or XMLFile
              >
              > Test 1 null
              > Test 2 null
              > null XMLFile
              > null XMLFile
              >
              > probably about 1% of the values are XMLFile 99% of the time XMLFile=null
              > and
              > the other value is used.
              >
              > So inside the LOOP if I see a NULL value I just fetch the XMLFile field
              for
              > that row instead.
              >
              > I will run the cursor without fetching XMLFile and see how much faster
              it
              > goes and it made no difference.
              >
              > I kind of thought the cursor only involves 2 field fetches i.e.
              > CURSOR FOR SELECT [LogRaw_ID],[LogValue] from [LogRaw] WHERE
              > [LogRaw_ID]>@Start_LogRaw_ID;
              >
              > But I spend 99% of my time in C# (and occasionally C++) and am rarely in
              > SQL
              > Server.
              >
              > On Wed, Feb 23, 2011 at 1:43 PM, John Warner <john@...> wrote:
              >
              > >
              > >
              > > Not sure I follow XMLFile 's purpose but XML inside a cursor loop can
              be
              > > expensive
              > >
              > > John Warner
              > >
            • Charles Carroll
              The XMLFile did not make any difference in speed/the cursor almost identical times. The query: select LogValue, count(*) as frequency, MIN(OccuredWhen) AS
              Message 6 of 8 , Feb 23, 2011
              • 0 Attachment
                The XMLFile did not make any difference in speed/the cursor almost identical
                times. The query:

                select LogValue, count(*) as frequency, MIN(OccuredWhen) AS First,
                MAX(OccuredWhen) AS Last, MAX(LogRaw_ID) AS LogRaw_ID_Last from
                TestAutoStoreRPC.dbo.LogRaw
                where LogValue IS NOT NULL
                group by LogValue

                order by frequency desc

                achieves the same thing very fast which makes me happy.

                select XMLFILE, count(*) as frequency, MIN(OccuredWhen) AS First,
                MAX(OccuredWhen) AS Last, MAX(LogRaw_ID) AS LogRaw_ID_Last from
                TestAutoStoreRPC.dbo.LogRaw
                where LogValue IS NULL
                group by XMLFile

                does not work as a UNION candidate but I have some ideas and could always
                process the XML separately or actually include the XML file duped in the
                nvarchar(max).

                Of course I get:
                The XML data type cannot be compared or sorted, except when using the IS
                NULL operator.


                On Wed, Feb 23, 2011 at 2:16 PM, John Warner <john@...> wrote:

                >
                >
                > Well, just a guess and my knee jerk reaction when I see XML within SQL
                > procs.
                >
                >
                > John Warner
                >
                > > -----Original Message-----
                > > From: SQLQueriesNoCode@yahoogroups.com
                > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Charles Carroll
                > > Sent: Wednesday, February 23, 2011 1:58 PM
                > > To: SQLQueriesNoCode@yahoogroups.com
                > > Subject: Re: [SQLQueriesNoCode] Speeding Up a Summary Table Sproc
                > >
                > > Values are either simple or XMLFile
                > >
                > > Test 1 null
                > > Test 2 null
                > > null XMLFile
                > > null XMLFile
                > >
                > > probably about 1% of the values are XMLFile 99% of the time XMLFile=null
                > > and
                > > the other value is used.
                > >
                > > So inside the LOOP if I see a NULL value I just fetch the XMLFile field
                > for
                > > that row instead.
                > >
                > > I will run the cursor without fetching XMLFile and see how much faster
                > it
                > > goes and it made no difference.
                > >
                > > I kind of thought the cursor only involves 2 field fetches i.e.
                > > CURSOR FOR SELECT [LogRaw_ID],[LogValue] from [LogRaw] WHERE
                > > [LogRaw_ID]>@Start_LogRaw_ID;
                > >
                > > But I spend 99% of my time in C# (and occasionally C++) and am rarely in
                > > SQL
                > > Server.
                > >
                > > On Wed, Feb 23, 2011 at 1:43 PM, John Warner <john@...> wrote:
                > >
                > > >
                > > >
                > > > Not sure I follow XMLFile 's purpose but XML inside a cursor loop can
                > be
                > > > expensive
                > > >
                > > > John Warner
                > > >
                >
                >
                >


                [Non-text portions of this message have been removed]
              • Charles Carroll
                update TestAutoStoreRPC.dbo.LogRaw Set LogValue=CAST (XMLFile As nvarchar(max)) where LogValue IS NULL; select LogValue, count(*) as frequency,
                Message 7 of 8 , Feb 23, 2011
                • 0 Attachment
                  update TestAutoStoreRPC.dbo.LogRaw Set LogValue=CAST (XMLFile As
                  nvarchar(max)) where LogValue IS NULL;


                  select LogValue, count(*) as frequency, MIN(OccuredWhen) AS First,
                  MAX(OccuredWhen) AS Last, MAX(LogRaw_ID) AS LogRaw_ID_Last from
                  TestAutoStoreRPC.dbo.LogRaw
                  where LogValue IS NOT NULL
                  group by LogValue

                  order by frequency desc


                  works GREAT.

                  Thannnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnks

                  Sprocs go away and its super fast.

                  On Wed, Feb 23, 2011 at 2:25 PM, Charles Carroll <911@...> wrote:

                  > The XMLFile did not make any difference in speed/the cursor almost
                  > identical times. The query:
                  >
                  >
                  > select LogValue, count(*) as frequency, MIN(OccuredWhen) AS First,
                  > MAX(OccuredWhen) AS Last, MAX(LogRaw_ID) AS LogRaw_ID_Last from
                  > TestAutoStoreRPC.dbo.LogRaw
                  > where LogValue IS NOT NULL
                  >
                  > group by LogValue
                  >
                  > order by frequency desc
                  >
                  > achieves the same thing very fast which makes me happy.
                  >
                  > select XMLFILE, count(*) as frequency, MIN(OccuredWhen) AS First,
                  > MAX(OccuredWhen) AS Last, MAX(LogRaw_ID) AS LogRaw_ID_Last from
                  > TestAutoStoreRPC.dbo.LogRaw
                  > where LogValue IS NULL
                  > group by XMLFile
                  >
                  > does not work as a UNION candidate but I have some ideas and could always
                  > process the XML separately or actually include the XML file duped in the
                  > nvarchar(max).
                  >
                  > Of course I get:
                  > The XML data type cannot be compared or sorted, except when using the IS
                  > NULL operator.
                  >
                  >
                  >
                  > On Wed, Feb 23, 2011 at 2:16 PM, John Warner <john@...> wrote:
                  >
                  >>
                  >>
                  >> Well, just a guess and my knee jerk reaction when I see XML within SQL
                  >> procs.
                  >>
                  >>
                  >> John Warner
                  >>
                  >> > -----Original Message-----
                  >> > From: SQLQueriesNoCode@yahoogroups.com
                  >> > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Charles Carroll
                  >> > Sent: Wednesday, February 23, 2011 1:58 PM
                  >> > To: SQLQueriesNoCode@yahoogroups.com
                  >> > Subject: Re: [SQLQueriesNoCode] Speeding Up a Summary Table Sproc
                  >> >
                  >> > Values are either simple or XMLFile
                  >> >
                  >> > Test 1 null
                  >> > Test 2 null
                  >> > null XMLFile
                  >> > null XMLFile
                  >> >
                  >> > probably about 1% of the values are XMLFile 99% of the time XMLFile=null
                  >> > and
                  >> > the other value is used.
                  >> >
                  >> > So inside the LOOP if I see a NULL value I just fetch the XMLFile field
                  >> for
                  >> > that row instead.
                  >> >
                  >> > I will run the cursor without fetching XMLFile and see how much faster
                  >> it
                  >> > goes and it made no difference.
                  >> >
                  >> > I kind of thought the cursor only involves 2 field fetches i.e.
                  >> > CURSOR FOR SELECT [LogRaw_ID],[LogValue] from [LogRaw] WHERE
                  >> > [LogRaw_ID]>@Start_LogRaw_ID;
                  >> >
                  >> > But I spend 99% of my time in C# (and occasionally C++) and am rarely in
                  >> > SQL
                  >> > Server.
                  >> >
                  >> > On Wed, Feb 23, 2011 at 1:43 PM, John Warner <john@...> wrote:
                  >> >
                  >> > >
                  >> > >
                  >> > > Not sure I follow XMLFile 's purpose but XML inside a cursor loop can
                  >> be
                  >> > > expensive
                  >> > >
                  >> > > John Warner
                  >> > >
                  >>
                  >>
                  >>
                  >
                  >


                  [Non-text portions of this message have been removed]
                Your message has been successfully submitted and would be delivered to recipients shortly.