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.

Faster with Temporal Tables?

5 posts in General Discussion Last posting was on 2012-02-14 18:31:45.0Z
RGS Posted on 2012-02-09 00:25:18.0Z
Sender: 2eb9.4f32f5b9.1804289383@sybase.com
From: RGS
Newsgroups: sybase.public.ase.general
Subject: Faster with Temporal Tables?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4f33126e.359a.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 8 Feb 2012 16:25:18 -0800
X-Trace: forums-1-dub 1328747118 172.20.134.41 (8 Feb 2012 16:25:18 -0800)
X-Original-Trace: 8 Feb 2012 16:25:18 -0800, 172.20.134.41
Lines: 37
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30904
Article PK: 73794

Hi!

I am using ASE 15.5

I have a stored procedure like this:

create proc sp1
as
select * from huge_table where key = @key and field = @i1
....
select * from huge_table where key = @key and field = @i2
....
select * from huge_table where key = @key and field = @i3
....

(huge_table has a nonclustered index by key,field)

If a change this program in the way:

create proc sp1
as
select * into #huge_table from huge_table where key = @key

select * from #huge_table where field = @i1
....
select * from #huge_table where field = @i2
....
select * from #huge_table where field = @i3
....

The program will be faster?

If the answer is YES, what happens with the DATA CACHE? For
me the time sholud be almost the same because the access to
DATA CACHE is very fast....

Thanks!


Rob V Posted on 2012-02-10 06:49:33.0Z
From: Rob V <rob@sypron.nl>
Reply-To: rob@sypron.nl
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:9.0) Gecko/20111222 Thunderbird/9.0.1
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Faster with Temporal Tables?
References: <4f33126e.359a.1681692777@sybase.com>
In-Reply-To: <4f33126e.359a.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: <4f34bdfd@forums-1-dub>
Date: 9 Feb 2012 22:49:33 -0800
X-Trace: forums-1-dub 1328856573 10.22.241.152 (9 Feb 2012 22:49:33 -0800)
X-Original-Trace: 9 Feb 2012 22:49:33 -0800, vip152.sybase.com
Lines: 71
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30906
Article PK: 73796

The answer depends on how your original queries perform. Specifically,
what is their query plan and how many I/Os do they use, and how many
rows do they retrieve? Also, it would be needed to know the query plan
for the query selecting into the temporary #table in your proposed
alternative. It is possible that this query is less efficient becuase
you're only using the first index column -- if you end up retrieving
many rows with that key, than it is possible that your original queries
are actually more efficient.
So, without more information, the answer is -as always- "it depends"...


--
HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Professional DBA for Sybase ASE, IQ, Replication Server

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE"
"The Complete Sybase IQ Quick Reference Guide" (new!)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------

On 09-Feb-2012 01:25, RGS wrote:
> Hi!
>
> I am using ASE 15.5
>
> I have a stored procedure like this:
>
> create proc sp1
> as
> select * from huge_table where key = @key and field = @i1
> .....
> select * from huge_table where key = @key and field = @i2
> .....
> select * from huge_table where key = @key and field = @i3
> .....
>
> (huge_table has a nonclustered index by key,field)
>
> If a change this program in the way:
>
> create proc sp1
> as
> select * into #huge_table from huge_table where key = @key
>
> select * from #huge_table where field = @i1
> .....
> select * from #huge_table where field = @i2
> .....
> select * from #huge_table where field = @i3
> .....
>
> The program will be faster?
>
> If the answer is YES, what happens with the DATA CACHE? For
> me the time sholud be almost the same because the access to
> DATA CACHE is very fast....
>
> Thanks!


