From derby-user-return-12493-apmail-db-derby-user-archive=db.apache.org@db.apache.org Sat Mar 20 18:13:17 2010 Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 35588 invoked from network); 20 Mar 2010 18:13:17 -0000 Received: from unknown (HELO mail.apache.org) (140.211.11.3) by 140.211.11.9 with SMTP; 20 Mar 2010 18:13:17 -0000 Received: (qmail 87702 invoked by uid 500); 20 Mar 2010 16:26:36 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 87678 invoked by uid 500); 20 Mar 2010 16:26:36 -0000 Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Discussion" Delivered-To: mailing list derby-user@db.apache.org Received: (qmail 87671 invoked by uid 99); 20 Mar 2010 16:26:36 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Sat, 20 Mar 2010 16:26:36 +0000 X-ASF-Spam-Status: No, hits=-0.0 required=10.0 tests=RCVD_IN_DNSWL_NONE,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: local policy) Received: from [209.85.219.225] (HELO mail-ew0-f225.google.com) (209.85.219.225) by apache.org (qpsmtpd/0.29) with ESMTP; Sat, 20 Mar 2010 16:26:27 +0000 Received: by ewy25 with SMTP id 25so1149822ewy.7 for ; Sat, 20 Mar 2010 09:26:06 -0700 (PDT) Received: by 10.213.1.143 with SMTP id 15mr1473044ebf.55.1269102366130; Sat, 20 Mar 2010 09:26:06 -0700 (PDT) Received: from [192.168.0.100] ([83.150.123.111]) by mx.google.com with ESMTPS id 15sm1640322ewy.4.2010.03.20.09.26.05 (version=SSLv3 cipher=RC4-MD5); Sat, 20 Mar 2010 09:26:05 -0700 (PDT) Message-ID: <4BA4F718.60208@absinth.fi> Date: Sat, 20 Mar 2010 18:26:00 +0200 From: Rami Ojares User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:1.9.1.8) Gecko/20100227 Thunderbird/3.0.3 MIME-Version: 1.0 To: Derby Discussion Subject: Table functions and optimization Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Hi, I have a table function that queries 2 tables and calculates some columns from them. All is going well. But when I start creating larger queries using that calculated table I find it very hard to tell derby that please invoke that table function only once per query. First does a table function have some kind of rowcount because rowcount is used by derby to decide how cheap it is to keep querying a table again and again. Best would be some kind of way in function definition to tell derby that invoke this table function only once per query and use the same result you received over and over again. Currently I cache the last results of the table function for 3 seconds which is obviously not optimal. - rami