ORACLE IN 1000 Limit

ORACLE IN 1000 Limit

Postby DeeFour » Fri 15 Jan 2010 09:24

hi,

Oracle has a limit of 1000 for list in an IN request like this:

SELECT id FROM table WHERE a IN (10,21,34,....)

In my coding i solved this problem this way, but this is not really performant:


Code: Select all
var matchingRvals = from expAppl in allUnioned
group expAppl by expAppl into g
where g.Count() >= applIdsPerZone.Count
select g.Key;

int AnzMatchingRvals = matchingRvals.Count();
int z = 0;
int skipper = 0;
int Differenz = 0;
int IDBlockAnz = 1000;
int maxAnz = Convert.ToInt32(Math.Ceiling(Convert.ToDecimal(AnzMatchingRvals / IDBlockAnz)));

while (z <= (int)maxAnz)
{
   var query =
   (
   from completeAppl in dsc.LlApplCompletes
   where matchingRvals.Skip(skipper).Take(IDBlockAnz).Contains(completeAppl.LlcoApplId)

   select completeAppl.LlcoApplId
   );
   hits = query.Count() + hits;   
   Differenz = AnzMatchingRvals - skipper;
   if (Differenz < IDBlockAnz)
   {
     skipper = Convert.ToInt32(skipper) + Differenz;
   } else {
     skipper = Convert.ToInt32(skipper) + IDBlockAnz;
   }
   z++;
   
}
return hits;


It would be cool if anybody has an idea to improve the performance of this code.

thanks
Frank
DeeFour
 
Posts: 2
Joined: Mon 11 Jan 2010 15:59

Postby AndreyR » Mon 18 Jan 2010 10:20

We recommend you to create an EntitySQL query where the WHERE clause contains a condition:
Code: Select all
where property IN ({first thousand from array}) AND property IN ({second thousand from array})...

Yes, this will result in monstruos SQL query, but I think its performance will be higher than that of the solution you are using.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby bolek75 » Thu 28 Oct 2010 09:16

AndreyR wrote:We recommend you to create an EntitySQL query where the WHERE clause contains a condition:
Code: Select all
where property IN ({first thousand from array}) AND property IN ({second thousand from array})...



I have the same problem I tried to solve the way you recommend.
Unfortunately the IN-Clause is translated in a concatenation of ORs in this way

WHERE A.myID= 1 OR A.myID=2 OR A.myID=3 OR A.myID=4 ...
instead of
WHERE A.myID IN {1,2,3...} OR A.MyID IN {1000, 1001,...} and so on.
bolek75
 
Posts: 16
Joined: Mon 10 May 2010 08:58

Postby AndreyR » Fri 29 Oct 2010 16:23

Thank you for the report, we will investigate the reasons of this behaviour.
I will let you know about the results of our investigation.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby bolek75 » Tue 09 Nov 2010 07:50

AndreyR wrote:Thank you for the report, we will investigate the reasons of this behaviour.
I will let you know about the results of our investigation.


Any news in this matter?
bolek75
 
Posts: 16
Joined: Mon 10 May 2010 08:58

Postby Shalex » Wed 10 Nov 2010 17:28

We are investigating the issue. We will notify you about the results as soon as possible.
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44

Postby bolek75 » Thu 11 Nov 2010 07:13

Shalex wrote:We are investigating the issue. We will notify you about the results as soon as possible.


I am curious about it.

Regards from Berlin
bolek75
 
Posts: 16
Joined: Mon 10 May 2010 08:58

Postby AndreyR » Mon 15 Nov 2010 15:52

We have fixed the issue. The fix will be included in the nearest build.
This fix will be mentioned in our blog as well.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby StanislavK » Fri 10 Dec 2010 17:25

The corresponding article is now available in our blog:
http://www.devart.com/blogs/dotconnect/?p=3725
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48


Return to dotConnect for Oracle