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.

Query performance on two servers

3 posts in General Discussion Last posting was on 2011-05-19 05:54:16.0Z
JA Posted on 2011-05-18 07:39:57.0Z
Sender: 726a.4dd35273.1804289383@sybase.com
From: JA
Newsgroups: sybase.public.ase.general
Subject: Query performance on two servers
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4dd377cd.76a1.1681692777@sybase.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="-=_forums-1-dub4dd377cd"
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 18 May 2011 00:39:57 -0700
X-Trace: forums-1-dub 1305704397 10.22.241.41 (18 May 2011 00:39:57 -0700)
X-Original-Trace: 18 May 2011 00:39:57 -0700, 10.22.241.41
Lines: 863
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30183
Article PK: 72360

Hi Guys,

I have a sql in stored procedure that causing performance
chaos at client site but works better in our environment on
the same copy of database. It takes 6 mins in our
environment and 35 mins in client’s environment. There are
issues with query that use 3 temp tables without any index
in nested queries to check NOT Exists. After adding index it
is performing very well in our environment (2 mins), haven't
heard from client yet.
My only concern is why there is a huge difference in query
plan and of both environment while stats were updated.
Though I understand there is always some minor difference.
I have attached both plan, if you go to the bottom of both
plans and see stats, scan count in client's plan for 5027
records is 1055421. I am wondering this might be a
configuration issue but our DBA says he can't find anything.

Thanks

INSERT INTO #my_result_3
SELECT macc.member_account_id,
port.portfolio_id,
port.name,
IsNull(maccin.investment_percent,0.0) default_percent,
IsNull(maccin.investment_percent,0.0) allocated_percent,
maccin.sequence_number,
0.0 amount,
'N' status,
macc.product_type_id,
0 member_account_transaction_id,
'Y' protect,
convert(decimal(18,6),0.00) units,
convert(decimal(18,6), 0.0) base_unit_price,
convert(decimal(18,6), 0.0) effective_unit_price,
'' pricing_status,
convert(decimal(18,6), 0.0) entry_fee_percent,
convert(decimal(18,6), 0.0) adviser_fee_percent,
convert(decimal(18,6), 0.0) trail_rate,
convert(decimal(18,6), 0.0) trail_rebate_rate,
convert(decimal(18,6), 0.0) fundmgr_fee_percent,
convert(decimal(18,6),0.00) std_entry_fee,
0.00 entry_fee_amount,
convert(decimal(18,6),0.00) std_trail_perc,
convert(decimal(18,6),0.00) std_trail_rebate_perc,
0 disb_rule_id,
0.00 adviser_fee_amount,
0.00 fund_manager_amount,
0 rounding_ttype_id,
0.00 fid_amount,
0.00 deductable_amount,
0.00 ue_tax_amount,
0.00 contribution_tax_amount,
port.entry_fee_allowed_flag,
port.trail_fee_allowed_flag,
port.trail_rebate_allowed_flag,
port.liquid_flag portfolio_liquid_flag,
port.upfront_or_trail_only_flag,
port.upfront_or_trail_default,
port.parcelised_trail_flag,
'' diff_date_used,
convert(datetime, NULL) dt_as_at_date_price,
'' exceed_std_entry_fee_flag,
'Y' update_defaults_recalc_flag,
convert(decimal(18,6),0.00) fund_manager_fee_perc,
convert(decimal(18,6),0.00) fund_manager_fee_gst_perc,
0.00 fund_manager_gst_amount,
convert(decimal(18,6),0.00) dealer_fee_perc,
0.00 dealer_fee_amount,
convert(decimal(18,6),0.00) std_adviser_fee,
0 macc_upfront_ttype_id,
0 adviser_upfront_ttype_id,
0 dealer_upfront_ttype_id,
0 fund_mgr_upfront_ttype_id,
0 fund_mgr_gst_upfront_ttype_id,
'N' port_msg_if_no_latest_uprice,
'' message_flag,
'Y'memacc_inv_flag,
'N' amount_modified,
disb_liquid_flag = CASE WHEN port.liquid_flag = 'Y' and port.ok_for_disbursement_flag = 'Y'
THEN 'Y'
ELSE 'N'
END,
0.00 topup_amount,
0.00 gross_amount,
0.00 fee_amount,
0.00 tax_amount,
0.00 prev_entry_fee_amount,
0.00 prev_adviser_fee_amount,
0.00 prev_fund_manager_amount,
0.00 prev_ue_tax_amount,
0.00 prev_contribution_tax_amount,
0.00 prev_fund_manager_gst_amount,
0.00 prev_dealer_fee_amount,
upfront_disb_type = CASE WHEN EXISTS (SELECT 1
FROM #tmp_trigtyp_disb tf
WHERE tf.product_type_id = port.product_type_id) THEN
'T'
ELSE 'O'
END,
null,
null,
null,
null

