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.

Object Qualifier

5 posts in General Discussion Last posting was on 2012-11-26 15:42:25.0Z
Karthikeyan.M Posted on 2012-11-22 12:03:53.0Z
Sender: 4a8b.50ae142f.1804289383@sybase.com
From: Karthikeyan.M
Newsgroups: sybase.public.ase.general
Subject: Object Qualifier
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <50ae14a9.4bc0.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 22 Nov 2012 04:03:53 -0800
X-Trace: forums-1-dub 1353585833 172.20.134.41 (22 Nov 2012 04:03:53 -0800)
X-Original-Trace: 22 Nov 2012 04:03:53 -0800, 172.20.134.41
Lines: 13
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31515
Article PK: 74403

All,

Why Object qualifier is som important?

select eno,ename from emp

select eno,ename from dbo.emp

How to measure the performance difference between the above
two statement?

From a performance perspective, which one will take less
time/resource ? I think dbo.emp only.


Rob V Posted on 2012-11-22 17:34:59.0Z
From: Rob V <rob@sypron.nl>
Reply-To: rob@sypron.nl
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:17.0) Gecko/17.0 Thunderbird/17.0
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Object Qualifier
References: <50ae14a9.4bc0.1681692777@sybase.com>
In-Reply-To: <50ae14a9.4bc0.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: <50ae6243@forums-1-dub>
Date: 22 Nov 2012 09:34:59 -0800
X-Trace: forums-1-dub 1353605699 172.20.134.152 (22 Nov 2012 09:34:59 -0800)
X-Original-Trace: 22 Nov 2012 09:34:59 -0800, vip152.sybase.com
Lines: 49
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31516
Article PK: 74407


On 22-Nov-2012 13:03, Karthikeyan.M wrote:
> All,
>
> Why Object qualifier is som important?
>
> select eno,ename from emp
>
> select eno,ename from dbo.emp
>
> How to measure the performance difference between the above
> two statement?
>
> From a performance perspective, which one will take less
> time/resource ? I think dbo.emp only.
>

There is no performance difference.
First, a fully qualified object is formed as dbname.owner.object.
By default, the dbname is the current database, and the owner is the
current user. Whether or not you specify those defaults explicitly makes
no difference.

But of course there can be multiples tables 'emp' owned not only by
'dbo', but also by users 'jsmith' and 'pjones' (i.e. jsmith.emp and
pjones.emp). If you want to query those, you must specify the owner
otherwise it is not clear what you are referrign to.
If you want to refer to objects in a different database, you must always
specify the database name.
(And of course those very same tables as above, with the same owners,
could also exist in a different database)

--
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"
"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
-----------------------------------------------------------------


M.Karthikeyan Posted on 2012-11-23 04:09:21.0Z
Sender: 4a8b.50ae142f.1804289383@sybase.com
From: M.KARTHIKEYAN
Newsgroups: sybase.public.ase.general
Subject: Re: Object Qualifier
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <50aef6f1.784e.1681692777@sybase.com>
References: <50ae6243@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 22 Nov 2012 20:09:21 -0800
X-Trace: forums-1-dub 1353643761 172.20.134.41 (22 Nov 2012 20:09:21 -0800)
X-Original-Trace: 22 Nov 2012 20:09:21 -0800, 172.20.134.41
Lines: 182
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31519
Article PK: 74412

I read some SQL article yesterday about this topic. It seems
like there is a performance difference in SQL.Thats why I
posted this question. Because I don't see any such topic
covered for sybase.

Microsoft say about object qualification and what are the
problems if not provided.
Quoted as per Microsoft.
"If user "dbo" owns object dbo.mystoredproc, and another
user "Harry" runs this stored procedure with the command
"exec mystoredproc," the initial cache lookup by object name
fails because the object is not owner-qualified. (It is not
yet known whether another stored procedure named
Harry.mystoredproc exists, so SQL cannot be sure that the
cached plan for dbo.mystoredproc is the right one to
execute.) SQL Server then acquires an exclusive compile lock
on the procedure and makes preparations to compile the
procedure, including resolving the object name to an object
ID. Before it compiles the plan, SQL Server uses this object
ID to perform a more precise search of the procedure cache
and is able to locate a previously compiled plan even
without the owner qualification.

If an existing plan is found, SQL Server reuses the cached
plan and does not actually compile the stored procedure.
However, the lack of owner-qualification forces SQL to
perform a second cache lookup and acquire an exclusive
compile lock before determining that the existing cached
execution plan can be reused. Acquiring the lock and
performing lookups and other work that is needed to get to
this point can introduce a delay that is sufficient for the
compile locks to lead to blocking. This is especially true
if a large number of users who are not the stored
procedure's owner simultaneously run it without supplying
the owner name. Note that even if you do not see SPIDs
waiting on compile locks, lack of owner-qualification can
introduce delays in stored procedure execution and
unnecessarily high CPU utilization."

Now let us try to prove it. What we will need to prove it is
exclusive access to a system that has SQL Server DB
installed. (Exclusive access is to reduce the noise
generated by other applications and users). For this test we
can create a test Database and create all the objects in it.
To create a simple Database with default settings, run this
command on the Query Analyzer/SSMS (2005)



if db_id('test') is null create database test



Now let us create the objects required for this simple test.



use testgoif object_id('dbo.t1','U') is not null drop table
dbo.t1
if object_id('dbo.spselectfromt1','P') is not null drop
proc dbo. spselectfromt1
-- let us create a user just for this test.exec sp_addlogin
'User1','test'GOexec sp_adduser 'User1'GO
create table dbo.t1(c1 int primary key)goinsert t1(c1)
values (987654321)gocreate procedure dbo.
spselectfromt1asselect * from t1go
grant execute on dbo. spselectfromt1 to User1go


Now all the objects have been created. Open up the SQL
Profiler and add just the below mentioned events to be
monitored and start monitoring.

SQLStarting - This is to show that the SP has started its
process
SP:CacheMiss - If present, it will prove that it could not
get the plan from the cache.
SP:ExecContextHit - Shows that it was able to find the plan
after doing additional work.
SP:Starting - Now the actual statements have started
processing
Lock: Acquired - Shows that a lock has been acquired.
Lock: Released - Shows that the lock that was made was
released.
Open up a new query window that was authenticated
(connected) by using the "Test1" user.

Make sure that you are in the right Database (Test). Once
you have started the profiler, execute the stored proc
without giving the Object Qualifier (Exec spselectfromt1).
If you look at the output of the profiler, you will see
these calls in order.

SQL: BatchStarting
SP: CacheMiss
Lock: Acquired
Lock: Acquired
SP:ExecContextHit
Lock: Released
SP:Starting
We see one CacheMiss, two LocksAcuired, one ExecContextHit,
One Lock Released and finally the SP Starting.

Now let us see what will happen if the Object is qualified.
Execute the Stored Proc specifying the Object Qualifier.
(Exec dbo.spselectfromt1)
Now look at the output from the profiler. You can see that
there are only couple of calls.

SQL: BatchStarting
SP:ExecContextHit
SP:Starting
Although we cannot quantify exactly how much additional work
is done by the SQL Server to execute when Object Qualifier
is not specified, the Engine must be doing something to make
all the additional calls. For one stored Proc you do not see
much but just imagine all the stored procs are being called
without the Object Qualifier. SQL Server Engine is going to
be taxed by all the additional calls.

We have now proved that not using Object Qualifier is
costly.

> On 22-Nov-2012 13:03, Karthikeyan.M wrote:
> > All,
> >
> > Why Object qualifier is som important?
> >
> > select eno,ename from emp
> >
> > select eno,ename from dbo.emp
> >
> > How to measure the performance difference between the
> > above two statement?
> >
> > From a performance perspective, which one will take
> > less time/resource ? I think dbo.emp only.
> >
>
> There is no performance difference.
> First, a fully qualified object is formed as
> dbname.owner.object. By default, the dbname is the current
> database, and the owner is the current user. Whether or
> not you specify those defaults explicitly makes no
> difference.
>
> But of course there can be multiples tables 'emp' owned
> not only by 'dbo', but also by users 'jsmith' and
> 'pjones' (i.e. jsmith.emp and pjones.emp). If you want to
> query those, you must specify the owner otherwise it is
> not clear what you are referrign to. If you want to refer
> to objects in a different database, you must always
> specify the database name. (And of course those very same
> tables as above, with the same owners, could also exist
> in a different database)
>
> --
> 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"
> "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
> ----------------------------------------------------------
> -------


