SLOW Queries !!!

In article <38lia4$97d@emory.mathcs.emory.edu>, pp@q8.dk (Poul Pedersen) writes: |> Now look at the Optimizer Query Plan: |> |> |> QUERY: (LOW) |> ------ |> select * from ks_gdkonto |> where gd_kontonr = "0003573508" |> and gd_prod_kode < 90000 |> |> |> Estimated Cost: 2 |> Estimated # of Rows Returned: 1 |> |> create table "kundedb".ks_gdkonto |> ( |> gd_seg_type char(3), |> Stuff deleted |> gd_prod_kode char(5), |> ^^^^^^^ AAAAAaaaaarrrrggghh !! |> Stuff deleted |> ); |> |> Why can't the Optimizer use a better Query Plan on this simple select ? |> |>> Ron Rupert replies >>>> |>> Hi Poul, |>> I think part of your problem lies with using an arithmetic qualifier, |>> the "<" sign against a character field. The optimizer is using the indexed |>> column gd_kontonr the way it should, but forcing it to look at gd_prod_kode |>> as a numeric value is possibly slowing it down. |>> |>> Just a guess. |>> |>> Anyone know for sure? |>> |>> -- Yes !, I've lost a lot of sleep trying to optimize queries and found that char(n) fields used as in the example above worked (i.e. seemed to deliver the goods) but were incredibly slow. We slapped single quotes around our (embedded) queries where a char field was compared and bingo !! the improvement was circa 62 secs down to under a second ! Its a lovely feature of OnLine to be able to compare a numeric value to a character field ;-) but can you afford the overhead ?? Try this... select * from ks_gdkonto where gd_kontonr = "0003573508" and gd_prod_kode < '90000' Hope this helps, Tom Hogarty Standard Disclaimers Apply SNI Paderborn Germany Tel +49-52518-11602 +49-5254-65553($HOME) e-mail <hogarty.pad@sni.de>