Results 1 to 15 of 15
  1. #1
    Join Date
    Oct 2004
    Posts
    105

    Unanswered: workingspeed of subselects in oracle

    Hi I have a problem
    i have this programme which so far gets its data out of a access database
    now the selects contain lots of subselects, IN and so on, so if there is a big amout of data in the database the programme works really slow
    I now need to put the data in a oracle database
    now my question is will that change the working speed of the programme or does the problem with subselects.. exits in oracle too?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I think you will find that Oracle is a rather more sophisticated DBMS than Access, and what Access finds hard Oracle finds easy.

    You will need to analyze the tables to enable Oracle's Cost-Based Optimizer to work properly,e.g.

    SQL> exec dbms_stats.gather_schema_stats ('MY_SCHEMA');

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    also, you would probably want to create indexes in Oracle based on the
    sub-select queries.

    ie:
    select * from table_a
    where cola IN
    (select cola from table_b
    where colb = 'XYZ')

    you might want to create an index on table_b.colb
    create the indexes, then analyze the tables/indexes as mentioned above

    you get the idea
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by The_Duck
    also, you would probably want to create indexes in Oracle based on the sub-select queries.
    Good point: does Access not have indexes?

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    I have no idea. A google search turned up nothing.
    Access has typically been known to not be able to handle the larger sized
    databases (not sure what size that starts at).

    My guess is that you cannot create indexes.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    There are indexes in Access, unique and not unique. And I believe The_Duck's suggestion is also relevant for Access DBs.

    However, you're right The_Duck, I also heard that Access performs very badly when data is becoming too big. I have not performed tests by myself, but I heard it was reasonable to use Access for data up to 200 Mb. Now, of course, it depends of the notion of "reasonable".

    RBARAER
    Last edited by RBARAER; 11-05-04 at 11:59.

  7. #7
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    To be more precise...

    As far as I know, there is no 'CREATE INDEX' SQL command in Access, which may be the reason why your google search gave nothing, The_Duck. However, you can interactively create "keys", unique or not unique, on one or more columns, but I don't know how they are actually managed and which real indexes are created : there is not much you can tell Access about the indexes you want to create.

    RBARAER

  8. #8
    Join Date
    Dec 2003
    Posts
    1,074
    My experience with subselects in Access is that they've always performed reaaly *slow*. Could never find a way around it.
    -cf

  9. #9
    Join Date
    Jul 2003
    Posts
    2,296
    interesting stuff.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  10. #10
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    You can issue SQL DDL with Access, IIRC you would need to look up the MSJET SQL syntax.

    Code:
    CREATE TABLE MH_GEO (
          UID        INTEGER CONSTRAINT MH_GEO_PK PRIMARY KEY,
          XMIN       DOUBLE,
          XMAX       DOUBLE,
          YMIN       DOUBLE,
          YMAX       DOUBLE,
          SHAPETYPE  BYTE,
          GEOMETRY   LONGBINARY
          );
    CREATE INDEX MH_GEO_YMAX_IDX ON MH_GEO( YMAX );
    I've never managed to find any way of running multiple statements, and end up firing them one by one :-(

    Performance wise, I've read it struggles at over 100,000 rows in any table, I haven't found this at all.

    For a specific application of mine which needs a SQL Db for a few (large) tables I have ran speed comparisons on Oracle 8i and 9i, Advantage, MSDE, MySQL and have found that the Access db with MSJET outperforms them all.

    One of the tables contains 3.1 million rows and there is a total of 1.5Gb of data.

    This quite surprised me. I am guessing that Access not being a 'proper' db it doesn't have the overhead of managing rollback, contention, consistency etc. So in this single user, read only application with a few large tables it flies. I imagine the second I connected another user to the same DB or tried to update it, it would crawl.

    Cheers
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  11. #11
    Join Date
    Oct 2004
    Posts
    105
    Hi thanks for all your help
    im all new to oracle though so i still have some questiones
    what does the indexing of tables and then analyzing it do to make it faster?
    does it tell me where the queries are slow or does it actually help to make it faster and if so how does that work?
    Thanks a lot

  12. #12
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    http://www.billmagee.co.uk/oracle/sqltune/index.html

    Explains the role of the optimiser and index statistics.

    Cheers
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  13. #13
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Hello,

    Thanks billm for these interesting remarks on Access. It may be useful someday.

    Regards,

    RBARAER

  14. #14
    Join Date
    Oct 2004
    Posts
    105
    thanks for the link ill have a look on that

  15. #15
    Join Date
    Dec 2003
    Posts
    1,074
    Another tip with Access, if you write a query with the subselect embedded in the query it'll take much longer:

    select mainquery.division, mainquery.employee, mainquery.salary
    from table mainquery
    where salary = (select max(subselect.salary)
    from table subselect
    where subselect.division = mainquery.division)

    than if you write the subselect as a standalone query, and include it within the other query

    <u>qryBuildSub</u>
    select max(salary)
    from table
    where division

    <u>fullQuery</u>
    select table.division, table.employee, table.salary
    from table, qryBuildSub
    where table.division = qryBuildSub.division and
    table.salary = qryBuildSub.salary

    -cf

Posting Permissions

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