FROM member_account_investment maccin,
portfolio port ,
correspondence corr,
member_account macc,
contribution_schedule cs,
contsched_member_account cma
WHERE corr.correspondence_id = @ai_corr_id
and cs.correspondence_id = corr.correspondence_id
and cma.contribution_schedule_id = cs.contribution_schedule_id
and macc.member_account_id = cma.member_account_id
and maccin.member_account_id = macc.member_account_id
and maccin.portfolio_id = port.portfolio_id
and port.status = 'A'

and NOT EXISTS (SELECT 1
FROM #my_result_1 r1
where r1.member_account_id = macc.member_account_id
and r1.portfolio_id = port.portfolio_id)
and NOT EXISTS (SELECT 1
FROM #my_result_2 r2
where r2.member_account_id = macc.member_account_id
and r2.portfolio_id = port.portfolio_id)
and NOT EXISTS (SELECT 1
FROM #my_result_3 r3
where r3.member_account_id = macc.member_account_id
and r3.portfolio_id = port.portfolio_id)
and NOT EXISTS (SELECT 1
FROM #tmp_disabled_portfolios dp
WHERE dp.portfolio_id = port.portfolio_id)

QUERY PLAN FOR STATEMENT 61 (at line 805).
Optimized at runtime using Deferred Compilation.


STEP 1
The type of query is INSERT.

27 operator(s) under root

|ROOT:EMIT Operator (VA = 27)

