SQL Query Problems???

aus_user · #1 (**permalink**) 02-03-04, 23:19

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.

Littlefoot · #2 (**permalink**) 02-04-04, 02:43

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!

andrewst · #3 (**permalink**) 02-04-04, 05:33

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: