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.

How to interpret the query plan for stored procedure

15 posts in General Discussion Last posting was on 2012-07-19 06:41:58.0Z
Taylor Posted on 2009-07-10 09:55:14.0Z
From: Taylor <heroxulei@hotmail.com>
Newsgroups: sybase.public.ase.general
Subject: How to interpret the query plan for stored procedure
Date: Fri, 10 Jul 2009 02:55:14 -0700 (PDT)
Organization: http://groups.google.com
Lines: 71
Message-ID: <c33f74d2-10a5-46af-b6a3-59f909ffdcdd@p36g2000vbn.googlegroups.com>
NNTP-Posting-Host: 192.250.175.25
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Trace: posting.google.com 1247219714 1783 127.0.0.1 (10 Jul 2009 09:55:14 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Fri, 10 Jul 2009 09:55:14 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: p36g2000vbn.googlegroups.com; posting-host=192.250.175.25; posting-account=CZamOQoAAAAUHTdhCDOUmRkybCZwE5Ph
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; .NET CLR 1.0.3705; InfoPath.1; .NET CLR 1.1.4322; .NET CLR 2.0.50727),gzip(gfe),gzip(gfe)
X-HTTP-Via: 1.1 nogrdcpx01a.statestr.com:80 (IronPort-WSA/5.6.2-102)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!nntp1.phx1.gblx.net!nntp.gblx.net!nntp.gblx.net!border2.nntp.dca.giganews.com!nntp.giganews.com!postnews.google.com!p36g2000vbn.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27984
Article PK: 77231

Hi,

The version we used is: Adaptive Server Enterprise/12.5.3/EBF 12330
ESD#1/P/Sun_svr4/OS 5.8/ase1253/1900/32-bit/FBO/Tue Jan 25 07:02:08
2005.

Take the following query plan as an example:
1> sp_showplan 78,null,null,null
2> go

QUERY PLAN FOR STATEMENT 8 (at line 57).


STEP 1
The type of query is SELECT.
Evaluate Ungrouped SUM OR AVERAGE AGGREGATE.
Evaluate Ungrouped SUM OR AVERAGE AGGREGATE.

FROM TABLE
#temp_portfolio
p
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 16 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

....

STEP 2
The type of query is INSERT.
The update mode is direct.

FROM TABLE
...

STEP
1
The type of query is SELECT (into
Worktable1).
GROUP
BY
Evaluate Grouped COUNT
AGGREGATE.

FROM
TABLE

#temp_portfolio

p

...

STEP
2
The type of query is
INSERT.
The update mode is
direct.

FROM
TABLE

...

Total estimated I/O cost for statement 8 (at line 57): 2147483647.

The session is executing a stored procedure. What does this mean:
step1, step2 and again step1, step2? not step1,2,3,4


"Mark A. Parsons" <iron_horse Posted on 2009-07-10 11:35:57.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: How to interpret the query plan for stored procedure
References: <c33f74d2-10a5-46af-b6a3-59f909ffdcdd@p36g2000vbn.googlegroups.com>
In-Reply-To: <c33f74d2-10a5-46af-b6a3-59f909ffdcdd@p36g2000vbn.googlegroups.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 090708-0, 07/08/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a57279d$2@forums-3-dub.sybase.com>
Date: 10 Jul 2009 04:35:57 -0700
X-Trace: forums-3-dub.sybase.com 1247225757 10.22.241.152 (10 Jul 2009 04:35:57 -0700)
X-Original-Trace: 10 Jul 2009 04:35:57 -0700, vip152.sybase.com
Lines: 77
X-Authenticated-User: TeamSybase
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27985
Article PK: 77232

You might want to try comparing the query plan steps to the query from line 57 of the stored proc to see if you can
match the various steps to the various components of the query in question.

Alternatively you could try posting said query back here to the newsgroup ... along with the *complete* query plan.

Taylor wrote:
> Hi,
>
> The version we used is: Adaptive Server Enterprise/12.5.3/EBF 12330
> ESD#1/P/Sun_svr4/OS 5.8/ase1253/1900/32-bit/FBO/Tue Jan 25 07:02:08
> 2005.
>
> Take the following query plan as an example:
> 1> sp_showplan 78,null,null,null
> 2> go
>
> QUERY PLAN FOR STATEMENT 8 (at line 57).
>
>
> STEP 1
> The type of query is SELECT.
> Evaluate Ungrouped SUM OR AVERAGE AGGREGATE.
> Evaluate Ungrouped SUM OR AVERAGE AGGREGATE.
>
> FROM TABLE
> #temp_portfolio
> p
> Nested iteration.
> Table Scan.
> Forward scan.
> Positioning at start of table.
> Using I/O Size 16 Kbytes for data pages.
> With LRU Buffer Replacement Strategy for data pages.
>
> ....
>
> STEP 2
> The type of query is INSERT.
> The update mode is direct.
>
> FROM TABLE
> ...
>
> STEP
> 1
> The type of query is SELECT (into
> Worktable1).
> GROUP
> BY
> Evaluate Grouped COUNT
> AGGREGATE.
>
> FROM
> TABLE
>
> #temp_portfolio
>
> p
>
> ...
>
> STEP
> 2
> The type of query is
> INSERT.
> The update mode is
> direct.
>
> FROM
> TABLE
>
> ...
>
> Total estimated I/O cost for statement 8 (at line 57): 2147483647.
>
> The session is executing a stored procedure. What does this mean:
> step1, step2 and again step1, step2? not step1,2,3,4


Taylor Posted on 2009-07-13 05:24:46.0Z
From: Taylor <heroxulei@hotmail.com>
Newsgroups: sybase.public.ase.general
Subject: Re: How to interpret the query plan for stored procedure
Date: Sun, 12 Jul 2009 22:24:46 -0700 (PDT)
Organization: http://groups.google.com
Lines: 604
Message-ID: <87448151-667e-49cb-8ddb-15208fd4d431@g23g2000vbr.googlegroups.com>
References: <c33f74d2-10a5-46af-b6a3-59f909ffdcdd@p36g2000vbn.googlegroups.com> <4a57279d$2@forums-3-dub.sybase.com>
NNTP-Posting-Host: 192.250.175.25
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Trace: posting.google.com 1247462687 29389 127.0.0.1 (13 Jul 2009 05:24:47 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Mon, 13 Jul 2009 05:24:47 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: g23g2000vbr.googlegroups.com; posting-host=192.250.175.25; posting-account=CZamOQoAAAAUHTdhCDOUmRkybCZwE5Ph
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; .NET CLR 1.0.3705; InfoPath.1; .NET CLR 1.1.4322; .NET CLR 2.0.50727),gzip(gfe),gzip(gfe)
X-HTTP-Via: 1.1 nogrdcpx01a.statestr.com:80 (IronPort-WSA/5.6.2-102)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!nntp1.phx1.gblx.net!nntp.gblx.net!nntp.gblx.net!border2.nntp.dca.giganews.com!nntp.giganews.com!postnews.google.com!g23g2000vbr.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27986
Article PK: 77233

Hi, please help me out. I posted stored procedure and query plan here.

1> sp_helptext edwsp_inv_oppenhm_pos_xtr
2> go
# Lines of Text
---------------
11

(1 row affected)


text


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

/****************************************************
Proc name: edwsp_inv_oppenhm_pos_xtr
Database:
Author: Amit Agrawal
Manager: Mike Gee
Project: Investec Custom Extract
Date: 03/09/2009
Called by: Autosys Job
Description: Generates Position Extract SOPOSIT.txt
Parameters:
@pPrtfo_grp_cd Portfolio Group code/Fund Number.
@pasof_dt Period Start Date

Comments:


o This report shows closing security positions as at
current business day. Ref 032
*****************************************************/

create procedure edwsp_inv_oppenhm_pos_xtr
(
@pPrtfo_grp_cd varchar(30),
@pasof_dt datetime,
@pxtr_typ char(3) = 'XTR'

)
as

declare @val_type varchar(12),@closedate datetime,@pasof_dt_int int,
@gmt_dttm char(5)

-- get GMT time.
select @gmt_dttm = convert(char(5),getutcdate(),18)
select @closedate='12/31/3999'


select @pasof_dt_int = datediff(dd, '01/01/1900', @pasof_dt)

create table #temp_qty (
SSTM_SCRTY_ID int,
NOMIN_QTY float,
LCL_MKT_PRCE float,
LCL_MKT_VLE float,
PRTFO_CD varchar(24))

create table #temp_portfolio(
SSTM_PRTFO_ID int not null,
PRTFO_CD char(12) not null,
PRTCT_DSCRN varchar(100) null,
PRTFO_CRNCY char(3) null,
PRTFO_UNDRG_CRNCY char(30) null,
PRTFO_CNTRY char(2) null,
GRP_CD char (24) null)


/* get portfolios for given portfolio*/
exec edwsp_inv_get_portfolio @pPrtfo_grp_cd

/* get sum of quantity by security code */
insert #temp_qty
select s.SSTM_SCRTY_ID, sum(v.NOMIN_QTY),v.LCL_MKT_PRCE,sum
(v.LCL_MKT_VLE),p.GRP_CD
from vw_valuation v , #temp_portfolio p , vw_security s
where p.SSTM_PRTFO_ID = v.SSTM_PRTFO_ID
and v.SSTM_SCRTY_ID = s.SSTM_SCRTY_ID
and v.EFCTE_DT = @pasof_dt_int
and p.PRTFO_CD like 'OGBGROW%'
and v.PSTN_TYPE = 'TRADED'
and s.SCRTY_TYPE_DSCRN <>'Cash Equivalents'

union all

select s.SSTM_SCRTY_ID,
v.NOMIN_QTY,v.LCL_MKT_PRCE,v.LCL_MKT_VLE,p.PRTFO_CD
from vw_valuation v , #temp_portfolio p , vw_security s
where p.SSTM_PRTFO_ID = v.SSTM_PRTFO_ID
and v.SSTM_SCRTY_ID = s.SSTM_SCRTY_ID
and v.EFCTE_DT = @pasof_dt_int
and p.PRTFO_CD not like 'OGBGROW%'
and v.PSTN_TYPE = 'TRADED'
and s.SCRTY_TYPE_DSCRN <>'Cash Equivalents'
group by s.SSTM_SCRTY_ID

select
@pPrtfo_grp_cd,
s.ISIN,
t.NOMIN_QTY,
s.ISSR_NME,
s.SCRTY_LNG_NME,
t.LCL_MKT_PRCE,
t.LCL_MKT_VLE,
null'FMC_CODE',
t.PRTFO_CD


from vw_security s , #temp_qty t

where
s.SSTM_SCRTY_ID = t.SSTM_SCRTY_ID
order by t.PRTFO_CD,s.ISIN


(11 rows affected, return status = 0)


1> sp_showplan 78,null,null,null
2> go

QUERY PLAN FOR STATEMENT 8 (at line 57).


STEP 1
The type of query is SELECT.
Evaluate Ungrouped SUM OR AVERAGE AGGREGATE.
Evaluate Ungrouped SUM OR AVERAGE AGGREGATE.

FROM TABLE
#temp_portfolio
p
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 16 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
rdm_portfolio_dim
rpd
Nested iteration.
Index : edw_x3_portfolio_dim
Forward scan.
Positioning by key.
Keys are:
pdh_odw_acid ASC
Using I/O Size 2 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
rdm_valuation_fact
rvf
Nested iteration.
Index : x2_valuation_fact
Forward scan.
Positioning by key.
Keys are:
vfr_portfolio_id ASC
vfr_valuation_date ASC
vfr_position_type ASC
Using I/O Size 16 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 16 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
rdm_security_dim
rsd
Nested iteration.
Index : x1_security_dim
Forward scan.
Positioning by key.
Keys are:
sdr_security_id ASC
Using I/O Size 2 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
rpt_security
Nested iteration.
Index : x1_rpt_security
Forward scan.
Positioning by key.
Keys are:
SSTM_SCRTY_ID ASC
Using I/O Size 2 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
edw_val_f2_data
f2
Nested iteration.
Index : x2_edw_val_f2_data
Forward scan.
Positioning by key.
Index contains all needed columns. Base table will not be
read.
Keys are:
sstm_val_id ASC
Using I/O Size 2 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.

STEP 2
The type of query is INSERT.
The update mode is direct.

FROM TABLE
rdm_valuation_fact
rvf
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 16 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
rdm_security_dim
rsd
Nested iteration.
Index : x1_security_dim
Forward scan.
Positioning by key.
Index contains all needed columns. Base table will not be
read.
Keys are:
sdr_security_id ASC
Using I/O Size 2 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.

