Start Free Trial

asked on

SQL92 query - SQL performance issue running off a PROGRESS DB

Hi guys,

I can’t find a PROGRESS database topic area so as the query Im writing is standard SQL I thought someone here could point me in the right direction.

I have a query with major performance issues!!! and any help would be most appreciated

Below is my query.
It uses two LEFT OUTER JOIN - both are necessary.
I have ran an update table and update index statistics scripts before running the query and this has little if no effect.

It is worth noting that we are running off a PROGRESS 9.1E database connecting via ODBC MERANT 3.60 32 Bit Progress SQL92

I'm hoping that my query is just badly written as it is small enough and Im running out of ideas fast!

Also this same query runs fine when running for our smaller departments, but our main departments it crashes on.

SELECT Table_One.Field_A, Table_One.Field_B,
Table_One.Field_C,
Table_One.Field_D, Table_One.Field_E ,Table_One.Field_F,
Table_One.Field_G, Table_One.Field_H, Table_Two.Field_I, Table_C.Field_J
FROM (PUB.Table_One Table_One
LEFT OUTER JOIN PUB.Table_Three Table_Three
ON (Table_One.Field_B=Table_Three.Field_B) AND (Table_One.Field_G=Table_Three.Field_G))
LEFT OUTER JOIN PUB.Table_Two Table_Two
ON ((Table_One.Field_G=Table_Two.Field_G)
AND (Table_Three.Field_K=Table_Two.Field_K))
AND (Table_Three.Field_L=Table_Two.Field_L)
WHERE Table_One.Field_F='R' AND Table_One.Field_G=39

Hi AlexKirwan,

Do you have statistics on the tables? Try:

update table statistics for pub.table_one;
update table statistics for pub.table_two;
update table statistics for pub.table_three;

Regards,
John

ASKER

Hi John,

Yes I had already ran these scripts.

I also ran

update index statistics for pub.table_one;
update index statistics for pub.table_two;
update index statistics for pub.table_three;

Now when I ran the update table statistics query it took around 30 seconds to execute for each table then it came back saying that 0 records had been modified however there were records in the sysprogress tables which would indicate that the update statistics query did run.

select * from sysprogress.systblstat
select * from sysprogress.sysidxstat

After running the above query’s following information was displayed with one line for each of
the tables updated:

TBLID CARD NPAGES RECSZ PAGESZ RSSID
NN NNNN NNNN NNNN NNNN NNN

Where TBLID is the table number
CARD is the cardinal number of the table
NPAGES is the number of pages in the table
RECSZ is the largest record size
PAGESZ is the memory page size, and
RSSID is the process ID of the Update Statistics

As I said this shows that the statistics were indeed updated - this is why I was left totally confused as to why the performance is so slow??

How does my SQL look? can u see any problems with it?

It's the left outer joins that are killing you. How are the records related? Here's my guess....

