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.

Slow query

3 posts in DataWindow Last posting was on 2008-05-29 02:48:12.0Z
Carlo Borreo Posted on 2008-05-28 10:50:23.0Z
From: "Carlo Borreo" <carlo.borreo@unv.org>
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Slow query
Lines: 22
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5512
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <483d38ef$1@forums-1-dub>
Date: 28 May 2008 03:50:23 -0700
X-Trace: forums-1-dub 1211971823 10.22.241.152 (28 May 2008 03:50:23 -0700)
X-Original-Trace: 28 May 2008 03:50:23 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:86977
Article PK: 416234

I have a simple application with some sle and a dw. See query of the dw is
at the bottom.
The database is Sybase ASE 12 (update to 15 will come in the next months).
The sle provide the criteria for the retrieve: for instance, if I enter the
email address in sle_email it will be passed as retrieval argument and used
accordingly. If I leave it blank, the retrieval argument will be blank, and
the left part of the OR will be true, and therefore I expect that the
database server will ignore this row even before looking at the table.
These sle will grow up to for instance 20.
The table has many records (250,000 and growing).
The problem: the query takes time to execute. A query by my_email takes 10
seconds, which is a lot, considering that there is an index on this field.
Am I doing something wrong?

SELECT my_category,my_email
FROM my_table
WHERE ( :ras_category='' OR my_category=:ras_category )
AND ( :ras_name='' OR my_name=:ras_name )
AND ( :ras_email='' OR my_email=:ras_email )
etc...


Jeremy Lakeman Posted on 2008-05-29 00:34:25.0Z
From: Jeremy Lakeman <jeremy.lakeman@gmail.com>
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Re: Slow query
Date: Wed, 28 May 2008 17:34:25 -0700 (PDT)
Organization: http://groups.google.com
Lines: 25
Message-ID: <1eef5854-0499-4809-b672-d4c8e4be91d4@x35g2000hsb.googlegroups.com>
References: <483d38ef$1@forums-1-dub>
NNTP-Posting-Host: 203.122.242.105
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Trace: posting.google.com 1212021265 24497 127.0.0.1 (29 May 2008 00:34:25 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Thu, 29 May 2008 00:34:25 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: x35g2000hsb.googlegroups.com; posting-host=203.122.242.105; posting-account=euaBtgoAAAC_vDxfsxmpMKlBxHaHpmRS
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.14) Gecko/20080404 Firefox/2.0.0.14,gzip(gfe),gzip(gfe)
X-HTTP-Via: 1.1 asp.essential.intranet:3128 (squid/2.5.STABLE8)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!newsfeed.gamma.ru!Gamma.RU!newsfeed.icl.net!newsfeed.fjserv.net!newsfeed.freenet.de!newspeer1.nac.net!border2.nntp.dca.giganews.com!nntp.giganews.com!postnews.google.com!x35g2000hsb.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:86984
Article PK: 416241


On May 28, 7:50 pm, "Carlo Borreo" <carlo.bor...@unv.org> wrote:
> I have a simple application with some sle and a dw. See query of the dw is
> at the bottom.
> The database is Sybase ASE 12 (update to 15 will come in the next months).
> The sle provide the criteria for the retrieve: for instance, if I enter the
> email address in sle_email it will be passed as retrieval argument and used
> accordingly. If I leave it blank, the retrieval argument will be blank, and
> the left part of the OR will be true, and therefore I expect that the
> database server will ignore this row even before looking at the table.
> These sle will grow up to for instance 20.
> The table has many records (250,000 and growing).
> The problem: the query takes time to execute. A query by my_email takes 10
> seconds, which is a lot, considering that there is an index on this field.
> Am I doing something wrong?
>
> SELECT my_category,my_email
> FROM my_table
> WHERE ( :ras_category='' OR my_category=:ras_category )
> AND ( :ras_name='' OR my_name=:ras_name )
> AND ( :ras_email='' OR my_email=:ras_email )
> etc...

You might be better off editing your where clause to remove clauses
you aren't using. Then the DB will have a better chance of using the
right index.


Jim Madderra Posted on 2008-05-29 02:48:12.0Z
From: "Jim Madderra" <jmadderra@astound.net>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <483d38ef$1@forums-1-dub> <1eef5854-0499-4809-b672-d4c8e4be91d4@x35g2000hsb.googlegroups.com>
Subject: Re: Slow query
Lines: 43
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <483e196c$1@forums-1-dub>
Date: 28 May 2008 19:48:12 -0700
X-Trace: forums-1-dub 1212029292 10.22.241.152 (28 May 2008 19:48:12 -0700)
X-Original-Trace: 28 May 2008 19:48:12 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:86985
Article PK: 416242

Rather than 'removing' unused clauses at runtime, I'd remove ALL of the
variable based WHERE clause (leaving explicit table joins) and ADD "AND
my_category = '" + ls_my_category + "'" to the where clause on-the-fly at
runtime (and/or any other wanted criteria)

In ANY case, I'd want to see the OR statements disappear if at all possible
(they are never a good thing for performance).

"Jeremy Lakeman" <jeremy.lakeman@gmail.com> wrote in message
news:1eef5854-0499-4809-b672-d4c8e4be91d4@x35g2000hsb.googlegroups.com...
> On May 28, 7:50 pm, "Carlo Borreo" <carlo.bor...@unv.org> wrote:
>> I have a simple application with some sle and a dw. See query of the dw
>> is
>> at the bottom.
>> The database is Sybase ASE 12 (update to 15 will come in the next
>> months).
>> The sle provide the criteria for the retrieve: for instance, if I enter
>> the
>> email address in sle_email it will be passed as retrieval argument and
>> used
>> accordingly. If I leave it blank, the retrieval argument will be blank,
>> and
>> the left part of the OR will be true, and therefore I expect that the
>> database server will ignore this row even before looking at the table.
>> These sle will grow up to for instance 20.
>> The table has many records (250,000 and growing).
>> The problem: the query takes time to execute. A query by my_email takes
>> 10
>> seconds, which is a lot, considering that there is an index on this
>> field.
>> Am I doing something wrong?
>>
>> SELECT my_category,my_email
>> FROM my_table
>> WHERE ( :ras_category='' OR my_category=:ras_category )
>> AND ( :ras_name='' OR my_name=:ras_name )
>> AND ( :ras_email='' OR my_email=:ras_email )
>> etc...
>
> You might be better off editing your where clause to remove clauses
> you aren't using. Then the DB will have a better chance of using the
> right index.