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.

Some basic questions

4 posts in General Discussion Last posting was on 2009-07-23 16:51:05.0Z
Meet Posted on 2009-07-23 06:49:09.0Z
Sender: 1e4d.4a67fe11.1804289383@sybase.com
From: Meet
Newsgroups: sybase.public.ase.general
Subject: Some basic questions
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4a6807e5.257b.1681692777@sybase.com>
NNTP-Posting-Host: forums-3-dub.sybase.com
X-Original-NNTP-Posting-Host: forums-3-dub.sybase.com
Date: 22 Jul 2009 23:49:09 -0700
X-Trace: forums-3-dub.sybase.com 1248331749 10.22.241.188 (22 Jul 2009 23:49:09 -0700)
X-Original-Trace: 22 Jul 2009 23:49:09 -0700, forums-3-dub.sybase.com
Lines: 30
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28027
Article PK: 77274

Hi all,

Recently, I was interviwed on sybase. Below are some of the
questions I couldn't answer. I was wondering if you can help
me with these.

1. In SP, how to know how much time each query/step took to
complete?
- My response to this question was to write "Select
getdate()" after every step, but it seemed that they wanted
a different answer. Is there any other way to find out how
much time a query took?

2. How to print/select numbers from 1-10 without using loop?
- My silly response to this question was to write select #
statement 10 times like below:
select 1
select 2
...
select 10
Which was obviously wrong as he countered it by saying "what
if I have to print number between 1-100".
Is there any other way?

I tried to google it but I didn't know what to look for?

please let me know.

Thanks in advance
Meet


"Mark A. Parsons" <iron_horse Posted on 2009-07-23 14:59:36.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: Some basic questions
References: <4a6807e5.257b.1681692777@sybase.com>
In-Reply-To: <4a6807e5.257b.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 090721-0, 07/21/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a687ad8$1@forums-3-dub.sybase.com>
Date: 23 Jul 2009 07:59:36 -0700
X-Trace: forums-3-dub.sybase.com 1248361176 10.22.241.152 (23 Jul 2009 07:59:36 -0700)
X-Original-Trace: 23 Jul 2009 07:59:36 -0700, vip152.sybase.com
Lines: 156
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28033
Article PK: 77279

Some ideas, though I wouldn't suggest you mention these unless you're prepared to discuss them in more detail with the
interviewer ...

Meet wrote:
> Hi all,
>
> Recently, I was interviwed on sybase. Below are some of the
> questions I couldn't answer. I was wondering if you can help
> me with these.
>
> 1. In SP, how to know how much time each query/step took to
> complete?
> - My response to this question was to write "Select
> getdate()" after every step, but it seemed that they wanted
> a different answer. Is there any other way to find out how
> much time a query took?

Without additional guidance from the person asking the questions, I would:

- assume ASE 12.5.0.3 (or better)
- assume MDA tables have been installed
- assume MDA tables have been configured to capture statement statistics
- pull individual query stats from the monSysStatement table

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

Alternatively, if by 'In SP' the requirement is for the stored proc itself to know performance metrics, then a variation
on the 'select getdate()' option could provide performance stats that are accurate to within 3-4 ms:

============================
declare @start_date datetime
select @start_dt=getdate()
select/update/delete/insert....
select delta_dt=datediff(ms,@start_dt,getdate()),
@start_dt=getdate()
select/update/delete/insert ...
select delta_dt=datediff(ms,@start_dt,getdate())
============================

Obviously (?) it's up to the sp developer to decide what to do with the delta_dt values ... select/print to the client,
insert into a table, dump to the ASE errorlog, etc.

NOTE: Technically the sp could be coded to pull query stats from monSysStatement.

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

Another option is to have the client application configured to capture query performance stats, eg:

============================
set statistics time on
go
exec proc
go
============================

This same 'solution' could also be implemented using Application Tracing (assuming ASE 15.0.2+).

The downside is that 'statistics time on' isn't always reliable.



> 2. How to print/select numbers from 1-10 without using loop?
> - My silly response to this question was to write select #
> statement 10 times like below:
> select 1
> select 2
> ...
> select 10
> Which was obviously wrong as he countered it by saying "what
> if I have to print number between 1-100".
> Is there any other way?

Short of using a loop, or repeated SELECTs (as in your example), it's not possible to generate multiple records of
output without referencing 1 or more tables.

NOTE: By 'table' I'm referring to permanent, #temp, derived and work tables.

So then you either have to ask the interviewer for more specifics ... or make some assumptions.

- assume no stored procs (not locally, not remotely via RPC, not remotely via proxy table) that contain a looping
construct or table reference(s)

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

- assuming the interviewer never wants to see more than 1000 numbers
- create a table with one integer column [eg, create table counter (id integer)]
- populate the table with 1000 records consisting of the numbers 1-1000
- [optional] place a unique clustered (ascending) index on the table
- when the interviewer wants X numbers, just run the following:

