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

Re: [firebird-support] Re: 1500 values in IN (...) clause fails

Expand Messages
  • Kjell Rilbe
    ... Yes, I m aware that this isn t optimal. It s the way ECO fetches data for a list of object identities (i.e. primary keys). With ECO, if you navigate from a
    Message 1 of 8 , Apr 1 12:27 AM
    View Source
    • 0 Attachment
      Den 2011-04-01 07:40 skrev karolbieniaszewski såhär:
      > Having 1500 values in "in" is not good choice
      > but try change this to
      >
      > in (
      > SELECT 601089 FROM RDB$DATABASE
      > UNION
      > SELECT 601092 FROM RDB$DATABASE
      > ...
      > )
      >
      > but better will be to fill some temporary table with this data

      Yes, I'm aware that this isn't optimal. It's the way ECO fetches data
      for a list of object identities (i.e. primary keys). With ECO, if you
      navigate from a list of objects to related objects, e.g. from a list of
      "Car" objects to their "Tires", ECO will, if you so require, load data
      for all the cars' Tyre objects. It will do this by issuing a select,
      listing the PK:s for the Tyre objects in an IN clause.

      It is possible that it would be better with a temp table, but 1) it's
      not the way it currently works, and 2) would it really perform better?
      Inserting 50000 PK:s in a temp table just to join it with a normal
      table, and then deleting the temp table seems like more work than to
      issue 50000/1500 = 34 selects with 1500 keys each in an in (...) clause.
      But maybe I'm wrong...

      Anyway, the issue is not if this is the optimal way to do things, the
      issue is that FB has a bug, albeit rather unimportant. It says to
      support 1500 values but supports no more than 1499.

      Kjell
      --
      --------------------------------------
      Kjell Rilbe
      DataDIA AB
      E-post: kjell@...
      Telefon: 08-761 06 55
      Mobil: 0733-44 24 64
    • Wei Yu
      HI, Kjell We have facing the same situation like yours, We don t have a good soluation other than putting them in a IN clause. By putting those into a temp
      Message 2 of 8 , Apr 1 8:42 AM
      View Source
      • 0 Attachment
        HI, Kjell

        We have facing the same situation like yours, We don't have a good
        soluation other than putting them in a IN clause. By putting those into a
        temp table, will hits the performance. If you have a good approach, please
        let me know.

        Regards,

        William


        On Fri, Apr 1, 2011 at 3:27 AM, Kjell Rilbe <kjell.rilbe@...> wrote:

        > Den 2011-04-01 07:40 skrev karolbieniaszewski såhär:
        > > Having 1500 values in "in" is not good choice
        > > but try change this to
        > >
        > > in (
        > > SELECT 601089 FROM RDB$DATABASE
        > > UNION
        > > SELECT 601092 FROM RDB$DATABASE
        > > ...
        > > )
        > >
        > > but better will be to fill some temporary table with this data
        >
        > Yes, I'm aware that this isn't optimal. It's the way ECO fetches data
        > for a list of object identities (i.e. primary keys). With ECO, if you
        > navigate from a list of objects to related objects, e.g. from a list of
        > "Car" objects to their "Tires", ECO will, if you so require, load data
        > for all the cars' Tyre objects. It will do this by issuing a select,
        > listing the PK:s for the Tyre objects in an IN clause.
        >
        > It is possible that it would be better with a temp table, but 1) it's
        > not the way it currently works, and 2) would it really perform better?
        > Inserting 50000 PK:s in a temp table just to join it with a normal
        > table, and then deleting the temp table seems like more work than to
        > issue 50000/1500 = 34 selects with 1500 keys each in an in (...) clause.
        > But maybe I'm wrong...
        >
        > Anyway, the issue is not if this is the optimal way to do things, the
        > issue is that FB has a bug, albeit rather unimportant. It says to
        > support 1500 values but supports no more than 1499.
        >
        > Kjell
        > --
        > --------------------------------------
        > Kjell Rilbe
        > DataDIA AB
        > E-post: kjell@...
        > Telefon: 08-761 06 55
        > Mobil: 0733-44 24 64
        >
        >
        >
        > ------------------------------------
        >
        > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
        >
        > Visit http://www.firebirdsql.org and click the Resources item
        > on the main (top) menu. Try Knowledgebase and FAQ links !
        >
        > Also search the knowledgebases at http://www.ibphoenix.com
        >
        > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
        > Yahoo! Groups Links
        >
        >
        >
        >


        [Non-text portions of this message have been removed]
      • JackR
        Where I used to work, they had a similar limitation on MS SQL Server, only the number was more like 150. So they built a function in their VB app that took a
        Message 3 of 8 , Apr 1 9:19 AM
        View Source
        • 0 Attachment
          Where I used to work, they had a similar limitation on MS SQL Server, only the number was more like 150. So they built a function in their VB app that took a string of more than 150 values in the form of ID IN(1,2,3,4...,etc.) and returned multiple strings in the form of
          ID IN(1,2,3,4...)
          OR ID IN (151,152,153...)
          etc.
          Would this approach work for you?
          If you are using Delphi, it seems like a StringList would make the job a lot easier.


          --- In firebird-support@yahoogroups.com, Wei Yu <william.wei.yu@...> wrote:
          >
          > HI, Kjell
          >
          > We have facing the same situation like yours, We don't have a good
          > soluation other than putting them in a IN clause. By putting those into a
          > temp table, will hits the performance. If you have a good approach, please
          > let me know.
          >
          > Regards,
          >
          > William
          >
          >
          > On Fri, Apr 1, 2011 at 3:27 AM, Kjell Rilbe <kjell.rilbe@...> wrote:
          >
          > > Den 2011-04-01 07:40 skrev karolbieniaszewski såhär:
          > > > Having 1500 values in "in" is not good choice
          > > > but try change this to
          > > >
          > > > in (
          > > > SELECT 601089 FROM RDB$DATABASE
          > > > UNION
          > > > SELECT 601092 FROM RDB$DATABASE
          > > > ...
          > > > )
          > > >
          > > > but better will be to fill some temporary table with this data
          > >
          > > Yes, I'm aware that this isn't optimal. It's the way ECO fetches data
          > > for a list of object identities (i.e. primary keys). With ECO, if you
          > > navigate from a list of objects to related objects, e.g. from a list of
          > > "Car" objects to their "Tires", ECO will, if you so require, load data
          > > for all the cars' Tyre objects. It will do this by issuing a select,
          > > listing the PK:s for the Tyre objects in an IN clause.
          > >
          > > It is possible that it would be better with a temp table, but 1) it's
          > > not the way it currently works, and 2) would it really perform better?
          > > Inserting 50000 PK:s in a temp table just to join it with a normal
          > > table, and then deleting the temp table seems like more work than to
          > > issue 50000/1500 = 34 selects with 1500 keys each in an in (...) clause.
          > > But maybe I'm wrong...
          > >
          > > Anyway, the issue is not if this is the optimal way to do things, the
          > > issue is that FB has a bug, albeit rather unimportant. It says to
          > > support 1500 values but supports no more than 1499.
          > >
          > > Kjell
          > > --
          > > --------------------------------------
          > > Kjell Rilbe
          > > DataDIA AB
          > > E-post: kjell@...
          > > Telefon: 08-761 06 55
          > > Mobil: 0733-44 24 64
          > >
          > >
          > >
          > > ------------------------------------
          > >
          > > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
          > >
          > > Visit http://www.firebirdsql.org and click the Resources item
          > > on the main (top) menu. Try Knowledgebase and FAQ links !
          > >
          > > Also search the knowledgebases at http://www.ibphoenix.com
          > >
          > > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
          > > Yahoo! Groups Links
          > >
          > >
          > >
          > >
          >
          >
          > [Non-text portions of this message have been removed]
          >
        • Wei Yu
          Thanks for the suggestion. That may helps. William ... [Non-text portions of this message have been removed]
          Message 4 of 8 , Apr 1 12:25 PM
          View Source
          • 0 Attachment
            Thanks for the suggestion. That may helps.

            William


            On Fri, Apr 1, 2011 at 12:19 PM, JackR <jack@...> wrote:

            >
            >
            >
            >
            > Where I used to work, they had a similar limitation on MS SQL Server, only
            > the number was more like 150. So they built a function in their VB app that
            > took a string of more than 150 values in the form of ID IN(1,2,3,4...,etc.)
            > and returned multiple strings in the form of
            > ID IN(1,2,3,4...)
            > OR ID IN (151,152,153...)
            > etc.
            > Would this approach work for you?
            > If you are using Delphi, it seems like a StringList would make the job a
            > lot easier.
            >
            >
            > --- In firebird-support@yahoogroups.com, Wei Yu <william.wei.yu@...>
            > wrote:
            > >
            > > HI, Kjell
            > >
            > > We have facing the same situation like yours, We don't have a good
            > > soluation other than putting them in a IN clause. By putting those into a
            > > temp table, will hits the performance. If you have a good approach,
            > please
            > > let me know.
            > >
            > > Regards,
            > >
            > > William
            > >
            > >
            > > On Fri, Apr 1, 2011 at 3:27 AM, Kjell Rilbe <kjell.rilbe@...> wrote:
            > >
            > > > Den 2011-04-01 07:40 skrev karolbieniaszewski s�h�r:
            > > > > Having 1500 values in "in" is not good choice
            > > > > but try change this to
            > > > >
            > > > > in (
            > > > > SELECT 601089 FROM RDB$DATABASE
            > > > > UNION
            > > > > SELECT 601092 FROM RDB$DATABASE
            > > > > ...
            > > > > )
            > > > >
            > > > > but better will be to fill some temporary table with this data
            > > >
            > > > Yes, I'm aware that this isn't optimal. It's the way ECO fetches data
            > > > for a list of object identities (i.e. primary keys). With ECO, if you
            > > > navigate from a list of objects to related objects, e.g. from a list of
            > > > "Car" objects to their "Tires", ECO will, if you so require, load data
            > > > for all the cars' Tyre objects. It will do this by issuing a select,
            > > > listing the PK:s for the Tyre objects in an IN clause.
            > > >
            > > > It is possible that it would be better with a temp table, but 1) it's
            > > > not the way it currently works, and 2) would it really perform better?
            > > > Inserting 50000 PK:s in a temp table just to join it with a normal
            > > > table, and then deleting the temp table seems like more work than to
            > > > issue 50000/1500 = 34 selects with 1500 keys each in an in (...)
            > clause.
            > > > But maybe I'm wrong...
            > > >
            > > > Anyway, the issue is not if this is the optimal way to do things, the
            > > > issue is that FB has a bug, albeit rather unimportant. It says to
            > > > support 1500 values but supports no more than 1499.
            > > >
            > > > Kjell
            > > > --
            > > > --------------------------------------
            > > > Kjell Rilbe
            > > > DataDIA AB
            > > > E-post: kjell@...
            >
            > > > Telefon: 08-761 06 55
            > > > Mobil: 0733-44 24 64
            > > >
            > > >
            > > >
            > > > ------------------------------------
            > > >
            > > > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
            > > >
            > > > Visit http://www.firebirdsql.org and click the Resources item
            > > > on the main (top) menu. Try Knowledgebase and FAQ links !
            > > >
            > > > Also search the knowledgebases at http://www.ibphoenix.com
            > > >
            > > > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
            > > > Yahoo! Groups Links
            > > >
            > > >
            > > >
            > > >
            > >
            > >
            > > [Non-text portions of this message have been removed]
            > >
            >
            >
            >


            [Non-text portions of this message have been removed]
          • Thomas Steinmaurer
            Kjell, ... http://tracker.firebirdsql.org/browse/CORE-1438 -- With regards, Thomas Steinmaurer Upscene Productions http://www.upscene.com
            Message 5 of 8 , Apr 2 12:48 AM
            View Source
            • 0 Attachment
              Kjell,

              > I just bumped up ECO to select up to 1500 objects from persistence in
              > one go, resulting in a select like this:
              >
              > SELECT "Uppgift_A"."ECO_ID",
              > "Uppgift_A"."ECO_TYPE",
              > "Uppgift_A"."Hållare"
              > FROM "Uppgift" "Uppgift_A"
              > WHERE "Uppgift_A"."Hållare" IN (601089, 601092, ...)
              >
              > I checked that it put exactly 1500 values in there, but FB fails:
              >
              > Message: isc_dsql_prepare failed
              >
              > SQL Message : -901
              > Unsuccessful execution caused by system error that does not preclude
              > successful execution of subsequent statements
              >
              > Engine Code : 335544569
              > Engine Message :
              > Dynamic SQL Error
              > SQL error code = -901
              > Implementation limit exceeded
              > Too many values (more than 1500) in member list to match against
              >
              >
              > So, either I've been misinformed or there's a bug in FB. Either FB
              > should support 1500 and has a bug, limiting it to 1499. Or FB is
              > supposed to support up to BUT NOT INCLUDING 1500 values, in which case
              > every place I've seen this limit mentioned has been incorrect.
              >
              > So, which is it?
              >
              > Not that it matters very much, it just nags me that it seems wrong.

              http://tracker.firebirdsql.org/browse/CORE-1438


              --
              With regards,

              Thomas Steinmaurer
              Upscene Productions
              http://www.upscene.com
              http://blog.upscene.com/thomas/

              Download LogManager Series, FB TraceManager today!
              Continuous Database Monitoring Solutions supporting
              Firebird, InterBase, Advantage Database, MS SQL Server
              and NexusDB!
            Your message has been successfully submitted and would be delivered to recipients shortly.