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.

Perfomance in Historical Table

11 posts in General Discussion Last posting was on 2010-08-28 19:06:26.0Z
RGarces Posted on 2010-08-25 20:36:49.0Z
Sender: 7369.4c757a41.1804289383@sybase.com
From: RGarces
Newsgroups: sybase.public.ase.general
Subject: Perfomance in Historical Table
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4c757ee1.7447.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 25 Aug 2010 13:36:49 -0700
X-Trace: forums-1-dub 1282768609 10.22.241.41 (25 Aug 2010 13:36:49 -0700)
X-Original-Trace: 25 Aug 2010 13:36:49 -0700, 10.22.241.41
Lines: 33
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29499
Article PK: 78728

I am using ASE 15.0.3

In my saving acount system, I have a table who save the
transactions of accounts

This table is historical, because it has information of 3
years of transactions

The table has:

- One million of records
- DOL lock schema
- Only non-clustered index , one of them on the field "date"

I need to execute a lot of querys, all of them use a range
of dates, like:

select *
from transaction_table
where date between @i_date1 and @i_date2

My querys are very slow and they generates a lot of I/O

Then I have to:

- Change the lock schema to ALL PAGES ?
- Change the index of "date" from non-clustered to
clustered?
- What else?

Thanks a lot!!

Rod


ET Posted on 2010-08-25 21:23:03.0Z
From: ET <dbccjob@gmail.Com>
Reply-To: ET
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; fr; rv:1.9.2.8) Gecko/20100802 Thunderbird/3.1.2
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Perfomance in Historical Table
References: <4c757ee1.7447.1681692777@sybase.com>
In-Reply-To: <4c757ee1.7447.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4c7589b7$1@forums-1-dub>
Date: 25 Aug 2010 14:23:03 -0700
X-Trace: forums-1-dub 1282771383 10.22.241.152 (25 Aug 2010 14:23:03 -0700)
X-Original-Trace: 25 Aug 2010 14:23:03 -0700, vip152.sybase.com
Lines: 61
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29502
Article PK: 78731

Changing lock schema would not help for your read queries.

Range queries generally get benefit from covering indexes.

You could first reduce i/o by limitating the number of columns retrieved
(not using * but restrict to useful columns only) and create a composite
index that will include all the needed columns.

for instance : create index ix1 on transaction_table (date ,account_id)

for
select account_id
from transaction_table
where date between @i_date1 and @i_date2

But you need to provide more details about your table, the indexes you
have, and some queries.

You can find useful guidelines for index covering in the books

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.sqlug/html/sqlug/sqlug309.htm

and for clustered indexes :
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.sqlug/html/sqlug/sqlug309.htm

Emmanuel

Le 25/08/2010 22:36, RGarces a écrit :

> I am using ASE 15.0.3
>
> In my saving acount system, I have a table who save the
> transactions of accounts
>
> This table is historical, because it has information of 3
> years of transactions
>
> The table has:
>
> - One million of records
> - DOL lock schema
> - Only non-clustered index , one of them on the field "date"
>
> I need to execute a lot of querys, all of them use a range
> of dates, like:
>
> select *
> from transaction_table
> where date between @i_date1 and @i_date2
>
> My querys are very slow and they generates a lot of I/O
>
> Then I have to:
>
> - Change the lock schema to ALL PAGES ?
> - Change the index of "date" from non-clustered to
> clustered?
> - What else?
>
> Thanks a lot!!
>
> Rod


Carl Kayser Posted on 2010-08-26 10:17:01.0Z
From: "Carl Kayser" <kayser_c@bls.gov>
Newsgroups: sybase.public.ase.general
References: <4c757ee1.7447.1681692777@sybase.com> <4c7589b7$1@forums-1-dub>
Subject: Re: Perfomance in Historical Table
Lines: 80
Organization: BLS
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5931
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5931
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4c763f1d$1@forums-1-dub>
Date: 26 Aug 2010 03:17:01 -0700
X-Trace: forums-1-dub 1282817821 10.22.241.152 (26 Aug 2010 03:17:01 -0700)
X-Original-Trace: 26 Aug 2010 03:17:01 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29507
Article PK: 78738


"ET" <dbccjob@gmail.Com> wrote in message news:4c7589b7$1@forums-1-dub...
> Changing lock schema would not help for your read queries.
>

Really?! Unlike your next statement, which you have qualified with
"generally", this claim doesn't deserve an all situations coverage. I'd
prefer an APL unique clustered index on (date, some other_cols) for

