-1

Consider my DB2 query as below.

Select something from some tables,
(select something from some tables where bhla=bhla
Unionall
select something from some tables where bhla=bhla
union
select something from some tables where bhla=bhla) x 
where bhla=bhla

and it looks like

select x.something, y.somethingelse from table y,
(select something from some tables where bhla=bhla
    Unionall
    select something from some tables where bhla=bhla
    union
    select something from some tables where bhla=bhla) x 
where bhla=bhla

My query is to optimise the above query(better way of use the query). please provide some inputs on indexes and how to use it. I need to optimise the above query so please pour some suggesstion.

Can you guyz suggest hw to recode the below where clause

where

(ID.EXCH_RT <> 0.000000 AND ID.EXCH_RT IS NOT NULL)  
AND I.ACT_LOC_TS BETWEEN ( :Param_290_From_Date CONCAT ' 00:00:00.00000')  AND ( :Param_300_To_Date CONCAT ' 23:59:59.99999')
A Programmer
  • 368
  • 4
  • 7
  • 26
  • 1
    Optimization can't be done on something this general. Please describe each table (preferably with sample data). How many rows does each table have? This is essential to know. Also, please describe in English what this query is supposed to do. – dan1111 Nov 22 '12 at 08:45

1 Answers1

3

It is not possible to optimize with the information given.

Normally people are just referred to How to Ask a Question when they ask a question that isn't specific enough. However, I see a lot of this specific case: asking for help with optimizing an SQL query that is not well-defined. So I wanted to address what is wrong here in more detail.

Why can't this be optimized?

  1. We don't know the task. Optimization is something you do for a task, not a piece of code. Your goal is to get the task done as efficiently as possible. The code you have posted may or may not be the best way of doing that. If we only have the code, and don't know what the code is for, we have no way of assessing the appropriateness of your code.

  2. We don't know the table structure. Optimization for SQL very much depends on the structure of the tables in question. What columns are involved, and what datatypes? How do the tables relate to each other? In this case, we don't even know how many tables are involved, because some of the queries act on "some tables".

  3. We don't know the data. How big is each table? Consider the example query below. Is it optimized? The answer is, that all depends on the data. If table1 is small and table2 is huge, that would be a good way to optimize the query. However, if table1 is huge and table2 is small, that would be a very poor query design that wastes lots of time. There is no way to tell the best design without knowing anything about the data.

  4. It depends on what database you are using (Thanks WarrenT). In this case, you have specified DB2, which is a good start compared to the many questions that just state "SQL". However, it would still be helpful to know which platform and version you are using. This makes a significant difference in what features are available.

Example query:

with complicated as (
    select id, 
        char(foo) || case when bar = 1234 then 'a' else 'c' end || 'baz'
             as complicated_field
        from table1
)
select * from table2
    join complicated on 
        complicated.id = table2.id and
        complicated.complicated_field = table2.some_field

I hope this is helpful. We would be glad to help further if you provide more information.

Community
  • 1
  • 1
dan1111
  • 6,576
  • 2
  • 18
  • 29
  • 1
    Also it can be important to specify what platform you are using: z, i, or LUW. – WarrenT Nov 22 '12 at 17:25
  • Can you suggest hw to rewrite the below where clause `(ID.EXCH_RT <> 0.000000 AND ID.EXCH_RT IS NOT NULL) AND I.ACT_LOC_TS BETWEEN ( :Param_290_From_Date CONCAT ' 00:00:00.00000') AND ( :Param_300_To_Date CONCAT ' 23:59:59.99999') ` – A Programmer Nov 23 '12 at 06:35
  • 1
    @javaProgrammer, we don't have enough context to do anything with that, for the same reasons I stated in my answer. We want to help, but we can't give a good answer unless you do the work of writing up a good question. – dan1111 Nov 23 '12 at 08:48