|
| |SEQUENCER Operator (VA = 26) has 2 children.
| |
| | |STORE Operator (VA = 2)
| | | Worktable1 created, in allpages locking mode, for REFORMATTING.
| | | Creating clustered index.
| | |
| | | |INSERT Operator (VA = 1)
| | | | The update mode is direct.
| | | |
| | | | |SCAN Operator (VA = 0)
| | | | | FROM TABLE
| | | | | portfolio
| | | | | port
| | | | | 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.
| | | |
| | | | TO TABLE
| | | | Worktable1.
| |
| | |INSERT Operator (VA = 25)
| | | The update mode is deferred.
| | |
| | | |SQFILTER Operator (VA = 24) has 2 children.
| | | |
| | | | |NESTED LOOP JOIN Operator (VA = 21) (Join Type: Inner Join)
| | | | |
| | | | | |SQFILTER Operator (VA = 19) has 4 children.
| | | | | |
| | | | | | |N-ARY NESTED LOOP JOIN Operator (VA = 12) has 3 children.
| | | | | | |
| | | | | | | |SQFILTER Operator (VA = 9) has 2 children.
| | | | | | | |
| | | | | | | | |N-ARY NESTED LOOP JOIN Operator (VA = 6) has 3 children.
| | | | | | | | |
| | | | | | | | | |SCAN Operator (VA = 3)
| | | | | | | | | | FROM TABLE
| | | | | | | | | | correspondence
| | | | | | | | | | corr
| | | | | | | | | | Index : pk_correspondence
| | | | | | | | | | Forward Scan.
| | | | | | | | | | Positioning by key.
| | | | | | | | | | Index contains all needed columns. Base table will not be read.
| | | | | | | | | | Keys are:
| | | | | | | | | | correspondence_id ASC
| | | | | | | | | | Using I/O Size 2 Kbytes for index leaf pages.
| | | | | | | | | | With LRU Buffer Replacement Strategy for index leaf pages.
| | | | | | | | |
| | | | | | | | | |SCAN Operator (VA = 4)
| | | | | | | | | | FROM TABLE
| | | | | | | | | | contribution_schedule
| | | | | | | | | | cs
| | | | | | | | | | Using Clustered Index.
| | | | | | | | | | Index : idx_contsched_correspondence
| | | | | | | | | | Forward Scan.
| | | | | | | | | | Positioning by key.
| | | | | | | | | | Keys are:
| | | | | | | | | | correspondence_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.
| | | | | | | | |
| | | | | | | | | |SCAN Operator (VA = 5)
| | | | | | | | | | FROM TABLE
| | | | | | | | | | Worktable1.
| | | | | | | | | | Using Clustered Index.
| | | | | | | | | | Forward Scan.
| | | | | | | | | | Positioning at start of table.
| | | | | | | | | | Using I/O Size 16 Kbytes for data pages.
| | | | | | | | | | With LRU Buffer Replacement Strategy for data pages.
| | | | | | | |
| | | | | | | | Run subquery 1 (at nesting level 1).
| | | | | | | |
| | | | | | | | QUERY PLAN FOR SUBQUERY 1 (at nesting level 1 and at line 111).
| | | | | | | |
| | | | | | | | Correlated Subquery.
| | | | | | | | Subquery under an EXISTS predicate.
| | | | | | | |
| | | | | | | | |SCALAR AGGREGATE Operator (VA = 8)
| | | | | | | | | Evaluate Ungrouped ANY AGGREGATE.
| | | | | | | | | Scanning only up to the first qualifying row.
| | | | | | | | |
| | | | | | | | | |SCAN Operator (VA = 7)
| | | | | | | | | | FROM TABLE
| | | | | | | | | | #tmp_disabled_portfolios
| | | | | | | | | | dp
| | | | | | | | | | 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.
| | | | | | | |
| | | | | | | | END OF QUERY PLAN FOR SUBQUERY 1.
| | | | | | |
| | | | | | | |SCAN Operator (VA = 10)
| | | | | | | | FROM TABLE
| | | | | | | | member_account_investment
| | | | | | | | maccin
| | | | | | | | Index : fk_maccin_portfolio
| | | | | | | | Forward Scan.
| | | | | | | | Positioning by key.
| | | | | | | | Keys are:
| | | | | | | | portfolio_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 16 Kbytes for data pages.
| | | | | | | | With LRU Buffer Replacement Strategy for data pages.
| | | | | | |
| | | | | | | |SCAN Operator (VA = 11)
| | | | | | | | FROM TABLE
| | | | | | | | contsched_member_account
| | | | | | | | cma
| | | | | | | | Index : idx_contschedmem_schedmem
| | | | | | | | Forward Scan.
| | | | | | | | Positioning by key.
| | | | | | | | Index contains all needed columns. Base table will not be read.
| | | | | | | | Keys are:
| | | | | | | | contribution_schedule_id ASC
| | | | | | | | member_account_id ASC
| | | | | | | | Using I/O Size 2 Kbytes for index leaf pages.
| | | | | | | | With LRU Buffer Replacement Strategy for index leaf pages.
| | | | | |
| | | | | | Run subquery 1 (at nesting level 1).
| | | | | |
| | | | | | QUERY PLAN FOR SUBQUERY 1 (at nesting level 1 and at line 99).
| | | | | |
| | | | | | Correlated Subquery.
| | | | | | Subquery under an EXISTS predicate.
| | | | | |
| | | | | | |SCALAR AGGREGATE Operator (VA = 14)
| | | | | | | Evaluate Ungrouped ANY AGGREGATE.
| | | | | | | Scanning only up to the first qualifying row.
| | | | | | |
| | | | | | | |SCAN Operator (VA = 13)
| | | | | | | | FROM TABLE
| | | | | | | | #my_result_1
| | | | | | | | r1
| | | | | | | | 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.
| | | | | |
| | | | | | END OF QUERY PLAN FOR SUBQUERY 1.
| | | | | |
| | | | | | Run subquery 2 (at nesting level 1).
| | | | | |
| | | | | | QUERY PLAN FOR SUBQUERY 2 (at nesting level 1 and at line 103).
| | | | | |
| | | | | | Correlated Subquery.
| | | | | | Subquery under an EXISTS predicate.
| | | | | |
| | | | | | |SCALAR AGGREGATE Operator (VA = 16)
| | | | | | | Evaluate Ungrouped ANY AGGREGATE.
| | | | | | | Scanning only up to the first qualifying row.
| | | | | | |
| | | | | | | |SCAN Operator (VA = 15)
| | | | | | | | FROM TABLE
| | | | | | | | #my_result_2
| | | | | | | | r2
| | | | | | | | 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.
| | | | | |
| | | | | | END OF QUERY PLAN FOR SUBQUERY 2.
| | | | | |
| | | | | | Run subquery 3 (at nesting level 1).
| | | | | |
| | | | | | QUERY PLAN FOR SUBQUERY 3 (at nesting level 1 and at line 107).
| | | | | |
| | | | | | Correlated Subquery.
| | | | | | Subquery under an EXISTS predicate.
| | | | | |
| | | | | | |SCALAR AGGREGATE Operator (VA = 18)
| | | | | | | Evaluate Ungrouped ANY AGGREGATE.
| | | | | | | Scanning only up to the first qualifying row.
| | | | | | |
| | | | | | | |SCAN Operator (VA = 17)
| | | | | | | | FROM TABLE
| | | | | | | | #my_result_3
| | | | | | | | r3
| | | | | | | | 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.
| | | | | |
| | | | | | END OF QUERY PLAN FOR SUBQUERY 3.
| | | | |
| | | | | |SCAN Operator (VA = 20)
| | | | | | FROM TABLE
| | | | | | member_account
| | | | | | macc
| | | | | | Using Clustered Index.
| | | | | | Index : pk_member_account
| | | | | | Forward Scan.
| | | | | | Positioning by key.
| | | | | | Keys are:
| | | | | | member_account_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.
| | | |
| | | | Run subquery 1 (at nesting level 1).
| | | |
| | | | QUERY PLAN FOR SUBQUERY 1 (at nesting level 1 and at line 75).
| | | |
| | | | Correlated Subquery.
| | | | Subquery under an EXISTS predicate.
| | | |
| | | | |SCALAR AGGREGATE Operator (VA = 23)
| | | | | Evaluate Ungrouped ANY AGGREGATE.
| | | | | Scanning only up to the first qualifying row.
| | | | |
| | | | | |SCAN Operator (VA = 22)
| | | | | | FROM TABLE
| | | | | | #tmp_trigtyp_disb
| | | | | | tf
| | | | | | 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.
| | | |
| | | | END OF QUERY PLAN FOR SUBQUERY 1.
| | |
| | | TO TABLE
| | | #my_result_3
| | | Using I/O Size 2 Kbytes for data pages.


