Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Large "in" SARG

2 posts in General Discussion Last posting was on 2010-02-19 20:31:49.0Z
jbuhl Posted on 2010-02-19 19:54:23.0Z
Sender: 5281.4b7b27a6.1804289383@sybase.com
From: jbuhl
Newsgroups: sybase.public.ase.general
Subject: Large "in" SARG
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4b7eec6f.6ef8.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 19 Feb 2010 11:54:23 -0800
X-Trace: forums-1-dub 1266609263 10.22.241.41 (19 Feb 2010 11:54:23 -0800)
X-Original-Trace: 19 Feb 2010 11:54:23 -0800, 10.22.241.41
Lines: 11
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28983
Article PK: 78222

We have an application that is dynamically creating a very
large "in" SARG that has been blowing up the procedure
cache. The argument list contains thousands of values. I
am pushing back on our developers that this is not a "best
practice" method and that this values list is really a data
set and should be dealt with according by some other method;
such as putting it in a temp table or something and not
putting it in the procedure cache. The developers are
pushing us to add more memory. Has anyone else dealt with
this type thing before from your application side of the
house?


"Mark A. Parsons" <iron_horse Posted on 2010-02-19 20:31:49.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Large "in" SARG
References: <4b7eec6f.6ef8.1681692777@sybase.com>
In-Reply-To: <4b7eec6f.6ef8.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 100218-0, 02/18/2010), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b7ef535$1@forums-1-dub>
Date: 19 Feb 2010 12:31:49 -0800
X-Trace: forums-1-dub 1266611509 10.22.241.152 (19 Feb 2010 12:31:49 -0800)
X-Original-Trace: 19 Feb 2010 12:31:49 -0800, vip152.sybase.com
Lines: 28
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28984
Article PK: 78221

I've found that it usually comes down to a question of who has a) the bigger (baseball) bat or b) the highest ranking
pointy-haired boss. ;-)

Adding the additional memory (if there's any 'extra' to allocate) is obviously the easiest solution (especially for the
applications folks 'cause then they don't have to make any code changes), but obviously 'wastes' memory that could
probably be put to better use elsewhere in the dataserver.

If you do manage to coerce[^H^H^H^H]nvince them to load the values into a table, you'll probably have to help them come
up with an efficient method. You *know* the first thing they're going to do is submit several thousand single-insert
batches across the network, into a table with a clustered index already in place, with no transactional management ...
and then blame you because *your* solution is too slow.

It might not be a bad idea to run some tests to a) see if you can come up with a faster, more efficient method and b)
have some (performance) ammo if/when you take the fight[^H^H^H^H^H]discussion to the pointy-haired types.

jbuhl wrote:
> We have an application that is dynamically creating a very
> large "in" SARG that has been blowing up the procedure
> cache. The argument list contains thousands of values. I
> am pushing back on our developers that this is not a "best
> practice" method and that this values list is really a data
> set and should be dealt with according by some other method;
> such as putting it in a temp table or something and not
> putting it in the procedure cache. The developers are
> pushing us to add more memory. Has anyone else dealt with
> this type thing before from your application side of the
> house?