RGS Posted on 2012-02-13 02:06:10.0Z
Sender: 67f1.4f386d8c.1804289383@sybase.com
From: RGS
Newsgroups: sybase.public.ase.general
Subject: Re: Faster with Temporal Tables?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4f387012.68dc.1681692777@sybase.com>
References: <4f34bdfd@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 12 Feb 2012 18:06:10 -0800
X-Trace: forums-1-dub 1329098770 172.20.134.41 (12 Feb 2012 18:06:10 -0800)
X-Original-Trace: 12 Feb 2012 18:06:10 -0800, 172.20.134.41
Lines: 90
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30910
Article PK: 73798

Than you Rob

Suposse that in the # table I retrieve 80% of rows that I
will use in the nexts select's. In this case, the second
alternative is better?

How much better it is? For your answer, I say that this
strategy is not always better. A lot of people in my work
think that this strategy is sure and always faster! And
they are change a lot of programs!!!!

> The answer depends on how your original queries perform.
> Specifically, what is their query plan and how many I/Os
> do they use, and how many rows do they retrieve? Also, it
> would be needed to know the query plan for the query
> selecting into the temporary #table in your proposed
> alternative. It is possible that this query is less
> efficient becuase you're only using the first index
> column -- if you end up retrieving many rows with that
> key, than it is possible that your original queries are
> actually more efficient. So, without more information, the
> answer is -as always- "it depends"...
>
>
> --
> HTH,
>
> Rob V.
> ----------------------------------------------------------
> ------- Rob Verschoor
>
> Certified Professional DBA for Sybase ASE, IQ, Replication
> Server
>
> Author of Sybase books (order online at
> www.sypron.nl/shop): "Tips, Tricks & Recipes for Sybase
> ASE" "The Complete Sybase IQ Quick Reference Guide" (new!)
> "The Complete Sybase ASE Quick Reference Guide"
> "The Complete Sybase Replication Server Quick Reference
> Guide"
>
> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
> @rob_verschoor Sypron B.V., The Netherlands | Chamber of
> Commerce 27138666
> ----------------------------------------------------------
> -------
>
> On 09-Feb-2012 01:25, RGS wrote:
> > Hi!
> >
> > I am using ASE 15.5
> >
> > I have a stored procedure like this:
> >
> > create proc sp1
> > as
> > select * from huge_table where key = @key and field =
> > @i1 .....
> > select * from huge_table where key = @key and field =
> > @i2 .....
> > select * from huge_table where key = @key and field =
> > @i3 .....
> >
> > (huge_table has a nonclustered index by key,field)
> >
> > If a change this program in the way:
> >
> > create proc sp1
> > as
> > select * into #huge_table from huge_table where key =
> @key >
> > select * from #huge_table where field = @i1
> > .....
> > select * from #huge_table where field = @i2
> > .....
> > select * from #huge_table where field = @i3
> > .....
> >
> > The program will be faster?
> >
> > If the answer is YES, what happens with the DATA CACHE?
> > For me the time sholud be almost the same because the
> > access to DATA CACHE is very fast....
> >
> > Thanks!
>
>
>


Rob V Posted on 2012-02-13 20:25:31.0Z
From: Rob V <rob@sypron.nl>
Reply-To: rob@sypron.nl
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:9.0) Gecko/20111222 Thunderbird/9.0.1
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Faster with Temporal Tables?
References: <4f34bdfd@forums-1-dub> <4f387012.68dc.1681692777@sybase.com>
In-Reply-To: <4f387012.68dc.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: <4f3971bb$1@forums-1-dub>
Date: 13 Feb 2012 12:25:31 -0800
X-Trace: forums-1-dub 1329164731 10.22.241.152 (13 Feb 2012 12:25:31 -0800)
X-Original-Trace: 13 Feb 2012 12:25:31 -0800, vip152.sybase.com
Lines: 128
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30914
Article PK: 73804

I guess it may be better in that case. But it may not be -- for example,
the query plans for the two types of queries may not be the same (since
the first searches against two columns and the second only against one
column). Or for example, if you retrieve the majority of the rows of the
table into the #temp table (and the subsequent selects form the #temp
table are not indexed).
With this type of problem, there's only one way to find a realistic
answer, and that is to test, understand the query plans and compare the
numbers of I/Os.

