Slow SQL Query 1

[JForsythe]

Hi everyone!

This is my very first question(the applause is heard).

The following query is not processing or taking more than 30 minutes which is my limit. Is the nested SELECT the issue?

SELECT DISTINCT
IM2_InventoryItemWhseDetl."ItemNumber", IM2_InventoryItemWhseDetl."WhseCode", IM2_InventoryItemWhseDetl."ReorderPointQty", IM2_InventoryItemWhseDetl."QtyOnHand", IM2_InventoryItemWhseDetl."QtyOnPurchhaseOrder", IM2_InventoryItemWhseDetl."QtyOnSalesOrder", IM2_InventoryItemWhseDetl."QtyOnBackOrder", IM2_InventoryItemWhseDetl."AveCost", IM2_InventoryItemWhseDetl."QtyOnWO", IM2_InventoryItemWhseDetl."QtyRequiredForWO",
IM1_InventoryMasterfile."ItemDescription", IM1_InventoryMasterfile."ProductLine", IM1_InventoryMasterfile."ProductType", IM1_InventoryMasterfile."StdUM", IM1_InventoryMasterfile."PrimaryVendorDivision", IM1_InventoryMasterfile."PrimaryVendorNumber", IM1_InventoryMasterfile."InventoryCycle", IM1_InventoryMasterfile."AveCost",
IM0_ParameterRecord."IncludePOOrWOInQtyAvailable",
SY0_CompanyParameters."CompanyName"
FROM
"IM0_ParameterRecord" IM0_ParameterRecord, "SY0_CompanyParameters" SY0_CompanyParameters, { oj "IM2_InventoryItemWhseDetl" IM2_InventoryItemWhseDetl LEFT OUTER JOIN "IM1_InventoryMasterfile" IM1_InventoryMasterfile ON IM2_InventoryItemWhseDetl."ItemNumber" = IM1_InventoryMasterfile."ItemNumber"}
WHERE
IM2_InventoryItemWhseDetl.&quot;BinLocation&quot; <> IM0_ParameterRecord.&quot;IMApplicationCode&quot; AND IM2_InventoryItemWhseDetl.&quot;ItemNumber&quot; IN (SELECT a.ItemNumber FROM IM2_InventoryItemWhseDetl a WHERE a.WhseCode = '000')
ORDER BY
IM2_InventoryItemWhseDetl.&quot;ItemNumber&quot; ASC


Thank you

JForsythe

 

[synapsevampire]

First, you're best served to post database questions in a database forum, this is geared towards Crystal developers, a large portion of which aren't SQL experts.

The subquery does add overhead, as does using select distinct.

I would speak with your dba about the performance, it may be related to indexes, and their are numerous tools to help improve the performance of a database.

-k

 

[JForsythe]

I am sorry, my question should be more specific. This is the query as it appears in Show SQL Query in Crystal 8.0.1. I have also turned the option on for more server side processing.

Thanks,

JForsythe

 

[synapsevampire]

I've never seeen Crystal generate a subquery, are you certain that someone didn't manually update the query?

I tend to use ANSI 92 standards for this sort of thing, which would mean creating a derived table from the subquery and joining to it.

Not knowing the total rows, indexes, and what is actually in the record selection formula, it's very difficult to help resolve this for you.

If you run this SQL from a SQL Execution tool, does it take the same amount of time?

-k

 

[lyanch]

It has been a bit since I looked at SQL with joins created like this but.. it looks like you may have a couple tables without a join? If so.. that will definitely make the query slow.

Lisa

 

[JForsythe]

This query is an extension of an existing query in Best software MAS 90. My contribution was the nested SELECT. This report was intended to be professional recreation and a favor for a friends company. It is turning out to be more tricky than I thought.

As far as execution tools go, SQl Designer is not loaded on the server. Can I create a derived table in Crystal? The database type is unknown, not Oracle. Any more suggestions?

Thank you,

JForsythe

 

[lyanch]

Ah.. update the subselect to a correlated subselect.. will run much faster:

IM2_InventoryItemWhseDetl.&quot;ItemNumber&quot; IN (SELECT a.ItemNumber FROM IM2_InventoryItemWhseDetl a WHERE a.WhseCode = '000' and a.ItemNumber = IM2_InventoryItemWhseDetl.&quot;ItemNumber&quot


Lisa

 

[synapsevampire]

The whole thing would have to be rewritten to use a join, and I doubt that you can do that, so use Lisa's solution, she's right, currently your comparing against the whole table, as opposed to a better qualified subquery.

-k

 

[JForsythe]

Thank you Lisa!

The correlated subquery really sped things up.

I appreciate all the suggestions.

JForsythe