ORACLE IN 1000 Limit

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
DeeFour
Posts: 2
Joined: Mon 11 Jan 2010 15:59

ORACLE IN 1000 Limit

Post by 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

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by 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.

bolek75
Posts: 16
Joined: Mon 10 May 2010 08:58

Post by 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.

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by 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.

bolek75
Posts: 16
Joined: Mon 10 May 2010 08:58

Post by 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?

Shalex
Site Admin
Posts: 8247
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Wed 10 Nov 2010 17:28

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

bolek75
Posts: 16
Joined: Mon 10 May 2010 08:58

Post by 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

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by 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.

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Fri 10 Dec 2010 17:25

The corresponding article is now available in our blog:
http://www.devart.com/blogs/dotconnect/?p=3725

Post Reply