HTH,

Rob V.
--
HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Professional DBA for Sybase ASE, IQ, Replication Server

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE"
"The Complete Sybase IQ Quick Reference Guide" (new!)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------

On 13-Feb-2012 03:06, RGS wrote:
> Than you Rob
>
> Suposse that in the # table I retrieve 80% of rows that I
> will use in the nexts select's. In this case, the second
> alternative is better?
>
> How much better it is? For your answer, I say that this
> strategy is not always better. A lot of people in my work
> think that this strategy is sure and always faster! And
> they are change a lot of programs!!!!
>
>
>
>> The answer depends on how your original queries perform.
>> Specifically, what is their query plan and how many I/Os
>> do they use, and how many rows do they retrieve? Also, it
>> would be needed to know the query plan for the query
>> selecting into the temporary #table in your proposed
>> alternative. It is possible that this query is less
>> efficient becuase you're only using the first index
>> column -- if you end up retrieving many rows with that
>> key, than it is possible that your original queries are
>> actually more efficient. So, without more information, the
>> answer is -as always- "it depends"...
>>
>>
>> --
>> HTH,
>>
>> Rob V.
>> ----------------------------------------------------------
>> ------- Rob Verschoor
>>
>> Certified Professional DBA for Sybase ASE, IQ, Replication
>> Server
>>
>> Author of Sybase books (order online at
>> www.sypron.nl/shop): "Tips, Tricks& Recipes for Sybase
>> ASE" "The Complete Sybase IQ Quick Reference Guide" (new!)
>> "The Complete Sybase ASE Quick Reference Guide"
>> "The Complete Sybase Replication Server Quick Reference
>> Guide"
>>
>> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
>> @rob_verschoor Sypron B.V., The Netherlands | Chamber of
>> Commerce 27138666
>> ----------------------------------------------------------
>> -------
>>
>> On 09-Feb-2012 01:25, RGS wrote:
>>> Hi!
>>>
>>> I am using ASE 15.5
>>>
>>> I have a stored procedure like this:
>>>
>>> create proc sp1
>>> as
>>> select * from huge_table where key = @key and field =
>>> @i1 .....
>>> select * from huge_table where key = @key and field =
>>> @i2 .....
>>> select * from huge_table where key = @key and field =
>>> @i3 .....
>>>
>>> (huge_table has a nonclustered index by key,field)
>>>
>>> If a change this program in the way:
>>>
>>> create proc sp1
>>> as
>>> select * into #huge_table from huge_table where key =
>> @key>
>>> select * from #huge_table where field = @i1
>>> .....
>>> select * from #huge_table where field = @i2
>>> .....
>>> select * from #huge_table where field = @i3
>>> .....
>>>
>>> The program will be faster?
>>>
>>> If the answer is YES, what happens with the DATA CACHE?
>>> For me the time sholud be almost the same because the
>>> access to DATA CACHE is very fast....
>>>
>>> Thanks!
>>
>>
>>


RGS Posted on 2012-02-14 18:31:45.0Z
Sender: 6a0b.4f3aa7d8.1804289383@sybase.com
From: RGS
Newsgroups: sybase.public.ase.general
Subject: Re: Faster with Temporal Tables?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4f3aa891.6a27.1681692777@sybase.com>
References: <4f3971bb$1@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 14 Feb 2012 10:31:45 -0800
X-Trace: forums-1-dub 1329244305 172.20.134.41 (14 Feb 2012 10:31:45 -0800)
X-Original-Trace: 14 Feb 2012 10:31:45 -0800, 172.20.134.41
Lines: 136
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30915
Article PK: 73806

Perfect, thanks!

