X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 9A42A3A4FF5 for ; Mon, 24 Jan 2005 14:14:51 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 29342-01 for ; Mon, 24 Jan 2005 14:14:33 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.200]) by svr1.postgresql.org (Postfix) with ESMTP id 109323A53A7 for ; Mon, 24 Jan 2005 14:14:36 +0000 (GMT) Received: by wproxy.gmail.com with SMTP id 57so178897wri for ; Mon, 24 Jan 2005 06:14:36 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:references; b=O3K6YtnbUMxBTRP3z96hDy7TXZTjUH15vQ9G5sHIQbnNRSUlDruiL1Ev7KELsFgMonOvxvK87clPaUl1RHI8w5ngCvpcOtHMOtTmKFqPGIPlk8FyiPxNPRpZ3RqRUpWTfgDlJ+FjN3QKkQ6gpuwq/8/wxavBk8KwJcV6YChXM1E= Received: by 10.54.22.7 with SMTP id 7mr662762wrv; Mon, 24 Jan 2005 06:14:36 -0800 (PST) Received: by 10.54.31.32 with HTTP; Mon, 24 Jan 2005 06:14:36 -0800 (PST) Message-ID: <2989532e0501240614197b2ac0@mail.gmail.com> Date: Mon, 24 Jan 2005 19:44:36 +0530 From: Antony Paul Reply-To: Antony Paul To: Merlin Moncure Subject: Re: How to boost performance of ilike queries ? Cc: Russell Smith , pgsql-performance@postgresql.org In-Reply-To: <6EE64EF3AB31D5448D0007DD34EEB3412A75D2@Herge.rcsinc.local> Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit References: <6EE64EF3AB31D5448D0007DD34EEB3412A75D2@Herge.rcsinc.local> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.54 tagged_above=0 required=5 tests=AWL, FROM_ENDS_IN_NUMS, RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200501/439 X-Sequence-Number: 10129 I used PreparedStatements to avoid SQL injection attack and it is the best way to do in JDBC. rgds Antony Paul On Mon, 24 Jan 2005 09:01:49 -0500, Merlin Moncure wrote: > Russell wrote: > > I am not sure what the effect of it being prepared will be, however > I've > > had much success > > with the method above without the queries being prepared. Others may > be > > able to offer advice > > about if prepare will effect it. > > > There are two general cases I tend to use prepared queries. First case > is when there is an extremely complex plan generation step that you want > to skip. IMO, this is fairly rare in the normal course of doing things. > > Second case is when you have a relatively simple query that gets > executed very, very frequently, such as select a,b,c from t where k. > Even though the query plan is simple, using a prepared query can shave > 5-15% off your query time depending on various factors (on a low latency > network). If you fire off the statement a lot, this adds up. Not > generally worthwhile to go this route if you are executing over a high > latency network like the internet. > > If your application behavior can benefit from the second case, it can > probably benefit from using parse/bind as well...use ExecPrepared, etc. > libpq interface functions. > > The cumulative savings of using ExecPrepared() vs. using vanilla > PQExec() (for simple queries over a high latency network) can be 50% or > better. This is both from client's perspective and in server CPU load > (especially when data is read from cache). This is most interesting to > driver and middleware writers who broker data exchange between the > application and the data. The performance minded application developer > (who can make calls to the connection object) can take advantage of this > however. > > Merlin > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >