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

Re: [SQLQueriesNoCode] Speeding Up a Summary Table Sproc

Expand Messages
  • 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 1 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 2 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 3 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 4 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.