Table: Worktable1 scan count 1, logical reads: (regular=266 apf=0 total=266), physical reads: (regular=174 apf=0 total=174), apf IOs used=0
Table: Worktable1 scan count 0, logical reads: (regular=2294 apf=0 total=2294), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: portfolio (port) scan count 1, logical reads: (regular=787 apf=0 total=787), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: #my_result_3 scan count 0, logical reads: (regular=0 apf=0 total=0), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: correspondence (corr) scan count 1, logical reads: (regular=3 apf=0 total=3), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: contribution_schedule (cs) scan count 1, logical reads: (regular=3 apf=0 total=3), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Worktable1 scan count 1, logical reads: (regular=2625 apf=0 total=2625), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: #tmp_disabled_portfolios (dp) scan count 2161, logical reads: (regular=2161 apf=0 total=2161), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: member_account_investment (maccin) scan count 2087, logical reads: (regular=643144 apf=0 total=643144), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: contsched_member_account (cma) scan count 1033941, logical reads: (regular=304135 apf=0 total=304135), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: #my_result_1 (r1) scan count 5027, logical reads: (regular=5027 apf=0 total=5027), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: #my_result_2 (r2) scan count 5027, logical reads: (regular=4262302 apf=0 total=4262302), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: #my_result_3 (r3) scan count 0, logical reads: (regular=0 apf=0 total=0), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: member_account (macc) scan count 0, logical reads: (regular=0 apf=0 total=0), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: #tmp_trigtyp_disb (tf) scan count 0, logical reads: (regular=0 apf=0 total=0), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Total writes for this command: 0

