Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Posts
    20

    Exclamation Unanswered: Urgent... time taking query need to run

    Hi all

    I need the help. First i need to explain my problem...

    If i m having a query like this

    select var1,var2 from table1_<date>, table2_<date> where var3 in (<any no of values>);

    here <date> need to be supplied dynamically. and there can be n number dates. so i need to supply the number of days. also in the where clause i can give any number of values, so i will supply it at the run time.
    this query runs on the live servers so it takes hours to run...

    now the problem comes, this all logic i can develop in the frontend like ASP...
    but for the queries those are taking so much time to run, i don't think it is recommened... what should i do???????????

    can i write a pl/sql program. which can handle this complexitiy.....????

    Plz help me.......
    Thanks.....

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Yes, you can write dynamic SQL in PL/SQL stored procedures. But whether your SQL statement is constructed in a stored procedure or ASP will make litle difference to your performance issues - though using stored procedures is preferable for lots of other reasons.

    For example:

    This stored procedure takes in any table name and returns the result set for "select * from " (that table):
    Code:
      1  create or replace procedure select_all
      2   ( p_tablename in varchar2
      3   , p_refcursor out sys_refcursor
      4   )
      5  is
      6    v_sql varchar2(1000);
      7  begin
      8    v_sql := 'select * from ' || p_tablename;
      9    open p_refcursor for v_sql;
     10* end;
    SQL> /
    
    Procedure created.
    That procedure could be called from ASP, but I'll demo it with SQL Plus:
    Code:
    SQL> -- declare a variable to receive the result set
    SQL> var rc refcursor
    SQL> -- Execute the procedure
    SQL> exec select_all('dept',:rc)
    
    PL/SQL procedure successfully completed.
    
    SQL> -- Process the result set locally
    SQL> print rc
    
        DEPTNO DNAME          LOC
    ---------- -------------- -------------
            10 ACCOUNTING     NEW YORK
            20 RESEARCH       DALLAS
            30 SALES          CHICAGO
            40 OPERATIONS     BOSTON

  3. #3
    Join Date
    Apr 2004
    Location
    USA
    Posts
    33

    Joins

    In the ex. you have given, where is the join between table1_<date> and table2_<date>?

    If you add more tables to this select without any join conditions, then the query will most certainly take a long time.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >what should i do???????????
    I suggest you hire somebody who know what they are doing.

    >can i write a pl/sql program. which can handle this complexitiy.....????
    I give up. Can you "handle this complexitiy"?

    If you have N tables in the FROM clause,
    you need at least N-1 conditions on the WHERE clause to avoid a cartesian join.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Apr 2004
    Location
    USA
    Posts
    33

    Unless

    Quote Originally Posted by sachin71280
    Hi all

    I need the help. First i need to explain my problem...

    If i m having a query like this

    select var1,var2 from table1_<date>, table2_<date> where var3 in (<any no of values>);

    here <date> need to be supplied dynamically. and there can be n number dates. so i need to supply the number of days. also in the where clause i can give any number of values, so i will supply it at the run time.
    this query runs on the live servers so it takes hours to run...

    now the problem comes, this all logic i can develop in the frontend like ASP...
    but for the queries those are taking so much time to run, i don't think it is recommened... what should i do???????????

    can i write a pl/sql program. which can handle this complexitiy.....????

    Plz help me.......
    Thanks.....

    Yes you will need N-1 joins, UNLESS the table1_, table2_ etc. have only one record each. Is that the case here?

  6. #6
    Join Date
    Jan 2004
    Location
    Singapore
    Posts
    89
    You know your data very well and Tony has given you the idea. Its perfect. Why dont you make use of it?
    Thanks and Regards,

    Praveen Pulikunnu

  7. #7
    Join Date
    Jan 2003
    Location
    Woking
    Posts
    107
    Use a TO_CHAR in your where clause
    nn

Posting Permissions

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