FROM TABLE
rdm_portfolio_dim
rpd
Nested iteration.
Index : x1_portfolio_dim
Forward scan.
Positioning by key.
Index contains all needed columns. Base table will not be
read.
Keys are:
pdr_portfolio_id ASC
Using I/O Size 2 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.

FROM TABLE
edw_val_f2_data
f2
Nested iteration.
Index : x2_edw_val_f2_data
Forward scan.
Positioning by key.
Index contains all needed columns. Base table will not be
read.
Keys are:
sstm_val_id ASC
Using I/O Size 2 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.

FROM TABLE
#temp_portfolio
p
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 16 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
rpt_security
Nested iteration.
Index : x1_rpt_security
Forward scan.
Positioning at index start.
Index contains all needed columns. Base table will not be
read.
Using I/O Size 16 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
TO TABLE
#temp_qty
Using I/O Size 2 Kbytes for data pages.

STEP
1
The type of query is SELECT (into
Worktable1).
GROUP
BY
Evaluate Grouped COUNT
AGGREGATE.

FROM
TABLE

#temp_portfolio

p
Nested
iteration.
Table
Scan.
Forward
scan.
Positioning at start of
table.
Using I/O Size 16 Kbytes for data
pages.
With LRU Buffer Replacement Strategy for data
pages.

FROM
TABLE

rdm_portfolio_dim

rpd
Nested
iteration.
Index :
edw_x3_portfolio_dim
Forward
scan.
Positioning by
key.
Keys
are:
pdh_odw_acid
ASC
Using I/O Size 2 Kbytes for index leaf
pages.
With LRU Buffer Replacement Strategy for index leaf
pages.
Using I/O Size 2 Kbytes for data
pages.
With LRU Buffer Replacement Strategy for data
pages.

FROM
TABLE

rdm_valuation_fact

rvf
Nested
iteration.
Index :
x2_valuation_fact
Forward
scan.
Positioning by
key.
Keys
are:
vfr_portfolio_id
ASC
vfr_valuation_date
ASC
vfr_position_type
ASC
Using I/O Size 16 Kbytes for index leaf
pages.
With LRU Buffer Replacement Strategy for index leaf
pages.
Using I/O Size 16 Kbytes for data
pages.
With LRU Buffer Replacement Strategy for data
pages.

FROM
TABLE

rdm_security_dim

rsd
Nested
iteration.
Index :
x1_security_dim
Forward
scan.
Positioning by
key.
Keys
are:
sdr_security_id
ASC
Using I/O Size 2 Kbytes for index leaf
pages.
With LRU Buffer Replacement Strategy for index leaf
pages.
Using I/O Size 2 Kbytes for data
pages.
With LRU Buffer Replacement Strategy for data
pages.

FROM
TABLE

rpt_security
Nested
iteration.
Index :
x1_rpt_security
Forward
scan.
Positioning by
key.
Keys
are:
SSTM_SCRTY_ID
ASC
Using I/O Size 2 Kbytes for index leaf
pages.
With LRU Buffer Replacement Strategy for index leaf
pages.
Using I/O Size 2 Kbytes for data
pages.
With LRU Buffer Replacement Strategy for data
pages.

FROM
TABLE

edw_val_f2_data

f2
Nested
iteration.
Index :
x2_edw_val_f2_data
Forward
scan.
Positioning by
key.
Index contains all needed columns. Base table will not be
read.
Keys
are:
sstm_val_id
ASC
Using I/O Size 2 Kbytes for index leaf
pages.
With LRU Buffer Replacement Strategy for index leaf
pages.
TO
TABLE

Worktable1.

STEP
2
The type of query is
INSERT.
The update mode is
direct.

FROM
TABLE

rdm_valuation_fact

rvf
Nested
iteration.
Table
Scan.
Forward
scan.
Positioning at start of
table.
Using I/O Size 16 Kbytes for data
pages.
With LRU Buffer Replacement Strategy for data
pages.

FROM
TABLE

rdm_security_dim

rsd
Nested
iteration.
Index :
x1_security_dim
Forward
scan.
Positioning by
key.
Index contains all needed columns. Base table will not be
read.
Keys
are:
sdr_security_id
ASC
Using I/O Size 2 Kbytes for index leaf
pages.
With LRU Buffer Replacement Strategy for index leaf
pages.

FROM
TABLE

rdm_portfolio_dim

rpd
Nested
iteration.
Index :
x1_portfolio_dim
Forward
scan.
Positioning by
key.
Index contains all needed columns. Base table will not be
read.
Keys
are:
pdr_portfolio_id
ASC
Using I/O Size 2 Kbytes for index leaf
pages.
With LRU Buffer Replacement Strategy for index leaf
pages.

FROM
TABLE

edw_val_f2_data

f2
Nested
iteration.
Index :
x2_edw_val_f2_data
Forward
scan.
Positioning by
key.
Index contains all needed columns. Base table will not be
read.
Keys
are:
sstm_val_id
ASC
Using I/O Size 2 Kbytes for index leaf
pages.
With LRU Buffer Replacement Strategy for index leaf
pages.

FROM
TABLE

#temp_portfolio

p
Nested
iteration.
Table
Scan.
Forward
scan.
Positioning at start of
table.
Using I/O Size 16 Kbytes for data
pages.
With LRU Buffer Replacement Strategy for data
pages.

FROM
TABLE

Worktable1.
Nested
iteration.
Table
Scan.
Forward
scan.
Positioning at start of
table.
Using I/O Size 16 Kbytes for data
pages.
With MRU Buffer Replacement Strategy for data
pages.
TO
TABLE

#temp_qty
Using I/O Size 2 Kbytes for data pages.

Total estimated I/O cost for statement 8 (at line 57): 2147483647.


