Ninja's_RGR'us (12/2/2011)

---

brian 20008 (12/2/2011)

---

No doubt it'll be easy to Tune... just had to get to a point in which tuning could be...

No doubt it'll be easy to Tune... just had to get to a point in which tuning could be done.

Grant Fritchey (12/2/2011)

---

First suggestion I have, bump up your cost threshold for parallelism to 40 or 50. You're getting parallel execution on this plan with an estimated cost of 25,...

Actual Execution Plan Attached

I can do that... gimmie a bit to re-run it.

Last execution took just over 10 minutes to complete and return 106 rows

Actual Execution Plan

<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.1" Build="10.0.2746.0"><BatchSequence><Batch><Statements><StmtSimple StatementText="select [Status] = esc.statusname, [Parent] = null, [Criteria] = ' = ''' + rtrim(esc.StatusCode) + '''', [Count] = sum(isnull(rq.[SubItemCount], 0)), [PickList] = rtrim(ESC.StatusDesc) + ' - [ ' +...

I don't usually work with the XML execution plans so pardon me if it's not a clean post...

Estimated Plan...

<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.1" Build="10.0.2746.0"><BatchSequence><Batch><Statements><StmtSimple StatementText="declare @ReaderId int declare @FilterAssigned bit set @readerId = 100; "...

Agreed, it just seems to be with the overall execution.

The indivudual queries (prior and post the union statement) run quick. It's only when everything is run and the union...

I'll post them up after hours. Our doctors would kill me if I hang the production server that long during business hours.

My development server doesn't have as large a...

As for the queries, those are in my original post

I'll have to post the text execution plan later as it'll kill our production server for 5 minutes or so while the query executes.

Sorry, both Union and Union All take 5 plus minutes to run.

Went down that road as well, and it had the same results with Union All

Great, thanks for the feedback on it. I'll be meeting with my team about it in two weeks myself.

Interesting idea. I'm not certain how this custom app would work against a view instead of a table. Basic CRUD works with views, however I'd have to test...