select most-columns
from transaction_table
where date between @i_date1 and @i_date2

Especially if there does not already exist a non-clustered index which
provides for an index scan. It's generally better to have N indexes than
N+1 indexes. (Replace the DOL placement with an APL clustered instead of
adding on an index specifically for all of the needed columns.) Especially
with the OPs statement that ALL of his queries use date ranges.

> Range queries generally get benefit from covering indexes.
>
> You could first reduce i/o by limitating the number of columns retrieved
> (not using * but restrict to useful columns only) and create a composite
> index that will include all the needed columns.
>
> for instance : create index ix1 on transaction_table (date ,account_id)
>
> for
> select account_id
> from transaction_table
> where date between @i_date1 and @i_date2
>
> But you need to provide more details about your table, the indexes you
> have, and some queries.
>
> You can find useful guidelines for index covering in the books
>
> http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.sqlug/html/sqlug/sqlug309.htm
>
> and for clustered indexes :
> http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.sqlug/html/sqlug/sqlug309.htm
>
> Emmanuel
>
> Le 25/08/2010 22:36, RGarces a écrit :
>> I am using ASE 15.0.3
>>
>> In my saving acount system, I have a table who save the
>> transactions of accounts
>>
>> This table is historical, because it has information of 3
>> years of transactions
>>
>> The table has:
>>
>> - One million of records
>> - DOL lock schema
>> - Only non-clustered index , one of them on the field "date"
>>
>> I need to execute a lot of querys, all of them use a range
>> of dates, like:
>>
>> select *
>> from transaction_table
>> where date between @i_date1 and @i_date2
>>
>> My querys are very slow and they generates a lot of I/O
>>
>> Then I have to:
>>
>> - Change the lock schema to ALL PAGES ?
>> - Change the index of "date" from non-clustered to
>> clustered?
>> - What else?
>>
>> Thanks a lot!!
>>
>> Rod


ET Posted on 2010-08-26 19:02:02.0Z
From: ET <ET>
Reply-To: ET
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; fr; rv:1.9.2.8) Gecko/20100802 Thunderbird/3.1.2
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Perfomance in Historical Table
References: <4c757ee1.7447.1681692777@sybase.com> <4c7589b7$1@forums-1-dub> <4c763f1d$1@forums-1-dub>
In-Reply-To: <4c763f1d$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4c76ba2a$1@forums-1-dub>
Date: 26 Aug 2010 12:02:02 -0700
X-Trace: forums-1-dub 1282849322 10.22.241.152 (26 Aug 2010 12:02:02 -0700)
X-Original-Trace: 26 Aug 2010 12:02:02 -0700, vip152.sybase.com
Lines: 96
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29513
Article PK: 78744

You are right I chose the N+1 index solution but as a pragmatic way of
answering the question, as a clustered index was already created on the
table with the date col.

The solution I am proposing implies certain restriction in the column
list to easily get a covered query and narrow index. With a clustered
index, leaf pages also contains data so most of the columns can be
specified in the select columns list.

But I cannot see how APL locking scheme could be a major improvement
factor, except better fragmentation resistance. But how about concurrent
access if this history table is heavily accessed for instance ?

Thank you
Emmanuel

Le 26/08/2010 12:17, Carl Kayser a écrit :

> "ET"<dbccjob@gmail.Com> wrote in message news:4c7589b7$1@forums-1-dub...
>> Changing lock schema would not help for your read queries.
>>
>
> Really?! Unlike your next statement, which you have qualified with
> "generally", this claim doesn't deserve an all situations coverage. I'd
> prefer an APL unique clustered index on (date, some other_cols) for
>
> select most-columns
> from transaction_table
> where date between @i_date1 and @i_date2
>
> Especially if there does not already exist a non-clustered index which
> provides for an index scan. It's generally better to have N indexes than
> N+1 indexes. (Replace the DOL placement with an APL clustered instead of
> adding on an index specifically for all of the needed columns.) Especially
> with the OPs statement that ALL of his queries use date ranges.
>
>> Range queries generally get benefit from covering indexes.
>>
>> You could first reduce i/o by limitating the number of columns retrieved
>> (not using * but restrict to useful columns only) and create a composite
>> index that will include all the needed columns.
>>
>> for instance : create index ix1 on transaction_table (date ,account_id)
>>
>> for
>> select account_id
>> from transaction_table
>> where date between @i_date1 and @i_date2
>>
>> But you need to provide more details about your table, the indexes you
>> have, and some queries.
>>
>> You can find useful guidelines for index covering in the books
>>
>> http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.sqlug/html/sqlug/sqlug309.htm
>>
>> and for clustered indexes :
>> http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.sqlug/html/sqlug/sqlug309.htm
>>
>> Emmanuel
>>
>> Le 25/08/2010 22:36, RGarces a écrit :
>>> I am using ASE 15.0.3
>>>
>>> In my saving acount system, I have a table who save the
>>> transactions of accounts
>>>
>>> This table is historical, because it has information of 3
>>> years of transactions
>>>
>>> The table has:
>>>
>>> - One million of records
>>> - DOL lock schema
>>> - Only non-clustered index , one of them on the field "date"
>>>
>>> I need to execute a lot of querys, all of them use a range
>>> of dates, like:
>>>
>>> select *
>>> from transaction_table
>>> where date between @i_date1 and @i_date2
>>>
>>> My querys are very slow and they generates a lot of I/O
>>>
>>> Then I have to:
>>>
>>> - Change the lock schema to ALL PAGES ?
>>> - Change the index of "date" from non-clustered to
>>> clustered?
>>> - What else?
>>>
>>> Thanks a lot!!
>>>
>>> Rod
>
>