> I guess it may be better in that case. But it may not be
> -- for example, the query plans for the two types of
> queries may not be the same (since the first searches
> against two columns and the second only against one
> column). Or for example, if you retrieve the majority of
> the rows of the table into the #temp table (and the
> subsequent selects form the #temp table are not indexed).
> With this type of problem, there's only one way to find a
> realistic answer, and that is to test, understand the
> query plans and compare the numbers of I/Os.
>
> HTH,
>
> Rob V.
> --
> HTH,
>
> Rob V.
> ----------------------------------------------------------
> ------- Rob Verschoor
>
> Certified Professional DBA for Sybase ASE, IQ, Replication
> Server
>
> Author of Sybase books (order online at
> www.sypron.nl/shop): "Tips, Tricks & Recipes for Sybase
> ASE" "The Complete Sybase IQ Quick Reference Guide" (new!)
> "The Complete Sybase ASE Quick Reference Guide"
> "The Complete Sybase Replication Server Quick Reference
> Guide"
>
> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
> @rob_verschoor Sypron B.V., The Netherlands | Chamber of
> Commerce 27138666
> ----------------------------------------------------------
> -------
>
>
>
>
> On 13-Feb-2012 03:06, RGS wrote:
> > Than you Rob
> >
> > Suposse that in the # table I retrieve 80% of rows that
> > I will use in the nexts select's. In this case, the
> > second alternative is better?
> >
> > How much better it is? For your answer, I say that this
> > strategy is not always better. A lot of people in my
> > work think that this strategy is sure and always faster!
> > And they are change a lot of programs!!!!
> >
> >
> >
> >> The answer depends on how your original queries
> perform. >> Specifically, what is their query plan and
> how many I/Os >> do they use, and how many rows do they
> retrieve? Also, it >> would be needed to know the query
> plan for the query >> selecting into the temporary #table
> in your proposed >> alternative. It is possible that this
> query is less >> efficient becuase you're only using the
> first index >> column -- if you end up retrieving many
> rows with that >> key, than it is possible that your
> original queries are >> actually more efficient. So,
> without more information, the >> answer is -as always- "it
> depends"... >>
> >>
> >> --
> >> HTH,
> >>
> >> Rob V.
> >>
> ----------------------------------------------------------
> >> ------- Rob Verschoor >>
> >> Certified Professional DBA for Sybase ASE, IQ,
> Replication >> Server
> >>
> >> Author of Sybase books (order online at
> >> www.sypron.nl/shop): "Tips, Tricks& Recipes for Sybase
> >> ASE" "The Complete Sybase IQ Quick Reference Guide"
> (new!) >> "The Complete Sybase ASE Quick Reference Guide"
> >> "The Complete Sybase Replication Server Quick Reference
> >> Guide"
> >>
> >> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
> >> @rob_verschoor Sypron B.V., The Netherlands | Chamber
> of >> Commerce 27138666
> >>
> ----------------------------------------------------------
> >> ------- >>
> >> On 09-Feb-2012 01:25, RGS wrote:
> >>> Hi!
> >>>
> >>> I am using ASE 15.5
> >>>
> >>> I have a stored procedure like this:
> >>>
> >>> create proc sp1
> >>> as
> >>> select * from huge_table where key = @key and field =
> >>> @i1 .....
> >>> select * from huge_table where key = @key and field =
> >>> @i2 .....
> >>> select * from huge_table where key = @key and field =
> >>> @i3 .....
> >>>
> >>> (huge_table has a nonclustered index by key,field)
> >>>
> >>> If a change this program in the way:
> >>>
> >>> create proc sp1
> >>> as
> >>> select * into #huge_table from huge_table where key =
> >> @key>
> >>> select * from #huge_table where field = @i1
> >>> .....
> >>> select * from #huge_table where field = @i2
> >>> .....
> >>> select * from #huge_table where field = @i3
> >>> .....
> >>>
> >>> The program will be faster?
> >>>
> >>> If the answer is YES, what happens with the DATA
> CACHE? >>> For me the time sholud be almost the same
> because the >>> access to DATA CACHE is very fast....
> >>>
> >>> Thanks!
> >>
> >>
> >>
>
>