Hi there..

I have an SP that takes a very long time to complete:

<snip>
CREATE PROCEDURE dbo.USP_RETURNDATA_NS_ORDERRETURN
@RETURNDATA_ID AS INT
AS
SET NOCOUNT ON
SELECT
VW_CUSTOMER_ADDRESS.DISTRIBUTOR_CODE ,
VW_CUSTOMER_ADDRESS.DISTRIBUTOR_CUSTOMERID ,

VW_CUSTOMER_ADDRESS.SIG_REQD ,
VW_CUSTOMER_ADDRESS.CUSTOMERTYPE ,
VW_CUSTOMER_ADDRESS.CREDIT_HOLD_STATUS ,
VW_CUSTOMER_ADDRESS.VENDORID ,

VW_CUSTOMER_ADDRESS.ROUTE_CODE ,
VW_CUSTOMER_ADDRESS.SEQ_NUMBER ,

VW_CUSTOMER_ADDRESS.DELIVERYDROP,
VW_CUSTOMER_ADDRESS.DELIVERYCOMMENT,

VW_CUSTOMER_ADDRESS.CUSTOMER_NAME,
VW_CUSTOMER_ADDRESS.STREET,
VW_CUSTOMER_ADDRESS.STREET_1,
VW_CUSTOMER_ADDRESS.SUBURB,
VW_CUSTOMER_ADDRESS.STATE,
VW_CUSTOMER_ADDRESS.POSTCODE,
VW_CUSTOMER_ADDRESS.COUNTRY,

ORDERHEADER.DISTRIBUTOR_ORDER_ID ,
CONVERT(VARCHAR(15), @RETURNDATA_ID) +'-'+
CONVERT(VARCHAR(15), ORDERHEADER.DISTRIBUTOR_ORDER_ID) AS
RESPONSE_CODE,

ORDERTYPE.CODE AS ORDERTYPE ,

UV_INVOICE_INVOICE_NUMBER.INVOICE_NUMBER,

VW_ORDERDETAIL_PRODUCT.ORDERDETAIL_ID,
VW_ORDERDETAIL_PRODUCT.ORDERDETAIL_NUM,
VW_ORDERDETAIL_PRODUCT.ORDERDETAIL_QUANTITY,
VW_ORDERDETAIL_PRODUCT.PICKED_QUANTITY,
LEFT(REPLACE(CONVERT(CHAR(15),
VW_ORDERDETAIL_PRODUCT.REQD_DELIVERY_DATE, 110), '-', ''), 8) AS
REQD_DELIVERY_DATE,
VW_ORDERDETAIL_PRODUCT.TRANSACTION_CODE ,
VW_ORDERDETAIL_PRODUCT.SUPPRESS_PRICE ,
VW_ORDERDETAIL_PRODUCT.CUSTOMER_GST ,
LEFT(REPLACE(CONVERT(CHAR(15),
VW_ORDERDETAIL_PRODUCT.PICKED_DATE, 110), '-', ''), 8) AS PICKED_DATE,

VW_ORDERDETAIL_PRODUCT.WAREHOUSE_CODE ,

VW_ORDERDETAIL_PRODUCT.ISSUE_NUMBER ,
VW_ORDERDETAIL_PRODUCT.ISSUE_TEXT ,
VW_ORDERDETAIL_PRODUCT.RETAIL_PRICE,
VW_ORDERDETAIL_PRODUCT.WHOLESALE_PRICE,
VW_ORDERDETAIL_PRODUCT.EXPECTED_RECALL,
VW_ORDERDETAIL_PRODUCT.FORECAST_SALE,
VW_ORDERDETAIL_PRODUCT.RETURN_STYLE,
LEFT(REPLACE(CONVERT(CHAR(15),
VW_ORDERDETAIL_PRODUCT.ONSALEDATE, 110), '-', ''), 8) AS ONSALEDATE,
VW_ORDERDETAIL_PRODUCT.KIND_OF_OBJECT ,
VW_ORDERDETAIL_PRODUCT.VARIANCE_TO_PACK ,

VW_ORDERDETAIL_PRODUCT.OBJECTID ,
VW_ORDERDETAIL_PRODUCT.TITLE ,
VW_ORDERDETAIL_PRODUCT.PUBLISHERID ,
VW_ORDERDETAIL_PRODUCT.PUBLISHERNAME ,
VW_ORDERDETAIL_PRODUCT.CHARGE_GST ,
VW_ORDERDETAIL_PRODUCT.RESTRICTED_TITLE ,
VW_ORDERDETAIL_PRODUCT.TITLE_TRACK_CATEG ,

CONNOTE.ID AS CON_NOTE_NUMBER,

COMMISSION_RESULT_PARCELS.PARCEL_NUMBER ,

VW_ORDERDETAIL_PACKAGING.KEY_QUANTITY,
VW_ORDERDETAIL_PACKAGING.BULK_QUANTITY