Michael Peppler [Team Sybase] Posted on 2009-07-13 17:41:20.0Z
From: "Michael Peppler [Team Sybase]" <mpeppler@peppler.org>
Organization: Peppler Consulting SARL
Subject: Re: How to interpret the query plan for stored procedure
User-Agent: Pan/0.14.2 (This is not a psychotic episode. It's a cleansing moment of clarity.)
Message-ID: <pan.2009.07.13.17.41.13.653150@peppler.org>
Newsgroups: sybase.public.ase.general
References: <c33f74d2-10a5-46af-b6a3-59f909ffdcdd@p36g2000vbn.googlegroups.com> <4a57279d$2@forums-3-dub.sybase.com> <87448151-667e-49cb-8ddb-15208fd4d431@g23g2000vbr.googlegroups.com>
To: bvenditti@odyssey-group.com
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 13 Jul 2009 10:41:20 -0700
X-Trace: forums-3-dub.sybase.com 1247506880 10.22.241.152 (13 Jul 2009 10:41:20 -0700)
X-Original-Trace: 13 Jul 2009 10:41:20 -0700, vip152.sybase.com
Lines: 620
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27989
Article PK: 77235

Hmmm... si je lis bien le code ça sort de Triple'A...

Au fait -

La question sur le left outer join - je pense que tu n'as pas (ou peu)
pratiqué les jointures "ANSI". La forme ANSI n'est pas forcément
nécessaire quand on fait des jointures entre deux ou trois tables, mais
quand on passe à 8 et plus cela donne une lisibilité à la jointure qui
est un réel plus!

Michael

On Sun, 12 Jul 2009 22:24:46 -0700, Taylor wrote:

> Hi, please help me out. I posted stored procedure and query plan here.
>
> 1> sp_helptext edwsp_inv_oppenhm_pos_xtr
> 2> go
> # Lines of Text
> ---------------
> 11
>
> (1 row affected)
>
>
> text
>
>
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> /****************************************************
> Proc name: edwsp_inv_oppenhm_pos_xtr
> Database:
> Author: Amit Agrawal
> Manager: Mike Gee
> Project: Investec Custom Extract
> Date: 03/09/2009
> Called by: Autosys Job
> Description: Generates Position Extract SOPOSIT.txt
> Parameters:
> @pPrtfo_grp_cd Portfolio Group code/Fund Number.
> @pasof_dt Period Start Date
>
> Comments:
>
>
> o This report shows closing security positions as at
> current business day. Ref 032
> *****************************************************/
>
> create procedure edwsp_inv_oppenhm_pos_xtr
> (
> @pPrtfo_grp_cd varchar(30),
> @pasof_dt datetime,
> @pxtr_typ char(3) = 'XTR'
>
> )
> as
>
> declare @val_type varchar(12),@closedate datetime,@pasof_dt_int int,
> @gmt_dttm char(5)
>
> -- get GMT time.
> select @gmt_dttm = convert(char(5),getutcdate(),18)
> select @closedate='12/31/3999'
>
>
> select @pasof_dt_int = datediff(dd, '01/01/1900', @pasof_dt)
>
> create table #temp_qty (
> SSTM_SCRTY_ID int,
> NOMIN_QTY float,
> LCL_MKT_PRCE float,
> LCL_MKT_VLE float,
> PRTFO_CD varchar(24))
>
> create table #temp_portfolio(
> SSTM_PRTFO_ID int not null,
> PRTFO_CD char(12) not null,
> PRTCT_DSCRN varchar(100) null,
> PRTFO_CRNCY char(3) null,
> PRTFO_UNDRG_CRNCY char(30) null,
> PRTFO_CNTRY char(2) null,
> GRP_CD char (24) null)
>
>
> /* get portfolios for given portfolio*/
> exec edwsp_inv_get_portfolio @pPrtfo_grp_cd
>
> /* get sum of quantity by security code */
> insert #temp_qty
> select s.SSTM_SCRTY_ID, sum(v.NOMIN_QTY),v.LCL_MKT_PRCE,sum
> (v.LCL_MKT_VLE),p.GRP_CD
> from vw_valuation v , #temp_portfolio p , vw_security s
> where p.SSTM_PRTFO_ID = v.SSTM_PRTFO_ID
> and v.SSTM_SCRTY_ID = s.SSTM_SCRTY_ID
> and v.EFCTE_DT = @pasof_dt_int
> and p.PRTFO_CD like 'OGBGROW%'
> and v.PSTN_TYPE = 'TRADED'
> and s.SCRTY_TYPE_DSCRN <>'Cash Equivalents'
>
> union all
>
> select s.SSTM_SCRTY_ID,
> v.NOMIN_QTY,v.LCL_MKT_PRCE,v.LCL_MKT_VLE,p.PRTFO_CD
> from vw_valuation v , #temp_portfolio p , vw_security s
> where p.SSTM_PRTFO_ID = v.SSTM_PRTFO_ID
> and v.SSTM_SCRTY_ID = s.SSTM_SCRTY_ID
> and v.EFCTE_DT = @pasof_dt_int
> and p.PRTFO_CD not like 'OGBGROW%'
> and v.PSTN_TYPE = 'TRADED'
> and s.SCRTY_TYPE_DSCRN <>'Cash Equivalents'
> group by s.SSTM_SCRTY_ID
>
> select
> @pPrtfo_grp_cd,
> s.ISIN,
> t.NOMIN_QTY,
> s.ISSR_NME,
> s.SCRTY_LNG_NME,
> t.LCL_MKT_PRCE,
> t.LCL_MKT_VLE,
> null'FMC_CODE',
> t.PRTFO_CD
>
>
> from vw_security s , #temp_qty t
>
> where
> s.SSTM_SCRTY_ID = t.SSTM_SCRTY_ID
> order by t.PRTFO_CD,s.ISIN
>
>
> (11 rows affected, return status = 0)
>
>
> 1> sp_showplan 78,null,null,null
> 2> go
>
> QUERY PLAN FOR STATEMENT 8 (at line 57).
>
>
> STEP 1
> The type of query is SELECT.
> Evaluate Ungrouped SUM OR AVERAGE AGGREGATE.
> Evaluate Ungrouped SUM OR AVERAGE AGGREGATE.
>
> FROM TABLE
> #temp_portfolio
> p
> Nested iteration.
> Table Scan.
> Forward scan.
> Positioning at start of table.
> Using I/O Size 16 Kbytes for data pages.
> With LRU Buffer Replacement Strategy for data pages.
>
> FROM TABLE
> rdm_portfolio_dim
> rpd
> Nested iteration.
> Index : edw_x3_portfolio_dim
> Forward scan.
> Positioning by key.
> Keys are:
> pdh_odw_acid ASC
> Using I/O Size 2 Kbytes for index leaf pages.
> With LRU Buffer Replacement Strategy for index leaf pages.
> Using I/O Size 2 Kbytes for data pages.
> With LRU Buffer Replacement Strategy for data pages.
>
> FROM TABLE
> rdm_valuation_fact
> rvf
> Nested iteration.
> Index : x2_valuation_fact
> Forward scan.
> Positioning by key.
> Keys are:
> vfr_portfolio_id ASC
> vfr_valuation_date ASC
> vfr_position_type ASC
> Using I/O Size 16 Kbytes for index leaf pages.
> With LRU Buffer Replacement Strategy for index leaf pages.
> Using I/O Size 16 Kbytes for data pages.
> With LRU Buffer Replacement Strategy for data pages.
>
> FROM TABLE
> rdm_security_dim
> rsd
> Nested iteration.
> Index : x1_security_dim
> Forward scan.
> Positioning by key.
> Keys are:
> sdr_security_id ASC
> Using I/O Size 2 Kbytes for index leaf pages.
> With LRU Buffer Replacement Strategy for index leaf pages.
> Using I/O Size 2 Kbytes for data pages.
> With LRU Buffer Replacement Strategy for data pages.
>
> FROM TABLE
> rpt_security
> Nested iteration.
> Index : x1_rpt_security
> Forward scan.
> Positioning by key.
> Keys are:
> SSTM_SCRTY_ID ASC
> Using I/O Size 2 Kbytes for index leaf pages.
> With LRU Buffer Replacement Strategy for index leaf pages.
> Using I/O Size 2 Kbytes for data pages.
> With LRU Buffer Replacement Strategy for data pages.
>
> FROM TABLE
> edw_val_f2_data
> f2
> Nested iteration.
> Index : x2_edw_val_f2_data
> Forward scan.
> Positioning by key.
> Index contains all needed columns. Base table will not be
> read.
> Keys are:
> sstm_val_id ASC
> Using I/O Size 2 Kbytes for index leaf pages.
> With LRU Buffer Replacement Strategy for index leaf pages.
>
> STEP 2
> The type of query is INSERT.
> The update mode is direct.
>
> FROM TABLE
> rdm_valuation_fact
> rvf
> Nested iteration.
> Table Scan.
> Forward scan.
> Positioning at start of table.
> Using I/O Size 16 Kbytes for data pages.
> With LRU Buffer Replacement Strategy for data pages.
>
> FROM TABLE
> rdm_security_dim
> rsd
> Nested iteration.
> Index : x1_security_dim
> Forward scan.
> Positioning by key.
> Index contains all needed columns. Base table will not be
> read.
> Keys are:
> sdr_security_id ASC
> Using I/O Size 2 Kbytes for index leaf pages.
> With LRU Buffer Replacement Strategy for index leaf pages.
>
> FROM TABLE
> rdm_portfolio_dim
> rpd
> Nested iteration.
> Index : x1_portfolio_dim
> Forward scan.
> Positioning by key.
> Index contains all needed columns. Base table will not be
> read.
> Keys are:
> pdr_portfolio_id ASC
> Using I/O Size 2 Kbytes for index leaf pages.
> With LRU Buffer Replacement Strategy for index leaf pages.
>
> FROM TABLE
> edw_val_f2_data
> f2
> Nested iteration.
> Index : x2_edw_val_f2_data
> Forward scan.
> Positioning by key.
> Index contains all needed columns. Base table will not be
> read.
> Keys are:
> sstm_val_id ASC
> Using I/O Size 2 Kbytes for index leaf pages.
> With LRU Buffer Replacement Strategy for index leaf pages.
>
> FROM TABLE
> #temp_portfolio
> p
> Nested iteration.
> Table Scan.
> Forward scan.
> Positioning at start of table.
> Using I/O Size 16 Kbytes for data pages.
> With LRU Buffer Replacement Strategy for data pages.
>
> FROM TABLE
> rpt_security
> Nested iteration.
> Index : x1_rpt_security
> Forward scan.
> Positioning at index start.
> Index contains all needed columns. Base table will not be
> read.
> Using I/O Size 16 Kbytes for index leaf pages.
> With LRU Buffer Replacement Strategy for index leaf pages.
> TO TABLE
> #temp_qty
> Using I/O Size 2 Kbytes for data pages.
>
> STEP
> 1
> The type of query is SELECT (into
> Worktable1).
> GROUP
> BY
> Evaluate Grouped COUNT
> AGGREGATE.
>
> FROM
> TABLE
>
> #temp_portfolio
>
> p
> Nested
> iteration.
> Table
> Scan.
> Forward
> scan.
> Positioning at start of
> table.
> Using I/O Size 16 Kbytes for data
> pages.
> With LRU Buffer Replacement Strategy for data
> pages.
>
> FROM
> TABLE
>
> rdm_portfolio_dim
>
> rpd
> Nested
> iteration.
> Index :
> edw_x3_portfolio_dim
> Forward
> scan.
> Positioning by
> key.
> Keys
> are:
> pdh_odw_acid
> ASC
> Using I/O Size 2 Kbytes for index leaf
> pages.
> With LRU Buffer Replacement Strategy for index leaf
> pages.
> Using I/O Size 2 Kbytes for data
> pages.
> With LRU Buffer Replacement Strategy for data
> pages.
>
> FROM
> TABLE
>
> rdm_valuation_fact
>
> rvf
> Nested
> iteration.
> Index :
> x2_valuation_fact
> Forward
> scan.
> Positioning by
> key.
> Keys
> are:
> vfr_portfolio_id
> ASC
> vfr_valuation_date
> ASC
> vfr_position_type
> ASC
> Using I/O Size 16 Kbytes for index leaf
> pages.
> With LRU Buffer Replacement Strategy for index leaf
> pages.
> Using I/O Size 16 Kbytes for data
> pages.
> With LRU Buffer Replacement Strategy for data
> pages.
>
> FROM
> TABLE
>
> rdm_security_dim
>
> rsd
> Nested
> iteration.
> Index :
> x1_security_dim
> Forward
> scan.
> Positioning by
> key.
> Keys
> are:
> sdr_security_id
> ASC
> Using I/O Size 2 Kbytes for index leaf
> pages.
> With LRU Buffer Replacement Strategy for index leaf
> pages.
> Using I/O Size 2 Kbytes for data
> pages.
> With LRU Buffer Replacement Strategy for data
> pages.
>
> FROM
> TABLE
>
> rpt_security
> Nested
> iteration.
> Index :
> x1_rpt_security
> Forward
> scan.
> Positioning by
> key.
> Keys
> are:
> SSTM_SCRTY_ID
> ASC
> Using I/O Size 2 Kbytes for index leaf
> pages.
> With LRU Buffer Replacement Strategy for index leaf
> pages.
> Using I/O Size 2 Kbytes for data
> pages.
> With LRU Buffer Replacement Strategy for data
> pages.
>
> FROM
> TABLE
>
> edw_val_f2_data
>
> f2
> Nested
> iteration.
> Index :
> x2_edw_val_f2_data
> Forward
> scan.
> Positioning by
> key.
> Index contains all needed columns. Base table will not be
> read.
> Keys
> are:
> sstm_val_id
> ASC
> Using I/O Size 2 Kbytes for index leaf
> pages.
> With LRU Buffer Replacement Strategy for index leaf
> pages.
> TO
> TABLE
>
> Worktable1.
>
> STEP
> 2
> The type of query is
> INSERT.
> The update mode is
> direct.
>
> FROM
> TABLE
>
> rdm_valuation_fact
>
> rvf
> Nested
> iteration.
> Table
> Scan.
> Forward
> scan.
> Positioning at start of
> table.
> Using I/O Size 16 Kbytes for data
> pages.
> With LRU Buffer Replacement Strategy for data
> pages.
>
> FROM
> TABLE
>
> rdm_security_dim
>
> rsd
> Nested
> iteration.
> Index :
> x1_security_dim
> Forward
> scan.
> Positioning by
> key.
> Index contains all needed columns. Base table will not be
> read.
> Keys
> are:
> sdr_security_id
> ASC
> Using I/O Size 2 Kbytes for index leaf
> pages.
> With LRU Buffer Replacement Strategy for index leaf
> pages.
>
> FROM
> TABLE
>
> rdm_portfolio_dim
>
> rpd
> Nested
> iteration.
> Index :
> x1_portfolio_dim
> Forward
> scan.
> Positioning by
> key.
> Index contains all needed columns. Base table will not be
> read.
> Keys
> are:
> pdr_portfolio_id
> ASC
> Using I/O Size 2 Kbytes for index leaf
> pages.
> With LRU Buffer Replacement Strategy for index leaf
> pages.
>
> FROM
> TABLE
>
> edw_val_f2_data
>
> f2
> Nested
> iteration.
> Index :
> x2_edw_val_f2_data
> Forward
> scan.
> Positioning by
> key.
> Index contains all needed columns. Base table will not be
> read.
> Keys
> are:
> sstm_val_id
> ASC
> Using I/O Size 2 Kbytes for index leaf
> pages.
> With LRU Buffer Replacement Strategy for index leaf
> pages.
>
> FROM
> TABLE
>
> #temp_portfolio
>
> p
> Nested
> iteration.
> Table
> Scan.
> Forward
> scan.
> Positioning at start of
> table.
> Using I/O Size 16 Kbytes for data
> pages.
> With LRU Buffer Replacement Strategy for data
> pages.
>
> FROM
> TABLE
>
> Worktable1.
> Nested
> iteration.
> Table
> Scan.
> Forward
> scan.
> Positioning at start of
> table.
> Using I/O Size 16 Kbytes for data
> pages.
> With MRU Buffer Replacement Strategy for data
> pages.
> TO
> TABLE
>
> #temp_qty
> Using I/O Size 2 Kbytes for data pages.
>
> Total estimated I/O cost for statement 8 (at line 57): 2147483647.


Taylor Posted on 2009-07-14 07:01:48.0Z
From: Taylor <heroxulei@hotmail.com>
Newsgroups: sybase.public.ase.general
Subject: Re: How to interpret the query plan for stored procedure
Date: Tue, 14 Jul 2009 00:01:48 -0700 (PDT)
Organization: http://groups.google.com
Lines: 23
Message-ID: <09744e00-d365-4d60-8b1f-21020e5839a2@k20g2000vbp.googlegroups.com>
References: <c33f74d2-10a5-46af-b6a3-59f909ffdcdd@p36g2000vbn.googlegroups.com> <4a57279d$2@forums-3-dub.sybase.com> <87448151-667e-49cb-8ddb-15208fd4d431@g23g2000vbr.googlegroups.com> <pan.2009.07.13.17.41.13.653150@peppler.org>
NNTP-Posting-Host: 192.250.175.25
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
X-Trace: posting.google.com 1247554909 14670 127.0.0.1 (14 Jul 2009 07:01:49 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Tue, 14 Jul 2009 07:01:49 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: k20g2000vbp.googlegroups.com; posting-host=192.250.175.25; posting-account=CZamOQoAAAAUHTdhCDOUmRkybCZwE5Ph
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; .NET CLR 1.0.3705; InfoPath.1; .NET CLR 1.1.4322; .NET CLR 2.0.50727),gzip(gfe),gzip(gfe)
X-HTTP-Via: 1.1 nogrdcpx01a.statestr.com:80 (IronPort-WSA/5.6.2-102)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!newsfeed.yul.equant.net!nntp1.roc.gblx.net!nntp.gblx.net!nntp.gblx.net!nlpi057.nbdc.sbc.com!prodigy.net!border1.nntp.dca.giganews.com!nntp.giganews.com!postnews.google.com!k20g2000vbp.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27994
Article PK: 77241

On 7月14日, 上午1时41分, "Michael Peppler [Team Sybase]"

<mpepp...@peppler.org> wrote:
> Hmmm... si je lis bien le code ça sort de Triple'A...
>
> Au fait -
>
> La question sur le left outer join - je pense que tu n'as pas (ou peu)
> pratiqué les jointures "ANSI". La forme ANSI n'est pas forcément
> nécessaire quand on fait des jointures entre deux ou trois tables, mais
> quand on passe à 8 et plus cela donne une lisibilité à la jointure qui
> est un réel plus!
>
> Michael
>
>
>
> On Sun, 12 Jul 2009 22:24:46 -0700, Taylor wrote:

I thought the statement 8 is not union all, that's the 7th statement,
am I right?