Carl Kayser Posted on 2010-08-27 16:38:31.0Z
From: "Carl Kayser" <kayser_c@bls.gov>
Newsgroups: sybase.public.ase.general
References: <4c757ee1.7447.1681692777@sybase.com> <4c7589b7$1@forums-1-dub> <4c763f1d$1@forums-1-dub> <4c76ba2a$1@forums-1-dub>
Subject: Re: Perfomance in Historical Table
Lines: 49
Organization: BLS
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5931
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5931
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4c77ea07$1@forums-1-dub>
Date: 27 Aug 2010 09:38:31 -0700
X-Trace: forums-1-dub 1282927111 10.22.241.152 (27 Aug 2010 09:38:31 -0700)
X-Original-Trace: 27 Aug 2010 09:38:31 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29515
Article PK: 78746


<ET> wrote in message news:4c76ba2a$1@forums-1-dub...
> You are right I chose the N+1 index solution but as a pragmatic way of
> answering the question, as a clustered index was already created on the
> table with the date col.
>
> The solution I am proposing implies certain restriction in the column list
> to easily get a covered query and narrow index. With a clustered index,
> leaf pages also contains data so most of the columns can be specified in
> the select columns list.
>
> But I cannot see how APL locking scheme could be a major improvement
> factor, except better fragmentation resistance. But how about concurrent
> access if this history table is heavily accessed for instance ?
>
> Thank you
> Emmanuel
>

I think that it depends upon the situation - and you pointed out that more
information would be needed from the OP. The OP stated that there would be
a number of queries all of which would filter on a date range (and possibly
other columns). Nothing was specified about write activities. The
signifigant points to me are:

(1) A placement or non-clustered index could provide for index coverage. I
believe that this is still the fastest method possible. But will it suffice
for ALL of the OPs queries? In some cases it might not and in some cases it
could be slightly inefficient by having more columns than are needed for
coverage.

(2) An APL [unique | primary] clustered index would always be useful
although the optimizer might sometimes use a non-clustered index instead
(owing to the usefullness of other columns specified in the where clause).
It depends upon the OPs collection of queries and other indexes.

(3) Per the OP: "My querys are very slow and they generates a lot of I/O".
Well, an APL [unique | primary] clustered index uses very little space.
About 1-5% compared to the data space? A non-clustered index that provides
for coverage of all of the OPs collection could be pretty wide. (I have
seen users create NC indexes which are bigger than the table.) Which
results in more total IO (index pages and data pages)? If an index scan is
not available for a query then I think that the APL index will frequently be
better.


(SNIP)


"Mark A. Parsons" <iron_horse Posted on 2010-08-25 21:04:44.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Perfomance in Historical Table
References: <4c757ee1.7447.1681692777@sybase.com>
In-Reply-To: <4c757ee1.7447.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4c75856c$1@forums-1-dub>
Date: 25 Aug 2010 14:04:44 -0700
X-Trace: forums-1-dub 1282770284 10.22.241.152 (25 Aug 2010 14:04:44 -0700)
X-Original-Trace: 25 Aug 2010 14:04:44 -0700, vip152.sybase.com
Lines: 66
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29501
Article PK: 78732

