Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Posts
    7

    Question Unanswered: SQL Query Problems???

    Hi Everyone,

    I've created CUSTOM SQL query on several tables, the only "CUSTOMISED" part is the WHERE CLAUSE.

    Goal: To get results that have received_date THREE MONTHS PRIOR to current date. This report runs every week in the repository.

    (This is running on Redbrick DB)

    Here it is:

    SELECT
    field1, field2...
    FROM
    [table1][table2]...
    WHERE
    ...
    Received_Date BETWEEN DateAdd(Month, -3, Current_Date) AND Current_Date ;


    I get ZERO results and query takes ages to run!!

    Please advice.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    If your query takes a very long time to run, I guess
    a) tables involved in the query are huge,
    b) those tables aren't properly indexed,
    c) WHERE clause doesn't join those tables the way it should.

    For example:
    Code:
    SELECT p.id, o.date
    FROM customer p, invoices o
    WHERE p.id = o.id
       AND o.date BETWEEN ADD_MONTHS(sysdate, -3) AND sysdate;
    Does your query contain something like "WHERE p.id = o.id"? Those are the columns that should be indexed ...

    I ran this query on my (Oracle) database; it fetched 15602 records in 15.66 seconds ... Execution plan looks like this:
    Code:
    call        count       cpu    elapsed     disk    query current        rows
    --------  -------  --------  --------- -------- -------- -------  ----------
    Parse           1      0.02       0.02        0        0       0           0
    Execute         1      0.00       0.00        0        0       0           0
    Fetch        1041      4.14      15.64     1178    56921       3       15602
    
    --------  -------  --------  --------- -------- -------- -------  ----------
    total        1043      4.16      15.66     1178    56921       3       15602
    
    Misses in library cache during parse: 1
    Optimizer hint: CHOOSE
    Parsing user id: 61  (AKONPOT)
    
    Rows     Execution Plan
    -------  ---------------------------------------------------
          0  SELECT STATEMENT   OPTIMIZER HINT: CHOOSE
      15602    SORT (ORDER BY)
      15602      MERGE JOIN
      55849        INDEX   OPTIMIZER HINT: ANALYZED (RANGE SCAN) OF 'PK_PO' (UNIQUE)
      15602        SORT (JOIN)
      64619          TABLE ACCESS   OPTIMIZER HINT: ANALYZED (FULL) OF 'INVOICES'
    P.S. Dear moderator gentlemen, is it possible to change the font used in "[ code ]" code? Fixed one, such as Courier, would be great!
    Last edited by Littlefoot; 02-04-04 at 03:48.

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    According to this link...

    http://www.dbforums.com/misc.php?action=bbcode#imgcode

    ... you should be able to specify a font like this:

    some text

    However, that doesn't seem to be working for me. Another option is PHP:

    PHP Code:
    Some more text 
    That does the trick, but uses some funky colours also!

  4. #4
    Join Date
    Jan 2004
    Posts
    7
    the query returns no records...??

    it not only runs slow but no records are fetched. Any ideas?

Posting Permissions

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