"Mark A. Parsons" <iron_horse Posted on 2009-07-14 10:18:12.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: How to interpret the query plan for stored procedure
References: <c33f74d2-10a5-46af-b6a3-59f909ffdcdd@p36g2000vbn.googlegroups.com> <4a57279d$2@forums-3-dub.sybase.com> <87448151-667e-49cb-8ddb-15208fd4d431@g23g2000vbr.googlegroups.com> <pan.2009.07.13.17.41.13.653150@peppler.org> <09744e00-d365-4d60-8b1f-21020e5839a2@k20g2000vbp.googlegroups.com>
In-Reply-To: <09744e00-d365-4d60-8b1f-21020e5839a2@k20g2000vbp.googlegroups.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
X-Antivirus: avast! (VPS 090713-0, 07/13/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a5c5b64$3@forums-3-dub.sybase.com>
Date: 14 Jul 2009 03:18:12 -0700
X-Trace: forums-3-dub.sybase.com 1247566692 10.22.241.152 (14 Jul 2009 03:18:12 -0700)
X-Original-Trace: 14 Jul 2009 03:18:12 -0700, vip152.sybase.com
Lines: 34
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27996
Article PK: 77243

Statements:

1 - declare
2,3,4 - select @<variable> =
5,6 - create table
7 - exec proc
8 - insert/select/union-all

-----------

I'd suggest you break the insert/select/union-all into 2 separate insert/select statements (so the current statement #8
will become #8 & #9). This should make it a little easier for you to read the query plan ... and hopefully show you
where there are some problems with the current query coding.

Taylor wrote:
> On 7月14日, 上午1时41分, "Michael Peppler [Team Sybase]"
> <mpepp...@peppler.org> wrote:
>> Hmmm... si je lis bien le code ça sort de Triple'A...
>>
>> Au fait -
>>
>> La question sur le left outer join - je pense que tu n'as pas (ou peu)
>> pratiqué les jointures "ANSI". La forme ANSI n'est pas forcément
>> nécessaire quand on fait des jointures entre deux ou trois tables, mais
>> quand on passe à 8 et plus cela donne une lisibilité à la jointure qui
>> est un réel plus!
>>
>> Michael
>>
>>
>>
>> On Sun, 12 Jul 2009 22:24:46 -0700, Taylor wrote:
> I thought the statement 8 is not union all, that's the 7th statement,
> am I right?


Taylor Posted on 2009-07-15 03:03:04.0Z
From: Taylor <heroxulei@hotmail.com>
Newsgroups: sybase.public.ase.general
Subject: Re: How to interpret the query plan for stored procedure
Date: Tue, 14 Jul 2009 20:03:04 -0700 (PDT)
Organization: http://groups.google.com
Lines: 113
Message-ID: <4645a983-2087-4543-abad-d8af8eae0bd8@g6g2000vbr.googlegroups.com>
References: <c33f74d2-10a5-46af-b6a3-59f909ffdcdd@p36g2000vbn.googlegroups.com> <4a57279d$2@forums-3-dub.sybase.com> <87448151-667e-49cb-8ddb-15208fd4d431@g23g2000vbr.googlegroups.com> <pan.2009.07.13.17.41.13.653150@peppler.org> <09744e00-d365-4d60-8b1f-21020e5839a2@k20g2000vbp.googlegroups.com> <4a5c5b64$3@forums-3-dub.sybase.com>
NNTP-Posting-Host: 192.250.175.25
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
X-Trace: posting.google.com 1247626984 4808 127.0.0.1 (15 Jul 2009 03:03:04 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Wed, 15 Jul 2009 03:03:04 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: g6g2000vbr.googlegroups.com; posting-host=192.250.175.25; posting-account=CZamOQoAAAAUHTdhCDOUmRkybCZwE5Ph
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; .NET CLR 1.0.3705; InfoPath.1; .NET CLR 1.1.4322; .NET CLR 2.0.50727),gzip(gfe),gzip(gfe)
X-HTTP-Via: 1.1 nogrdcpx01a.statestr.com:80 (IronPort-WSA/5.6.2-102)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!nntp1.phx1.gblx.net!nntp.gblx.net!nntp.gblx.net!newsfeed.news2me.com!nx02.iad01.newshosting.com!newshosting.com!69.16.185.16.MISMATCH!npeer02.iad.highwinds-media.com!news.highwinds-media.com!feed-me.highwinds-media.com!postnews.google.com!g6g2000vbr.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28008
Article PK: 77254

On 7月14日, 下午6时18分, "Mark A. Parsons"

<iron_horse@no_spamola.compuserve.com> wrote:
> Statements:
>
> 1     - declare
> 2,3,4 - select @<variable> =
> 5,6   - create table
> 7     - exec proc
> 8     - insert/select/union-all
>

I tested and found the 'declare statement' is not regarded as a
statement. I am a little confused.

1> sp_helptext p8
2> go
# Lines of Text
---------------
2

(1 row affected)
text



------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------
----------------
create proc p8
as
begin
declare @par1 int
select @par1=5
create table #tab1
(col1 int)

--Adaptive Server has expanded all '*' elements in the following
statement
select test2.col1 from test2

--Adaptive Server has expanded all '*' elements in the followi
ng statement
select test3.col2 from test3
end

1> set showplan on
2> go
1> exec p8
2> go

QUERY PLAN FOR STATEMENT 1 (at line 1).


STEP 1
The type of query is EXECUTE.


QUERY PLAN FOR STATEMENT 1 (at line 5).


STEP 1
The type of query is SELECT.


QUERY PLAN FOR STATEMENT 2 (at line 6).


STEP 1
The type of query is CREATE TABLE.


QUERY PLAN FOR STATEMENT 3 (at line 8).


STEP 1
The type of query is SELECT.

FROM TABLE
test2
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.


QUERY PLAN FOR STATEMENT 4 (at line 9).


STEP 1
The type of query is SELECT.

FROM TABLE
test3
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

col1
-----------
3

(1 row affected)
col2
-----------
4


"Mark A. Parsons" <iron_horse Posted on 2009-07-15 11:24:32.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: How to interpret the query plan for stored procedure
References: <c33f74d2-10a5-46af-b6a3-59f909ffdcdd@p36g2000vbn.googlegroups.com> <4a57279d$2@forums-3-dub.sybase.com> <87448151-667e-49cb-8ddb-15208fd4d431@g23g2000vbr.googlegroups.com> <pan.2009.07.13.17.41.13.653150@peppler.org> <09744e00-d365-4d60-8b1f-21020e5839a2@k20g2000vbp.googlegroups.com> <4a5c5b64$3@forums-3-dub.sybase.com> <4645a983-2087-4543-abad-d8af8eae0bd8@g6g2000vbr.googlegroups.com>
In-Reply-To: <4645a983-2087-4543-abad-d8af8eae0bd8@g6g2000vbr.googlegroups.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
X-Antivirus: avast! (VPS 090714-0, 07/14/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a5dbc70$2@forums-3-dub.sybase.com>
Date: 15 Jul 2009 04:24:32 -0700
X-Trace: forums-3-dub.sybase.com 1247657072 10.22.241.152 (15 Jul 2009 04:24:32 -0700)
X-Original-Trace: 15 Jul 2009 04:24:32 -0700, vip152.sybase.com
Lines: 128
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28011
Article PK: 77257

OK. I'd suggest you open a case with Sybase TechSupport.

Personally, I ignore the statement number that shows up in the query plan output. (As you've seen, it doesn't always
match up with the source code.)

Also, the line number you see in the query plan may also be 'wrong', but I can usually use it to get me close to the
query in question.

Ultimately I use the information in the query plan (table names, update vs insert vs delete, aggregates, etc) to zero in
on the query in question.

In your case you can also use the process of elimination to rule out the various statements/queries of your stored proc,
ie, which queries have nothing to do with the tables mentioned in the query plan?

'course, this entire thread is based on the assumption that you've correctly matched the original query plan with the
correct stored proc.

Taylor wrote:
> On 7月14日, 下午6时18分, "Mark A. Parsons"
> <iron_horse@no_spamola.compuserve.com> wrote:
>> Statements:
>>
>> 1 - declare
>> 2,3,4 - select @<variable> =
>> 5,6 - create table
>> 7 - exec proc
>> 8 - insert/select/union-all
>>
>
> I tested and found the 'declare statement' is not regarded as a
> statement. I am a little confused.
>
> 1> sp_helptext p8
> 2> go
> # Lines of Text
> ---------------
> 2
>
> (1 row affected)
> text
>
>
>
> ------------------------------------------------------------------------------------------
> -------------------------------------------------------------------------------------------
> ----------------
> create proc p8
> as
> begin
> declare @par1 int
> select @par1=5
> create table #tab1
> (col1 int)
>
> --Adaptive Server has expanded all '*' elements in the following
> statement
> select test2.col1 from test2
>
> --Adaptive Server has expanded all '*' elements in the followi
> ng statement
> select test3.col2 from test3
> end
>
> 1> set showplan on
> 2> go
> 1> exec p8
> 2> go
>
> QUERY PLAN FOR STATEMENT 1 (at line 1).
>
>
> STEP 1
> The type of query is EXECUTE.
>
>
> QUERY PLAN FOR STATEMENT 1 (at line 5).
>
>
> STEP 1
> The type of query is SELECT.
>
>
> QUERY PLAN FOR STATEMENT 2 (at line 6).
>
>
> STEP 1
> The type of query is CREATE TABLE.
>
>
> QUERY PLAN FOR STATEMENT 3 (at line 8).
>
>
> STEP 1
> The type of query is SELECT.
>
> FROM TABLE
> test2
> Nested iteration.
> Table Scan.
> Forward scan.
> Positioning at start of table.
> Using I/O Size 2 Kbytes for data pages.
> With LRU Buffer Replacement Strategy for data pages.
>
>
> QUERY PLAN FOR STATEMENT 4 (at line 9).
>
>
> STEP 1
> The type of query is SELECT.
>
> FROM TABLE
> test3
> Nested iteration.
> Table Scan.
> Forward scan.
> Positioning at start of table.
> Using I/O Size 2 Kbytes for data pages.
> With LRU Buffer Replacement Strategy for data pages.
>
> col1
> -----------
> 3
>
> (1 row affected)
> col2
> -----------
> 4


Jeff Tallman [Sybase] Posted on 2009-07-23 22:25:13.0Z
From: "Jeff Tallman [Sybase]" <jeff.tallman@sybase.com>
User-Agent: Thunderbird 2.0.0.22 (Windows/20090605)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: How to interpret the query plan for stored procedure
References: <c33f74d2-10a5-46af-b6a3-59f909ffdcdd@p36g2000vbn.googlegroups.com> <4a57279d$2@forums-3-dub.sybase.com> <87448151-667e-49cb-8ddb-15208fd4d431@g23g2000vbr.googlegroups.com> <pan.2009.07.13.17.41.13.653150@peppler.org> <09744e00-d365-4d60-8b1f-21020e5839a2@k20g2000vbp.googlegroups.com> <4a5c5b64$3@forums-3-dub.sybase.com> <4645a983-2087-4543-abad-d8af8eae0bd8@g6g2000vbr.googlegroups.com> <4a5dbc70$2@forums-3-dub.sybase.com>
In-Reply-To: <4a5dbc70$2@forums-3-dub.sybase.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a68e349$3@forums-3-dub.sybase.com>
Date: 23 Jul 2009 15:25:13 -0700
X-Trace: forums-3-dub.sybase.com 1248387913 10.22.241.152 (23 Jul 2009 15:25:13 -0700)
X-Original-Trace: 23 Jul 2009 15:25:13 -0700, vip152.sybase.com
Lines: 204
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28040
Article PK: 77286

Actually, the line numbers are accurate - and so are the statement
number - but you have to begin at what ASE has in syscomments for the
proc definition vs. the source code - or go to the source code and start
counting from the previous "go" as that is the batch terminator that
designates the line number. As Kevin also points out, compiler
instructions are not statements - so begin/end, declare, comments, etc.
are not counted as statements. ASE 15 improves on sp_helptext by
allowing sp_helptext procname, <start>, <numlines>,
'showsql,linenumbers'....but....


...confusing the issue here is the subproc....with the exec...the
statement number may be reported as the statement in execution path vs.
statement within the proc......given the showplan however, it does line
up with the union all query - I would take a careful look at the views
involved - joins between 2 views quite often can degrade to partial
cartesians as the same tables may be involved - but not "linked" by
joining between the views.

Otherwise, if you look at your SARGS:

and v.EFCTE_DT = @pasof_dt_int

..is not too usable in 12.5 as it is a @var defined in the proc so only
the total density is possible...

and p.PRTFO_CD not like 'OGBGROW%'

...from temp table...likely not indexed and likely not a large number
of rows...so....


and v.PSTN_TYPE = 'TRADED'

...possibly usable, but likely a low cardinality column - question is
whether 'TRADED' << 10% of the table. If so, it would help to be indexed.

and s.SCRTY_TYPE_DSCRN <>'Cash Equivalents'

...ditto with the above.


Overall, my guess is that the SARGS are complete wipeout, so ASE can
only do a queryplan based on the join costing.

A bigger point is that these are extended group by syntax queries:

select s.SSTM_SCRTY_ID, sum(v.NOMIN_QTY),v.LCL_MKT_PRCE,sum
(v.LCL_MKT_VLE),p.GRP_CD
...
union all
select s.SSTM_SCRTY_ID,
v.NOMIN_QTY,v.LCL_MKT_PRCE,v.LCL_MKT_VLE,p.PRTFO_CD
...
group by s.SSTM_SCRTY_ID

In the first, it has to rejoin with both v & p to pick up the other
columns (and no having clause) whereas there is nothing (aggregate wise)
to be grouped in the second. Not only was Mark correct in saying this
should be two queries to be better understood, it should be two queries
period as it currently is malformed. Additionally, I think the first
part needs the whacky group by/having syntax where having clause is
duplicate of where clause.


Jeff Tallman
Enterprise Data Management Products Technical Evangelism
jeff.tallman@sybase.com
http://blogs.sybase.com/database

Mark A. Parsons wrote:
> OK. I'd suggest you open a case with Sybase TechSupport.
>
> Personally, I ignore the statement number that shows up in the query
> plan output. (As you've seen, it doesn't always match up with the
> source code.)
>
> Also, the line number you see in the query plan may also be 'wrong', but
> I can usually use it to get me close to the query in question.
>
> Ultimately I use the information in the query plan (table names, update
> vs insert vs delete, aggregates, etc) to zero in on the query in question.
>
> In your case you can also use the process of elimination to rule out the
> various statements/queries of your stored proc, ie, which queries have
> nothing to do with the tables mentioned in the query plan?
>
> 'course, this entire thread is based on the assumption that you've
> correctly matched the original query plan with the correct stored proc.
>
> Taylor wrote:
>> On 7月14日, 下午6时18分, "Mark A. Parsons"
>> <iron_horse@no_spamola.compuserve.com> wrote:
>>> Statements:
>>>
>>> 1 - declare
>>> 2,3,4 - select @<variable> =
>>> 5,6 - create table
>>> 7 - exec proc
>>> 8 - insert/select/union-all
>>>
>>
>> I tested and found the 'declare statement' is not regarded as a
>> statement. I am a little confused.
>>
>> 1> sp_helptext p8
>> 2> go
>> # Lines of Text
>> ---------------
>> 2
>>
>> (1 row affected)
>> text
>>
>>
>>
>> ------------------------------------------------------------------------------------------
>>
>> -------------------------------------------------------------------------------------------
>>
>> ----------------
>> create proc p8
>> as
>> begin
>> declare @par1 int
>> select @par1=5
>> create table #tab1
>> (col1 int)
>>
>> --Adaptive Server has expanded all '*' elements in the following
>> statement
>> select test2.col1 from test2
>>
>> --Adaptive Server has expanded all '*' elements in the followi
>> ng statement
>> select test3.col2 from test3
>> end
>>
>> 1> set showplan on
>> 2> go
>> 1> exec p8
>> 2> go
>>
>> QUERY PLAN FOR STATEMENT 1 (at line 1).
>>
>>
>> STEP 1
>> The type of query is EXECUTE.
>>
>>
>> QUERY PLAN FOR STATEMENT 1 (at line 5).
>>
>>
>> STEP 1
>> The type of query is SELECT.
>>
>>
>> QUERY PLAN FOR STATEMENT 2 (at line 6).
>>
>>
>> STEP 1
>> The type of query is CREATE TABLE.
>>
>>
>> QUERY PLAN FOR STATEMENT 3 (at line 8).
>>
>>
>> STEP 1
>> The type of query is SELECT.
>>
>> FROM TABLE
>> test2
>> Nested iteration.
>> Table Scan.
>> Forward scan.
>> Positioning at start of table.
>> Using I/O Size 2 Kbytes for data pages.
>> With LRU Buffer Replacement Strategy for data pages.
>>
>>
>> QUERY PLAN FOR STATEMENT 4 (at line 9).
>>
>>
>> STEP 1
>> The type of query is SELECT.
>>
>> FROM TABLE
>> test3
>> Nested iteration.
>> Table Scan.
>> Forward scan.
>> Positioning at start of table.
>> Using I/O Size 2 Kbytes for data pages.
>> With LRU Buffer Replacement Strategy for data pages.
>>
>> col1
>> -----------
>> 3
>>
>> (1 row affected)
>> col2
>> -----------
>> 4


Jeff Tallman [Sybase] Posted on 2009-07-23 22:24:59.0Z
Message-ID: <4A68E33D.5020507@sybase.com>
From: "Jeff Tallman [Sybase]" <jeff.tallman@sybase.com>
User-Agent: Thunderbird 2.0.0.22 (Windows/20090605)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
To: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
Subject: Re: How to interpret the query plan for stored procedure
References: <c33f74d2-10a5-46af-b6a3-59f909ffdcdd@p36g2000vbn.googlegroups.com> <4a57279d$2@forums-3-dub.sybase.com> <87448151-667e-49cb-8ddb-15208fd4d431@g23g2000vbr.googlegroups.com> <pan.2009.07.13.17.41.13.653150@peppler.org> <09744e00-d365-4d60-8b1f-21020e5839a2@k20g2000vbp.googlegroups.com> <4a5c5b64$3@forums-3-dub.sybase.com> <4645a983-2087-4543-abad-d8af8eae0bd8@g6g2000vbr.googlegroups.com> <4a5dbc70$2@forums-3-dub.sybase.com>
In-Reply-To: <4a5dbc70$2@forums-3-dub.sybase.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 23 Jul 2009 15:24:59 -0700
X-Trace: forums-3-dub.sybase.com 1248387899 10.22.241.152 (23 Jul 2009 15:24:59 -0700)
X-Original-Trace: 23 Jul 2009 15:24:59 -0700, vip152.sybase.com
Lines: 204
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28039
Article PK: 77287

Actually, the line numbers are accurate - and so are the statement
number - but you have to begin at what ASE has in syscomments for the
proc definition vs. the source code - or go to the source code and start
counting from the previous "go" as that is the batch terminator that
designates the line number. As Kevin also points out, compiler
instructions are not statements - so begin/end, declare, comments, etc.
are not counted as statements. ASE 15 improves on sp_helptext by
allowing sp_helptext procname, <start>, <numlines>,
'showsql,linenumbers'....but....


...confusing the issue here is the subproc....with the exec...the
statement number may be reported as the statement in execution path vs.
statement within the proc......given the showplan however, it does line
up with the union all query - I would take a careful look at the views
involved - joins between 2 views quite often can degrade to partial
cartesians as the same tables may be involved - but not "linked" by
joining between the views.

Otherwise, if you look at your SARGS:

and v.EFCTE_DT = @pasof_dt_int

..is not too usable in 12.5 as it is a @var defined in the proc so only
the total density is possible...

and p.PRTFO_CD not like 'OGBGROW%'

...from temp table...likely not indexed and likely not a large number
of rows...so....


and v.PSTN_TYPE = 'TRADED'

...possibly usable, but likely a low cardinality column - question is
whether 'TRADED' << 10% of the table. If so, it would help to be indexed.

and s.SCRTY_TYPE_DSCRN <>'Cash Equivalents'

...ditto with the above.


Overall, my guess is that the SARGS are complete wipeout, so ASE can
only do a queryplan based on the join costing.

A bigger point is that these are extended group by syntax queries:

select s.SSTM_SCRTY_ID, sum(v.NOMIN_QTY),v.LCL_MKT_PRCE,sum
(v.LCL_MKT_VLE),p.GRP_CD
...
union all
select s.SSTM_SCRTY_ID,
v.NOMIN_QTY,v.LCL_MKT_PRCE,v.LCL_MKT_VLE,p.PRTFO_CD
...
group by s.SSTM_SCRTY_ID

In the first, it has to rejoin with both v & p to pick up the other
columns (and no having clause) whereas there is nothing (aggregate wise)
to be grouped in the second. Not only was Mark correct in saying this
should be two queries to be better understood, it should be two queries
period as it currently is malformed. Additionally, I think the first
part needs the whacky group by/having syntax where having clause is
duplicate of where clause.


Jeff Tallman
Enterprise Data Management Products Technical Evangelism
jeff.tallman@sybase.com
http://blogs.sybase.com/database

Mark A. Parsons wrote:
> OK. I'd suggest you open a case with Sybase TechSupport.
>
> Personally, I ignore the statement number that shows up in the query
> plan output. (As you've seen, it doesn't always match up with the
> source code.)
>
> Also, the line number you see in the query plan may also be 'wrong', but
> I can usually use it to get me close to the query in question.
>
> Ultimately I use the information in the query plan (table names, update
> vs insert vs delete, aggregates, etc) to zero in on the query in question.
>
> In your case you can also use the process of elimination to rule out the
> various statements/queries of your stored proc, ie, which queries have
> nothing to do with the tables mentioned in the query plan?
>
> 'course, this entire thread is based on the assumption that you've
> correctly matched the original query plan with the correct stored proc.
>
> Taylor wrote:
>> On 7月14日, 下午6时18分, "Mark A. Parsons"
>> <iron_horse@no_spamola.compuserve.com> wrote:
>>> Statements:
>>>
>>> 1 - declare
>>> 2,3,4 - select @<variable> =
>>> 5,6 - create table
>>> 7 - exec proc
>>> 8 - insert/select/union-all
>>>
>>
>> I tested and found the 'declare statement' is not regarded as a
>> statement. I am a little confused.
>>
>> 1> sp_helptext p8
>> 2> go
>> # Lines of Text
>> ---------------
>> 2
>>
>> (1 row affected)
>> text
>>
>>
>>
>> ------------------------------------------------------------------------------------------
>>
>> -------------------------------------------------------------------------------------------
>>
>> ----------------
>> create proc p8
>> as
>> begin
>> declare @par1 int
>> select @par1=5
>> create table #tab1
>> (col1 int)
>>
>> --Adaptive Server has expanded all '*' elements in the following
>> statement
>> select test2.col1 from test2
>>
>> --Adaptive Server has expanded all '*' elements in the followi
>> ng statement
>> select test3.col2 from test3
>> end
>>
>> 1> set showplan on
>> 2> go
>> 1> exec p8
>> 2> go
>>
>> QUERY PLAN FOR STATEMENT 1 (at line 1).
>>
>>
>> STEP 1
>> The type of query is EXECUTE.
>>
>>
>> QUERY PLAN FOR STATEMENT 1 (at line 5).
>>
>>
>> STEP 1
>> The type of query is SELECT.
>>
>>
>> QUERY PLAN FOR STATEMENT 2 (at line 6).
>>
>>
>> STEP 1
>> The type of query is CREATE TABLE.
>>
>>
>> QUERY PLAN FOR STATEMENT 3 (at line 8).
>>
>>
>> STEP 1
>> The type of query is SELECT.
>>
>> FROM TABLE
>> test2
>> Nested iteration.
>> Table Scan.
>> Forward scan.
>> Positioning at start of table.
>> Using I/O Size 2 Kbytes for data pages.
>> With LRU Buffer Replacement Strategy for data pages.
>>
>>
>> QUERY PLAN FOR STATEMENT 4 (at line 9).
>>
>>
>> STEP 1
>> The type of query is SELECT.
>>
>> FROM TABLE
>> test3
>> Nested iteration.
>> Table Scan.
>> Forward scan.
>> Positioning at start of table.
>> Using I/O Size 2 Kbytes for data pages.
>> With LRU Buffer Replacement Strategy for data pages.
>>
>> col1
>> -----------
>> 3
>>
>> (1 row affected)
>> col2
>> -----------
>> 4


Sherlock, Kevin [TeamSybase] Posted on 2009-07-15 15:48:25.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.general
References: <c33f74d2-10a5-46af-b6a3-59f909ffdcdd@p36g2000vbn.googlegroups.com> <4a57279d$2@forums-3-dub.sybase.com> <87448151-667e-49cb-8ddb-15208fd4d431@g23g2000vbr.googlegroups.com> <pan.2009.07.13.17.41.13.653150@peppler.org> <09744e00-d365-4d60-8b1f-21020e5839a2@k20g2000vbp.googlegroups.com> <4a5c5b64$3@forums-3-dub.sybase.com> <4645a983-2087-4543-abad-d8af8eae0bd8@g6g2000vbr.googlegroups.com>
Subject: Re: How to interpret the query plan for stored procedure
Lines: 117
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.3198
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a5dfa49$1@forums-3-dub.sybase.com>
Date: 15 Jul 2009 08:48:25 -0700
X-Trace: forums-3-dub.sybase.com 1247672905 10.22.241.152 (15 Jul 2009 08:48:25 -0700)
X-Original-Trace: 15 Jul 2009 08:48:25 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28012
Article PK: 77258

that's correct. "Declare" statements are not executable runtime statements,
but rather "compile time" statements that allocated memory structures.

"Taylor" <heroxulei@hotmail.com> wrote in message
news:4645a983-2087-4543-abad-d8af8eae0bd8@g6g2000vbr.googlegroups.com...
On 7?14?, ??6?18?, "Mark A. Parsons"

<iron_horse@no_spamola.compuserve.com> wrote:
> Statements:
>
> 1 - declare
> 2,3,4 - select @<variable> =
> 5,6 - create table
> 7 - exec proc
> 8 - insert/select/union-all
>

I tested and found the 'declare statement' is not regarded as a
statement. I am a little confused.

1> sp_helptext p8
2> go
# Lines of Text
---------------
2

(1 row affected)
text



------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------
----------------
create proc p8
as
begin
declare @par1 int
select @par1=5
create table #tab1
(col1 int)

--Adaptive Server has expanded all '*' elements in the following
statement
select test2.col1 from test2

--Adaptive Server has expanded all '*' elements in the followi
ng statement
select test3.col2 from test3
end

1> set showplan on
2> go
1> exec p8
2> go

QUERY PLAN FOR STATEMENT 1 (at line 1).


STEP 1
The type of query is EXECUTE.


QUERY PLAN FOR STATEMENT 1 (at line 5).


STEP 1
The type of query is SELECT.


QUERY PLAN FOR STATEMENT 2 (at line 6).


STEP 1
The type of query is CREATE TABLE.


QUERY PLAN FOR STATEMENT 3 (at line 8).


STEP 1
The type of query is SELECT.

FROM TABLE
test2
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.


QUERY PLAN FOR STATEMENT 4 (at line 9).


STEP 1
The type of query is SELECT.

FROM TABLE
test3
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

col1
-----------
3

(1 row affected)
col2
-----------
4


Michael Peppler [Team Sybase] Posted on 2009-07-14 18:24:30.0Z
From: "Michael Peppler [Team Sybase]" <mpeppler@peppler.org>
Organization: Peppler Consulting SARL
Subject: Re: How to interpret the query plan for stored procedure
User-Agent: Pan/0.14.2 (This is not a psychotic episode. It's a cleansing moment of clarity.)
Message-ID: <pan.2009.07.14.18.24.27.747502@peppler.org>
Newsgroups: sybase.public.ase.general
References: <c33f74d2-10a5-46af-b6a3-59f909ffdcdd@p36g2000vbn.googlegroups.com> <4a57279d$2@forums-3-dub.sybase.com> <87448151-667e-49cb-8ddb-15208fd4d431@g23g2000vbr.googlegroups.com> <pan.2009.07.13.17.41.13.653150@peppler.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 14 Jul 2009 11:24:30 -0700
X-Trace: forums-3-dub.sybase.com 1247595870 10.22.241.152 (14 Jul 2009 11:24:30 -0700)
X-Original-Trace: 14 Jul 2009 11:24:30 -0700, vip152.sybase.com
Lines: 625
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28005
Article PK: 77252

Oops - that was not intended for public consumption... my apologies.

On Mon, 13 Jul 2009 10:41:20 -0700, Michael Peppler [Team Sybase] wrote:

> Hmmm... si je lis bien le code ça sort de Triple'A...
>
> Au fait -
>
> La question sur le left outer join - je pense que tu n'as pas (ou peu)
> pratiqué les jointures "ANSI". La forme ANSI n'est pas forcément
> nécessaire quand on fait des jointures entre deux ou trois tables, mais
> quand on passe à 8 et plus cela donne une lisibilité à la jointure qui
> est un réel plus!
>
> Michael
>
>
> On Sun, 12 Jul 2009 22:24:46 -0700, Taylor wrote:
>
>> Hi, please help me out. I posted stored procedure and query plan here.
>>
>> 1> sp_helptext edwsp_inv_oppenhm_pos_xtr
>> 2> go
>> # Lines of Text
>> ---------------
>> 11
>>
>> (1 row affected)
>>
>>
>> text
>>
>>
>> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>>
>> /****************************************************
>> Proc name: edwsp_inv_oppenhm_pos_xtr
>> Database:
>> Author: Amit Agrawal
>> Manager: Mike Gee
>> Project: Investec Custom Extract
>> Date: 03/09/2009
>> Called by: Autosys Job
>> Description: Generates Position Extract SOPOSIT.txt
>> Parameters:
>> @pPrtfo_grp_cd Portfolio Group code/Fund Number.
>> @pasof_dt Period Start Date
>>
>> Comments:
>>
>>
>> o This report shows closing security positions as at
>> current business day. Ref 032
>> *****************************************************/
>>
>> create procedure edwsp_inv_oppenhm_pos_xtr
>> (
>> @pPrtfo_grp_cd varchar(30),
>> @pasof_dt datetime,
>> @pxtr_typ char(3) = 'XTR'
>>
>> )
>> as
>>
>> declare @val_type varchar(12),@closedate datetime,@pasof_dt_int int,
>> @gmt_dttm char(5)
>>
>> -- get GMT time.
>> select @gmt_dttm = convert(char(5),getutcdate(),18)
>> select @closedate='12/31/3999'
>>
>>
>> select @pasof_dt_int = datediff(dd, '01/01/1900', @pasof_dt)
>>
>> create table #temp_qty (
>> SSTM_SCRTY_ID int,
>> NOMIN_QTY float,
>> LCL_MKT_PRCE float,
>> LCL_MKT_VLE float,
>> PRTFO_CD varchar(24))
>>
>> create table #temp_portfolio(
>> SSTM_PRTFO_ID int not null,
>> PRTFO_CD char(12) not null,
>> PRTCT_DSCRN varchar(100) null,
>> PRTFO_CRNCY char(3) null,
>> PRTFO_UNDRG_CRNCY char(30) null,
>> PRTFO_CNTRY char(2) null,
>> GRP_CD char (24) null)
>>
>>
>> /* get portfolios for given portfolio*/
>> exec edwsp_inv_get_portfolio @pPrtfo_grp_cd
>>
>> /* get sum of quantity by security code */
>> insert #temp_qty
>> select s.SSTM_SCRTY_ID, sum(v.NOMIN_QTY),v.LCL_MKT_PRCE,sum
>> (v.LCL_MKT_VLE),p.GRP_CD
>> from vw_valuation v , #temp_portfolio p , vw_security s
>> where p.SSTM_PRTFO_ID = v.SSTM_PRTFO_ID
>> and v.SSTM_SCRTY_ID = s.SSTM_SCRTY_ID
>> and v.EFCTE_DT = @pasof_dt_int
>> and p.PRTFO_CD like 'OGBGROW%'
>> and v.PSTN_TYPE = 'TRADED'
>> and s.SCRTY_TYPE_DSCRN <>'Cash Equivalents'
>>
>> union all
>>
>> select s.SSTM_SCRTY_ID,
>> v.NOMIN_QTY,v.LCL_MKT_PRCE,v.LCL_MKT_VLE,p.PRTFO_CD
>> from vw_valuation v , #temp_portfolio p , vw_security s
>> where p.SSTM_PRTFO_ID = v.SSTM_PRTFO_ID
>> and v.SSTM_SCRTY_ID = s.SSTM_SCRTY_ID
>> and v.EFCTE_DT = @pasof_dt_int
>> and p.PRTFO_CD not like 'OGBGROW%'
>> and v.PSTN_TYPE = 'TRADED'
>> and s.SCRTY_TYPE_DSCRN <>'Cash Equivalents'
>> group by s.SSTM_SCRTY_ID
>>
>> select
>> @pPrtfo_grp_cd,
>> s.ISIN,
>> t.NOMIN_QTY,
>> s.ISSR_NME,
>> s.SCRTY_LNG_NME,
>> t.LCL_MKT_PRCE,
>> t.LCL_MKT_VLE,
>> null'FMC_CODE',
>> t.PRTFO_CD
>>
>>
>> from vw_security s , #temp_qty t
>>
>> where
>> s.SSTM_SCRTY_ID = t.SSTM_SCRTY_ID
>> order by t.PRTFO_CD,s.ISIN
>>
>>
>> (11 rows affected, return status = 0)
>>
>>
>> 1> sp_showplan 78,null,null,null
>> 2> go
>>
>> QUERY PLAN FOR STATEMENT 8 (at line 57).
>>
>>
>> STEP 1
>> The type of query is SELECT.
>> Evaluate Ungrouped SUM OR AVERAGE AGGREGATE.
>> Evaluate Ungrouped SUM OR AVERAGE AGGREGATE.
>>
>> FROM TABLE
>> #temp_portfolio
>> p
>> Nested iteration.
>> Table Scan.
>> Forward scan.
>> Positioning at start of table.
>> Using I/O Size 16 Kbytes for data pages.
>> With LRU Buffer Replacement Strategy for data pages.
>>
>> FROM TABLE
>> rdm_portfolio_dim
>> rpd
>> Nested iteration.
>> Index : edw_x3_portfolio_dim
>> Forward scan.
>> Positioning by key.
>> Keys are:
>> pdh_odw_acid ASC
>> Using I/O Size 2 Kbytes for index leaf pages.
>> With LRU Buffer Replacement Strategy for index leaf pages.
>> Using I/O Size 2 Kbytes for data pages.
>> With LRU Buffer Replacement Strategy for data pages.
>>
>> FROM TABLE
>> rdm_valuation_fact
>> rvf
>> Nested iteration.
>> Index : x2_valuation_fact
>> Forward scan.
>> Positioning by key.
>> Keys are:
>> vfr_portfolio_id ASC
>> vfr_valuation_date ASC
>> vfr_position_type ASC
>> Using I/O Size 16 Kbytes for index leaf pages.
>> With LRU Buffer Replacement Strategy for index leaf pages.
>> Using I/O Size 16 Kbytes for data pages.
>> With LRU Buffer Replacement Strategy for data pages.
>>
>> FROM TABLE
>> rdm_security_dim
>> rsd
>> Nested iteration.
>> Index : x1_security_dim
>> Forward scan.
>> Positioning by key.
>> Keys are:
>> sdr_security_id ASC
>> Using I/O Size 2 Kbytes for index leaf pages.
>> With LRU Buffer Replacement Strategy for index leaf pages.
>> Using I/O Size 2 Kbytes for data pages.
>> With LRU Buffer Replacement Strategy for data pages.
>>
>> FROM TABLE
>> rpt_security
>> Nested iteration.
>> Index : x1_rpt_security
>> Forward scan.
>> Positioning by key.
>> Keys are:
>> SSTM_SCRTY_ID ASC
>> Using I/O Size 2 Kbytes for index leaf pages.
>> With LRU Buffer Replacement Strategy for index leaf pages.
>> Using I/O Size 2 Kbytes for data pages.
>> With LRU Buffer Replacement Strategy for data pages.
>>
>> FROM TABLE
>> edw_val_f2_data
>> f2
>> Nested iteration.
>> Index : x2_edw_val_f2_data
>> Forward scan.
>> Positioning by key.
>> Index contains all needed columns. Base table will not be
>> read.
>> Keys are:
>> sstm_val_id ASC
>> Using I/O Size 2 Kbytes for index leaf pages.
>> With LRU Buffer Replacement Strategy for index leaf pages.
>>
>> STEP 2
>> The type of query is INSERT.
>> The update mode is direct.
>>
>> FROM TABLE
>> rdm_valuation_fact
>> rvf
>> Nested iteration.
>> Table Scan.
>> Forward scan.
>> Positioning at start of table.
>> Using I/O Size 16 Kbytes for data pages.
>> With LRU Buffer Replacement Strategy for data pages.
>>
>> FROM TABLE
>> rdm_security_dim
>> rsd
>> Nested iteration.
>> Index : x1_security_dim
>> Forward scan.
>> Positioning by key.
>> Index contains all needed columns. Base table will not be
>> read.
>> Keys are:
>> sdr_security_id ASC
>> Using I/O Size 2 Kbytes for index leaf pages.
>> With LRU Buffer Replacement Strategy for index leaf pages.
>>
>> FROM TABLE
>> rdm_portfolio_dim
>> rpd
>> Nested iteration.
>> Index : x1_portfolio_dim
>> Forward scan.
>> Positioning by key.
>> Index contains all needed columns. Base table will not be
>> read.
>> Keys are:
>> pdr_portfolio_id ASC
>> Using I/O Size 2 Kbytes for index leaf pages.
>> With LRU Buffer Replacement Strategy for index leaf pages.
>>
>> FROM TABLE
>> edw_val_f2_data
>> f2
>> Nested iteration.
>> Index : x2_edw_val_f2_data
>> Forward scan.
>> Positioning by key.
>> Index contains all needed columns. Base table will not be
>> read.
>> Keys are:
>> sstm_val_id ASC
>> Using I/O Size 2 Kbytes for index leaf pages.
>> With LRU Buffer Replacement Strategy for index leaf pages.
>>
>> FROM TABLE
>> #temp_portfolio
>> p
>> Nested iteration.
>> Table Scan.
>> Forward scan.
>> Positioning at start of table.
>> Using I/O Size 16 Kbytes for data pages.
>> With LRU Buffer Replacement Strategy for data pages.
>>
>> FROM TABLE
>> rpt_security
>> Nested iteration.
>> Index : x1_rpt_security
>> Forward scan.
>> Positioning at index start.
>> Index contains all needed columns. Base table will not be
>> read.
>> Using I/O Size 16 Kbytes for index leaf pages.
>> With LRU Buffer Replacement Strategy for index leaf pages.
>> TO TABLE
>> #temp_qty
>> Using I/O Size 2 Kbytes for data pages.
>>
>> STEP
>> 1
>> The type of query is SELECT (into
>> Worktable1).
>> GROUP
>> BY
>> Evaluate Grouped COUNT
>> AGGREGATE.
>>
>> FROM
>> TABLE
>>
>> #temp_portfolio
>>
>> p
>> Nested
>> iteration.
>> Table
>> Scan.
>> Forward
>> scan.
>> Positioning at start of
>> table.
>> Using I/O Size 16 Kbytes for data
>> pages.
>> With LRU Buffer Replacement Strategy for data
>> pages.
>>
>> FROM
>> TABLE
>>
>> rdm_portfolio_dim
>>
>> rpd
>> Nested
>> iteration.
>> Index :
>> edw_x3_portfolio_dim
>> Forward
>> scan.
>> Positioning by
>> key.
>> Keys
>> are:
>> pdh_odw_acid
>> ASC
>> Using I/O Size 2 Kbytes for index leaf
>> pages.
>> With LRU Buffer Replacement Strategy for index leaf
>> pages.
>> Using I/O Size 2 Kbytes for data
>> pages.
>> With LRU Buffer Replacement Strategy for data
>> pages.
>>
>> FROM
>> TABLE
>>
>> rdm_valuation_fact
>>
>> rvf
>> Nested
>> iteration.
>> Index :
>> x2_valuation_fact
>> Forward
>> scan.
>> Positioning by
>> key.
>> Keys
>> are:
>> vfr_portfolio_id
>> ASC
>> vfr_valuation_date
>> ASC
>> vfr_position_type
>> ASC
>> Using I/O Size 16 Kbytes for index leaf
>> pages.
>> With LRU Buffer Replacement Strategy for index leaf
>> pages.
>> Using I/O Size 16 Kbytes for data
>> pages.
>> With LRU Buffer Replacement Strategy for data
>> pages.
>>
>> FROM
>> TABLE
>>
>> rdm_security_dim
>>
>> rsd
>> Nested
>> iteration.
>> Index :
>> x1_security_dim
>> Forward
>> scan.
>> Positioning by
>> key.
>> Keys
>> are:
>> sdr_security_id
>> ASC
>> Using I/O Size 2 Kbytes for index leaf
>> pages.
>> With LRU Buffer Replacement Strategy for index leaf
>> pages.
>> Using I/O Size 2 Kbytes for data
>> pages.
>> With LRU Buffer Replacement Strategy for data
>> pages.
>>
>> FROM
>> TABLE
>>
>> rpt_security
>> Nested
>> iteration.
>> Index :
>> x1_rpt_security
>> Forward
>> scan.
>> Positioning by
>> key.
>> Keys
>> are:
>> SSTM_SCRTY_ID
>> ASC
>> Using I/O Size 2 Kbytes for index leaf
>> pages.
>> With LRU Buffer Replacement Strategy for index leaf
>> pages.
>> Using I/O Size 2 Kbytes for data
>> pages.
>> With LRU Buffer Replacement Strategy for data
>> pages.
>>
>> FROM
>> TABLE
>>
>> edw_val_f2_data
>>
>> f2
>> Nested
>> iteration.
>> Index :
>> x2_edw_val_f2_data
>> Forward
>> scan.
>> Positioning by
>> key.
>> Index contains all needed columns. Base table will not be
>> read.
>> Keys
>> are:
>> sstm_val_id
>> ASC
>> Using I/O Size 2 Kbytes for index leaf
>> pages.
>> With LRU Buffer Replacement Strategy for index leaf
>> pages.
>> TO
>> TABLE
>>
>> Worktable1.
>>
>> STEP
>> 2
>> The type of query is
>> INSERT.
>> The update mode is
>> direct.
>>
>> FROM
>> TABLE
>>
>> rdm_valuation_fact
>>
>> rvf
>> Nested
>> iteration.
>> Table
>> Scan.
>> Forward
>> scan.
>> Positioning at start of
>> table.
>> Using I/O Size 16 Kbytes for data
>> pages.
>> With LRU Buffer Replacement Strategy for data
>> pages.
>>
>> FROM
>> TABLE
>>
>> rdm_security_dim
>>
>> rsd
>> Nested
>> iteration.
>> Index :
>> x1_security_dim
>> Forward
>> scan.
>> Positioning by
>> key.
>> Index contains all needed columns. Base table will not be
>> read.
>> Keys
>> are:
>> sdr_security_id
>> ASC
>> Using I/O Size 2 Kbytes for index leaf
>> pages.
>> With LRU Buffer Replacement Strategy for index leaf
>> pages.
>>
>> FROM
>> TABLE
>>
>> rdm_portfolio_dim
>>
>> rpd
>> Nested
>> iteration.
>> Index :
>> x1_portfolio_dim
>> Forward
>> scan.
>> Positioning by
>> key.
>> Index contains all needed columns. Base table will not be
>> read.
>> Keys
>> are:
>> pdr_portfolio_id
>> ASC
>> Using I/O Size 2 Kbytes for index leaf
>> pages.
>> With LRU Buffer Replacement Strategy for index leaf
>> pages.
>>
>> FROM
>> TABLE
>>
>> edw_val_f2_data
>>
>> f2
>> Nested
>> iteration.
>> Index :
>> x2_edw_val_f2_data
>> Forward
>> scan.
>> Positioning by
>> key.
>> Index contains all needed columns. Base table will not be
>> read.
>> Keys
>> are:
>> sstm_val_id
>> ASC
>> Using I/O Size 2 Kbytes for index leaf
>> pages.
>> With LRU Buffer Replacement Strategy for index leaf
>> pages.
>>
>> FROM
>> TABLE
>>
>> #temp_portfolio
>>
>> p
>> Nested
>> iteration.
>> Table
>> Scan.
>> Forward
>> scan.
>> Positioning at start of
>> table.
>> Using I/O Size 16 Kbytes for data
>> pages.
>> With LRU Buffer Replacement Strategy for data
>> pages.
>>
>> FROM
>> TABLE
>>
>> Worktable1.
>> Nested
>> iteration.
>> Table
>> Scan.
>> Forward
>> scan.
>> Positioning at start of
>> table.
>> Using I/O Size 16 Kbytes for data
>> pages.
>> With MRU Buffer Replacement Strategy for data
>> pages.
>> TO
>> TABLE
>>
>> #temp_qty
>> Using I/O Size 2 Kbytes for data pages.
>>
>> Total estimated I/O cost for statement 8 (at line 57): 2147483647.


Taylor Posted on 2009-07-15 03:06:26.0Z
From: Taylor <heroxulei@hotmail.com>
Newsgroups: sybase.public.ase.general
Subject: Re: How to interpret the query plan for stored procedure
Date: Tue, 14 Jul 2009 20:06:26 -0700 (PDT)
Organization: http://groups.google.com
Lines: 106
Message-ID: <a45875eb-a7a5-485c-898e-eca6b8d635e3@f16g2000vbf.googlegroups.com>
References: <c33f74d2-10a5-46af-b6a3-59f909ffdcdd@p36g2000vbn.googlegroups.com> <4a57279d$2@forums-3-dub.sybase.com> <87448151-667e-49cb-8ddb-15208fd4d431@g23g2000vbr.googlegroups.com> <pan.2009.07.13.17.41.13.653150@peppler.org> <pan.2009.07.14.18.24.27.747502@peppler.org>
NNTP-Posting-Host: 192.250.175.25
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Trace: posting.google.com 1247627186 31306 127.0.0.1 (15 Jul 2009 03:06:26 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Wed, 15 Jul 2009 03:06:26 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: f16g2000vbf.googlegroups.com; posting-host=192.250.175.25; posting-account=CZamOQoAAAAUHTdhCDOUmRkybCZwE5Ph
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; .NET CLR 1.0.3705; InfoPath.1; .NET CLR 1.1.4322; .NET CLR 2.0.50727),gzip(gfe),gzip(gfe)
X-HTTP-Via: 1.1 nogrdcpx01a.statestr.com:80 (IronPort-WSA/5.6.2-102)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!nntp1.phx1.gblx.net!nntp.gblx.net!nntp.gblx.net!border2.nntp.dca.giganews.com!nntp.giganews.com!postnews.google.com!f16g2000vbf.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28009
Article PK: 77256

I tested and found the 'declare' is not regarded as a statement. I am
confused.

1> sp_helptext p8
2> go
# Lines of Text
---------------
2

(1 row affected)
text



-----------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
----------------
create proc p8
as
begin
declare @par1 int
select @par1=5
create table #tab1
(col1 int)

--Adaptive Server has expanded all '*' elements in the following
statement
select test2.col1 from test2

--Adaptive Server has expanded all '*' elements in the followi
ng statement
select test3.col2 from test3
end




(2 rows affected)
(return status = 0)
1> set showplan on
2> go
1> exec p8
2> go

QUERY PLAN FOR STATEMENT 1 (at line 1).


STEP 1
The type of query is EXECUTE.


QUERY PLAN FOR STATEMENT 1 (at line 5).


STEP 1
The type of query is SELECT.


QUERY PLAN FOR STATEMENT 2 (at line 6).


STEP 1
The type of query is CREATE TABLE.


QUERY PLAN FOR STATEMENT 3 (at line 8).


STEP 1
The type of query is SELECT.

FROM TABLE
test2
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.


QUERY PLAN FOR STATEMENT 4 (at line 9).


STEP 1
The type of query is SELECT.

FROM TABLE
test3
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

col1
-----------
3

(1 row affected)
col2
-----------
4

(1 row affected)


"Mark A. Parsons" <iron_horse Posted on 2009-07-13 12:13:59.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: How to interpret the query plan for stored procedure
References: <c33f74d2-10a5-46af-b6a3-59f909ffdcdd@p36g2000vbn.googlegroups.com> <4a57279d$2@forums-3-dub.sybase.com> <87448151-667e-49cb-8ddb-15208fd4d431@g23g2000vbr.googlegroups.com>
In-Reply-To: <87448151-667e-49cb-8ddb-15208fd4d431@g23g2000vbr.googlegroups.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 090710-0, 07/10/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a5b2507$1@forums-3-dub.sybase.com>
Date: 13 Jul 2009 05:13:59 -0700
X-Trace: forums-3-dub.sybase.com 1247487239 10.22.241.152 (13 Jul 2009 05:13:59 -0700)
X-Original-Trace: 13 Jul 2009 05:13:59 -0700, vip152.sybase.com
Lines: 555
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27987
Article PK: 77236

The query plan appears to be related to the UNION ALL query. ("Duh, Mark!" ?)

You'll see a 'STEP' entry in the query plan for each separate SELECT in the UNION ALL query.

The UNION ALL appears to be referencing some views (vw_valuation, vw_security); these are expanded into their underlying
tables (eg, rdm%) in the query plan.

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

The first part of the UNION ALL query references 2 aggregate columns and 3 non-aggregate columns in the SELECT list.

The second part of the UNION ALL query references 0 aggregate columns and 5 non-aggregate columns in the SELECT list.

The UNION ALL has a GROUP BY clause that references 1 non-aggregate column.

Normal, ANSI-standard queries will have all non-aggregate columns in the SELECT clause also listed in the GROUP BY
clause ... and all GROUP BY columns listed in the SELECT clause.

Your query has a mismatch of columns between the SELECT and GROUP BY clauses, ie, you have a non-ANSI standard query.

While the ASE optimizer will allow non-ANSI standard queries, it may have problems coming up with an efficient
(correct?) query plan.

Without knowing what your view definitions look like it's possible that there could be additional non-ANSI code in the
views which is further complicating the optimizer's job.

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

With an ANSI-standard coded query (including the view definitions when pulled into the main query) you'll typically see
1 query plan.

With a non-ANSI-standard coded query you'll typically see what looks like 2 sets of query plans. In this scenario the
optimizer isn't quite sure what you're asking for so it will run 2 different sets of the query and then join the 2
result sets to (hopefully) come up with the final result set that you're looking for.

In your case you have a non-ANSI-standard query that is causing the optimizer problems. The optimizer is having to
compile the query in 2 different fashions (hence the 2 sets of STEP 1/2 sections).

You need to find the person who wrote this query and have them review what exactly it is that they're looking to
accomplish in the UNION ALL; they will most likely need to a) rewrite the query or b) replace the query with differend
logic/queries. Regardless, the SQL coder should try to insure the query is written to ANSI-standards when it comes to
performing aggregations ... you'll get a cleaner/easier-to-read query plan, a more efficient running query, and (most
likely) the desired result.

