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.

BETWEEN queries take a long time

7 posts in General Discussion Last posting was on 2006-08-19 15:22:02.0Z
Robert Paresi Posted on 2006-08-18 17:53:30.0Z
From: "Robert Paresi" <FirstInitialLastName@innquest.com>
Newsgroups: ianywhere.public.general
Subject: BETWEEN queries take a long time
Lines: 25
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2869
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2869
NNTP-Posting-Host: smtp.innquest.com
X-Original-NNTP-Posting-Host: smtp.innquest.com
Message-ID: <44e5fe9a@forums-1-dub>
Date: 18 Aug 2006 10:53:30 -0700
X-Trace: forums-1-dub 1155923610 24.173.150.22 (18 Aug 2006 10:53:30 -0700)
X-Original-Trace: 18 Aug 2006 10:53:30 -0700, smtp.innquest.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5562
Article PK: 1594

Hello,

Am I missing something in a query to make it more efficient. When I select
on a between range, the query seems to take a long time. In this case (7
days worth), it takes 9 seconds. A months worth of data takes 30 seconds,
and a years worth takes so long I have to stop the query (+8 minutes!)

SELECT DAILYACT.AUDITDATE as 'occupancyDate'
(SELECT COUNT(NUMBER) FROM HISTHD WHERE DAILYACT.AUDITDATE BETWEEN
HISTHD.CHECKIN AND HISTHD.CHECKOUT-1) AS 'roomsSold',
(SELECT COUNT(NUMBER) FROM HISTHD WHERE HISTHD.CHECKIN = DAILYACT.AUDITDATE)
AS 'roomArrivals',
(SELECT COUNT(NUMBER) FROM HISTHD WHERE HISTHD.CHECKOUT =
DAILYACT.AUDITDATE) AS 'roomDepartures'
FROM DAILYACT
where auditdate BETWEEN '2006-01-01' and '2006-01-07'
ORDER BY AUDITDATE


In the above case, the 2nd and 3rd count statements are not an issue, as
they do not change the time of the query. The first SELECT COUNT is the
issue, as I am trying to pull a bunch of records between a date range.


Greg Fenton Posted on 2006-08-18 20:22:51.0Z
From: Greg Fenton <greg.fenton_nospam_@googles-mail-site.com>
User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317)
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: BETWEEN queries take a long time
References: <44e5fe9a@forums-1-dub>
In-Reply-To: <44e5fe9a@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Original-NNTP-Posting-Host: wsp04839139wss.cr.net.cable.rogers.com
Message-ID: <44e61f40$1@forums-2-dub>
X-Original-Trace: 18 Aug 2006 13:12:48 -0700, wsp04839139wss.cr.net.cable.rogers.com
Lines: 20
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 18 Aug 2006 13:12:52 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 18 Aug 2006 13:22:51 -0700
X-Trace: forums-1-dub 1155932571 10.22.108.75 (18 Aug 2006 13:22:51 -0700)
X-Original-Trace: 18 Aug 2006 13:22:51 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5563
Article PK: 1593


Robert Paresi wrote:
> Hello,
>
> Am I missing something in a query to make it more efficient. When I select
> on a between range, the query seems to take a long time. In this case (7
> days worth), it takes 9 seconds. A months worth of data takes 30 seconds,
> and a years worth takes so long I have to stop the query (+8 minutes!)

What does the plan look like for the query?
Does it change dramatically when you modify the range values?

Are you running the query via DBISQL? Timing tests via dbisql are not
reliable as the tool itself does all sorts of "talking" back and forth
to the server. You should try the Performance tools instead (e.g
%ASANY%\Samples\ASA\PerformanceFetch)

g.f
--
Greg Fenton
Some Random Dude


Robert Paresi Posted on 2006-08-19 12:34:43.0Z
From: "Robert Paresi" <robert@innquest.com>
Newsgroups: ianywhere.public.general
References: <44e5fe9a@forums-1-dub> <44e61f40$1@forums-2-dub>
Subject: Re: BETWEEN queries take a long time
Lines: 37
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2869
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: 252-119.124-70.tampabay.res.rr.com
X-Original-NNTP-Posting-Host: 252-119.124-70.tampabay.res.rr.com
Message-ID: <44e70563@forums-1-dub>
Date: 19 Aug 2006 05:34:43 -0700
X-Trace: forums-1-dub 1155990883 70.124.119.252 (19 Aug 2006 05:34:43 -0700)
X-Original-Trace: 19 Aug 2006 05:34:43 -0700, 252-119.124-70.tampabay.res.rr.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5565
Article PK: 1596


> Timing tests via dbisql are not reliable as the tool itself does all
> sorts of "talking" back and forth to the server.

I am looking at my wrist, as I have a watch on it. :-)

It's just too long for the user.

I will look at the plan and see what it says. The database does have 1m
records in it ... and there are keys on CHECKIN and CHECKOUT.

-Robert

"Greg Fenton" <greg.fenton_nospam_@googles-mail-site.com> wrote in message
news:44e61f40$1@forums-2-dub...
> Robert Paresi wrote:
>> Hello,
>>
>> Am I missing something in a query to make it more efficient. When I
>> select on a between range, the query seems to take a long time. In this
>> case (7 days worth), it takes 9 seconds. A months worth of data takes 30
>> seconds, and a years worth takes so long I have to stop the query (+8
>> minutes!)
>
> What does the plan look like for the query?
> Does it change dramatically when you modify the range values?
>
> Are you running the query via DBISQL? Timing tests via dbisql are not
> reliable as the tool itself does all sorts of "talking" back and forth to
> the server. You should try the Performance tools instead (e.g
> %ASANY%\Samples\ASA\PerformanceFetch)
>
> g.f
> --
> Greg Fenton
> Some Random Dude


Greg Fenton Posted on 2006-08-19 15:22:02.0Z
From: Greg Fenton <greg.fenton@googles-mail-site>
User-Agent: Thunderbird 1.5.0.5 (Windows/20060719)
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: BETWEEN queries take a long time
References: <44e5fe9a@forums-1-dub> <44e61f40$1@forums-2-dub> <44e70563@forums-1-dub>
In-Reply-To: <44e70563@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Original-NNTP-Posting-Host: wsp04839139wss.cr.net.cable.rogers.com
Message-ID: <44e72a3b$1@forums-2-dub>
X-Original-Trace: 19 Aug 2006 08:11:55 -0700, wsp04839139wss.cr.net.cable.rogers.com
Lines: 33
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 19 Aug 2006 08:11:57 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 19 Aug 2006 08:22:02 -0700
X-Trace: forums-1-dub 1156000922 10.22.108.75 (19 Aug 2006 08:22:02 -0700)
X-Original-Trace: 19 Aug 2006 08:22:02 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5568
Article PK: 1599


Robert Paresi wrote:
>> Timing tests via dbisql are not reliable as the tool itself does all
>> sorts of "talking" back and forth to the server.
>
> I am looking at my wrist, as I have a watch on it. :-)
>
> It's just too long for the user.
>

But this is exactly the kind of timings that I am talking about. Dbisql
does all sorts of work, including result set rendering and cursor
manipulation, that can make a query "feel" slow, but when you actually
run the query by itself (outside of dbisql) the server is responding
very quickly.

IMO, though dbisql is a great tool, the primary purpose is functionality
and correctness, not performance.

There is a good chance that the query is slow, but you cannot be sure
when running it via dbisql.

Fetchtst.exe is easy to use and will isolate the timing down to the work
that you actually ask the tool to do.

Another fantastic approach is the use of request level logging and its
analysis stored procs. That will let you know exactly how long the
engine takes from the moment it receives the query to the instant it
responds.

g.f
--
Greg Fenton
Some Random Dude


Breck Carter [Team iAnywhere] Posted on 2006-08-18 21:42:30.0Z
From: "Breck Carter [Team iAnywhere]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: ianywhere.public.general
Subject: Re: BETWEEN queries take a long time
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <9dcce2563hr6c46f2jdtg47ckf6qkb0ege@4ax.com>
References: <44e5fe9a@forums-1-dub>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Original-NNTP-Posting-Host: bcarter.sentex.ca
X-Original-Trace: 18 Aug 2006 14:32:30 -0700, bcarter.sentex.ca
Lines: 40
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 18 Aug 2006 14:32:31 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 18 Aug 2006 14:42:30 -0700
X-Trace: forums-1-dub 1155937350 10.22.108.75 (18 Aug 2006 14:42:30 -0700)
X-Original-Trace: 18 Aug 2006 14:42:30 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5564
Article PK: 1595

If the rows are physically inserted in ascending order by auditdate,
then the query may benefit greatly from a clustered index on that
column. If there already is a nonclustered index, you can instantly
ALTER INDEX it to be clustered.


On 18 Aug 2006 10:53:30 -0700, "Robert Paresi"

<FirstInitialLastName@innquest.com> wrote:

>Hello,
>
>Am I missing something in a query to make it more efficient. When I select
>on a between range, the query seems to take a long time. In this case (7
>days worth), it takes 9 seconds. A months worth of data takes 30 seconds,
>and a years worth takes so long I have to stop the query (+8 minutes!)
>
>SELECT DAILYACT.AUDITDATE as 'occupancyDate'
>(SELECT COUNT(NUMBER) FROM HISTHD WHERE DAILYACT.AUDITDATE BETWEEN
>HISTHD.CHECKIN AND HISTHD.CHECKOUT-1) AS 'roomsSold',
>(SELECT COUNT(NUMBER) FROM HISTHD WHERE HISTHD.CHECKIN = DAILYACT.AUDITDATE)
>AS 'roomArrivals',
>(SELECT COUNT(NUMBER) FROM HISTHD WHERE HISTHD.CHECKOUT =
>DAILYACT.AUDITDATE) AS 'roomDepartures'
>FROM DAILYACT
>where auditdate BETWEEN '2006-01-01' and '2006-01-07'
>ORDER BY AUDITDATE
>
>
>In the above case, the 2nd and 3rd count statements are not an issue, as
>they do not change the time of the query. The first SELECT COUNT is the
>issue, as I am trying to pull a bunch of records between a date range.
>
>

--
Breck Carter [Team iAnywhere]
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
The book: http://www.risingroad.com/SQL_Anywhere_Studio_9_Developers_Guide.html
breck.carter@risingroad.com


Robert Paresi Posted on 2006-08-19 12:39:22.0Z
From: "Robert Paresi" <robert@innquest.com>
Newsgroups: ianywhere.public.general
References: <44e5fe9a@forums-1-dub> <9dcce2563hr6c46f2jdtg47ckf6qkb0ege@4ax.com>
Subject: Re: BETWEEN queries take a long time
Lines: 64
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2869
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: 252-119.124-70.tampabay.res.rr.com
X-Original-NNTP-Posting-Host: 252-119.124-70.tampabay.res.rr.com
Message-ID: <44e7067a@forums-1-dub>
Date: 19 Aug 2006 05:39:22 -0700
X-Trace: forums-1-dub 1155991162 70.124.119.252 (19 Aug 2006 05:39:22 -0700)
X-Original-Trace: 19 Aug 2006 05:39:22 -0700, 252-119.124-70.tampabay.res.rr.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5566
Article PK: 1597

Hi!

The AUDITDATE table is not the issue, as that file simply just has ONE
record per day and nothing else.

The "HISTHD" table is the 1m record table.

The query is reading one record for each day, and then going out to HISTHD
to count the people who are in the hotel for that day, within the CHECKIN
and CHECKOUT range of the Audit Date for each day.

Make sense?

Is there something I could be doing better on the HISTHD index?

-Robert

"Breck Carter [Team iAnywhere]" <NOSPAM__bcarter@risingroad.com> wrote in
message news:9dcce2563hr6c46f2jdtg47ckf6qkb0ege@4ax.com...
> If the rows are physically inserted in ascending order by auditdate,
> then the query may benefit greatly from a clustered index on that
> column. If there already is a nonclustered index, you can instantly
> ALTER INDEX it to be clustered.
>
>
> On 18 Aug 2006 10:53:30 -0700, "Robert Paresi"
> <FirstInitialLastName@innquest.com> wrote:
>
>>Hello,
>>
>>Am I missing something in a query to make it more efficient. When I
>>select
>>on a between range, the query seems to take a long time. In this case (7
>>days worth), it takes 9 seconds. A months worth of data takes 30 seconds,
>>and a years worth takes so long I have to stop the query (+8 minutes!)
>>
>>SELECT DAILYACT.AUDITDATE as 'occupancyDate'
>>(SELECT COUNT(NUMBER) FROM HISTHD WHERE DAILYACT.AUDITDATE BETWEEN
>>HISTHD.CHECKIN AND HISTHD.CHECKOUT-1) AS 'roomsSold',
>>(SELECT COUNT(NUMBER) FROM HISTHD WHERE HISTHD.CHECKIN =
>>DAILYACT.AUDITDATE)
>>AS 'roomArrivals',
>>(SELECT COUNT(NUMBER) FROM HISTHD WHERE HISTHD.CHECKOUT =
>>DAILYACT.AUDITDATE) AS 'roomDepartures'
>>FROM DAILYACT
>>where auditdate BETWEEN '2006-01-01' and '2006-01-07'
>>ORDER BY AUDITDATE
>>
>>
>>In the above case, the 2nd and 3rd count statements are not an issue, as
>>they do not change the time of the query. The first SELECT COUNT is the
>>issue, as I am trying to pull a bunch of records between a date range.
>>
>>
>
> --
> Breck Carter [Team iAnywhere]
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com
> The book:
> http://www.risingroad.com/SQL_Anywhere_Studio_9_Developers_Guide.html
> breck.carter@risingroad.com