Creating a clustered index on the date column of the DOL table will initially order the records by date. The dataserver
will attempt to maintain records in date order but there's no guarantee that they'll be physically ordered by date, so
over time the chance for excessive fragmentation exists. If you're inserting records sequentially by date then the
fragmentation should not be as excessive (ie, you wouldn't need to rebuild the clustered index as often).

Creating a clustered index on the date column of the DOL table will maintain the records in date order. Though if your
inserts are not sequential by date you will eventually end up with some fragmentation of the clustered index.

But before you go creating a clustered index and/or changing the table's locking scheme, you'll want to find out if the
optimizer is doing the best it can with the index you currently have in place ... and that's going to require a good bit
more info about your queries and their associated query plans ...

- do you have statement cache and literal autoparam enabled?

- what datatypes are @i_date1 and @i_date2?

- what datatype is the transaction_table.date column?

- is this sample query from a stored proc or a standalone query?

- if this query is from a stored proc, are the @i_dateX variables defined as input parameters to the stored proc or are
they 'declare'd inside the stored proc?

- what are the values of the @i_dateX variables for a given test run, and what's the associated query plan?

- how often do you update statistics on this table and what settings do you use (eg, sampling percentage? histogram
steps? etc)?

- do some instances of this query run 'fast' and others 'slow', and if so, what the values of the associated @i_dateX
variables, the query plans, and the number of records returned?

RGarces wrote:
> I am using ASE 15.0.3
>
> In my saving acount system, I have a table who save the
> transactions of accounts
>
> This table is historical, because it has information of 3
> years of transactions
>
> The table has:
>
> - One million of records
> - DOL lock schema
> - Only non-clustered index , one of them on the field "date"
>
> I need to execute a lot of querys, all of them use a range
> of dates, like:
>
> select *
> from transaction_table
> where date between @i_date1 and @i_date2
>
> My querys are very slow and they generates a lot of I/O
>
> Then I have to:
>
> - Change the lock schema to ALL PAGES ?
> - Change the index of "date" from non-clustered to
> clustered?
> - What else?
>
> Thanks a lot!!
>
> Rod


Carl Kayser Posted on 2010-08-26 10:02:02.0Z
From: "Carl Kayser" <kayser_c@bls.gov>
Newsgroups: sybase.public.ase.general
References: <4c757ee1.7447.1681692777@sybase.com> <4c75856c$1@forums-1-dub>
Subject: Re: Perfomance in Historical Table
Lines: 24
Organization: BLS
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5931
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5931
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4c763b9a$1@forums-1-dub>
Date: 26 Aug 2010 03:02:02 -0700
X-Trace: forums-1-dub 1282816922 10.22.241.152 (26 Aug 2010 03:02:02 -0700)
X-Original-Trace: 26 Aug 2010 03:02:02 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29506
Article PK: 78737


"Mark A. Parsons" <iron_horse@no_spamola.compuserve.com> wrote in message
news:4c75856c$1@forums-1-dub...
> Creating a clustered index on the date column of the DOL table will
> initially order the records by date. The dataserver will attempt to
> maintain records in date order but there's no guarantee that they'll be
> physically ordered by date, so over time the chance for excessive
> fragmentation exists. If you're inserting records sequentially by date
> then the fragmentation should not be as excessive (ie, you wouldn't need
> to rebuild the clustered index as often).
>
> Creating a clustered index on the date column of the DOL table will
> maintain the records in date order. Though if your inserts are not
> sequential by date you will eventually end up with some fragmentation of
> the clustered index.
>

(SNIP)

I think that Mark means APL in the second "paragraph". And unique clustered
(via additional subkeys) would probably be better since it would eliminate
possible overflow pages.


"Mark A. Parsons" <iron_horse Posted on 2010-08-26 18:12:52.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Perfomance in Historical Table
References: <4c757ee1.7447.1681692777@sybase.com> <4c75856c$1@forums-1-dub> <4c763b9a$1@forums-1-dub>
In-Reply-To: <4c763b9a$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4c76aea4$1@forums-1-dub>
Date: 26 Aug 2010 11:12:52 -0700
X-Trace: forums-1-dub 1282846372 10.22.241.152 (26 Aug 2010 11:12:52 -0700)
X-Original-Trace: 26 Aug 2010 11:12:52 -0700, vip152.sybase.com
Lines: 26
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29512
Article PK: 78743

yep, APL ... that was a test to see if anyone actually reads these posts ... ;-)

