|
Home > Archive > SQL Anywhere database > June 2005 > SQL Select Performance...
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 |
SQL Select Performance...
|
|
| Troy Coombs 2005-06-22, 8:23 pm |
| When I add the following, "or associate.assoc_id IN( '11581,18485'))", to
the SQL below the performance greatly slows down. According to the Plan in
ISQL the index on "associate"."assoc_name_1" is still used but by it it says
"(All Rows)". I've never seen that before, not totally sure what it means
but I'm assuming although it's using the associate_name index it's still
scanning all rows probably due to my OR.
Any ideas or suggestions on how I can keep the functionality of the below
Select but increase its performance? oh yeah using ASA 8.0.2.
Troy
SELECT "associate"."assoc_name_1",
"associate"."assoc_active",
"associate_customer"."assoc_id",
"associate"."assoc_asscl_id",
"associate_address"."assad_add_1",
"places"."place_name",
"associate_address"."assad_provc_id",
upper("associate"."assoc_name_reversed")
FROM "associate",
"associate_customer",
"associate_add_ptr",
"associate_address",
"places"
WHERE ( "associate"."assoc_id" = "associate_customer"."assoc_id" ) and
( "associate_address"."assad_assoc_id" =
"associate_add_ptr"."assap_assoc_id_ptr" ) and
( "associate_address"."assad_sequence" =
"associate_add_ptr"."assap_sequence" ) and
( "associate"."assoc_id" =
"associate_add_ptr"."assap_assoc_id_ptr" ) and
( "places"."place_id" = "associate_address"."assad_place_id" ) and
( ( "associate"."assoc_active" = '1' ) ) AND
"associate_add_ptr"."assap_assat_id" = 'PL' AND
"associate_add_ptr"."assap_assty_id" = 'CU' AND
("associate"."assoc_name_1" like 'tran%' or associate.assoc_id
IN( '11581,18485'))
ORDER BY "associate"."assoc_name_1" ASC
| |
| Martin Baur 2005-06-23, 3:23 am |
|
> ("associate"."assoc_name_1" like 'tran%' or associate.assoc_id
> IN( '11581,18485'))
shouldn't this read like
in (11581,18485)
The single quoes make those two numbers one string which is for sure wrong.
Martin
| |
| Troy Coombs 2005-06-23, 3:23 am |
| Yes your right I later fixed that. Please ignore that point.
"Martin Baur" <tinu@mindpower.com> wrote in message
news:MPG. 1d24347bde26587c9898
0b@forums.sybase.com...
>
>
> shouldn't this read like
>
> in (11581,18485)
>
> The single quoes make those two numbers one string which is for sure
wrong.
>
> Martin
| |
| Glenn Paulley 2005-06-23, 9:23 am |
| "Troy Coombs" <coombst@quorumis.com> wrote in
news:42b9e449$1@foru
ms-1-dub:
> When I add the following, "or associate.assoc_id IN( '11581,18485'))",
> to the SQL below the performance greatly slows down. According to the
> Plan in ISQL the index on "associate"."assoc_name_1" is still used but
> by it it says "(All Rows)". I've never seen that before, not totally
> sure what it means but I'm assuming although it's using the
> associate_name index it's still scanning all rows probably due to my
> OR.
>
> Any ideas or suggestions on how I can keep the functionality of the
> below Select but increase its performance? oh yeah using ASA 8.0.2.
>
> Troy
>
> SELECT "associate"."assoc_name_1",
> "associate"."assoc_active",
> "associate_customer"."assoc_id",
> "associate"."assoc_asscl_id",
> "associate_address"."assad_add_1",
> "places"."place_name",
> "associate_address"."assad_provc_id",
> upper("associate"."assoc_name_reversed")
> FROM "associate",
> "associate_customer",
> "associate_add_ptr",
> "associate_address",
> "places"
> WHERE ( "associate"."assoc_id" = "associate_customer"."assoc_id" )
> and
> ( "associate_address"."assad_assoc_id" =
> "associate_add_ptr"."assap_assoc_id_ptr" ) and
> ( "associate_address"."assad_sequence" =
> "associate_add_ptr"."assap_sequence" ) and
> ( "associate"."assoc_id" =
> "associate_add_ptr"."assap_assoc_id_ptr" ) and
> ( "places"."place_id" = "associate_address"."assad_place_id"
> ) and ( ( "associate"."assoc_active" = '1' ) ) AND
> "associate_add_ptr"."assap_assat_id" = 'PL' AND
> "associate_add_ptr"."assap_assty_id" = 'CU' AND
> ("associate"."assoc_name_1" like 'tran%' or
> associate.assoc_id
> IN( '11581,18485'))
> ORDER BY "associate"."assoc_name_1" ASC
>
>
"ALL ROWS" means that the engine is doing a complete index scan to
satisfy the ORDER BY clause (and hence not require a separate sort).
What optimization goal are you using?
--
Glenn Paulley
Research and Development Manager, Query Processing
iAnywhere Solutions Engineering
EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all
To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm
SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288
|
|
|
|
|