select top X id from counter
order by id -- only needed if no clustered index in place

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

- assuming the desire is for a contiguous list of numbers, starting with 1
- assuming I can create a (permanent or #temp) table as part of the process

============================
select top 100 -- replace 100 with desired row count
id=identity(6)
into #counter
from sysobjects, syscomments, sysindexes, syscolumns
go
select id from #counter
go
============================

The above cartesian product, when run in my 'empty' ASE 15.0.3 tempdb database, is capable of generating just under 7.9
million records. Add/Subtract tables in the FROM clause to increase/decrease the max number of records that can be
generated by the query.

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

- assuming the desire is for a contiguous list of numbers, starting with 1
- assuming I cannot create any new tables
- assuming I can use system tables
- assuming ASE 12.5+ (in order to use Application Context Functions - ACFs)
- assuming I have to minimize the number of queries and/or batches

============================
-- clear out previously used ACF tuple;
-- could eliminate this step in favor of
-- some additional logic in the follow-on
-- query

select rm_appcontext('my','counter') * 0

-- replace 100 with desired number of
-- entries to be generated

select
top 100
isnull(convert(int,get_appcontext('my','counter'+str(o.id,0))),1) +
set_appcontext('my','counter',
convert(varchar,
1+
isnull(convert(int,get_appcontext('my','counter'+str(o.id,0))),1) +
rm_appcontext('my','counter'+str(o.id,0)) * 0
)
) * 0
from sysobjects o, syscomments, sysindexes, syscolumns
go
============================

Again, the above cartesian product, when run in my 'empty' ASE 15.0.3 tempdb database, is capable of generating just
under 7.9 million records. Add/Subtract tables to the FROM clause to increase/decrease the max number of records that
can be generated by the query.

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

The above is certainly not an exhaustive list.

Usually there are multiple ways to address an issue ... you're just limited to your knowledge and experience with ASE
... and the limitations of the scenario imposed on you by the interviewer.


vtpcnk Posted on 2009-07-23 13:24:05.0Z
Sender: 67d9.4a68608d.1804289383@sybase.com
From: vtpcnk
Newsgroups: sybase.public.ase.general
Subject: Re: Some basic questions
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4a686475.6ad1.1681692777@sybase.com>
References: <4a6807e5.257b.1681692777@sybase.com>
NNTP-Posting-Host: forums-3-dub.sybase.com
X-Original-NNTP-Posting-Host: forums-3-dub.sybase.com
Date: 23 Jul 2009 06:24:05 -0700
X-Trace: forums-3-dub.sybase.com 1248355445 10.22.241.188 (23 Jul 2009 06:24:05 -0700)
X-Original-Trace: 23 Jul 2009 06:24:05 -0700, forums-3-dub.sybase.com
Lines: 4
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28032
Article PK: 77280


> 1. In SP, how to know how much time each query/step took
> to complete?

set statistics time on


Rob V [Sybase] Posted on 2009-07-23 16:51:05.0Z
Reply-To: "Rob V [Sybase]" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
From: "Rob V [Sybase]" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Newsgroups: sybase.public.ase.general
References: <4a6807e5.257b.1681692777@sybase.com>
Subject: Re: Some basic questions
Lines: 54
Organization: Sypron BV / TeamSybase / Sybase Inc
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3350
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a6894f9$3@forums-3-dub.sybase.com>
Date: 23 Jul 2009 09:51:05 -0700
X-Trace: forums-3-dub.sybase.com 1248367865 10.22.241.152 (23 Jul 2009 09:51:05 -0700)
X-Original-Trace: 23 Jul 2009 09:51:05 -0700, vip152.sybase.com
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28037
Article PK: 77284

1: use monSysStatement or 'set statistics time on'

2: use an identity column
-----------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
and Replication Server 15.0.1/12.5 // TeamSybase

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

mailto:rob@sypron.nl
http://www.sypron.nl
Sypron B.V., Amersfoort, The Netherlands
Chamber of Commerce 27138666
-----------------------------------------------------------------

<Meet> wrote in message news:4a6807e5.257b.1681692777@sybase.com...
> Hi all,
>
> Recently, I was interviwed on sybase. Below are some of the
> questions I couldn't answer. I was wondering if you can help
> me with these.
>
> 1. In SP, how to know how much time each query/step took to
> complete?
> - My response to this question was to write "Select
> getdate()" after every step, but it seemed that they wanted
> a different answer. Is there any other way to find out how
> much time a query took?
>
> 2. How to print/select numbers from 1-10 without using loop?
> - My silly response to this question was to write select #
> statement 10 times like below:
> select 1
> select 2
> ...
> select 10
> Which was obviously wrong as he countered it by saying "what
> if I have to print number between 1-100".
> Is there any other way?
>
> I tried to google it but I didn't know what to look for?
>
> please let me know.
>
> Thanks in advance
> Meet