Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
ORDER BY clause on SELECT columns

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
manoopatil

New User


Joined: 07 Dec 2003
Posts: 56
Location: Montreal

PostPosted: Sat Nov 01, 2008 2:40 am    Post subject: ORDER BY clause on SELECT columns
Reply with quote

Hi,

I am analyzing a query which take a substantial CPU. This query performs a join between the two tables each with 3 million records. and have "ORDER BY" clause on 4 columns. Interesting 2 columns from these ORDER BY clause are not there in "SELECT" clause, in other words those would not be the part of the result set. There are about 100,000 records in the result set.

When I ran a Strobe, the big part of CPU is going the RDS SORT process. Can I conclude that having the non selected columns in ORDER BY clause are causing expensive performance of query?

If if include those two columns in "ORDER BY" clause to the SELECT clause as well to get in result set, will it improve the performance?

Please let me know if any additional information is needed.

Note : I dont have access to run these queries and check the cost of the query

Thanks
-Manohar
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sat Nov 01, 2008 3:16 am    Post subject:
Reply with quote

Hello,

Please post the problem query as well as mentioning which columns are keys/parts of keys in the tables.
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10326
Location: italy

PostPosted: Sat Nov 01, 2008 4:40 am    Post subject: Reply to: ORDER BY ON NOT SELECTED COLUMNS
Reply with quote

Quote:
Can I conclude that having the non selected columns in ORDER BY clause are causing expensive performance of query?


It would be strange if it was that way ...
the most time is spent in accessing data and shuffling it as requested by the query,
the resources spent to move data from the db2 buffers to the user working storage are, I would say, irrelevant

/first glance on icon_biggrin.gif
the requirement is odd anyway, even if You might have good reasons for it
getting data in an order which is not visible, is from a user perspective a bad design
( I get something and I do not realize why I am getting it in that way)
Back to top
View user's profile Send private message
manoopatil

New User


Joined: 07 Dec 2003
Posts: 56
Location: Montreal

PostPosted: Tue Nov 04, 2008 1:14 am    Post subject: Reply to: ORDER BY clause on SELECT columns
Reply with quote

Thanks Dick & Erico,

Here is the query -

Code:
   SELECT    A.BILLING_NUMBER,
             A.SEQ_NO,
             A.SERVICE_TYPE_CD,
             B.FK0COGFK_S_NSB_CUS,
             B.FK0COGFK_S_TELCOTE,
             B.FK0COGCOG_SEQ_ID
   FROM    M_TBL1 A, M_TBL2 B
   WHERE   B.FK_COGFK_S_NSB_CUS = :WS-BPCOG-CUST
   AND     B.FK_COGFK_S_TELCOTE = :WS-BPCOG-TELCO
   AND     B.FK_COGCOG_SEQ_ID   = :WS-BPCOG-SEQ
   AND     A.FK_S_NSB_CUSTOMCUS = B.FK0COGFK_S_NSB_CUS
   AND     A.FK_S_TELCOTELCO_ID = B.FK0COGFK_S_TELCOTE
   AND     A.COG_SEQ_ID         = B.FK0COGCOG_SEQ_ID
   AND     A.START_DATE        <= :WS-DB2-DATE-IN
   AND     A.STOP_DATE         >= :WS-DB2-DATE-IN
   AND     B.START_DATE        <= :WS-DB2-DATE-IN
   AND     B.STOP_DATE         >= :WS-DB2-DATE-IN
   ORDER BY BILLING_NUMBER, SEQ_NO, FK0COGFK_S_TELCOTE,
               A.START_DATE DESC, B.START_DATE DESC

Please note - A.START_DATE and B.START_DATE are not the part of Select Clause

The relavent idex on M_TBL1 is -

Code:
IXNAME              COLNAME                          COLNO  COLSEQ  ORDERING
------------------  ------------------                    ------  ------        --------
XNSB4801            FK_S_NSB_CUSTOMCUS      21       1              A
XNSB4801            FK_S_TELCOTELCO_ID        22       2              A
XNSB4801            COG_SEQ_ID                       1       3               D

The relavent idex on M_TBL2 is -

Code:
COLNAME                   IXNAME               COLNO  COLSEQ  ORDERING
------------------             ------------------  ------  ------        --------   -------
FK_COGFK_S_NSB_CUS  XNSB3803             12        1           A         
FK_COGFK_S_TELCOTE  XNSB3803               13       2           A         
FK_COGCOG_SEQ_ID    XNSB3803               14       3           A       


Please let me know if any additional information is needed
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Nov 04, 2008 1:36 am    Post subject:
Reply with quote

Hello,

Please repost using the "Code" tag.

I tried to Code your index info and it is too fragmented. When preparing a reply, it is best to Preview to make sure your post has been "Code"ed as you want it and that the entire post appears properly.

When you are satisfied with the Preview, Submit.
Back to top
View user's profile Send private message
manoopatil

New User


Joined: 07 Dec 2003
Posts: 56
Location: Montreal

PostPosted: Tue Nov 04, 2008 2:14 am    Post subject: Re: Reply to: ORDER BY clause on SELECT columns
Reply with quote