You have a record in Table 1. You may or may not have a matching record in Table 3. If there is no record in Table 3, will there be a record in Table 2 (I'm guessing no)? If there is a matching record in Table 3, there may or may not be a matching record in Table 2.

Let me know. There's another way to organize the query depending on these relationships.

ASKER

You are correct!

I have a record in Table 1 - I may or may not have a matching record in Table 3 but I still need to see the record in Table 1. If there is no record in table 3 there will be no record in table 2 and if there is a matching record in Table 3, there may or may not be a matching record in Table 2.

You got it in one there for the requirments

Thanks for your help so far!

You can do it as a union. My only other thought is about these fields:

Table_One.Field_F='R' AND Table_One.Field_G=39

Are they indexed? If not, that should help a lot.

SELECT Table_One.Field_A, Table_One.Field_B,
Table_One.Field_C,
Table_One.Field_D, Table_One.Field_E ,Table_One.Field_F,
Table_One.Field_G, Table_One.Field_H, Table_Two.Field_I, Table_C.Field_J
FROM PUB.Table_One Table_One
WHERE Table_One.Field_F='R' AND Table_One.Field_G=39
AND NOT EXISTS
(SELECT 1 FROM PUB.Table_Three Table_Three
WHERE (Table_One.Field_B=Table_Three.Field_B) AND (Table_One.Field_G=Table_Three.Field_G))
UNION ALL
SELECT Table_One.Field_A, Table_One.Field_B,
Table_One.Field_C,
Table_One.Field_D, Table_One.Field_E ,Table_One.Field_F,
Table_One.Field_G, Table_One.Field_H, Table_Two.Field_I, Table_C.Field_J
FROM (PUB.Table_One Table_One
INNER JOIN PUB.Table_Three Table_Three
ON (Table_One.Field_B=Table_Three.Field_B) AND (Table_One.Field_G=Table_Three.Field_G))
LEFT OUTER JOIN PUB.Table_Two Table_Two
ON ((Table_One.Field_G=Table_Two.Field_G)
AND (Table_Three.Field_K=Table_Two.Field_K))
AND (Table_Three.Field_L=Table_Two.Field_L)
WHERE Table_One.Field_F='R' AND Table_One.Field_G=39

ASKER

Thanks for your help

let me work on this and get back to you as soon as possible

cheers

ASKER

Hi jrb1

I don’t think the whole of that code is compatible with the PROGRESS DB Im running off.

Im getting a few syntax errors. It does not seem to like

AND NOT EXISTS
(SELECT 1 FROM PUB.Table_Three Table_Three

Although I don’t know that for certain.

I know for a fact that PROGRESS does accept and work with the UNION command and it does run to the SQL92 standard so I can’t see anything wrong.

Are you familiar with Progress? Have you run a similar query off PROGRESS before? If not is there another similar way to run this?

Thanks again for your time!

I am familiar with Progress, and this should work. It's been a while, so I may be missing something specific with the syntax though (and I don't have a db I can work with now). Try "select *" instead of "select 1".

Look in thie doc for EXISTS:

http://www.psdn.com/library/servlet/KbServlet/download/1094-102-902/s92.pdf

Page 2-35 has an example.

ASKER

Hi John,

Quick update.... the SELECT * was the way to go

I havent gone through the doc as of yet (working through it) in regards to the EXISTS command.

But when I run the script now with the "SELECT *" there is no syntax error but it seems to be looking for the Table_2 and Table_3 in the first part

SELECT Table_One.Field_A, Table_One.Field_B,
Table_One.Field_C,
Table_One.Field_D, Table_One.Field_E ,Table_One.Field_F,
Table_One.Field_G, Table_One.Field_H, Table_Two.Field_I, Table_C.Field_J
FROM PUB.Table_One Table_One
WHERE Table_One.Field_F='R' AND Table_One.Field_G=39

If I add both Table_2 & Table_3 it runs but returns nothing......

Is there a way around this from your experience?

ASKER

Hi John,

From tests and the doc the EXISTS command is fine.

I ran some smaller scripts using the EXISTS commands and they ran OK - so thanks!

Im looking again at other aspects including the beginning as mentioned in previous post

For the first SELECT statement it looks like its looking for both Table_2 & 3 as the SELECT statement references FIELDS_I & J from those tables.

I have taken those fields out but it wont work as it does not match the other side of the set up etc.... and if I add the tables into the FROM clause it returns nothing when run.

The script appears fine and is only giving me that error.

I really appreciate your help so far and it’s definitely got me beginning to have more faith in this PROGRESS DB and if I get it working..... Well let’s just say the drinks will be on me ;)... If you’re ever in Dublin that is!

oh, sorry. you just need to flip the query over and substitute nulls:

SELECT Table_One.Field_A, Table_One.Field_B,
Table_One.Field_C,
Table_One.Field_D, Table_One.Field_E ,Table_One.Field_F,
Table_One.Field_G, Table_One.Field_H, Table_Two.Field_I, Table_C.Field_J
FROM (PUB.Table_One Table_One
INNER JOIN PUB.Table_Three Table_Three
ON (Table_One.Field_B=Table_Three.Field_B) AND (Table_One.Field_G=Table_Three.Field_G))
LEFT OUTER JOIN PUB.Table_Two Table_Two
ON ((Table_One.Field_G=Table_Two.Field_G)
AND (Table_Three.Field_K=Table_Two.Field_K))
AND (Table_Three.Field_L=Table_Two.Field_L)
WHERE Table_One.Field_F='R' AND Table_One.Field_G=39
UNION ALL
SELECT Table_One.Field_A, Table_One.Field_B,
Table_One.Field_C,
Table_One.Field_D, Table_One.Field_E ,Table_One.Field_F,
Table_One.Field_G, Table_One.Field_H, null, null
FROM PUB.Table_One Table_One
WHERE Table_One.Field_F='R' AND Table_One.Field_G=39
AND NOT EXISTS
(SELECT * FROM PUB.Table_Three Table_Three
WHERE (Table_One.Field_B=Table_Three.Field_B) AND (Table_One.Field_G=Table_Three.Field_G))

ASKER

Thanks I will post up my findings and points when I get a chance to run

Thanks for your time!!

ASKER

Hi John,

Hope the Weekend was good over in your part of the land!!

Just in regards to this (irritating) query Im trying to run. First off I better just say that the initial relations I said to u at the beginning were slightly incorrect

I got the last part wrong when I said "and if there is a matching record in Table 3, there may or may not be a matching record in Table 2" I should have said that if there is a matching record in Table_3 then there has to be a matching record in Table_2.

So in full....
I have a record in Table 1 - I may or may not have a matching record in Table 3 but I still need to see the record in Table 1. If there is no record in table 3 there will be no record in table 2 and if there is a matching record in Table 3, then there has to be a matching record in Table 2.

This is not much of a change and I went to change the JOINS around? Or is there another way to organize the query based on the new relation requirement?

However Im still getting Syntax errors (even when sticking to the original relations and code).... I have gone through the doc and all appears to be within the standard. Can you see any reason why this wont run?

Thanks again for your time,

Alex

I'm not sure what the Syntax error would be caused by (unless it is a missing "(" or typo or something). How about this:

SELECT Table_One.Field_A, Table_One.Field_B,
Table_One.Field_C,
Table_One.Field_D, Table_One.Field_E ,Table_One.Field_F,
Table_One.Field_G, Table_One.Field_H, Table_Two.Field_I, Table_C.Field_J
FROM (PUB.Table_One Table_One
LEFT OUTER JOIN
(PUB.Table_Three Table_Three
INNER JOIN PUB.Table_Two Table_Two
ON (Table_Three.Field_K=Table_Two.Field_K)
AND (Table_Three.Field_L=Table_Two.Field_L))
ON (Table_One.Field_B=Table_Three.Field_B)
AND (Table_One.Field_G=Table_Three.Field_G)
AND ((Table_One.Field_G=Table_Two.Field_G))
WHERE Table_One.Field_F='R' AND Table_One.Field_G=39

ASKER

still no joy Im afraid...... Its very hard when all you get is syntax error and no indication where etc.... I will plug away nonetheless

I take it the last code you gave changed due to the new relationships of the tables?

Im looking at the brackets as possible syntax error...... 8 brackets on the left 7 on the right

will update and reward when Ive run it

Thanks so far John!

ASKER

Hi I got that script to run but its still hanging for the last 20 mins now...... It seems to be performing worse as I ran it for one of our smaller departments which was comming back within 30 seconds before??

Yikes. OK, I'm guessing it's due to the joins. Let's go back to my other solution. Does each part run independently?

SELECT Table_One.Field_A, Table_One.Field_B,
Table_One.Field_C,
Table_One.Field_D, Table_One.Field_E ,Table_One.Field_F,
Table_One.Field_G, Table_One.Field_H, Table_Two.Field_I, Table_C.Field_J
FROM (PUB.Table_One Table_One
INNER JOIN PUB.Table_Three Table_Three
ON (Table_One.Field_B=Table_Three.Field_B) AND (Table_One.Field_G=Table_Three.Field_G))
LEFT OUTER JOIN PUB.Table_Two Table_Two
ON ((Table_One.Field_G=Table_Two.Field_G)
AND (Table_Three.Field_K=Table_Two.Field_K))
AND (Table_Three.Field_L=Table_Two.Field_L)
WHERE Table_One.Field_F='R' AND Table_One.Field_G=39

--and--

SELECT Table_One.Field_A, Table_One.Field_B,
Table_One.Field_C,
Table_One.Field_D, Table_One.Field_E ,Table_One.Field_F,
Table_One.Field_G, Table_One.Field_H, null, null
FROM PUB.Table_One Table_One
WHERE Table_One.Field_F='R' AND Table_One.Field_G=39
AND NOT EXISTS
(SELECT * FROM PUB.Table_Three Table_Three
WHERE (Table_One.Field_B=Table_Three.Field_B) AND (Table_One.Field_G=Table_Three.Field_G))

ASKER

The first part runs..... The second part does not run (syntax error) - It runs after I remove the two NULL fields.

Is this taking into account the new relationships ie: If there is a matching record in Table_3 there has to be a record in Table_2

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Hi John

It now runs although does not return any data. ?

The performance was as expected for the smaller departments Im running for the larger one now and its still executing but will return 0 data either way

Thnks

Alex

Do both parts run now, but some don't return data? Does it now run with the UNION?

ASKER

The first part runs but returns no data
The second part also runs and returns no data

It now runs with the UNION ALL command.

Here is your original. How many rows does it return?

SELECT Table_One.Field_A, Table_One.Field_B,
Table_One.Field_C,
Table_One.Field_D, Table_One.Field_E ,Table_One.Field_F,
Table_One.Field_G, Table_One.Field_H, Table_Two.Field_I, Table_C.Field_J
FROM (PUB.Table_One Table_One
LEFT OUTER JOIN PUB.Table_Three Table_Three
ON (Table_One.Field_B=Table_Three.Field_B) AND (Table_One.Field_G=Table_Three.Field_G))
LEFT OUTER JOIN PUB.Table_Two Table_Two
ON ((Table_One.Field_G=Table_Two.Field_G)
AND (Table_Three.Field_K=Table_Two.Field_K))
AND (Table_Three.Field_L=Table_Two.Field_L)
WHERE Table_One.Field_F='R' AND Table_One.Field_G=39

And here is your original with one extra line:

SELECT Table_One.Field_A, Table_One.Field_B,
Table_One.Field_C,
Table_One.Field_D, Table_One.Field_E ,Table_One.Field_F,
Table_One.Field_G, Table_One.Field_H, Table_Two.Field_I, Table_C.Field_J
FROM (PUB.Table_One Table_One
LEFT OUTER JOIN PUB.Table_Three Table_Three
ON (Table_One.Field_B=Table_Three.Field_B) AND (Table_One.Field_G=Table_Three.Field_G))
LEFT OUTER JOIN PUB.Table_Two Table_Two
ON ((Table_One.Field_G=Table_Two.Field_G)
AND (Table_Three.Field_K=Table_Two.Field_K))
AND (Table_Three.Field_L=Table_Two.Field_L)
WHERE Table_One.Field_F='R' AND Table_One.Field_G=39
AND Table_Three.Field_B IS NULL

How many rows does this return?

ASKER

The original returns 6 rows

the original with one extra line does not run..... its a string and I ran it with ('') but still did not run

ASKER

sorry it did run and it returned 6 rows also

I don't get that. The docs show (and I remember doing it...)

You can select or reject a row containing NULL values in a column by using the [NOT] NULL
search condition:

... WHERE commission IS NOT NULL ;
... WHERE commission IS NULL ;

Also, you should have been able to do the NULL select. Does this return anything?

select null as nullcolumn
from PUB.Table_One

OK, that means that every TABLE_ONE row had a matching TABLE_THREE row. So we are expecting the results from:

SELECT Table_One.Field_A, Table_One.Field_B,
Table_One.Field_C,
Table_One.Field_D, Table_One.Field_E ,Table_One.Field_F,
Table_One.Field_G, Table_One.Field_H
FROM PUB.Table_One Table_One
WHERE Table_One.Field_F='R' AND Table_One.Field_G=39
AND NOT EXISTS
(SELECT * FROM PUB.Table_Three Table_Three
WHERE (Table_One.Field_B=Table_Three.Field_B) AND (Table_One.Field_G=Table_Three.Field_G))

ASKER

Hi sorry for not getting back to you

Im out of the office now and my vpn back in is down so I dont have access to my DB any longer.... I will post my results when Im back in

Thanks for your time so far John

ASKER

Hi John.

The last query above returns no data. where u expecting the same 6 rows back?

Yes. I'll put together a few more simple queries that should show where the problem is.

ASKER

During your time did you manage to run complex SQL query's off of a PROGRESS DB in an efficient manner?

PROGRESS isn't good at the same things that Oracle and SQL Server are good at. For example, SELECT COUNT(*) FROM PUB.Table_One will be very slow on a large PROGRESS table, but the same query would be fairly quick on Oracle or SQL Server. Here's another example. SELECT * FROM PUB.TBLNAME WHERE 1<>1 executes very slow on a large PROGRESS table because PROGRESS will scan the entire table, blindly comparing 1 to 1 for each row.

So how did you perform during your time with PROGRESS ?

Thanks,
Alex

Performance was never great. For small tables (which most of them were) it was OK, but any time things got complicated, it was bad. We moved from that to SQL Server 2000, which was quite a bit better. I prefer Oracle but haven't had a chance to user SQL Server 2005 yet--which I hear is pretty good.

try these:

select Table_One.Field_B Table_One.Field_G from PUB.Table_One Table_One
where Table_One.Field_F='R' AND Table_One.Field_G=39

Then

select Table_One.Field_B Table_One.Field_G,
from PUB.Table_One Table_One
left outer join PUB.Table_Three Table_Three
ON (Table_One.Field_B=Table_Three.Field_B) AND (Table_One.Field_G=Table_Three.Field_G))
where Table_One.Field_F='R' AND Table_One.Field_G=39

ASKER

Hi thanks John....

Im going to concentrate on importing PROGRESS tables to SQL Server for reporting purposes. PROGRESS just cant handle the complex queries for our larger departments.

Thanks for your help I will use your code after I have imported the tables into SQL server.

Do you have any experience in writing SP to copy tables from one DB to another on separate servers? If so could I trouble you to have a look at this open question please?

https://www.experts-exchange.com/questions/21894186/Stored-procedure-to-copy-the-data-from-Progress-to-SQL-Server.html

Thanks as always,
Alex