> /* get sum of quantity by security code */
> insert #temp_qty
> select s.SSTM_SCRTY_ID, sum(v.NOMIN_QTY),v.LCL_MKT_PRCE,sum
> (v.LCL_MKT_VLE),p.GRP_CD
> from vw_valuation v , #temp_portfolio p , vw_security s
> where p.SSTM_PRTFO_ID = v.SSTM_PRTFO_ID
> and v.SSTM_SCRTY_ID = s.SSTM_SCRTY_ID
> and v.EFCTE_DT = @pasof_dt_int
> and p.PRTFO_CD like 'OGBGROW%'
> and v.PSTN_TYPE = 'TRADED'
> and s.SCRTY_TYPE_DSCRN <>'Cash Equivalents'
>
> union all
>
> select s.SSTM_SCRTY_ID,
> v.NOMIN_QTY,v.LCL_MKT_PRCE,v.LCL_MKT_VLE,p.PRTFO_CD
> from vw_valuation v , #temp_portfolio p , vw_security s
> where p.SSTM_PRTFO_ID = v.SSTM_PRTFO_ID
> and v.SSTM_SCRTY_ID = s.SSTM_SCRTY_ID
> and v.EFCTE_DT = @pasof_dt_int
> and p.PRTFO_CD not like 'OGBGROW%'
> and v.PSTN_TYPE = 'TRADED'
> and s.SCRTY_TYPE_DSCRN <>'Cash Equivalents'
> group by s.SSTM_SCRTY_ID