Execution Time 116.
Adaptive Server cpu time: 11600 ms. Adaptive Server elapsed time: 11613 ms.
Total writes for this command: 0

QUERY PLAN FOR STATEMENT 61 (at line 805).
Optimized at runtime using Deferred Compilation.


STEP 1
The type of query is INSERT.

26 operator(s) under root

|ROOT:EMIT Operator (VA = 26)

|
| |INSERT Operator (VA = 25)
| | The update mode is deferred.
| |
| | |SQFILTER Operator (VA = 24) has 2 children.
| | |
| | | |MERGE JOIN Operator (Join Type: Inner Join) (VA = 21)
| | | | Using Worktable4 for internal storage.
| | | | Key Count: 2
| | | | Key Ordering: ASC ASC
| | | |
| | | | |SORT Operator (VA = 19)
| | | | | Average Row width is 71.250473
| | | | | Using Worktable3 for internal storage.
| | | | |
| | | | | |SQFILTER Operator (VA = 18) has 4 children.
| | | | | |
| | | | | | |MERGE JOIN Operator (Join Type: Inner Join) (VA = 11)
| | | | | | | Using Worktable2 for internal storage.
| | | | | | | Key Count: 1
| | | | | | | Key Ordering: ASC
| | | | | | |
| | | | | | | |SORT Operator (VA = 9)
| | | | | | | | Average Row width is 67.250473
| | | | | | | | Using Worktable1 for internal storage.
| | | | | | | |
| | | | | | | | |NESTED LOOP JOIN Operator (VA = 8) (Join Type: Inner Join)
| | | | | | | | |
| | | | | | | | | |SQFILTER Operator (VA = 6) has 2 children.
| | | | | | | | | |
| | | | | | | | | | |N-ARY NESTED LOOP JOIN Operator (VA = 3) has 3 children.
| | | | | | | | | | |
| | | | | | | | | | | |SCAN Operator (VA = 0)
| | | | | | | | | | | | FROM TABLE
| | | | | | | | | | | | correspondence
| | | | | | | | | | | | corr
| | | | | | | | | | | | Index : pk_correspondence
| | | | | | | | | | | | Forward Scan.
| | | | | | | | | | | | Positioning by key.
| | | | | | | | | | | | Index contains all needed columns. Base table will not be read.
| | | | | | | | | | | | Keys are:
| | | | | | | | | | | | correspondence_id ASC
| | | | | | | | | | | | Using I/O Size 2 Kbytes for index leaf pages.
| | | | | | | | | | | | With LRU Buffer Replacement Strategy for index leaf pages.
| | | | | | | | | | |
| | | | | | | | | | | |SCAN Operator (VA = 1)
| | | | | | | | | | | | FROM TABLE
| | | | | | | | | | | | contribution_schedule
| | | | | | | | | | | | cs
| | | | | | | | | | | | Using Clustered Index.
| | | | | | | | | | | | Index : idx_contsched_correspondence
| | | | | | | | | | | | Forward Scan.
| | | | | | | | | | | | Positioning by key.
| | | | | | | | | | | | Keys are:
| | | | | | | | | | | | correspondence_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.
| | | | | | | | | | |
| | | | | | | | | | | |SCAN Operator (VA = 2)
| | | | | | | | | | | | FROM TABLE
| | | | | | | | | | | | portfolio
| | | | | | | | | | | | port
| | | | | | | | | | | | 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.
| | | | | | | | | |
| | | | | | | | | | Run subquery 1 (at nesting level 1).
| | | | | | | | | |
| | | | | | | | | | QUERY PLAN FOR SUBQUERY 1 (at nesting level 1 and at line 111).
| | | | | | | | | |
| | | | | | | | | | Correlated Subquery.
| | | | | | | | | | Subquery under an EXISTS predicate.
| | | | | | | | | |
| | | | | | | | | | |SCALAR AGGREGATE Operator (VA = 5)
| | | | | | | | | | | Evaluate Ungrouped ANY AGGREGATE.
| | | | | | | | | | | Scanning only up to the first qualifying row.
| | | | | | | | | | |
| | | | | | | | | | | |SCAN Operator (VA = 4)
| | | | | | | | | | | | FROM TABLE
| | | | | | | | | | | | #tmp_disabled_portfolios
| | | | | | | | | | | | dp
| | | | | | | | | | | | 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.
| | | | | | | | | |
| | | | | | | | | | END OF QUERY PLAN FOR SUBQUERY 1.
| | | | | | | | |
| | | | | | | | | |SCAN Operator (VA = 7)
| | | | | | | | | | FROM TABLE
| | | | | | | | | | member_account_investment
| | | | | | | | | | maccin
| | | | | | | | | | Index : fk_maccin_portfolio
| | | | | | | | | | Forward Scan.
| | | | | | | | | | Positioning by key.
| | | | | | | | | | Keys are:
| | | | | | | | | | portfolio_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 16 Kbytes for data pages.
| | | | | | | | | | With LRU Buffer Replacement Strategy for data pages.
| | | | | | |
| | | | | | | |SCAN Operator (VA = 10)
| | | | | | | | FROM TABLE
| | | | | | | | member_account
| | | | | | | | macc
| | | | | | | | Using Clustered Index.
| | | | | | | | Index : pk_member_account
| | | | | | | | Forward Scan.
| | | | | | | | Positioning at index start.
| | | | | | | | 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 MRU Buffer Replacement Strategy for data pages.
| | | | | |
| | | | | | Run subquery 1 (at nesting level 1).
| | | | | |
| | | | | | QUERY PLAN FOR SUBQUERY 1 (at nesting level 1 and at line 99).
| | | | | |
| | | | | | Correlated Subquery.
| | | | | | Subquery under an EXISTS predicate.
| | | | | |
| | | | | | |SCALAR AGGREGATE Operator (VA = 13)
| | | | | | | Evaluate Ungrouped ANY AGGREGATE.
| | | | | | | Scanning only up to the first qualifying row.
| | | | | | |
| | | | | | | |SCAN Operator (VA = 12)
| | | | | | | | FROM TABLE
| | | | | | | | #my_result_1
| | | | | | | | r1
| | | | | | | | 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.
| | | | | |
| | | | | | END OF QUERY PLAN FOR SUBQUERY 1.
| | | | | |
| | | | | | Run subquery 2 (at nesting level 1).
| | | | | |
| | | | | | QUERY PLAN FOR SUBQUERY 2 (at nesting level 1 and at line 103).
| | | | | |
| | | | | | Correlated Subquery.
| | | | | | Subquery under an EXISTS predicate.
| | | | | |
| | | | | | |SCALAR AGGREGATE Operator (VA = 15)
| | | | | | | Evaluate Ungrouped ANY AGGREGATE.
| | | | | | | Scanning only up to the first qualifying row.
| | | | | | |
| | | | | | | |SCAN Operator (VA = 14)
| | | | | | | | FROM TABLE
| | | | | | | | #my_result_2
| | | | | | | | r2
| | | | | | | | 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.
| | | | | |
| | | | | | END OF QUERY PLAN FOR SUBQUERY 2.
| | | | | |
| | | | | | Run subquery 3 (at nesting level 1).
| | | | | |
| | | | | | QUERY PLAN FOR SUBQUERY 3 (at nesting level 1 and at line 107).
| | | | | |
| | | | | | Correlated Subquery.
| | | | | | Subquery under an EXISTS predicate.
| | | | | |
| | | | | | |SCALAR AGGREGATE Operator (VA = 17)
| | | | | | | Evaluate Ungrouped ANY AGGREGATE.
| | | | | | | Scanning only up to the first qualifying row.
| | | | | | |
| | | | | | | |SCAN Operator (VA = 16)
| | | | | | | | FROM TABLE
| | | | | | | | #my_result_3
| | | | | | | | r3
| | | | | | | | 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.
| | | | | |
| | | | | | END OF QUERY PLAN FOR SUBQUERY 3.
| | | |
| | | | |SCAN Operator (VA = 20)
| | | | | FROM TABLE
| | | | | contsched_member_account
| | | | | cma
| | | | | Index : idx_contschedmem_schedmem
| | | | | 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.
| | |
| | | Run subquery 1 (at nesting level 1).
| | |
| | | QUERY PLAN FOR SUBQUERY 1 (at nesting level 1 and at line 75).
| | |
| | | Correlated Subquery.
| | | Subquery under an EXISTS predicate.
| | |
| | | |SCALAR AGGREGATE Operator (VA = 23)
| | | | Evaluate Ungrouped ANY AGGREGATE.
| | | | Scanning only up to the first qualifying row.
| | | |
| | | | |SCAN Operator (VA = 22)
| | | | | FROM TABLE
| | | | | #tmp_trigtyp_disb
| | | | | tf
| | | | | 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.
| | |
| | | END OF QUERY PLAN FOR SUBQUERY 1.
| |
| | TO TABLE
| | #my_result_3
| | Using I/O Size 2 Kbytes for data pages.


