Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2003
    Posts
    6

    Unanswered: Query with different performances? What's wrong

    The following query perfoms on my computer within 1 second and on other computers sometimes more than 2 minutes. Does anybody have a suggestion?


    SELECT COUNT (*)
    FROM gls_locatie
    WHERE (gls_locatie.eigenaar IN (SELECT DATA
    FROM gls_tabellen
    WHERE code = 'EIGENAAR'))
    AND (gls_locatie.TYPE <> 'W' OR gls_locatie.TYPE IS NULL)
    AND ( (na1987 <> 'N' OR na1987 IS NULL)
    AND ( gls_locatie.jaar_oo = '2002'
    OR gls_locatie.jaar_no = '2002'
    OR gls_locatie.jaar_eut = '2002'
    OR gls_locatie.jaar_se = '2002'
    OR TO_CHAR (gls_locatie.san_eind, 'YYYY') = '2002'
    OR (gls_locatie.loc_code IN (
    SELECT loc_code
    FROM gls_taak
    WHERE ( taak_code IN
    ('J01', 'J02', 'J03', 'J04', 'J05')
    AND TO_CHAR (begindatum, 'YYYY') = '2002'
    )
    OR ( taak_code IN ('F10', 'F20', 'F30')
    AND TO_CHAR (dat_tech_a, 'YYYY') = '2002'
    )
    OR (taak_id IN (
    SELECT taak_id
    FROM gls_vordering
    WHERE (TO_CHAR (vord_dat, 'YYYY') = '2002')
    AND (NOT (fin_type IN ('M', 'S', 'V'))))
    )
    OR (taak_id IN (
    SELECT taak_id
    FROM gls_kosten
    WHERE (TO_CHAR (fact_ontv, 'YYYY') =
    '2002'
    ))
    )
    )
    )
    )
    OR (gls_locatie.loc_code IN (
    SELECT loc_code
    FROM gls_taak
    WHERE ( taak_code = 'M00'
    AND (TO_CHAR (begindatum, 'YYYY') <= '2002')
    ))
    )
    )

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Query with different performances? What's wrong

    What do you mean by "on my computer" and "on other computers"?

    Do you mean different databases, or same database but different clients, or what?

    If it is different databases, then there could be many reasons: different data, different indexes, different system parameters, ...

  3. #3
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    if you really mean different client on the same database, check the TNS setting. It's known that improper setup for tns might cause a client to search long before it finds the database.

    Edwin

  4. #4
    Join Date
    Sep 2003
    Location
    Assen, Nederland
    Posts
    55
    Assuming that "my computer" is the server--phew boy you need to run the autotrace, explain plan to see why it may be taking 1 minute.(well if just fts's are takingplace then you ought to take al ook at hte indexes, but anyways maybe 1 min is hte best you can get..bottom line you know your data...:-)

    and that the other computer is the client...We'll actually it's hard to say what you want...

    Try to pkg it all and place it on the server nad let the cleints execute the proc with small network packets..

    Cheers...
    Hope that helped...
    Visit My Website : http://www.oraflame.com
    _____________________________
    Tarry Singh

    OCP DBA 8i
    Currently: SQL Server DBA 7,2000
    Oracle, PHP Programmer

  5. #5
    Join Date
    Sep 2003
    Location
    Assen, Nederland
    Posts
    55
    Code:
    
    
    SELECT COUNT (*)
      FROM gls_locatie
     WHERE (gls_locatie.eigenaar IN (SELECT DATA
                                       FROM gls_tabellen
                                      WHERE code = 'EIGENAAR'))
       AND (gls_locatie.TYPE <> 'W' OR gls_locatie.TYPE IS NULL)
       AND (       (na1987 <> 'N' OR na1987 IS NULL)
               AND (   gls_locatie.jaar_oo = '2002'
                    OR gls_locatie.jaar_no = '2002'
                    OR gls_locatie.jaar_eut = '2002'
                    OR gls_locatie.jaar_se = '2002'
                    OR TO_CHAR (gls_locatie.san_eind, 'YYYY') = '2002'
                    OR (gls_locatie.loc_code IN (
                           SELECT loc_code
                             FROM gls_taak
                            WHERE (    taak_code IN
                                              ('J01', 'J02', 'J03', 'J04', 'J05')
                                   AND TO_CHAR (begindatum, 'YYYY') = '2002'
                                  )
                               OR (    taak_code IN ('F10', 'F20', 'F30')
                                   AND TO_CHAR (dat_tech_a, 'YYYY') = '2002'
                                  )
                               OR (taak_id IN (
                                      SELECT taak_id
                                        FROM gls_vordering
                                       WHERE (TO_CHAR (vord_dat, 'YYYY') = '2002'
                                             )
                                         AND (NOT (fin_type IN ('M', 'S', 'V'))))
                                  )
                               OR (taak_id IN (
                                       SELECT taak_id
                                         FROM gls_kosten
                                        WHERE (TO_CHAR (fact_ontv, 'YYYY') =
                                                                            '2002'
                                              ))
                                  ))
                       )
                   )
            OR (gls_locatie.loc_code IN (
                   SELECT loc_code
                     FROM gls_taak
                    WHERE (    taak_code = 'M00'
                           AND (TO_CHAR (begindatum, 'YYYY') <= '2002')
                          ))
               )
           )

    Check out hte vb code so nexttime you can paste it between it for better readability. Here I've formatted it for you for others to read just in case you need some performance advise... succes
    Hope that helped...
    Visit My Website : http://www.oraflame.com
    _____________________________
    Tarry Singh

    OCP DBA 8i
    Currently: SQL Server DBA 7,2000
    Oracle, PHP Programmer

  6. #6
    Join Date
    Sep 2003
    Posts
    6

    Re: Query with different performances? What's wrong

    The database which performs badly is not the same database as where I do my testing. But the database is a exact copy except for the data. Indexes, grants, constraints are exactly the same. My database even got more data.

    An other difference is that I work with Oracle 9i and the other database is in Oracle 8i. Where to search for the solution? Is it necessary to tune the sql select (I think the SQL query is not so difficult and I doubt if it causes the problem) or is it more likely that the settings of the database causes the problem. If that's so which setting can cause such a problem?

    Thx!

    Originally posted by NSM
    The following query perfoms on my computer within 1 second and on other computers sometimes more than 2 minutes. Does anybody have a suggestion?


    SELECT COUNT (*)
    FROM gls_locatie
    WHERE (gls_locatie.eigenaar IN (SELECT DATA
    FROM gls_tabellen
    WHERE code = 'EIGENAAR'))
    AND (gls_locatie.TYPE <> 'W' OR gls_locatie.TYPE IS NULL)
    AND ( (na1987 <> 'N' OR na1987 IS NULL)
    AND ( gls_locatie.jaar_oo = '2002'
    OR gls_locatie.jaar_no = '2002'
    OR gls_locatie.jaar_eut = '2002'
    OR gls_locatie.jaar_se = '2002'
    OR TO_CHAR (gls_locatie.san_eind, 'YYYY') = '2002'
    OR (gls_locatie.loc_code IN (
    SELECT loc_code
    FROM gls_taak
    WHERE ( taak_code IN
    ('J01', 'J02', 'J03', 'J04', 'J05')
    AND TO_CHAR (begindatum, 'YYYY') = '2002'
    )
    OR ( taak_code IN ('F10', 'F20', 'F30')
    AND TO_CHAR (dat_tech_a, 'YYYY') = '2002'
    )
    OR (taak_id IN (
    SELECT taak_id
    FROM gls_vordering
    WHERE (TO_CHAR (vord_dat, 'YYYY') = '2002')
    AND (NOT (fin_type IN ('M', 'S', 'V'))))
    )
    OR (taak_id IN (
    SELECT taak_id
    FROM gls_kosten
    WHERE (TO_CHAR (fact_ontv, 'YYYY') =
    '2002'
    ))
    )
    )
    )
    )
    OR (gls_locatie.loc_code IN (
    SELECT loc_code
    FROM gls_taak
    WHERE ( taak_code = 'M00'
    AND (TO_CHAR (begindatum, 'YYYY') <= '2002')
    ))
    )
    )

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Query with different performances? What's wrong

    1) Make sure you have up to date statistics on both databases.

    2) Try using EXPLAIN PLAN or SQL_TRACE/TKPROF on both to see how the query plans differ.

  8. #8
    Join Date
    Sep 2003
    Location
    Assen, Nederland
    Posts
    55
    Except fot the data, is it too much , too less ,skewed . All those things are to be investigated first. There isn't much of a version issue as your statement is kind of rather generic. Well then the tip is to use some function ot avoid the too many and's an or's...

    Check the SQL Reference for that matter, also handy too take a look into the perf tuning guide...

    Cheers...
    Hope that helped...
    Visit My Website : http://www.oraflame.com
    _____________________________
    Tarry Singh

    OCP DBA 8i
    Currently: SQL Server DBA 7,2000
    Oracle, PHP Programmer

  9. #9
    Join Date
    Apr 2003
    Location
    Los Alamos/Santa Fe, NM
    Posts
    69

    weird..same problem

    I have the smae problem. My query runs in 30 secs on 8.1.7 but 20 min on 9.2.0. I wonder if it has something to do with the database? are you running the query direct in the db or is there a java driver (any driver) connecting the code to the db?

    Michellea
    Michellea Southern-David

  10. #10
    Join Date
    Apr 2003
    Location
    Los Alamos/Santa Fe, NM
    Posts
    69

    more..

    oh I need to add both 8.1.7 and 9.2.0 were tested on the same machine with the same init.ora file and using the same code. The server is a E450 running Solaris 5.8
    Michellea Southern-David

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •