June 27, 2013 at 6:27 am
Dear All,
In the below query, if I remove two sub queries "SELECT COUNT('x')..." the query gets executed in seconds. But with these sub-queries, query is taking 7-8 minutes.
Is there any other way to rearrange this query so that load of sub-queries can be reduced!
Query:
SELECT TOP (1000)
jp.labor_type,
(SELECT
COUNT('x')
FROM
dbo.task_seek js(nolock)
WHERE
jp.task_id = js.task_id
AND js.status IN (1,7,8) AND js.worker_code = 'ADO'
) AS available,
(SELECT
COUNT('x')
FROM
dbo.work_order wo(nolock)
WHERE
wo.task_id = jp.task_id
AND wo.status NOT IN (5,36,0,6)
AND wo.sequence = 1 AND wo.worker_code = 'ADO'
) AS hired,
jp.revision,
jp.seeker_name
FROM
dbo.task_posting jp(nolock)
INNER JOIN dbo.task_distribution jpd(nolock) ON jp.task_id = jpd.task_id
June 27, 2013 at 6:36 am
Hi
It could be re-written like this:
SELECT TOP (1000)
jp.labor_type,
jp.revision,
jp.seeker_name,
available.availablecount,
hired.hiredcount
FROM
dbo.task_posting jp(nolock)
INNER JOIN dbo.task_distribution jpd(nolock) ON jp.task_id = jpd.task_id
LEFT JOIN
(SELECT
COUNT(*) availablecount
,task_id
FROM
dbo.task_seek js(nolock)
WHERE
js.status IN (1,7,8) AND js.worker_code = 'ADO'
GROUP BY
task_id
) AS available
ON jp.task_id = available.task_id
LEFT JOIN
(SELECT
COUNT(*) hiredcount
,task_id
FROM
dbo.work_order wo(nolock)
WHERE
wo.status NOT IN (5,36,0,6)
AND wo.sequence = 1 AND wo.worker_code = 'ADO'
GROUP BY
task_id
) AS hired
ON jp.task_id = hired.task_id
Also be careful of using the no-locks, your allowing dirty reads
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
June 27, 2013 at 7:01 am
Post the actual execution plan as a .sqlplan attachment.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 27, 2013 at 7:23 am
There could be multiple reasons to it,,
There is huge date in the work_order and task_seek table
index is missing or inappropriate
sol:put the result of these tables in temp tables without applying filter on taskid
then use those temp tables in your main query
Pramod
SQL Server DBA | MCSE SQL Server 2012/2014
in.linkedin.com/in/pramodsingla/
http://pramodsingla.wordpress.com/
June 30, 2013 at 11:50 pm
I tried one more thing in above suggestions, as pasted below.
It is not asking for missing indexes and performing much better.
SELECT
x.labor_type,
x.revision,
x.seeker_name,
x.availablecount,
x.hiredcount
FROM
(
SELECT TOP (1000)
jp.labor_type,
jp.revision,
jp.seeker_name,
available.availablecount,
hired.hiredcount
FROM
dbo.task_posting jp(nolock)
INNER JOIN dbo.task_distribution jpd(nolock) ON jp.task_id = jpd.task_id
LEFT JOIN
(SELECT
COUNT(*) availablecount
,task_id
FROM
dbo.task_seek js(nolock)
WHERE
js.status IN (1,7,8) AND js.worker_code = 'ADO'
GROUP BY
task_id
) AS available
ON jp.task_id = available.task_id
LEFT JOIN
(SELECT
COUNT(*) hiredcount
,task_id
FROM
dbo.work_order wo(nolock)
WHERE
wo.status NOT IN (5,36,0,6)
AND wo.sequence = 1 AND wo.worker_code = 'ADO'
GROUP BY
task_id
) AS hired
ON jp.task_id = hired.task_id
) x
July 1, 2013 at 3:21 am
T.Ashish (6/30/2013)
I tried one more thing in above suggestions, as pasted below.It is not asking for missing indexes and performing much better.
This is Andy's suggestion. You don't need the outer select - it doesn't do anything except make the query look more complicated than it is.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 1, 2013 at 3:56 am
Yes, I used Andy's suggestion and it was working fine. After that, Optimizer was suggesting some index. Instead of creating index, I put all the query inside another query.
Now it is not suggesting any indexes and performance is also improved. I know it is not authentic but I really don't know how optimizer is reading this.
July 1, 2013 at 4:01 am
T.Ashish (7/1/2013)
Yes, I used Andy's suggestion and it was working fine. After that, Optimizer was suggesting some index. Instead of creating index, I put all the query inside another query.Now it is not suggesting any indexes and performance is also improved. I know it is not authentic but I really don't know how optimizer is reading this.
That's interesting...can you post the actual execution plans (as .sqlplan attachments)? It's most unexpected!
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 1, 2013 at 4:09 am
Agreed, I would be interested in seeing that as well!
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
July 1, 2013 at 5:04 am
I have attached the execution plans as required.
Waiting for your comments...
July 1, 2013 at 5:35 am
T.Ashish (7/1/2013)
I have attached the execution plans as required.Waiting for your comments...
The query with the redundant outer select times out - SQL Server effectively gives up trying to find the best plan. I'd recommend you simplify the WHERE clause and also standardise your joins - you're using a mix of join types.
If anyone's interested, here's the WHERE clause from the first query of batch 17, which goes some way to explaining the timeout:
where jp . job_posting_id = jpd . job_posting_id
and jpd . supplier_code = @13
and ( ( jp . status in ( @14 , @15 , @16 ) )
or ( jp . status in ( @17 , @18 )
and jp . visibility_to_supplier_rule > = @19 ) )
and ( ( jpd . status in ( @20 , @21 ) )
or ( jpd . status = @22
and jpd . distribution_level = @23
and jp . visibility_to_supplier_rule > = @24 ) )
and ( jp . status not in ( @25 , @26 , @27 )
or ( jp . status = @28
and jpd . status in ( @29 , @30 )
and jp . closed_time > = getdate ( ) - 7 ) )
and ( ( jp . labor_type_id = @32
or jp . labor_type_id is null )
or @33 = @34
or exists (
select 'x'
from dbo . person_labor_type plt ( nolock )
where plt . labor_type_id = jp . labor_type_id
and plt . person_id = @35 ) )
and ( ( jp . service_type = @36
and jp . start_date < = @37 )
or ( jp . start_date < = @38
and jp . end_date > = @39 ) ) ) x
where x . status ! = @40
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply