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.

query runs slow when getdate() used

8 posts in General Discussion Last posting was on 2011-12-17 14:31:28.0Z
Ericrad Posted on 2011-11-15 19:18:26.0Z
Sender: 1d63.4ec2b710.1804289383@sybase.com
From: ericrad
Newsgroups: sybase.public.ase.general
Subject: query runs slow when getdate() used
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ec2bb02.1efd.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 15 Nov 2011 11:18:26 -0800
X-Trace: forums-1-dub 1321384706 10.22.241.41 (15 Nov 2011 11:18:26 -0800)
X-Original-Trace: 15 Nov 2011 11:18:26 -0800, 10.22.241.41
Lines: 43
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30687
Article PK: 73579

Hi,
I am working on the following query and it runs very
quickly.

select distinct t2.pay_method_number "credit_card_number",
t1.order_number,
t4.ship_company_name,
t2.method_of_payment,
t5.amount 'GC Amt$'

from order_header t1, payment_header t2, shipping_detail t3
(index shipping_detail_date_index), shipping_header t4,
payment_detail t5

where t1.order_number = t2.order_number
and t1.order_number = t4.order_number
and t2.order_number=t5.order_number
and t2.payment_number=t5.payment_number
and t3.date_shipped >= dateadd(dd,-1,"11/15/11")
and t3.date_shipped < dateadd(dd,0,"11/15/11")
and t1.order_number=t3.order_number
and t1.source_key = "RNWHUMC"
and t1.order_status not in ("H", "SH", "DE")
and t2.method_of_payment = "GC"
and t1.customer_number != 149XXXX

checking SHOW PLAN indexes are used on all the tables. And
checks table t3 first, then t1 -- using the proper indexes.

If I change the following line:
and t3.date_shipped >= dateadd(dd,-1,"11/15/11")

to be what I need it to be:
and t3.date_shipped >= dateadd(dd,-1,getdate())

The query runs until I kill it. Checking SHOW PLAN it now
does a table scan on table t1 first, a very large table.

Can someone help me with why the query plan changed so
much, and started using a table scan?

thanks
eric


HarryLai Posted on 2011-11-16 15:52:29.0Z
From: "HarryLai" <nospam_harrylhy@bigfoot.com>
Newsgroups: sybase.public.ase.general
References: <4ec2bb02.1efd.1681692777@sybase.com>
In-Reply-To: <4ec2bb02.1efd.1681692777@sybase.com>
Subject: Re: query runs slow when getdate() used
Lines: 1
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="big5"; reply-type=original
Content-Transfer-Encoding: 8bit
X-Priority: 3
X-MSMail-Priority: Normal
Importance: Normal
X-Newsreader: Microsoft Windows Live Mail 15.4.3538.513
X-MimeOLE: Produced By Microsoft MimeOLE V15.4.3538.513
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ec3dc3d$1@forums-1-dub>
Date: 16 Nov 2011 07:52:29 -0800
X-Trace: forums-1-dub 1321458749 10.22.241.152 (16 Nov 2011 07:52:29 -0800)
X-Original-Trace: 16 Nov 2011 07:52:29 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30691
Article PK: 73581

I have not met this case before, but have you tried to use the variable to store the getdate() first?

declare @shipdate datetime

select @shipdate = getdate()

select ...
from ...
where ...
and t3.date_shipped >= dateadd(dd, -1, @shipdate)


"ericrad" 礎b繞l瞼籀簣i繞K瞻繙簧e瞼D礎簧 4ec2bb02.1efd.1681692777@sybase.com 瞻瞻翹繞翹g...

Hi,
I am working on the following query and it runs very
quickly.

select distinct t2.pay_method_number "credit_card_number",
t1.order_number,
t4.ship_company_name,
t2.method_of_payment,
t5.amount 'GC Amt$'

from order_header t1, payment_header t2, shipping_detail t3
(index shipping_detail_date_index), shipping_header t4,
payment_detail t5

where t1.order_number = t2.order_number
and t1.order_number = t4.order_number
and t2.order_number=t5.order_number
and t2.payment_number=t5.payment_number
and t3.date_shipped >= dateadd(dd,-1,"11/15/11")
and t3.date_shipped < dateadd(dd,0,"11/15/11")
and t1.order_number=t3.order_number
and t1.source_key = "RNWHUMC"
and t1.order_status not in ("H", "SH", "DE")
and t2.method_of_payment = "GC"
and t1.customer_number != 149XXXX

checking SHOW PLAN indexes are used on all the tables. And
checks table t3 first, then t1 -- using the proper indexes.

If I change the following line:
and t3.date_shipped >= dateadd(dd,-1,"11/15/11")

to be what I need it to be:
and t3.date_shipped >= dateadd(dd,-1,getdate())

The query runs until I kill it. Checking SHOW PLAN it now
does a table scan on table t1 first, a very large table.

Can someone help me with why the query plan changed so
much, and started using a table scan?

thanks
eric


Ericrad Posted on 2011-11-16 16:25:02.0Z
Sender: 80f.4ec3e2c2.1804289383@sybase.com
From: Ericrad
Newsgroups: sybase.public.ase.general
Subject: Re: query runs slow when getdate() used
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ec3e3de.880.1681692777@sybase.com>
References: <4ec3dc3d$1@forums-1-dub>
MIME-Version: 1.0
Content-Type: text/plain; charset="ISO-8859-1"
Content-Transfer-Encoding: quoted-printable
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 16 Nov 2011 08:25:02 -0800
X-Trace: forums-1-dub 1321460702 10.22.241.41 (16 Nov 2011 08:25:02 -0800)
X-Original-Trace: 16 Nov 2011 08:25:02 -0800, 10.22.241.41
Lines: 77
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30692
Article PK: 73584

Hi,
Yes, I did try a variable. It seems that as soon as I use
anything other than a hard coded date in that line, the plan
goes from using indexes for everything to making the largest
table (t1) a table scan first.

My estimate I/O cost in the plan goes from 456 using the
hard coded value to 2147483647 using a variable or
getdate()

I can send the two plans if you like.

Thanks
eric

> I have not met this case before, but have you tried to use
> the variable to store the getdate() first?
>
> declare @shipdate datetime
>
> select @shipdate = getdate()
>
> select ...
> from ...
> where ...
> and t3.date_shipped >= dateadd(dd, -1, @shipdate)
>
>
> "ericrad" ¦b¶l¥ó±i¶K¤º®e¥D¦®
> 4ec2bb02.1efd.1681692777@sybase.com ¤¤¼¶¼g...
>
> Hi,
> I am working on the following query and it runs very
> quickly.
>
> select distinct t2.pay_method_number "credit_card_number",
> t1.order_number,
> t4.ship_company_name,
> t2.method_of_payment,
> t5.amount 'GC Amt$'
>
> from order_header t1, payment_header t2, shipping_detail
> t3 (index shipping_detail_date_index), shipping_header t4,
> payment_detail t5
>
> where t1.order_number = t2.order_number
> and t1.order_number = t4.order_number
> and t2.order_number=t5.order_number
> and t2.payment_number=t5.payment_number
> and t3.date_shipped >= dateadd(dd,-1,"11/15/11")
> and t3.date_shipped < dateadd(dd,0,"11/15/11")
> and t1.order_number=t3.order_number
> and t1.source_key = "RNWHUMC"
> and t1.order_status not in ("H", "SH", "DE")
> and t2.method_of_payment = "GC"
> and t1.customer_number != 149XXXX
>
> checking SHOW PLAN indexes are used on all the tables. And
> checks table t3 first, then t1 -- using the proper
> indexes.
>
> If I change the following line:
> and t3.date_shipped >= dateadd(dd,-1,"11/15/11")
>
> to be what I need it to be:
> and t3.date_shipped >= dateadd(dd,-1,getdate())
>
> The query runs until I kill it. Checking SHOW PLAN it now
> does a table scan on table t1 first, a very large table.
>
> Can someone help me with why the query plan changed so
> much, and started using a table scan?
>
> thanks
> eric
>


