Can this be optimized? Newbie question

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

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_REQUIS
 ITION_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_sta
mp 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'
)

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_REQUIS
 ITION_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_sta
mp 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'
> )

I'm using this query (and up to 20 similar ones combined with 'UNION')
in a Crystal Report. The report may or may not be able to remove dupes,
but I doubt it (I'm new at Crystal Reports). I'm using DBArtisan to
design the query. The database is an undocumented nightmare with few
indexes. I know it's hideous, but I was hoping I was missing something
really obvious....:)

Bill,

There is nothing wrong with the query, except that maybe the DISTINCT is
not necessary and could save some time if you dropped it.

The key of this query's performance is in the available indexes (and
maybe the hardware configuration). If no usuable indexes are available
and the tables are large then this query will run like a dog. You should
really turn to the DBA who can put the proper indexes in place...

Gert-Jan


"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
>
> 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_REQUIS
 ITION_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_sta
mp 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'
> )

T_MULTILIST.description may be wide. Sorting wide result sets may be
slow. try removing duplicates before joining with  T_MULTILIST. Look up
article "The Less SQL Server Sorts, the Faster It Responds"

wgblackmon@yahoo.com (wgblackmon@yahoo.com)  writes:
> 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!

My newsserver had an outage, so the reply I posted originally got lost.
What I said in that post was not that fantastic:

Without know the tables and indexes it's about impossible to give
suggestions. If you post the CREATE TABLE and CREATE INDEX statements
(don't forget constraints!), as well some indication of table sizes,
we might be able to give some tips.

Even better if you can run:

SET STATISTICS PROFILE ON
go
-- query goes here
go
SET STATISTICS PROFILE OFF
go

and post the output. (Preferably in an attachment, as the output is far too
wide for news article).

However, a few minutes later one more thing occurred to me, and that was
when I discovered that the newsserver was sick.

Anyway, what you could try is to run DBCC DBREINDEX on all involved tables.
While it is not going to cause the query to run with the speed of light
all of a sudden, you could see an improvement with 20-30% if there is
serious fragmentation of the tables.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx