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.

performance issue while using DATEDIFF

2 posts in Performance and Tuning Last posting was on 2012-12-26 16:36:09.0Z
Sumit Das Posted on 2012-12-26 16:08:33.0Z
Sender: 3fb1.50db20c2.1804289383@sybase.com
From: Sumit Das
Newsgroups: sybase.public.ase.performance+tuning
Subject: performance issue while using DATEDIFF
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <50db2101.3fd6.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 26 Dec 2012 08:08:33 -0800
X-Trace: forums-1-dub 1356538113 172.20.134.41 (26 Dec 2012 08:08:33 -0800)
X-Original-Trace: 26 Dec 2012 08:08:33 -0800, 172.20.134.41
Lines: 35
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13335
Article PK: 1158618

We have a table with 13 million records that stores
historical values for every dates. Data from this table is
fetched through a select query in a stored procedure for a
particular date. This select query is executed 36 times
through a cursor for every execution of the stored
procedure.

Initially, the complete cycle used to complete in less than
6 minutes and each execution of the select query required
less than 10 seconds. 3 weeks ago, the execution time for
the complete cycle suddenly increased to 50 minutes and each
iteration of select query required more than 80 seconds.

The rate of increase of data in the table was fairly
uniform. So, the sudden increase of time was surprising. The
DBAs did all possible checks(checking the blocking
processes, dbcc to verify database health) to find the
cause. No anamoly was found in the database except that the
part of the query that selects the value after comparing
date is taking maximum amount of time. This was found with
"dbcc traceon". Doing a reorg rebuild of the indexes and
updating statitics of this table did not help.

Then we replaced the part of the query "datediff(dd,
column_name, @input_date) = 0" with "column_name =
@input_date" and this solved the problem. We could complete
the entire cycle in less than 6 minutes agian. But, we are
yet to find out the reason for sudden increase in execution
time without any change.

Could anybody please share their thought if they have faced
similar issues before?

Thanks,
Sumit


"Mark A. Parsons" <iron_horse Posted on 2012-12-26 16:36:09.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: performance issue while using DATEDIFF
References: <50db2101.3fd6.1681692777@sybase.com>
In-Reply-To: <50db2101.3fd6.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 121124-1, 11/24/2012), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <50db2779$1@forums-1-dub>
Date: 26 Dec 2012 08:36:09 -0800
X-Trace: forums-1-dub 1356539769 172.20.134.152 (26 Dec 2012 08:36:09 -0800)
X-Original-Trace: 26 Dec 2012 08:36:09 -0800, vip152.sybase.com
Lines: 62
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13336
Article PK: 1158620

Without the complete query, query plans, and the table/index structures ... all we can do is guess.

--------------

The clause 'datediff(dd,column_name,@input_date)=0' is unable to use the column_name as a lookup value when searching an
index; the issue is that that the indexed column must be all by itself on its side of the equation (eg,
column_name=@input_date).

--------------

I'm going to guess that the earlier/faster queries were able to scan an index that contained column_name ... while
scanning an entire index isn't very efficient it is usually more efficient than scanning an entire table.

As for the later/slower queries I'm guessing the optimizer chose to scan a different (and even more inefficient) index
or perhaps perform a table scan.

By modifying the query to use 'column_name = @input_date' you now give the optimizer the ability to use the column_name
for performing a more efficient index access (whether or not you see an index scan will depend on the index structure
and the other WHERE clauses of the query).

-------------

Other possibilities include the optimizer choosing a different (and more inefficient) join order, or a change in the
cache structure which is requiring more physical IOs for each query run, or added load on the dataserver which is
putting a heavier use on the data cache (thus requiring more physical IOs), etc, etc, etc.

On 12/26/2012 09:08, Sumit Das wrote:
> We have a table with 13 million records that stores
> historical values for every dates. Data from this table is
> fetched through a select query in a stored procedure for a
> particular date. This select query is executed 36 times
> through a cursor for every execution of the stored
> procedure.
>
> Initially, the complete cycle used to complete in less than
> 6 minutes and each execution of the select query required
> less than 10 seconds. 3 weeks ago, the execution time for
> the complete cycle suddenly increased to 50 minutes and each
> iteration of select query required more than 80 seconds.
>
> The rate of increase of data in the table was fairly
> uniform. So, the sudden increase of time was surprising. The
> DBAs did all possible checks(checking the blocking
> processes, dbcc to verify database health) to find the
> cause. No anamoly was found in the database except that the
> part of the query that selects the value after comparing
> date is taking maximum amount of time. This was found with
> "dbcc traceon". Doing a reorg rebuild of the indexes and
> updating statitics of this table did not help.
>
> Then we replaced the part of the query "datediff(dd,
> column_name, @input_date) = 0" with "column_name =
> @input_date" and this solved the problem. We could complete
> the entire cycle in less than 6 minutes agian. But, we are
> yet to find out the reason for sudden increase in execution
> time without any change.
>
> Could anybody please share their thought if they have faced
> similar issues before?
>
> Thanks,
> Sumit