Sukhesh Posted on 2011-11-17 17:01:35.0Z
From: "Sukhesh" <sukheshnair@yahoo.com.sg>
Newsgroups: sybase.public.ase.general
References: <4ec3dc3d$1@forums-1-dub> <4ec3e3de.880.1681692777@sybase.com>
Subject: Re: query runs slow when getdate() used
Lines: 116
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3664
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3664
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ec53def$1@forums-1-dub>
Date: 17 Nov 2011 09:01:35 -0800
X-Trace: forums-1-dub 1321549295 10.22.241.152 (17 Nov 2011 09:01:35 -0800)
X-Original-Trace: 17 Nov 2011 09:01:35 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30694
Article PK: 73587

Hi,

Which version of ASE you use?
Normally, within queries, the optimizer choose to use an index if there a
definite SARG (Search Argument) mentioned.
Since getdate returns a value at run time, the optimizer might choose to do
a tablescan as against using an index.

In your case, in the first instance, the optimizer knows that there is a
value in t3.dateshipped which it can reach faster by following the index
fast.
And then it can use it to match with the other columns in table t1. In the
second instance, you provide getdate() as input. Since, the optimizer
doesn;t know the value untill runtime, it will choose to
search through the table t1 using existing known values and then search for
the t3.dateshipped value.

Using a variable will not resolve this as well as the variable will be
assigned only during runtime especially since you are using getdate().

One thing you can try is force index for table t3 and see if that works out.

One more thing worth noting is that you are using this in the search ...
"and t1.customer_number != 149XXXX"
Instead you can use "and t1.customer_number < 148XXXX or t1.customer_number
> 150XXXX".
NOT operators are always expensive as it has to traverse the entire index
chain or do the table scan.

Cheers
Sukhesh





<Ericrad> wrote in message news:4ec3e3de.880.1681692777@sybase.com...
Hi,
Yes, I did try a variable. It seems that as soon as I use
anything other than a hard coded date in that line, the plan
goes from using indexes for everything to making the largest
table (t1) a table scan first.

My estimate I/O cost in the plan goes from 456 using the
hard coded value to 2147483647 using a variable or
getdate()

I can send the two plans if you like.

Thanks
eric

> I have not met this case before, but have you tried to use
> the variable to store the getdate() first?
>
> declare @shipdate datetime
>
> select @shipdate = getdate()
>
> select ...
> from ...
> where ...
> and t3.date_shipped >= dateadd(dd, -1, @shipdate)
>
>
> "ericrad" ¦b¶l¥ó±i¶K¤º®e¥D¦®
> 4ec2bb02.1efd.1681692777@sybase.com ¤¤¼¶¼g...
>
> Hi,
> I am working on the following query and it runs very
> quickly.
>
> select distinct t2.pay_method_number "credit_card_number",
> t1.order_number,
> t4.ship_company_name,
> t2.method_of_payment,
> t5.amount 'GC Amt$'
>
> from order_header t1, payment_header t2, shipping_detail
> t3 (index shipping_detail_date_index), shipping_header t4,
> payment_detail t5
>
> where t1.order_number = t2.order_number
> and t1.order_number = t4.order_number
> and t2.order_number=t5.order_number
> and t2.payment_number=t5.payment_number
> and t3.date_shipped >= dateadd(dd,-1,"11/15/11")
> and t3.date_shipped < dateadd(dd,0,"11/15/11")
> and t1.order_number=t3.order_number
> and t1.source_key = "RNWHUMC"
> and t1.order_status not in ("H", "SH", "DE")
> and t2.method_of_payment = "GC"
> and t1.customer_number != 149XXXX
>
> checking SHOW PLAN indexes are used on all the tables. And
> checks table t3 first, then t1 -- using the proper
> indexes.
>
> If I change the following line:
> and t3.date_shipped >= dateadd(dd,-1,"11/15/11")
>
> to be what I need it to be:
> and t3.date_shipped >= dateadd(dd,-1,getdate())
>
> The query runs until I kill it. Checking SHOW PLAN it now
> does a table scan on table t1 first, a very large table.
>
> Can someone help me with why the query plan changed so
> much, and started using a table scan?
>
> thanks
> eric
>