> 1> sp_showplan 78,null,null,null
> 2> go
>
> QUERY PLAN FOR STATEMENT 8 (at line 57).
>
>
> STEP 1
> The type of query is SELECT.
> Evaluate Ungrouped SUM OR AVERAGE AGGREGATE.
> Evaluate Ungrouped SUM OR AVERAGE AGGREGATE.
>
> FROM TABLE
> #temp_portfolio
> p
> Nested iteration.
> Table Scan.
> Forward scan.
> Positioning at start of table.
> Using I/O Size 16 Kbytes for data pages.
> With LRU Buffer Replacement Strategy for data pages.
>
> FROM TABLE
> rdm_portfolio_dim
> rpd
> Nested iteration.
> Index : edw_x3_portfolio_dim
> Forward scan.
> Positioning by key.
> Keys are:
> pdh_odw_acid ASC
> Using I/O Size 2 Kbytes for index leaf pages.
> With LRU Buffer Replacement Strategy for index leaf pages.
> Using I/O Size 2 Kbytes for data pages.
> With LRU Buffer Replacement Strategy for data pages.
>
> FROM TABLE
> rdm_valuation_fact
> rvf
> Nested iteration.
> Index : x2_valuation_fact
> Forward scan.
> Positioning by key.
> Keys are:
> vfr_portfolio_id ASC
> vfr_valuation_date ASC
> vfr_position_type ASC
> Using I/O Size 16 Kbytes for index leaf pages.
> With LRU Buffer Replacement Strategy for index leaf pages.
> Using I/O Size 16 Kbytes for data pages.
> With LRU Buffer Replacement Strategy for data pages.
>
> FROM TABLE
> rdm_security_dim
> rsd
> Nested iteration.
> Index : x1_security_dim
> Forward scan.
> Positioning by key.
> Keys are:
> sdr_security_id ASC
> Using I/O Size 2 Kbytes for index leaf pages.
> With LRU Buffer Replacement Strategy for index leaf pages.
> Using I/O Size 2 Kbytes for data pages.
> With LRU Buffer Replacement Strategy for data pages.
>
> FROM TABLE
> rpt_security
> Nested iteration.
> Index : x1_rpt_security
> Forward scan.
> Positioning by key.
> Keys are:
> SSTM_SCRTY_ID ASC
> Using I/O Size 2 Kbytes for index leaf pages.
> With LRU Buffer Replacement Strategy for index leaf pages.
> Using I/O Size 2 Kbytes for data pages.
> With LRU Buffer Replacement Strategy for data pages.
>
> FROM TABLE
> edw_val_f2_data
> f2
> Nested iteration.
> Index : x2_edw_val_f2_data
> Forward scan.
> Positioning by key.
> Index contains all needed columns. Base table will not be
> read.
> Keys are:
> sstm_val_id ASC
> Using I/O Size 2 Kbytes for index leaf pages.
> With LRU Buffer Replacement Strategy for index leaf pages.
>
> STEP 2
> The type of query is INSERT.
> The update mode is direct.
>
> FROM TABLE
> rdm_valuation_fact
> rvf
> Nested iteration.
> Table Scan.
> Forward scan.
> Positioning at start of table.
> Using I/O Size 16 Kbytes for data pages.
> With LRU Buffer Replacement Strategy for data pages.
>
> FROM TABLE
> rdm_security_dim
> rsd
> Nested iteration.
> Index : x1_security_dim
> Forward scan.
> Positioning by key.
> Index contains all needed columns. Base table will not be
> read.
> Keys are:
> sdr_security_id ASC
> Using I/O Size 2 Kbytes for index leaf pages.
> With LRU Buffer Replacement Strategy for index leaf pages.
>
> FROM TABLE
> rdm_portfolio_dim
> rpd
> Nested iteration.
> Index : x1_portfolio_dim
> Forward scan.
> Positioning by key.
> Index contains all needed columns. Base table will not be
> read.
> Keys are:
> pdr_portfolio_id ASC
> Using I/O Size 2 Kbytes for index leaf pages.
> With LRU Buffer Replacement Strategy for index leaf pages.
>
> FROM TABLE
> edw_val_f2_data
> f2
> Nested iteration.
> Index : x2_edw_val_f2_data
> Forward scan.
> Positioning by key.
> Index contains all needed columns. Base table will not be
> read.
> Keys are:
> sstm_val_id ASC
> Using I/O Size 2 Kbytes for index leaf pages.
> With LRU Buffer Replacement Strategy for index leaf pages.
>
> FROM TABLE
> #temp_portfolio
> p
> Nested iteration.
> Table Scan.
> Forward scan.
> Positioning at start of table.
> Using I/O Size 16 Kbytes for data pages.
> With LRU Buffer Replacement Strategy for data pages.
>
> FROM TABLE
> rpt_security
> Nested iteration.
> Index : x1_rpt_security
> Forward scan.
> Positioning at index start.
> Index contains all needed columns. Base table will not be
> read.
> Using I/O Size 16 Kbytes for index leaf pages.
> With LRU Buffer Replacement Strategy for index leaf pages.
> TO TABLE
> #temp_qty
> Using I/O Size 2 Kbytes for data pages.
>
> STEP
> 1
> The type of query is SELECT (into
> Worktable1).
> GROUP
> BY
> Evaluate Grouped COUNT
> AGGREGATE.
>
> FROM
> TABLE
>
> #temp_portfolio
>
> p
> Nested
> iteration.
> Table
> Scan.
> Forward
> scan.
> Positioning at start of
> table.
> Using I/O Size 16 Kbytes for data
> pages.
> With LRU Buffer Replacement Strategy for data
> pages.
>
> FROM
> TABLE
>
> rdm_portfolio_dim
>
> rpd
> Nested
> iteration.
> Index :
> edw_x3_portfolio_dim
> Forward
> scan.
> Positioning by
> key.
> Keys
> are:
> pdh_odw_acid
> ASC
> Using I/O Size 2 Kbytes for index leaf
> pages.
> With LRU Buffer Replacement Strategy for index leaf
> pages.
> Using I/O Size 2 Kbytes for data
> pages.
> With LRU Buffer Replacement Strategy for data
> pages.
>
> FROM
> TABLE
>
> rdm_valuation_fact
>
> rvf
> Nested
> iteration.
> Index :
> x2_valuation_fact
> Forward
> scan.
> Positioning by
> key.
> Keys
> are:
> vfr_portfolio_id
> ASC
> vfr_valuation_date
> ASC
> vfr_position_type
> ASC
> Using I/O Size 16 Kbytes for index leaf
> pages.
> With LRU Buffer Replacement Strategy for index leaf
> pages.
> Using I/O Size 16 Kbytes for data
> pages.
> With LRU Buffer Replacement Strategy for data
> pages.
>
> FROM
> TABLE
>
> rdm_security_dim
>
> rsd
> Nested
> iteration.
> Index :
> x1_security_dim
> Forward
> scan.
> Positioning by
> key.
> Keys
> are:
> sdr_security_id
> ASC
> Using I/O Size 2 Kbytes for index leaf
> pages.
> With LRU Buffer Replacement Strategy for index leaf
> pages.
> Using I/O Size 2 Kbytes for data
> pages.
> With LRU Buffer Replacement Strategy for data
> pages.
>
> FROM
> TABLE
>
> rpt_security
> Nested
> iteration.
> Index :
> x1_rpt_security
> Forward
> scan.
> Positioning by
> key.
> Keys
> are:
> SSTM_SCRTY_ID
> ASC
> Using I/O Size 2 Kbytes for index leaf
> pages.
> With LRU Buffer Replacement Strategy for index leaf
> pages.
> Using I/O Size 2 Kbytes for data
> pages.
> With LRU Buffer Replacement Strategy for data
> pages.
>
> FROM
> TABLE
>
> edw_val_f2_data
>
> f2
> Nested
> iteration.
> Index :
> x2_edw_val_f2_data
> Forward
> scan.
> Positioning by
> key.
> Index contains all needed columns. Base table will not be
> read.
> Keys
> are:
> sstm_val_id
> ASC
> Using I/O Size 2 Kbytes for index leaf
> pages.
> With LRU Buffer Replacement Strategy for index leaf
> pages.
> TO
> TABLE
>
> Worktable1.
>
> STEP
> 2
> The type of query is
> INSERT.
> The update mode is
> direct.
>
> FROM
> TABLE
>
> rdm_valuation_fact
>
> rvf
> Nested
> iteration.
> Table
> Scan.
> Forward
> scan.
> Positioning at start of
> table.
> Using I/O Size 16 Kbytes for data
> pages.
> With LRU Buffer Replacement Strategy for data
> pages.
>
> FROM
> TABLE
>
> rdm_security_dim
>
> rsd
> Nested
> iteration.
> Index :
> x1_security_dim
> Forward
> scan.
> Positioning by
> key.
> Index contains all needed columns. Base table will not be
> read.
> Keys
> are:
> sdr_security_id
> ASC
> Using I/O Size 2 Kbytes for index leaf
> pages.
> With LRU Buffer Replacement Strategy for index leaf
> pages.
>
> FROM
> TABLE
>
> rdm_portfolio_dim
>
> rpd
> Nested
> iteration.
> Index :
> x1_portfolio_dim
> Forward
> scan.
> Positioning by
> key.
> Index contains all needed columns. Base table will not be
> read.
> Keys
> are:
> pdr_portfolio_id
> ASC
> Using I/O Size 2 Kbytes for index leaf
> pages.
> With LRU Buffer Replacement Strategy for index leaf
> pages.
>
> FROM
> TABLE
>
> edw_val_f2_data
>
> f2
> Nested
> iteration.
> Index :
> x2_edw_val_f2_data
> Forward
> scan.
> Positioning by
> key.
> Index contains all needed columns. Base table will not be
> read.
> Keys
> are:
> sstm_val_id
> ASC
> Using I/O Size 2 Kbytes for index leaf
> pages.
> With LRU Buffer Replacement Strategy for index leaf
> pages.
>
> FROM
> TABLE
>
> #temp_portfolio
>
> p
> Nested
> iteration.
> Table
> Scan.
> Forward
> scan.
> Positioning at start of
> table.
> Using I/O Size 16 Kbytes for data
> pages.
> With LRU Buffer Replacement Strategy for data
> pages.
>
> FROM
> TABLE
>
> Worktable1.
> Nested
> iteration.
> Table
> Scan.
> Forward
> scan.
> Positioning at start of
> table.
> Using I/O Size 16 Kbytes for data
> pages.
> With MRU Buffer Replacement Strategy for data
> pages.
> TO
> TABLE
>
> #temp_qty
> Using I/O Size 2 Kbytes for data pages.
>
> Total estimated I/O cost for statement 8 (at line 57): 2147483647.