topic Help on SQL Funny in QlikView

Help on SQL Funny

pkelly — Thu, 29 Jul 2010 16:35:14 GMT

We are doing an upgrade on our main database moving from Progress version 10.1b to 10.2B...

We have a test environment set up so that we can test the system and our reports.

I have hit a funny which I hope someone can explain to me...

The attached word document shows a script that I am running.

This works fine in the 10.1B environment (taking about 20 seconds to run) but in the 10.2B environment it takes about 30 minutes.

The from section of the script looks like...

PUB.calendar, PUB.customer, PUB."despatch_date", PUB."invoice_detail", PUB.location, PUB.product, PUB.salesperson, PUB."sales_invoice_record", PUB."ship_method", PUB."ship_to"

I have found that if I move the refence to ship_method and ship_to to near the beginning of the from section i.e.

PUB.calendar, PUB."ship_to", PUB."ship_method", PUB.customer, PUB."despatch_date", PUB."invoice_detail", PUB.location, PUB.product, PUB."sales_invoice_record", PUB.salesperson

and run this against the 10.2 database, it now runs fine....i.e. takes about 20 seconds...

Why would this make such a difference to the performance?

Help on SQL Funny

pkelly — Thu, 29 Jul 2010 20:32:26 GMT

Any ideas anyone?

Help on SQL Funny

Fri, 30 Jul 2010 01:06:17 GMT

Hi Paul,

I'm not familiar with 'Progress' but I suspect that it's not optimizing your code before it runs it. By changing the location of the table in the list in your from clause, I suspect you're changing the execution plan of the SQL query. Alterations to the execution plan can make huge differences to time to execute. (It's essentially the basis of query optimization)

This is one of the reasons that I no longer use the where clause join syntax. I used to have this problem regularly in Oracle. I find I have better success with optimization, and understanding the execution plans when I write explicit join clauses in the from clause, and not in the where clause. (It's also quite a bit easier to read and understand)

Ie:

SELECT

Whatever

FROM

Table A

INNER JOIN Table B ON TableA.ID - TableB.ID

INNER JOIN (etc)

Help on SQL Funny

pkelly — Fri, 30 Jul 2010 07:46:16 GMT

Thanks Ryan...I will give it a go....

Help on SQL Funny

pkelly — Fri, 30 Jul 2010 08:13:28 GMT

Hi Ryan

I amended my query as per your suggestion...

This works fine with 10.1B (about 20 seconds) but with the new updated software (10.2B) it is taking ages to run....

Tried moving the inner join statements about as I did with the From Tables - no success...

This really is weird...

Help on SQL Funny

Bjorn_Wedbratt — Fri, 30 Jul 2010 11:03:16 GMT

I agree with Ryan. I have no knowledge about Progress, but I know that some db:s are very sensitive to the query matching the indexes available. If it can't find an exact matching index the db will perform a table scan instead, which could be very time consuming.

I recall working with AS/400 years ago, and if you for example had an index on a table covering field A, B, C, but only used A and B in your query the index wasn't used. By including the field C in the query (even though not making any filtering on the field) so the query exactly matched the index you could see that the index was used, hence a huge performance boost.

It could also be that you need to rebuild your indexes, or other issues related to the upgrade. I suggest you turn the question to your Progress consultant which can help you out tuning the db for you, if you're not lucky finding a Progress expert here on the forum 🙂

Help on SQL Funny

pkelly — Fri, 30 Jul 2010 22:01:48 GMT

Thanks Bjorn and Ryan....

Agree, think we will have to go back to our software house.

Many thanks

Paul