Eugene Korolkov Posted on 2011-12-17 14:31:28.0Z
From: "Eugene Korolkov" <ekorolkov@optimum.net>
Newsgroups: sybase.public.ase.general
References: <4ec53def$1@forums-1-dub> <4ec56c6e.c61.1681692777@sybase.com> <4ec57154$1@forums-1-dub>
Subject: Re: query runs slow when getdate() used
Lines: 210
X-Newsreader: Microsoft Outlook Express 6.00.2900.5931
X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2900.6157
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4eeca7c0$1@forums-1-dub>
Date: 17 Dec 2011 06:31:28 -0800
X-Trace: forums-1-dub 1324132288 10.22.241.152 (17 Dec 2011 06:31:28 -0800)
X-Original-Trace: 17 Dec 2011 06:31:28 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30772
Article PK: 73663

To use getdate() in upper procedure, so that it would be a parameter to
lower proc.which has a whole query => optimizer would know it during
optimization phase and create plan as with constant.

Regards,
Eugene

"Bret Halford" <bret@sybase.com> wrote in message
news:4ec57154$1@forums-1-dub...
> There are still options.
>
> For instance,
> A) in 15.0.2 and above, you can use deferred compilation.
> "Deferring the optimization of statements benefits the query processor
> because the values for local variables are available for optimization for
> their respective statements. "
>
> B) you can use forceplan to strongly recommend a particular
> join order to the optimizer
>
> C) you can specify the use of an Abstract Plan to completely
> or partially override the optimizer.
>
>
> D) the client app could build in an additional clause that uses
> a literal value of a recent date as a baseline for the optimizer
> with the getdate() only being used for fine tuning:
>
>
> select distinct t2.pay_method_number
> "credit_card_number", t1.order_number,
> t4.ship_company_name,
> t2.method_of_payment,
> t5.amount 'GC Amt$'
>
> from order_header t1, payment_header t2, shipping_detail
> t3 (index shipping_detail_date_index), shipping_header
> t4, payment_detail t5
>
> where t1.order_number = t2.order_number
> and t1.order_number = t4.order_number
> and t2.order_number=t5.order_number
> and t2.payment_number=t5.payment_number
>
> -- dummy value optimizer can use to determine selectivity
> and t3.date_shipped >= "11/12/2011")
> and t3.date_shipped >= dateadd(dd,-1,getdate())
> and t3.date_shipped < getdate()
>
> and t1.order_number=t3.order_number
> and t1.source_key = "RNWHUMC"
> and t1.order_status not in ("H", "SH", "DE")
> and t2.method_of_payment = "GC"
> and t1.customer_number != 149XXXX
>
> E) You could build up the query in a string variable, causing
> the variable value to be converted to a literal before it hits
> the optimizer. You then execute the string with dynamic execute.
> This approach requires some care with embedded quotation marks.
>
> declare @cmd varchar(12000)
> select @cmd = 'select ... ' + @datevalue + '...'
> execute(@cmd)
>
> F) I'm sure there are other options...
>
> -bret
>
>
>
> On 11/17/2011 1:19 PM, Ericrad wrote:
>> Hi Sukhesh,
>> Thank you for the response. I did try to force the index
>> on t3.date_shipped but it didn't help.
>>
>> Someone suggested I try update index statistics on that
>> index. Would that help?
>>
>> From reading your explanation it seems like using getdate()
>> or a variable will not help at all, and then I have no
>> options. Is that correct?
>>
>>
>> I will make the suggestion to the developer about the !=,
>> it may be due to a testing case and that will not be part of
>> the final query.
>>
>>> Hi,
>>>
>>> Which version of ASE you use?
>>> Normally, within queries, the optimizer choose to use an
>>> index if there a definite SARG (Search Argument)
>>> mentioned. Since getdate returns a value at run time, the
>>> optimizer might choose to do a tablescan as against using
>>> an index.
>>>
>>> In your case, in the first instance, the optimizer knows
>>> that there is a value in t3.dateshipped which it can
>>> reach faster by following the index fast.
>>> And then it can use it to match with the other columns in
>>> table t1. In the second instance, you provide getdate()
>>> as input. Since, the optimizer doesn;t know the value
>>> untill runtime, it will choose to search through the table
>>> t1 using existing known values and then search for the
>>> t3.dateshipped value.
>>>
>>> Using a variable will not resolve this as well as the
>>> variable will be assigned only during runtime especially
>>> since you are using getdate().
>>>
>>> One thing you can try is force index for table t3 and see
>>> if that works out.
>>>
>>> One more thing worth noting is that you are using this in
>>> the search ... "and t1.customer_number != 149XXXX"
>>> Instead you can use "and t1.customer_number< 148XXXX or
>>> t1.customer_number
>>> > 150XXXX".
>>> NOT operators are always expensive as it has to traverse
>>> the entire index chain or do the table scan.
>>>
>>> Cheers
>>> Sukhesh
>>>
>>>
>>>
>>>
>>>
>>> <Ericrad> wrote in message
>>> news:4ec3e3de.880.1681692777@sybase.com... Hi,
>>> Yes, I did try a variable. It seems that as soon as I use
>>> anything other than a hard coded date in that line, the
>>> plan goes from using indexes for everything to making the
>>> largest table (t1) a table scan first.
>>>
>>> My estimate I/O cost in the plan goes from 456 using the
>>> hard coded value to 2147483647 using a variable or
>>> getdate()
>>>
>>> I can send the two plans if you like.
>>>
>>> Thanks
>>> eric
>>>
>>>
>>>> I have not met this case before, but have you tried to
>>>> use the variable to store the getdate() first?
>>>>
>>>> declare @shipdate datetime
>>>>
>>>> select @shipdate = getdate()
>>>>
>>>> select ...
>>>> from ...
>>>> where ...
>>>> and t3.date_shipped>= dateadd(dd, -1, @shipdate)
>>>>
>>>>
>>>> "ericrad" ¦b¶l¥ó±i¶K¤º®e¥D¦®
>>>> 4ec2bb02.1efd.1681692777@sybase.com ¤¤¼¶¼g...
>>>>
>>>> Hi,
>>>> I am working on the following query and it runs very
>>>> quickly.
>>>>
>>>> select distinct t2.pay_method_number
>>>> "credit_card_number", t1.order_number,
>>>> t4.ship_company_name,
>>>> t2.method_of_payment,
>>>> t5.amount 'GC Amt$'
>>>>
>>>> from order_header t1, payment_header t2, shipping_detail
>>>> t3 (index shipping_detail_date_index), shipping_header
>>>> t4, payment_detail t5
>>>>
>>>> where t1.order_number = t2.order_number
>>>> and t1.order_number = t4.order_number
>>>> and t2.order_number=t5.order_number
>>>> and t2.payment_number=t5.payment_number
>>>> and t3.date_shipped>= dateadd(dd,-1,"11/15/11")
>>>> and t3.date_shipped< dateadd(dd,0,"11/15/11")
>>>> and t1.order_number=t3.order_number
>>>> and t1.source_key = "RNWHUMC"
>>>> and t1.order_status not in ("H", "SH", "DE")
>>>> and t2.method_of_payment = "GC"
>>>> and t1.customer_number != 149XXXX
>>>>
>>>> checking SHOW PLAN indexes are used on all the tables.
>>>> And checks table t3 first, then t1 -- using the proper
>>>> indexes.
>>>>
>>>> If I change the following line:
>>>> and t3.date_shipped>= dateadd(dd,-1,"11/15/11")
>>>>
>>>> to be what I need it to be:
>>>> and t3.date_shipped>= dateadd(dd,-1,getdate())
>>>>
>>>> The query runs until I kill it. Checking SHOW PLAN it
>>>> now does a table scan on table t1 first, a very large
>>> table.>
>>>> Can someone help me with why the query plan changed so
>>>> much, and started using a table scan?
>>>>
>>>> thanks
>>>> eric
>>>>
>>>
>>>
>