Hi all,

If you can help me, I'm not able to discover why this query below hangs ("fetching rows...") when accessing this ultralite database: http://www.4shared.com/file/Jj_LbFd-/ESP_pcom.html with Interactive SQL.

SELECT distinct top 100 price.code, price.product, "table", fobPrice, cifPrice, descount, cost,      MaximumDiscount, MinimumQuantity, QuantityMultiplier, UnitOfMeasure.Code as UOMCode, UnitOfMeasure.Name as UOMName, Product.description, Product.displaystock, Product.reference, Product.special, ProductStock.Stock, Packing FROM price inner join UnitOfMeasure on price.UnitOfMeasure = UnitOfMeasure.Code INNER JOIN Product ON price.Product = Product.Code LEFT OUTER JOIN ProductStock ON ProductStock.Product = price.Product AND price.UnitOfMeasure = ProductStock.UnitOfMeasure 
WHERE description LIKE 'lixa%' AND "table" = 1 AND Product.status = 'A' 
ORDER BY Product.reference

What is weird is that if I change the like clause to '%lixa' (witch should be worse) instead of 'lixa%', the query runs OK.

This problem started when I upgraded to version 12.0.1.3840. With version 12.0.1.3352 the query run normally.

Thanks in advance

Aderval Mendonça

asked 19 Feb '13, 10:01

Aderval's gravatar image

Aderval
1113
accept rate: 0%

edited 19 Feb '13, 10:04

Please zip and forward the database to my email (first_name.last_name@sap.com).

(19 Feb '13, 13:47) Chris Keating

Done. Waiting now...

(19 Feb '13, 14:37) Aderval

Dropping and recreating the index index_product1 resolves the pegged CPU that was the hang behaviour. The drop causes the query to use the index index_product4. As an aside, the like "%lixa" query plan uses the primary key index to resolve the query.

permanent link

answered 19 Feb '13, 22:01

Chris%20Keating's gravatar image

Chris Keating
7.3k48121
accept rate: 31%

It worked!! Thank you very much!!!

(20 Feb '13, 07:35) Aderval
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×160
×12

question asked: 19 Feb '13, 10:01

question was seen: 1,764 times

last updated: 20 Feb '13, 07:59