Table: #my_result_3 scan count 0, logical reads: (regular=0 apf=0 total=0), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Worktable3 scan count 1, logical reads: (regular=90570 apf=0 total=90570), physical reads: (regular=87273 apf=0 total=87273), apf IOs used=0
Table: Worktable1 scan count 1, logical reads: (regular=85132 apf=0 total=85132), physical reads: (regular=81936 apf=0 total=81936), apf IOs used=0
Table: correspondence (corr) scan count 1, logical reads: (regular=3 apf=0 total=3), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: contribution_schedule (cs) scan count 1, logical reads: (regular=3 apf=0 total=3), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: portfolio (port) scan count 1, logical reads: (regular=901 apf=0 total=901), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: #tmp_disabled_portfolios (dp) scan count 2161, logical reads: (regular=2161 apf=0 total=2161), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: member_account_investment (maccin) scan count 2087, logical reads: (regular=660346 apf=0 total=660346), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: member_account (macc) scan count 1, logical reads: (regular=138406 apf=1 total=138407), physical reads: (regular=13 apf=10746 total=10759), apf IOs used=10749
Table: #my_result_1 (r1) scan count 1055421, logical reads: (regular=1055421 apf=0 total=1055421), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: #my_result_2 (r2) scan count 1055421, logical reads: (regular=1162027985 apf=0 total=1162027985), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: #my_result_3 (r3) scan count 1039796, logical reads: (regular=1039796 apf=0 total=1039796), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: contsched_member_account (cma) scan count 1, logical reads: (regular=3455 apf=0 total=3455), physical reads: (regular=8 apf=414 total=422), apf IOs used=413
Table: #tmp_trigtyp_disb (tf) scan count 0, logical reads: (regular=0 apf=0 total=0), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Total writes for this command: 0

