|
Home > Archive > SQL Anywhere database > August 2005 > Performance of SQL varies by factor of 20-30
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Performance of SQL varies by factor of 20-30
|
|
| Peter Simonsen 2005-08-23, 7:24 am |
| ASA 9.0.2.3044
Win2k
Every night a batch job is performed with many sqls
on our server.
Before the batch job, we perform create statistics
on every table.
The SQL in question returns between 0 and 5 rows,
but there is no relation between number of returned
rows and performance. Usually, the SQL takes 8-12
minuttes, occationally (increasingly lately) it takes
3-4 hours!
Between create statistics and the SQL, a number of
other SQLs are executed, but none of them does insert,
update or deletes data on a large scale.
How can I get information on what is killing the
performance?
BTW indexes and tables are reorganized regularly.
TIA
regards/Peter Simonsen
-----------------------------------------------------
SQL:
select distinct
query_code_id = 'S0113',
bundle_id = b.bundle_id,
actual_indu_date = b.actual_indu_date,
collection_date = b.collection_date,
indu_time = b.indu_time,
last_pickup_time = b.last_pickup_time,
reported_indu_date = b.reported_indu_date,
actual_indu_method_k
ey = b. actual_indu_method_k
ey,
actual_zipcode = b.actual_zipcode
from mail m join bundle b on m.bundle_id = b.bundle_id,
mail m join zip_range_ext zre on m.drop_zip_range = zre.zip_range_key,
bundle b join calendar cal on b.collection_date = cal.cal_date,
zip_range_ext zre join combination com on zre.ctryo_key = com.area_key
where b.cancel_code_id = 0
and b. actual_indu_method_k
ey= com.part_1_key
and cal.week_day_key = com.part_2_key
and com. combination_type_key
= 175
and not exists ( select 1
from tbl_bundle_history tbh
where tbh.bundle_id = b.bundle_id
and ( ( tbh.query_code_id = 'S0113' and tbh.collection_date = b.collection_date )
or tbh.query_status = 'D' ) )
and not exists ( select 1
from app_set a
where a.set_id = m.production_set_id
and a.set_type = 'Pro'
and a.set_id like '%T%' )
and ( select ','+parm+','
from setup_parm
where system_module = 'VAL'
and setting_name = 'central_validation'
and setting_subpart = 'exclude_receiver_id
s' ) not like '%,'+string(m.receiver_id)+',%'
and ( select ','+parm+','
from setup_parm
where system_module = 'VAL'
and setting_name = 'central_validation'
and setting_subpart = 'exclude_dropper_ids
' ) not like '%,'+string(m.dropper_id)+',%'
and ( com.combination_allowed = 'N' or
exists ( select 1
from holiday h join zip_range_ext zr on zr.holi_key = h.area_key
where h.collection = 'N'
and h.holiday_date = b.collection_date
and zr.zip_range_key = m.drop_zip_range ) )
Long plan:
( Plan [ Total Cost Estimate: .6953957703 ]
( WorkTable
( HashDistinct
( Filter [ NOT EXISTS( SubQ 8 ) ]
( NestedLoopsJoin[ TRUE ]
( Filter [ NOT EXISTS( SubQ 7 ) ]
( NestedLoopsJoin[ TRUE ]
( NestedLoopsJoin[ TRUE ]
( *HashJoin
( HashFilter
( IndexScan ( combination_all c ) fk_combinationall_co
mbinationtype[ ( NOT EXISTS( SubQ 3 ) ) OR ( ( EXISTS( SubQ 2 ) ) AND ( c.valid_to >= SubQ 1 : 50% Guess ) AND ( c.valid_from <= SubQ 1 : 50% Guess ) ) : 62.5% Guess ] )
)
( IndexScan ( bundle b ) fk_bundle_cancelcode
[ hash( b. actual_indu_method_k
ey ) in hashmap( c.part_1_key ) : 100% Guess ] )
)
( IndexScan ( calendar cal ) calendar[ cal.week_day_key = c.part_2_key : .1753278504% Column-Column ] )
)
( IndexScan ( mail m ) fk_mail_bundle[ ( SubQ 6 NOT LIKE expr( '%,', m.receiver_id, ',%' ) : 50% Guess ) AND ( SubQ 5 NOT LIKE expr( '%,', m.dropper_id, ',%' ) : 50% Guess ) AND ( ( c.combination_allowed = 'N' : 2.277325839% Statistics
) OR ( EXISTS( SubQ 4 ) ) : 100% Guess ) ] )
)
)
( IndexScan ( zip_range_ext zre ) zip_range_ext_index[ zre.ctryo_key = c.area_key : .0639142368% Column-Column ] )
)
)
)
)
)
( SubQ 1 [ Total Cost Estimate: .001595 ]
( SingleRowGroupBy
( TableScan prod_week[ prod_week.prod_status_id < 5000 : 43.94904524% Statistics ] )
)
)
( SubQ 2 [ Total Cost Estimate: 1.665854E-5 ]
( IndexScan ( combination_type ct ) combination_type[ ct.combination_class = 'C_G' : 53.65853906% Statistics ] )
)
( SubQ 3 [ Total Cost Estimate: 1.665854E-5 ]
( IndexScan ( combination_type ct ) combination_type[ ct.combination_class = 'C_G' : 53.65853906% Statistics ] )
)
( SubQ 4 [ Total Cost Estimate: .0032931208 ]
( HashJoin*
( IndexScan ( holiday h ) holiday[ ( h.collection = 'N' : 99.21550751% Statistics ) AND ( hash( h.area_key ) in hashmap( zr.holi_key ) : 100% Guess ) ] )
( HashFilter
( IndexScan ( zip_range_ext zr ) zip_range_ext_index )
)
)
)
( SubQ 5 [ Total Cost Estimate: 3.783111E-5 ]
( IndexScan setup_parm setup_parm )
)
( SubQ 6 [ Total Cost Estimate: 3.783111E-5 ]
( IndexScan setup_parm setup_parm )
)
( SubQ 7 [ Total Cost Estimate: .0000075 ]
( IndexScan ( app_set a ) app_set[ ( a.set_type = 'Pro' : 59.375% Statistics ) AND ( a.set_id LIKE '%T%' : 1% Guess ) ] )
)
( SubQ 8 [ Total Cost Estimate: .0181773906 ]
( IndexScan ( tbl_bundle_history tbh ) fk_tblbundlehistory_
bundle[ ( tbh.query_status = 'D' : 12.82337904% Statistics ) OR ( ( tbh.collection_date = expr( b.collection_date ) : .5131524261% Column-Column ) AND ( tbh.query_code_id = 'S0113' : 1.5660874
55% Statistics ) ) : 12.83141546% Combined ] )
)
| |
| Donna Osburn 2005-08-23, 11:24 am |
| What is killing you is all the "NOT's" in your query. They
kill performance. This goes back to optimum database
design. You need to figure a way to rewrite the query by
removing the NOT Exists and NOT IN's.
We had the same issues. What we did was create a temporary
table of all the NOT exists select statement - which was
relatively small compared to the entire table, then did our
NOT Exist against that much smaller table. It sped it up
tremendously. But the best thing is the eliminate the NOT
Exist and swap our for an EXISTS if at all possible.
Donna
> ASA 9.0.2.3044
> Win2k
>
> Every night a batch job is performed with many sqls
> on our server.
> Before the batch job, we perform create statistics
> on every table.
>
> The SQL in question returns between 0 and 5 rows,
> but there is no relation between number of returned
> rows and performance. Usually, the SQL takes 8-12
> minuttes, occationally (increasingly lately) it takes
> 3-4 hours!
>
> Between create statistics and the SQL, a number of
> other SQLs are executed, but none of them does insert,
> update or deletes data on a large scale.
>
> How can I get information on what is killing the
> performance?
>
> BTW indexes and tables are reorganized regularly.
>
> TIA
> regards/Peter Simonsen
>
> -----------------------------------------------------
> SQL:
> select distinct
> query_code_id = 'S0113',
> bundle_id = b.bundle_id,
> actual_indu_date = b.actual_indu_date,
> collection_date = b.collection_date,
> indu_time = b.indu_time,
> last_pickup_time = b.last_pickup_time,
> reported_indu_date = b.reported_indu_date,
> actual_indu_method_k
ey =
> b. actual_indu_method_k
ey,
> actual_zipcode = b.actual_zipcode
> from mail m join bundle b
> on m.bundle_id = b.bundle_id,
> mail m join zip_range_ext zre
> on m.drop_zip_range = zre.zip_range_key,
> bundle b join calendar cal
> on b.collection_date = cal.cal_date,
> zip_range_ext zre join combination com
> on zre.ctryo_key = com.area_key
> where b.cancel_code_id = 0
> and b. actual_indu_method_k
ey= com.part_1_key
> and cal.week_day_key = com.part_2_key
> and com. combination_type_key
= 175
> and not exists ( select 1
> from tbl_bundle_history tbh
> where tbh.bundle_id = b.bundle_id
> and ( ( tbh.query_code_id =
> 'S0113' and tbh.collection_date = b.collection_date )
> or tbh.query_status = 'D' )
> )
> and not exists ( select 1
> from app_set a
> where a.set_id =
> m.production_set_id
> and a.set_type = 'Pro'
> and a.set_id like '%T%' )
> and ( select ','+parm+','
> from setup_parm
> where system_module = 'VAL'
> and setting_name = 'central_validation'
> and setting_subpart = 'exclude_receiver_id
s'
> ) not like '%,'+string(m.receiver_id)+',%'
> and ( select ','+parm+','
> from setup_parm
> where system_module = 'VAL'
> and setting_name = 'central_validation'
> and setting_subpart = 'exclude_dropper_ids
'
> ) not like '%,'+string(m.dropper_id)+',%'
> and ( com.combination_allowed = 'N' or
> exists ( select 1
> from holiday h join zip_range_ext zr
> on zr.holi_key = h.area_key
> where h.collection = 'N'
> and h.holiday_date =
> b.collection_date
> and zr.zip_range_key =
> m.drop_zip_range ) )
>
>
> Long plan:
> ( Plan [ Total Cost Estimate: .6953957703 ]
> ( WorkTable
> ( HashDistinct
> ( Filter [ NOT EXISTS( SubQ 8 ) ]
> ( NestedLoopsJoin[ TRUE ]
> ( Filter [ NOT EXISTS( SubQ 7 ) ]
> ( NestedLoopsJoin[ TRUE ]
> ( NestedLoopsJoin[ TRUE ]
> ( *HashJoin
> ( HashFilter
> ( IndexScan ( combination_all c )
> fk_combinationall_co
mbinationtype[ ( NOT EXISTS( SubQ 3 )
> ) OR ( ( EXISTS( SubQ 2 ) ) AND ( c.valid_to >= SubQ 1 :
> 50% Guess ) AND ( c.valid_from <= SubQ 1 : 50% Guess ) ) :
> 62.5% Guess ] )
> )
> ( IndexScan ( bundle b )
> fk_bundle_cancelcode
[ hash( b. actual_indu_method_k
ey ) in
> hashmap( c.part_1_key ) : 100% Guess ] )
> )
> ( IndexScan ( calendar cal ) calendar[
> cal.week_day_key = c.part_2_key : .1753278504%
> Column-Column ] )
> )
> ( IndexScan ( mail m ) fk_mail_bundle[ (
> SubQ 6 NOT LIKE expr( '%,', m.receiver_id, ',%' ) : 50%
> Guess ) AND ( SubQ 5 NOT LIKE expr( '%,', m.dropper_id,
> ',%' ) : 50% Guess ) AND ( ( c.combination_allowed = 'N'
> : 2.277325839% Statistics ) OR ( EXISTS( SubQ 4 ) ) : 100%
> Guess ) ] )
> )
> )
> ( IndexScan ( zip_range_ext zre )
> zip_range_ext_index[ zre.ctryo_key = c.area_key :
> .0639142368% Column-Column ] )
> )
> )
> )
> )
> )
> ( SubQ 1 [ Total Cost Estimate: .001595 ]
> ( SingleRowGroupBy
> ( TableScan prod_week[ prod_week.prod_status_id <
> 5000 : 43.94904524% Statistics ] )
> )
> )
> ( SubQ 2 [ Total Cost Estimate: 1.665854E-5 ]
> ( IndexScan ( combination_type ct ) combination_type[
> ct.combination_class = 'C_G' : 53.65853906% Statistics ]
> ) )
> ( SubQ 3 [ Total Cost Estimate: 1.665854E-5 ]
> ( IndexScan ( combination_type ct ) combination_type[
> ct.combination_class = 'C_G' : 53.65853906% Statistics ]
> ) )
> ( SubQ 4 [ Total Cost Estimate: .0032931208 ]
> ( HashJoin*
> ( IndexScan ( holiday h ) holiday[ ( h.collection =
> 'N' : 99.21550751% Statistics ) AND ( hash( h.area_key )
> in hashmap( zr.holi_key ) : 100% Guess ) ] )
> ( HashFilter
> ( IndexScan ( zip_range_ext zr ) zip_range_ext_index
> )
> )
> )
> )
> ( SubQ 5 [ Total Cost Estimate: 3.783111E-5 ]
> ( IndexScan setup_parm setup_parm )
> )
> ( SubQ 6 [ Total Cost Estimate: 3.783111E-5 ]
> ( IndexScan setup_parm setup_parm )
> )
> ( SubQ 7 [ Total Cost Estimate: .0000075 ]
> ( IndexScan ( app_set a ) app_set[ ( a.set_type = 'Pro'
> : 59.375% Statistics ) AND ( a.set_id LIKE '%T%' : 1%
> Guess ) ] ) )
> ( SubQ 8 [ Total Cost Estimate: .0181773906 ]
> ( IndexScan ( tbl_bundle_history tbh )
> fk_tblbundlehistory_
bundle[ ( tbh.query_status = 'D' :
> 12.82337904% Statistics ) OR ( ( tbh.collection_date =
> expr( b.collection_date ) : .5131524261% Column-Column )
> AND ( tbh.query_code_id = 'S0113' : 1.566087455%
> Statistics ) ) : 12.83141546% Combined ] ) )
| |
| Rob Waywell 2005-08-23, 1:23 pm |
| Can you provide a graphical plan with statistics that shows a fast execution
of the query and a slow execution of the query?
Have you tried not recreating the statistics every time you run the batch
job? While there are specific instances where recreating the statistics may
help, you do risk losing information/granularity each time you recreate the
statistics.
You have only shown a single SQL statement, but you mention that there are
many SQL statements executed in this batch process. Is the entire slowdown
accounted for by the single statement that you have shown? If not, how much
of the variation in execution time appears to be related to this single
query?
Have you run the Index Consultant against the workload of this batch
process?
I agree with Donna that some of your NOT conditions are going to be
expensive. However, I believe the bigger issue is the huge variation between
executions ranging from a best case of 8 minutes to a worst case of 4 hours.
--
-----------------------------------------------
Robert Waywell
Sybase Adaptive Server Anywhere Developer - Version 8
Sybase Certified Professional
Sybase's iAnywhere Solutions
Please respond ONLY to newsgroup
EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all
To Submit Bug Reports:
http://case-express.sybase.com/cx/c...sc?CASETYPE=Bug
SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288
Whitepapers, TechDocs, and bug fixes are all available through the iAnywhere
Developer Community at www.ianywhere.com/developer
"Peter Simonsen" <"Peter Simonsen"> wrote in message
news:430b095d$1@foru
ms-2-dub...
> ASA 9.0.2.3044
> Win2k
>
> Every night a batch job is performed with many sqls
> on our server.
> Before the batch job, we perform create statistics
> on every table.
>
> The SQL in question returns between 0 and 5 rows,
> but there is no relation between number of returned
> rows and performance. Usually, the SQL takes 8-12
> minuttes, occationally (increasingly lately) it takes
> 3-4 hours!
>
> Between create statistics and the SQL, a number of
> other SQLs are executed, but none of them does insert,
> update or deletes data on a large scale.
>
> How can I get information on what is killing the
> performance?
>
> BTW indexes and tables are reorganized regularly.
>
> TIA
> regards/Peter Simonsen
>
> -----------------------------------------------------
> SQL:
> select distinct query_code_id = 'S0113', bundle_id
> = b.bundle_id, actual_indu_date = b.actual_indu_date,
> collection_date = b.collection_date, indu_time =
> b.indu_time, last_pickup_time = b.last_pickup_time,
> reported_indu_date = b.reported_indu_date, actual_indu_method_k
ey =
> b. actual_indu_method_k
ey, actual_zipcode = b.actual_zipcode from
> mail m join bundle b on m.bundle_id =
> b.bundle_id, mail m join zip_range_ext zre on
> m.drop_zip_range = zre.zip_range_key, bundle b join calendar
> cal on b.collection_date = cal.cal_date, zip_range_ext zre join
> combination com on zre.ctryo_key = com.area_key where
> b.cancel_code_id = 0 and b. actual_indu_method_k
ey= com.part_1_key
> and cal.week_day_key = com.part_2_key and com. combination_type_key
=
> 175 and not exists ( select 1 from tbl_bundle_history tbh where
> tbh.bundle_id = b.bundle_id and ( ( tbh.query_code_id = 'S0113' and
> tbh.collection_date = b.collection_date ) or tbh.query_status = 'D' ) )
> and not exists ( select 1 from app_set a where a.set_id =
> m.production_set_id and a.set_type = 'Pro' and a.set_id like '%T%' )
> and ( select ','+parm+',' from setup_parm where system_module = 'VAL'
> and setting_name = 'central_validation'
and setting_subpart =
> 'exclude_receiver_id
s' ) not like '%,'+string(m.receiver_id)+',%' and (
> select ','+parm+',' from setup_parm where system_module = 'VAL' and
> setting_name = 'central_validation'
and setting_subpart =
> 'exclude_dropper_ids
' ) not like '%,'+string(m.dropper_id)+',%' and (
> com.combination_allowed = 'N' or exists ( select 1 from holiday h join
> zip_range_ext zr on zr.holi_key = h.area_key where h.collection = 'N' and
> h.holiday_date = b.collection_date and zr.zip_range_key =
> m.drop_zip_range ) )
>
> Long plan:
> ( Plan [ Total Cost Estimate: .6953957703 ] ( WorkTable
> ( HashDistinct
> ( Filter [ NOT EXISTS( SubQ 8 ) ]
> ( NestedLoopsJoin[ TRUE ]
> ( Filter [ NOT EXISTS( SubQ 7 ) ]
> ( NestedLoopsJoin[ TRUE ]
> ( NestedLoopsJoin[ TRUE ]
> ( *HashJoin
> ( HashFilter ( IndexScan ( combination_all c )
> fk_combinationall_co
mbinationtype[ ( NOT EXISTS( SubQ 3 ) ) OR ( (
> EXISTS( SubQ 2 ) ) AND ( c.valid_to >= SubQ 1 : 50% Guess ) AND (
> c.valid_from <= SubQ 1 : 50% Guess ) ) : 62.5% Guess ] )
> )
> ( IndexScan ( bundle b ) fk_bundle_cancelcode
[ hash(
> b. actual_indu_method_k
ey ) in hashmap( c.part_1_key ) : 100% Guess ] )
> )
> ( IndexScan ( calendar cal ) calendar[ cal.week_day_key =
> c.part_2_key : .1753278504% Column-Column ] )
> )
> ( IndexScan ( mail m ) fk_mail_bundle[ ( SubQ 6 NOT LIKE
> expr( '%,', m.receiver_id, ',%' ) : 50% Guess ) AND ( SubQ 5 NOT LIKE
> expr( '%,', m.dropper_id, ',%' ) : 50% Guess ) AND ( (
> c.combination_allowed = 'N' : 2.277325839% Statistics ) OR ( EXISTS( SubQ
> 4 ) ) : 100% Guess ) ] )
> )
> )
> ( IndexScan ( zip_range_ext zre ) zip_range_ext_index[
> zre.ctryo_key = c.area_key : .0639142368% Column-Column ] )
> )
> )
> )
> )
> )
> ( SubQ 1 [ Total Cost Estimate: .001595 ] ( SingleRowGroupBy
> ( TableScan prod_week[ prod_week.prod_status_id < 5000 : 43.94904524%
> Statistics ] )
> )
> )
> ( SubQ 2 [ Total Cost Estimate: 1.665854E-5 ] ( IndexScan (
> combination_type ct ) combination_type[ ct.combination_class = 'C_G' :
> 53.65853906% Statistics ] )
> )
> ( SubQ 3 [ Total Cost Estimate: 1.665854E-5 ] ( IndexScan (
> combination_type ct ) combination_type[ ct.combination_class = 'C_G' :
> 53.65853906% Statistics ] )
> )
> ( SubQ 4 [ Total Cost Estimate: .0032931208 ] ( HashJoin*
> ( IndexScan ( holiday h ) holiday[ ( h.collection = 'N' : 99.21550751%
> Statistics ) AND ( hash( h.area_key ) in hashmap( zr.holi_key ) : 100%
> Guess ) ] )
> ( HashFilter ( IndexScan ( zip_range_ext zr ) zip_range_ext_index )
> )
> )
> )
> ( SubQ 5 [ Total Cost Estimate: 3.783111E-5 ] ( IndexScan setup_parm
> setup_parm )
> )
> ( SubQ 6 [ Total Cost Estimate: 3.783111E-5 ] ( IndexScan setup_parm
> setup_parm )
> )
> ( SubQ 7 [ Total Cost Estimate: .0000075 ] ( IndexScan ( app_set a )
> app_set[ ( a.set_type = 'Pro' : 59.375% Statistics ) AND ( a.set_id LIKE
> '%T%' : 1% Guess ) ] )
> )
> ( SubQ 8 [ Total Cost Estimate: .0181773906 ] ( IndexScan (
> tbl_bundle_history tbh ) fk_tblbundlehistory_
bundle[ ( tbh.query_status =
> 'D' : 12.82337904% Statistics ) OR ( ( tbh.collection_date = expr(
> b.collection_date ) : .5131524261% Column-Column ) AND ( tbh.query_code_id
> = 'S0113' : 1.566087455% Statistics ) ) : 12.83141546% Combined ] )
> )
| |
| Peter Simonsen 2005-08-24, 3:23 am |
| Donna Osburn wrote:
> What is killing you is all the "NOT's" in your query. They
:
:
> tremendously. But the best thing is the eliminate the NOT
> Exist and swap our for an EXISTS if at all possible.
Thanks for the advice, Donna.
As a last resort, creating temporary tables beforehand is an option.
But I'd prefer to avoid adding the complexity, when the SQL performs
within 10 min 90% of the time.
regards/Peter Simonsen
| |
| Peter Simonsen 2005-08-24, 3:23 am |
| Rob Waywell wrote:
> Can you provide a graphical plan with statistics that shows a fast execution
> of the query and a slow execution of the query?
I've added code that saves the plan in XML. I'll post it when I have the
plans for both situations.
> Have you tried not recreating the statistics every time you run the batch
> job? While there are specific instances where recreating the statistics may
> help, you do risk losing information/granularity each time you recreate the
> statistics.
Well we added the create statistics step because of this query. We started
out having no idea why it suddenly became slow. And a consultant came in,
ran create statistics, and it was 'fast' again. We tried this two times,
and then added decided to add step every night.
> You have only shown a single SQL statement, but you mention that there are
> many SQL statements executed in this batch process. Is the entire slowdown
> accounted for by the single statement that you have shown?
Yes.
> Have you run the Index Consultant against the workload of this batch
> process?
Yes, it had no suggestions.
> I agree with Donna that some of your NOT conditions are going to be
> expensive. However, I believe the bigger issue is the huge variation between
> executions ranging from a best case of 8 minutes to a worst case of 4 hours.
Exactly. I hope the best & worse case execution plan will shed some light.
Thanks.
regards/Peter
|
|
|
|
|