Thanks Dick & Erinco,

Here is the query -


Code:
 
   SELECT   A.BILLING_NUMBER,
                 A.SEQ_NO,
                 A.SERVICE_TYPE_CD,
                 B.FK0COGFK_S_NSB_CUS,
                 B.FK0COGFK_S_TELCOTE,
                 B.FK0COGCOG_SEQ_ID

   FROM    M_TBL1 A, M_TBL2 B

   WHERE B.FK_COGFK_S_NSB_CUS      = :WS-BPCOG-CUST
   AND     B.FK_COGFK_S_TELCOTE    = :WS-BPCOG-TELCO
   AND     B.FK_COGCOG_SEQ_ID      = :WS-BPCOG-SEQ
   AND     A.FK_S_NSB_CUSTOMCUS    = B.FK0COGFK_S_NSB_CUS
   AND     A.FK_S_TELCOTELCO_ID    = B.FK0COGFK_S_TELCOTE
   AND     A.COG_SEQ_ID            = B.FK0COGCOG_SEQ_ID
   AND     A.START_DATE            <= :WS-DB2-DATE-IN
   AND     A.STOP_DATE             >= :WS-DB2-DATE-IN
   AND     B.START_DATE            <= :WS-DB2-DATE-IN
   AND     B.STOP_DATE             >= :WS-DB2-DATE-IN

   ORDER BY BILLING_NUMBER, SEQ_NO, FK0COGFK_S_TELCOTE,
               A.START_DATE DESC, B.START_DATE DESC


Please note - A.START_DATE and B.START_DATE are not the part of Select Clause

The relavent index on M_TBL1 is on these columns -

Code:

COLNAME                         
------------------------------
FK_S_NSB_CUSTOMCUS   
FK_S_TELCOTELCO_ID     
COG_SEQ_ID                   



The relavent index on M_TBL2 is on these columns -

Code:

COLNAME                       
-------------------------     
FK_COGFK_S_NSB_CUS 
FK_COGFK_S_TELCOTE   
FK_COGCOG_SEQ_ID     


Please let me know if any additional information is needed[/quote]
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Nov 04, 2008 2:26 am    Post subject:
Reply with quote

Hello,

For a first attempt, i'd make these
Code:
   AND     A.FK_S_NSB_CUSTOMCUS    = B.FK0COGFK_S_NSB_CUS
   AND     A.FK_S_TELCOTELCO_ID    = B.FK0COGFK_S_TELCOTE
   AND     A.COG_SEQ_ID            = B.FK0COGCOG_SEQ_ID
the first 3 conditions in the where.

How many rows will probably match on those 3 columns?

How many rows will probably be returned with the full select?
Back to top
View user's profile Send private message
manoopatil

New User


Joined: 07 Dec 2003
Posts: 56
Location: Montreal

PostPosted: Tue Nov 04, 2008 3:09 am    Post subject: Reply to: ORDER BY clause on SELECT columns
Reply with quote

Hi Dick,

Unfortunately I dont have access to the actual tables. I have access only to the catalogue table.

The total select is expected to return - 100,000 records.

The size of Table A and Table B is 3 millions records each.

Just to add I am more keen to understand if adding the A.StartDate and B.StartDate to the Select clause will improve the performance?

Please let me know if you have any suggestions

Thanks
Manohar
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Nov 04, 2008 3:57 am    Post subject:
Reply with quote

Hello,

Quote:
Just to add I am more keen to understand if adding the A.StartDate and B.StartDate to the Select clause will improve the performance?
They are already in the "where" icon_confused.gif Adding them to the list of columns return should not be a performance enhancer.

I would still try a test with the 3 key columns as the first 3 columns in the "where" - in the same order as they are in the key.

You might also talk with your dba to run an EXPLAIN for your process.

Another possibility is creating a new index that contains the first 6 columns in the where. Again, this is something to be taken up with your dba and dependng on the activity on those tables and how often this problem select must be run, adding a new index may or may not be a good solution.
Back to top
View user's profile Send private message
ksk

Active User


Joined: 08 Jun 2006
Posts: 356
Location: New York

PostPosted: Tue Nov 04, 2008 10:50 am    Post subject:
Reply with quote

Manohar,

Can you change the query? If so, execute the query without ORDER BY clause and then sort this file using SORT utility on the specified columns. But in this case you can't sort the file on the dates which were not specified in SELECT clause.

We had also same problem with ORDER BY clause and our DBA sugeested to seperate the the sort part and took significanlty less time. But before proceeding, you too have to talk with your DBA.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Sorting on text - but in non-alphabet... Roy Ware SYNCSORT 5 Wed Aug 23, 2017 9:15 pm
No new posts Can we limit length in concatenation ... balaji81_k DB2 7 Tue Aug 22, 2017 2:50 am
No new posts Select numeric portion from CHAR data... balaji81_k DB2 6 Sat Aug 19, 2017 1:51 am
No new posts SQL - select data available in index Nileshkul DB2 3 Mon Jun 26, 2017 1:30 am
No new posts unload data from table with lob columns farhad_evan DB2 1 Sat Apr 22, 2017 1:32 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us