Carl Kayser wrote:
> "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com> wrote in message
> news:4c75856c$1@forums-1-dub...
>> Creating a clustered index on the date column of the DOL table will
>> initially order the records by date. The dataserver will attempt to
>> maintain records in date order but there's no guarantee that they'll be
>> physically ordered by date, so over time the chance for excessive
>> fragmentation exists. If you're inserting records sequentially by date
>> then the fragmentation should not be as excessive (ie, you wouldn't need
>> to rebuild the clustered index as often).
>>
>> Creating a clustered index on the date column of the DOL table will
>> maintain the records in date order. Though if your inserts are not
>> sequential by date you will eventually end up with some fragmentation of
>> the clustered index.
>>
>
> (SNIP)
>
> I think that Mark means APL in the second "paragraph". And unique clustered
> (via additional subkeys) would probably be better since it would eliminate
> possible overflow pages.
>
>


Sherlock, Kevin [TeamSybase] Posted on 2010-08-26 13:55:11.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.general
References: <4c757ee1.7447.1681692777@sybase.com>
Subject: Re: Perfomance in Historical Table
Lines: 40
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: <4c76723f$1@forums-1-dub>
Date: 26 Aug 2010 06:55:11 -0700
X-Trace: forums-1-dub 1282830911 10.22.241.152 (26 Aug 2010 06:55:11 -0700)
X-Original-Trace: 26 Aug 2010 06:55:11 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29510
Article PK: 78741

What is the query plan? Are @i_date1 and @i_date2 variables or stored
procedure parameters? What is the output of "optdiag" for the
"transaction_table" ? Keep in mind that the default selectivity for between
operator is 25% of the table.

<RGarces> wrote in message news:4c757ee1.7447.1681692777@sybase.com...
>I am using ASE 15.0.3
>
> In my saving acount system, I have a table who save the
> transactions of accounts
>
> This table is historical, because it has information of 3
> years of transactions
>
> The table has:
>
> - One million of records
> - DOL lock schema
> - Only non-clustered index , one of them on the field "date"
>
> I need to execute a lot of querys, all of them use a range
> of dates, like:
>
> select *
> from transaction_table
> where date between @i_date1 and @i_date2
>
> My querys are very slow and they generates a lot of I/O
>
> Then I have to:
>
> - Change the lock schema to ALL PAGES ?
> - Change the index of "date" from non-clustered to
> clustered?
> - What else?
>
> Thanks a lot!!
>
> Rod


Sathesh Posted on 2010-08-28 18:12:42.0Z
From: Sathesh <Satsun85@hotmail.com>
Newsgroups: sybase.public.ase.general
Subject: Re: Perfomance in Historical Table
Message-ID: <MPG.26e3af31f0a60705989680@forums.sybase.com>
References: <4c757ee1.7447.1681692777@sybase.com>
Organization: Sathesh
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
User-Agent: MicroPlanet-Gravity/3.0.2
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 28 Aug 2010 11:12:42 -0700
X-Trace: forums-1-dub 1283019162 10.22.241.152 (28 Aug 2010 11:12:42 -0700)
X-Original-Trace: 28 Aug 2010 11:12:42 -0700, vip152.sybase.com
Lines: 14
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29516
Article PK: 78745

[This followup was posted to sybase.public.ase.general and a copy was
sent to the cited author.]

Hi All,

I believe partitioning the table will improve the performance... Your
toughts?


Thanks,
Satsun.


"Mark A. Parsons" <iron_horse Posted on 2010-08-28 19:06:26.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Perfomance in Historical Table
References: <4c757ee1.7447.1681692777@sybase.com> <MPG.26e3af31f0a60705989680@forums.sybase.com>
In-Reply-To: <MPG.26e3af31f0a60705989680@forums.sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4c795e32$1@forums-1-dub>
Date: 28 Aug 2010 12:06:26 -0700
X-Trace: forums-1-dub 1283022386 10.22.241.152 (28 Aug 2010 12:06:26 -0700)
X-Original-Trace: 28 Aug 2010 12:06:26 -0700, vip152.sybase.com
Lines: 29
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29517
Article PK: 78751

My thoughts?

Why do you think partitioning would improve performance?

How do you propose the OP go about determining if partitioning is a viable solution?

What type of partitioning? How many partitions?

APL or DOL?

Local or global indexes? clustered or non-clustered indexes?

Pros and cons of partitioning?

Sathesh wrote:
> [This followup was posted to sybase.public.ase.general and a copy was
> sent to the cited author.]
>
> Hi All,
>
> I believe partitioning the table will improve the performance... Your
> toughts?
>
>
> Thanks,
> Satsun.
>
>
>