|
|
Posted by Joe Weinstein on 03/30/06 02:11
wgblackmon@yahoo.com wrote:
> Hi,
> I'm running the following SQL to get values for 4 fields. It is
> unacceptably slow. I have no control over the structure of the
> database, field names, indexes etc. - what I'm given as far as DB
> design is all I'm going to get. If anyone could make any suggestions
> I'd really appreciate it!
>
> Thanks,
> Bill
Well, it sounds like you're pretty much screwed. How many rows
does the query return? Is it appreciably faster if you remove
the 'DISTINCT' and do you have the opportunity to detect
and ignore duplicates at the client? Can you even find out
what indexes are on the tables or get the query plan for this?
There may be other query criteria that you could drop, and
instead post-qualify rows in the client.
Hope this (or someone else smarter) helps,
Joe Weinstein at BEA Systems
> SELECT DISTINCT
> T_MULTILIST_GRADE.grade, T_MULTILIST.description, T_MULTILIST.code,
> T_RECEIVING_DETAIL.amount
> FROM T_MULTILIST, T_RECEIVING_DETAIL,
> T_MULTILIST_GRADE,T_REQUISITION,T_REQUISITION_DETAIL,T_ORDER,T_DEPOSITORY,
> T_RECEIVING
> WHERE
> (
> T_RECEIVING_DETAIL.invoice_number =T_RECEIVING.invoice_number
> AND T_RECEIVING_DETAIL.order_id =T_ORDER.id
> AND T_ORDER.depository_id =T_DEPOSITORY.id
> AND T_REQUISITION.id =T_ORDER.requisition_id
> AND T_REQUISITION_DETAIL.requisition_id =T_REQUISITION.id
> AND T_REQUISITION_DETAIL.multilist_code
> =T_MULTILIST_GRADE.multilist_code
> AND T_MULTILIST_GRADE.multilist_code =T_MULTILIST.code
>
> AND T_ORDER.requisition_time_stamp BETWEEN '05/31/2005' AND
> '06/01/2006'
> AND T_MULTILIST.expiration_year > '2005'
> AND T_MULTILIST.code IN ('0043','1043')
> AND T_DEPOSITORY.depository_type = 'PRIVATE'
> AND T_RECEIVING.status <> 'PAID'
> )
Navigation:
[Reply to this message]
|