Execution Time 57674.
Adaptive Server cpu time: 5767400 ms. Adaptive Server elapsed time: 5625450 ms.
Total writes for this command: 0


"Mark A. Parsons" <iron_horse Posted on 2011-05-18 18:11:54.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Query performance on two servers
References: <4dd377cd.76a1.1681692777@sybase.com>
In-Reply-To: <4dd377cd.76a1.1681692777@sybase.com>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4dd40bea$1@forums-1-dub>
Date: 18 May 2011 11:11:54 -0700
X-Trace: forums-1-dub 1305742314 10.22.241.152 (18 May 2011 11:11:54 -0700)
X-Original-Trace: 18 May 2011 11:11:54 -0700, vip152.sybase.com
Lines: 32
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30184
Article PK: 72362

Your query plan shows all NESTED LOOP joins, while the client's query plan shows some MERGE joins. I'd want to verify
both dataservers are configured with the same optimization goal. I'd also look for login triggers or differences in
stored proc definitions that could lead to different optimization goals/settings being set.

You've obviously got different join orders in the 2 query plans, with the client's query plan leading to a much higher
volume of hits/scans against the #my_result% tables. While indexes on the #my_result% tables should help the client's
query to run more quickly, I'd want to look at how to reduce the volume of hits/scans in the client's query (ie, get the
client's query to run with a query plan like you've got for your version of the query) - even with indexes in place, 5K
scans is going to run quicker than 1M scans, eh.