Breck Carter [Team iAnywhere] Posted on 2006-08-19 14:49:45.0Z
From: "Breck Carter [Team iAnywhere]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: ianywhere.public.general
Subject: Re: BETWEEN queries take a long time
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <1h8ee2tui9l92g8eervjfugk4ibvjn5hhh@4ax.com>
References: <44e5fe9a@forums-1-dub> <9dcce2563hr6c46f2jdtg47ckf6qkb0ege@4ax.com> <44e7067a@forums-1-dub>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Original-NNTP-Posting-Host: bcarter.sentex.ca
X-Original-Trace: 19 Aug 2006 07:39:40 -0700, bcarter.sentex.ca
Lines: 84
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 19 Aug 2006 07:39:41 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 19 Aug 2006 07:49:45 -0700
X-Trace: forums-1-dub 1155998985 10.22.108.75 (19 Aug 2006 07:49:45 -0700)
X-Original-Trace: 19 Aug 2006 07:49:45 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5567
Article PK: 1598

Sorry... guilty of Cursory Glance, a heinous crime :)

What version and build? Can you show us the schema for the tables?
(copy and paste from Foxhound is one easy way, especially since it
shows rowcounts and space usage)

Can you send me the Graphical Plan with Statistics *.xml file? Or just
the text file produced by the "LogExpensiveQueries" facility as
described at
http://www.ianywhere.com/developer/sql_anywhere_developer_corner/logexpensivequeries_jasper.html

On 19 Aug 2006 05:39:22 -0700, "Robert Paresi" <robert@innquest.com>

wrote:

>Hi!
>
>The AUDITDATE table is not the issue, as that file simply just has ONE
>record per day and nothing else.
>
>The "HISTHD" table is the 1m record table.
>
>The query is reading one record for each day, and then going out to HISTHD
>to count the people who are in the hotel for that day, within the CHECKIN
>and CHECKOUT range of the Audit Date for each day.
>
>Make sense?
>
>Is there something I could be doing better on the HISTHD index?
>
>-Robert
>
>"Breck Carter [Team iAnywhere]" <NOSPAM__bcarter@risingroad.com> wrote in
>message news:9dcce2563hr6c46f2jdtg47ckf6qkb0ege@4ax.com...
>> If the rows are physically inserted in ascending order by auditdate,
>> then the query may benefit greatly from a clustered index on that
>> column. If there already is a nonclustered index, you can instantly
>> ALTER INDEX it to be clustered.
>>
>>
>> On 18 Aug 2006 10:53:30 -0700, "Robert Paresi"
>> <FirstInitialLastName@innquest.com> wrote:
>>
>>>Hello,
>>>
>>>Am I missing something in a query to make it more efficient. When I
>>>select
>>>on a between range, the query seems to take a long time. In this case (7
>>>days worth), it takes 9 seconds. A months worth of data takes 30 seconds,
>>>and a years worth takes so long I have to stop the query (+8 minutes!)
>>>
>>>SELECT DAILYACT.AUDITDATE as 'occupancyDate'
>>>(SELECT COUNT(NUMBER) FROM HISTHD WHERE DAILYACT.AUDITDATE BETWEEN
>>>HISTHD.CHECKIN AND HISTHD.CHECKOUT-1) AS 'roomsSold',
>>>(SELECT COUNT(NUMBER) FROM HISTHD WHERE HISTHD.CHECKIN =
>>>DAILYACT.AUDITDATE)
>>>AS 'roomArrivals',
>>>(SELECT COUNT(NUMBER) FROM HISTHD WHERE HISTHD.CHECKOUT =
>>>DAILYACT.AUDITDATE) AS 'roomDepartures'
>>>FROM DAILYACT
>>>where auditdate BETWEEN '2006-01-01' and '2006-01-07'
>>>ORDER BY AUDITDATE
>>>
>>>
>>>In the above case, the 2nd and 3rd count statements are not an issue, as
>>>they do not change the time of the query. The first SELECT COUNT is the
>>>issue, as I am trying to pull a bunch of records between a date range.
>>>
>>>
>>
>> --
>> Breck Carter [Team iAnywhere]
>> RisingRoad SQL Anywhere and MobiLink Professional Services
>> www.risingroad.com
>> The book:
>> http://www.risingroad.com/SQL_Anywhere_Studio_9_Developers_Guide.html
>> breck.carter@risingroad.com
>

--
Breck Carter [Team iAnywhere]
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
The book: http://www.risingroad.com/SQL_Anywhere_Studio_9_Developers_Guide.html
breck.carter@risingroad.com