Rob V Posted on 2012-11-23 10:58:21.0Z
From: Rob V <rob@sypron.nl>
Reply-To: rob@sypron.nl
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:17.0) Gecko/17.0 Thunderbird/17.0
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Object Qualifier
References: <50ae6243@forums-1-dub> <50aef6f1.784e.1681692777@sybase.com>
In-Reply-To: <50aef6f1.784e.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: <50af56cd$1@forums-1-dub>
Date: 23 Nov 2012 02:58:21 -0800
X-Trace: forums-1-dub 1353668301 172.20.134.152 (23 Nov 2012 02:58:21 -0800)
X-Original-Trace: 23 Nov 2012 02:58:21 -0800, vip152.sybase.com
Lines: 202
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31524
Article PK: 74413

You should forget about how MSSQL Server does these things, since it is
completely different in ASE. For example, there is no such thing as a
'compile lock' in ASE.
More generally, even though MSSQL and ASE have many things in common on
the outside (since MSSQL originally started life as ASE which was then
named Sybase SQL Server), you should assume that all detailed aspects
related to tuning, resource usage etc. in MSSQL do not apply to ASE. One
reason is that the products have been optimized for different aspects
thorugh the years and how that is done internally will be totally
different. If you want to get into ASE performance tuning, pick up a
copy of the ASE Performance & Tuning guide (7 volumes:
http://tinyurl.com/cpezoq6)

HTH,

Rob V.

On 23-Nov-2012 05:09, M.KARTHIKEYAN wrote:
> I read some SQL article yesterday about this topic. It seems
> like there is a performance difference in SQL.Thats why I
> posted this question. Because I don't see any such topic
> covered for sybase.
>
> Microsoft say about object qualification and what are the
> problems if not provided.
> Quoted as per Microsoft.
> "If user "dbo" owns object dbo.mystoredproc, and another
> user "Harry" runs this stored procedure with the command
> "exec mystoredproc," the initial cache lookup by object name
> fails because the object is not owner-qualified. (It is not
> yet known whether another stored procedure named
> Harry.mystoredproc exists, so SQL cannot be sure that the
> cached plan for dbo.mystoredproc is the right one to
> execute.) SQL Server then acquires an exclusive compile lock
> on the procedure and makes preparations to compile the
> procedure, including resolving the object name to an object
> ID. Before it compiles the plan, SQL Server uses this object
> ID to perform a more precise search of the procedure cache
> and is able to locate a previously compiled plan even
> without the owner qualification.
>
> If an existing plan is found, SQL Server reuses the cached
> plan and does not actually compile the stored procedure.
> However, the lack of owner-qualification forces SQL to
> perform a second cache lookup and acquire an exclusive
> compile lock before determining that the existing cached
> execution plan can be reused. Acquiring the lock and
> performing lookups and other work that is needed to get to
> this point can introduce a delay that is sufficient for the
> compile locks to lead to blocking. This is especially true
> if a large number of users who are not the stored
> procedure's owner simultaneously run it without supplying
> the owner name. Note that even if you do not see SPIDs
> waiting on compile locks, lack of owner-qualification can
> introduce delays in stored procedure execution and
> unnecessarily high CPU utilization."
>
> Now let us try to prove it. What we will need to prove it is
> exclusive access to a system that has SQL Server DB
> installed. (Exclusive access is to reduce the noise
> generated by other applications and users). For this test we
> can create a test Database and create all the objects in it.
> To create a simple Database with default settings, run this
> command on the Query Analyzer/SSMS (2005)
>
>
>
> if db_id('test') is null create database test
>
>
>
> Now let us create the objects required for this simple test.
>
>
>
> use testgoif object_id('dbo.t1','U') is not null drop table
> dbo.t1
> if object_id('dbo.spselectfromt1','P') is not null drop
> proc dbo. spselectfromt1
> -- let us create a user just for this test.exec sp_addlogin
> 'User1','test'GOexec sp_adduser 'User1'GO
> create table dbo.t1(c1 int primary key)goinsert t1(c1)
> values (987654321)gocreate procedure dbo.
> spselectfromt1asselect * from t1go
> grant execute on dbo. spselectfromt1 to User1go
>
>
> Now all the objects have been created. Open up the SQL
> Profiler and add just the below mentioned events to be
> monitored and start monitoring.
>
> SQLStarting - This is to show that the SP has started its
> process
> SP:CacheMiss - If present, it will prove that it could not
> get the plan from the cache.
> SP:ExecContextHit - Shows that it was able to find the plan
> after doing additional work.
> SP:Starting - Now the actual statements have started
> processing
> Lock: Acquired - Shows that a lock has been acquired.
> Lock: Released - Shows that the lock that was made was
> released.
> Open up a new query window that was authenticated
> (connected) by using the "Test1" user.
>
> Make sure that you are in the right Database (Test). Once
> you have started the profiler, execute the stored proc
> without giving the Object Qualifier (Exec spselectfromt1).
> If you look at the output of the profiler, you will see
> these calls in order.
>
> SQL: BatchStarting
> SP: CacheMiss
> Lock: Acquired
> Lock: Acquired
> SP:ExecContextHit
> Lock: Released
> SP:Starting
> We see one CacheMiss, two LocksAcuired, one ExecContextHit,
> One Lock Released and finally the SP Starting.
>
> Now let us see what will happen if the Object is qualified.
> Execute the Stored Proc specifying the Object Qualifier.
> (Exec dbo.spselectfromt1)
> Now look at the output from the profiler. You can see that
> there are only couple of calls.
>
> SQL: BatchStarting
> SP:ExecContextHit
> SP:Starting
> Although we cannot quantify exactly how much additional work
> is done by the SQL Server to execute when Object Qualifier
> is not specified, the Engine must be doing something to make
> all the additional calls. For one stored Proc you do not see
> much but just imagine all the stored procs are being called
> without the Object Qualifier. SQL Server Engine is going to
> be taxed by all the additional calls.
>
> We have now proved that not using Object Qualifier is
> costly.
>
>
>
>
>
>> On 22-Nov-2012 13:03, Karthikeyan.M wrote:
>>> All,
>>>
>>> Why Object qualifier is som important?
>>>
>>> select eno,ename from emp
>>>
>>> select eno,ename from dbo.emp
>>>
>>> How to measure the performance difference between the
>>> above two statement?
>>>
>>> From a performance perspective, which one will take
>>> less time/resource ? I think dbo.emp only.
>>>
>>
>> There is no performance difference.
>> First, a fully qualified object is formed as
>> dbname.owner.object. By default, the dbname is the current
>> database, and the owner is the current user. Whether or
>> not you specify those defaults explicitly makes no
>> difference.
>>
>> But of course there can be multiples tables 'emp' owned
>> not only by 'dbo', but also by users 'jsmith' and
>> 'pjones' (i.e. jsmith.emp and pjones.emp). If you want to
>> query those, you must specify the owner otherwise it is
>> not clear what you are referrign to. If you want to refer
>> to objects in a different database, you must always
>> specify the database name. (And of course those very same
>> tables as above, with the same owners, could also exist
>> in a different database)
>>
>> --
>> 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"
>> "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
>> ----------------------------------------------------------
>> -------


Bret Halford Posted on 2012-11-26 15:42:25.0Z
From: Bret Halford <bret.halford@sap.com>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:16.0) Gecko/20121026 Thunderbird/16.0.2
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Object Qualifier
References: <50ae6243@forums-1-dub> <50aef6f1.784e.1681692777@sybase.com> <50af56cd$1@forums-1-dub>
In-Reply-To: <50af56cd$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: <50b38de1$1@forums-1-dub>
Date: 26 Nov 2012 07:42:25 -0800
X-Trace: forums-1-dub 1353944545 172.20.134.152 (26 Nov 2012 07:42:25 -0800)
X-Original-Trace: 26 Nov 2012 07:42:25 -0800, vip152.sybase.com
Lines: 11
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31532
Article PK: 74421


On 11/23/2012 3:58 AM, Rob V wrote:
> (since MSSQL originally started life as ASE which was then named Sybase
> SQL Server)

Minor correction:

MSSQL originally started life as Sybase SQL Server. After the companies
parted ways, Sybase renamed Sybase SQL Server to Adaptive Server Enterprise.

-bret