FROM VW_CUSTOMER_ADDRESS
INNER JOIN ORDERHEADER
ON ORDERHEADER.CUSTOMER_ID = VW_CUSTOMER_ADDRESS.CUSTOMER_ID
INNER JOIN ORDERTYPE
ON ORDERHEADER.ORDERTYPE_ID = ORDERTYPE.ID
INNER JOIN VW_ORDERDETAIL_PRODUCT
ON VW_ORDERDETAIL_PRODUCT.ORDERHEADER_ID = ORDERHEADER.ID
INNER JOIN UV_INVOICE_INVOICE_NUMBER
ON UV_INVOICE_INVOICE_NUMBER.CUSTOMER_ID =
VW_CUSTOMER_ADDRESS.CUSTOMER_ID
INNER JOIN CONNOTE
ON CONNOTE.CONSIGNMENT_ID =
VW_ORDERDETAIL_PRODUCT.CONSIGNMENT_ID
AND CONNOTE.CUSTOMER_ID = VW_CUSTOMER_ADDRESS.CUSTOMER_ID
INNER JOIN COMMISSION_RESULT_PARCELS
ON COMMISSION_RESULT_PARCELS.COMMISSION_ID =
VW_ORDERDETAIL_PRODUCT.COMMISSION_ID
AND COMMISSION_RESULT_PARCELS.INVOICE_ID =
UV_INVOICE_INVOICE_NUMBER.ID
INNER JOIN VW_ORDERDETAIL_PACKAGING
ON VW_ORDERDETAIL_PACKAGING.ORDERDETAIL_ID =
VW_ORDERDETAIL_PRODUCT.ORDERDETAIL_ID
WHERE VW_CUSTOMER_ADDRESS.DISTRIBUTOR_ID = 1
AND VW_ORDERDETAIL_PRODUCT.RETURNDATA_ID = @RETURNDATA_ID

ORDER BY VW_CUSTOMER_ADDRESS.DISTRIBUTOR_CODE,
VW_CUSTOMER_ADDRESS.DISTRIBUTOR_CUSTOMERID,
UV_INVOICE_INVOICE_NUMBER.INVOICE_NUMBER,
VW_ORDERDETAIL_PRODUCT.ORDERDETAIL_ID

FOR XML AUTO--, XMLDATA

GO
</snip>

if I run it as is, it takes 15 minutes to run. However, if i just run
the content in QA, it takes < 2 seconds.

a few things I have alrealy checked:
* there are no deadlocks. sp_who2 just shows it running.
* in Profiler, there is no statement running between the StmtStarting
and StmtEnding (or whatever it is) events.
* Q293177 does not apply
* this is on my workstation - 1 CPU, no HT, SQL Dev Edtn. But the
problem occurs on the prod box too... I got the database from a backup
off the prod box (where the database was created and populated), 2xHT
Xeon w/- 3gig ram; the times are similar.
* I tried using WITH RECOMPILE to no effect. I've even tried munging
up the SP to try to make SQL Server recompile plans with no luck.
Anyone have any ideas?

Thanks,
Dan.

I'm running them both from QA, as in
exec dbo.USP_RETURNDATA_NS_ORDERRETURN 9
or the content as appropriate (leaving it as is and declaring
@RETURNDATA_ID at the top)

I need to have the FOR XML AUTO with the ORDER BY, as I am returning
data to Biztalk. If you don't have the right order of things, the XML
comes out in funny ways. But executing it either in QA or in Biztalk
takes the same time. Biztalk has very little to do with the execution
of this though, it just calls the SP and streams out the XML

I don't have any cursors or temp tables anywhere in this execution...
Oh and BTW, removing the FOR XML AUTO has no effect on the time inside
the SP.

select * from VW_ORDERDETAIL_PRODUCT where RETURNDATA_ID = 9 returns
about 650 rows, so it's not like it's too big either...

[quoted text, click to view]

Deadlocks are not the correct term. You mean blocking.

Where are you running it from that it's taking so long? From an
application? What is the difference between the execution plan from QA
and the app?

It's a very large query with a lot of tables and only 1 filter:
VW_ORDERDETAIL_PRODUCT.RETURNDATA_ID = @RETURNDATA_ID

How many rows are coming back to the application? Where are these rows
going? Data structures, a grid control, etc. Is it the duration of the
query that is different or the CPU and execution plan.

If you're only seeing a difference in duration, it's likely because you
are using a server-side cursor and the application is taking a long time
to fetch through all the results. Grids can be very slow to load. If
this is the case, consider using a client-side cursor for this query.
The results will get pulled down quickly to the client and then the app
can process at a more leisurely rate. But it would still take 15
minutes, just wouldn't leave locks on the server.

Do you really need the ORDER BY in the query? Consider ditching all
ORDER BY clauses unless absolutely necessary. They just add unnecesssary
overhead for most applications that don't even require ordered data come
back from the database.

But something tells me that 15 minutes implies something else is amiss.


--
David Gugick
Imceda Software
www.imceda.com

[quoted text, click to view]

I don't udnerstand. You said in your original post:
"if I run it as is, it takes 15 minutes to run. However, if i just run
the content in QA, it takes < 2 seconds."

What did you mean by this? What does the execution plan look like? Is it
just a long duration or is the CPU high as well (use Profiler for this)?


--
David Gugick
Imceda Software
www.imceda.com

Hi David,

What i meant is that when I execute it as "exec
dbo.USP_RETURNDATA_NS_ORDERRETURN 9" it takes a loooong time, but when
i run the same logic as "SELECT blah FROM tables" it takes 2 sec.

After examining the query plans, it looks like the Query Planner has
had a major road accident in calculating the plan for the SP. In it,
some of the row counts inside the SP get up to >1000000, whereas when
the statments are executed separately the max row count is about 2000.
Remeber I am running exactly the same logic with same return and same
everything, except on one hand it is wrapped in an SP, where on the
other it is a SELECT statement.

My current theory is that if i can force a flush of the query plan
cache it might fix it...

sp_updatestats made it run in <3 seconds! yippee!

Thank you for your help David

[quoted text, click to view]

We probably should have tried that first. Updated statistics seem to
correct a lot of problems.

--
David Gugick
Imceda Software
www.imceda.com