Solved

SQL92 query - SQL performance issue running off a PROGRESS DB

Posted on 2006-06-14
33
574 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:AlexKirwan
  • 19
  • 14
33 Comments
 
LVL 25

Expert Comment

by:jrb1
ID: 16909048
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
0
 
LVL 2

Author Comment

by:AlexKirwan
ID: 16909601
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?



0
 
LVL 25

Expert Comment

by:jrb1
ID: 16911676
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.
0
 
LVL 2

Author Comment

by:AlexKirwan
ID: 16912371
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!
0
 
LVL 25

Expert Comment

by:jrb1
ID: 16912428
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
0
 
LVL 2

Author Comment

by:AlexKirwan
ID: 16918543
Thanks for your help

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

cheers
0
 
LVL 2

Author Comment

by:AlexKirwan
ID: 16919317
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!
0
 
LVL 25

Expert Comment

by:jrb1
ID: 16920120
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.
0
 
LVL 2

Author Comment

by:AlexKirwan
ID: 16920566
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?
0
 
LVL 2

Author Comment

by:AlexKirwan
ID: 16921240
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!
0
 
LVL 25

Expert Comment

by:jrb1
ID: 16921248
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))
0
 
LVL 2

Author Comment

by:AlexKirwan
ID: 16921667
Thanks I will post up my findings and points when I get a chance to run

Thanks for your time!!
0
 
LVL 2

Author Comment

by:AlexKirwan
ID: 16933543
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
0
 
LVL 25

Expert Comment

by:jrb1
ID: 16934541
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
0
 
LVL 2

Author Comment

by:AlexKirwan
ID: 16934828
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!
0
 
LVL 2

Author Comment

by:AlexKirwan
ID: 16935030
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??



0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 25

Expert Comment

by:jrb1
ID: 16935469
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))
0
 
LVL 2

Author Comment

by:AlexKirwan
ID: 16935607
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

0
 
LVL 25

Accepted Solution

by:
jrb1 earned 500 total points
ID: 16935656
The first part can change to 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
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))
INNER 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

What are the datatypes of:

Table_Two.Field_I
Table_C.Field_J

If they are a string, change the "NULL" to ' ', or if a number, make them 0.
0
 
LVL 2

Author Comment

by:AlexKirwan
ID: 16935842
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
0
 
LVL 25

Expert Comment

by:jrb1
ID: 16935901
Do both parts run now, but some don't return data?  Does it now run with the UNION?
0
 
LVL 2

Author Comment

by:AlexKirwan
ID: 16935935
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.

0
 
LVL 25

Expert Comment

by:jrb1
ID: 16936015
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?
0
 
LVL 2

Author Comment

by:AlexKirwan
ID: 16936064
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
0
 
LVL 2

Author Comment

by:AlexKirwan
ID: 16936083
sorry it did run and it returned 6 rows also
0
 
LVL 25

Expert Comment

by:jrb1
ID: 16936113
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
0
 
LVL 25

Expert Comment

by:jrb1
ID: 16936133
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))
0
 
LVL 2

Author Comment

by:AlexKirwan
ID: 16936649
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
0
 
LVL 2

Author Comment

by:AlexKirwan
ID: 16949968
Hi John.

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

Expert Comment

by:jrb1
ID: 16951352
Yes.  I'll put together a few more simple queries that should show where the problem is.
0
 
LVL 2

Author Comment

by:AlexKirwan
ID: 16951878
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
0
 
LVL 25

Expert Comment

by:jrb1
ID: 16954194
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
0
 
LVL 2

Author Comment

by:AlexKirwan
ID: 16969040
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?

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21894186.html

Thanks as always,
Alex
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now