On 05/18/2011 03:39, JA wrote:
> Hi Guys,
>
> I have a sql in stored procedure that causing performance
> chaos at client site but works better in our environment on
> the same copy of database. It takes 6 mins in our
> environment and 35 mins in clientÂ’s environment. There are
> issues with query that use 3 temp tables without any index
> in nested queries to check NOT Exists. After adding index it
> is performing very well in our environment (2 mins), haven't
> heard from client yet.
> My only concern is why there is a huge difference in query
> plan and of both environment while stats were updated.
> Though I understand there is always some minor difference.
> I have attached both plan, if you go to the bottom of both
> plans and see stats, scan count in client's plan for 5027
> records is 1055421. I am wondering this might be a
> configuration issue but our DBA says he can't find anything.
>
> Thanks


JA Posted on 2011-05-19 05:54:16.0Z
Sender: 726a.4dd35273.1804289383@sybase.com
From: JA
Newsgroups: sybase.public.ase.general
Subject: Re: Query performance on two servers
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4dd4b088.20be.1681692777@sybase.com>
References: <4dd40bea$1@forums-1-dub>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="-=_forums-1-dub4dd4b088"
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 18 May 2011 22:54:16 -0700
X-Trace: forums-1-dub 1305784456 10.22.241.41 (18 May 2011 22:54:16 -0700)
X-Original-Trace: 18 May 2011 22:54:16 -0700, 10.22.241.41
Lines: 950
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30186
Article PK: 72365

Client reported today that stored procedure is working great
after indexes. I couldn't get query plan of their
environment but manage to get "Query Tuning" configurations.
Everything is same except "number of worker processes".
Query tuning Configurations of both servers are attched with
latest query plan of our environment.

Problem has been resolved just want to find out reason for
my learning.

Thanks

> Your query plan shows all NESTED LOOP joins, while the
> client's query plan shows some MERGE joins. I'd want to
> verify both dataservers are configured with the same
> optimization goal. I'd also look for login triggers or
> differences in stored proc definitions that could lead to
> different optimization goals/settings being set.
>
> You've obviously got different join orders in the 2 query
> plans, with the client's query plan leading to a much
> higher volume of hits/scans against the #my_result%
> tables. While indexes on the #my_result% tables should
> help the client's query to run more quickly, I'd want to
> look at how to reduce the volume of hits/scans in the
> client's query (ie, get the client's query to run with a
> query plan like you've got for your version of the query)
> - even with indexes in place, 5K scans is going to run
> quicker than 1M scans, eh.
>
>
>
> On 05/18/2011 03:39, JA wrote:
> > Hi Guys,
> >
> > I have a sql in stored procedure that causing
> > performance chaos at client site but works better in our
> > environment on the same copy of database. It takes 6
> > mins in our environment and 35 mins in client’s
> > environment. There are issues with query that use 3 temp
> > tables without any index in nested queries to check NOT
> > Exists. After adding index it is performing very well in
> > our environment (2 mins), haven't heard from client yet.
> > My only concern is why there is a huge difference in
> > query plan and of both environment while stats were
> > updated. Though I understand there is always some minor
> > difference. I have attached both plan, if you go to the
> > bottom of both plans and see stats, scan count in
> > client's plan for 5027 records is 1055421. I am
> > wondering this might be a configuration issue but our
> DBA says he can't find anything. >
> > Thanks