June 17, 2013 at 1:18 am
Dear All,
I have attached two text files, one with SQL query and another with test data and tables. This is from my local system.
On production server the queries are taking really long time. Right now we are using first query on prod server.
I don't have the execution plan from prod server, so it is not attached, but I will try to get it for you.
Any help will be appreciated.
Query pasted for quick reference:
select case when comp.activity_flag = 1 then 'Active' else 'Inactive' end as activity_flag,
comp.name,
comp.comp_code,
(select count(1)
from dbo.person (nolock)
where person.comp_code = comp.comp_code
and person.activity_flag = 1
and person.reg_flag = 0) as active_users,
(select count(1)
from dbo.person (nolock)
where person.comp_code = comp.comp_code
and person.activity_flag = 1
and person.reg_flag = 1) as new_users,
(select count(1)
from dbo.person (nolock)
where person.comp_code = comp.comp_code
and person.activity_flag = 0) as inactive_users,
(select count(1)
from dbo.worker (nolock)
where worker.owner_code = comp.comp_code) as worker,
case when
(selecttop (1) 1
fromdbo.os_contract (nolock)
whereos_contract.owner_code = comp.comp_code
andos_contract.msp_flag = 1) is null then 'false' else 'true' end as msp_flag,
test_comp_flag,
parent_comp_flag
from dbo.comp (nolock)
where comp.comp_type = 1
June 17, 2013 at 1:46 am
For me, it's giving 32 records each and it's almost in a flash. Where is the issue ?
June 17, 2013 at 2:12 am
Why read the persons table three times when you only have to read it once?
SELECT
activity_flag = case when comp.activity_flag = 1 then 'Active' else 'Inactive' end,
comp.name,
comp.comp_code,
x1.active_users,
x1.new_users,
x1.inactive_users,
worker = (select count(1)
from dbo.worker (nolock)
where worker.owner_code = comp.comp_code),
msp_flag = CASE WHEN x2.owner_code IS NULL THEN 'false' ELSE 'true' END,
test_comp_flag,
parent_comp_flag
FROM dbo.comp
OUTER APPLY (
SELECT
active_users = SUM(CASE WHEN activity_flag = 1 AND reg_flag = 0 THEN 1 ELSE 0 END),
new_users = SUM(CASE WHEN activity_flag = 1 AND reg_flag = 1 THEN 1 ELSE 0 END),
inactive_users = SUM(CASE WHEN activity_flag = 0 THEN 1 ELSE 0 END)
FROM dbo.person p
WHERE p.comp_code = comp.comp_code
) x1
LEFT JOIN (
SELECT owner_code
FROM dbo.os_contract
WHERE msp_flag = 1
GROUP BY owner_code
) x2
ON x2.owner_code = comp.comp_code
WHERE comp.comp_type = 1
If your queries are forcing SQL Server to work three times harder than it needs to, then it should come as no surprise that they are slow when running against larger data sets than were used for development. Getting the correct results from a query represents about 20 or 30% of the effort required. Most of the balance is tuning the query.
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 17, 2013 at 3:51 am
Provided test data is from my local, so it will not give any issue. Getting data from prod server is not possible for me.
I have tried all three options (Two queries I attached and third suggested by you), there is a marginal difference between them. All are taking between 5 - 5.30 minutes to execute and returning 341 rows.
These are the I/O stats :
Table 'worker'. Scan count 1, logical reads 25439, physical reads 0, read-ahead reads 1, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'person'. Scan count 341, logical reads 17962891, physical reads 0, read-ahead reads 10466, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'comp'. Scan count 1, logical reads 1065, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'os_contract'. Scan count 1, logical reads 146, physical reads 0, read-ahead reads 7, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
June 17, 2013 at 4:08 am
-- try changing this
OUTER APPLY (
SELECT
active_users = SUM(CASE WHEN activity_flag = 1 AND reg_flag = 0 THEN 1 ELSE 0 END),
new_users = SUM(CASE WHEN activity_flag = 1 AND reg_flag = 1 THEN 1 ELSE 0 END),
inactive_users = SUM(CASE WHEN activity_flag = 0 THEN 1 ELSE 0 END)
FROM dbo.person p
WHERE p.comp_code = comp.comp_code
) x1
-- to this
LEFT JOIN (
SELECT
comp_code,
active_users = SUM(CASE WHEN activity_flag = 1 AND reg_flag = 0 THEN 1 ELSE 0 END),
new_users = SUM(CASE WHEN activity_flag = 1 AND reg_flag = 1 THEN 1 ELSE 0 END),
inactive_users = SUM(CASE WHEN activity_flag = 0 THEN 1 ELSE 0 END)
FROM dbo.person
GROUP BY comp_code
) x1 ON x1.comp_code = comp.comp_code
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 17, 2013 at 4:23 am
T.Ashish (6/17/2013)
Provided test data is from my local, so it will not give any issue. Getting data from prod server is not possible for me.I have tried all three options (Two queries I attached and third suggested by you), there is a marginal difference between them. All are taking between 5 - 5.30 minutes to execute and returning 341 rows.
These are the I/O stats :
Table 'worker'. Scan count 1, logical reads 25439, physical reads 0, read-ahead reads 1, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'person'. Scan count 341, logical reads 17962891, physical reads 0, read-ahead reads 10466, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'comp'. Scan count 1, logical reads 1065, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'os_contract'. Scan count 1, logical reads 146, physical reads 0, read-ahead reads 7, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Have you tested with a couple of new indexes?
(32 row(s) affected)
Table 'os_contract'. Scan count 32, logical reads 64, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'comp'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'person'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'worker'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
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 17, 2013 at 4:36 am
Still not helping.
We are using a filter "where comp.comp_type = 1" in the query.
If I change the filter comp.comp_type = 2 then it is taking only 15 seconds.
We have 3 comp types in table
Comp_type Count
1 1
2 341
3 21374
June 17, 2013 at 8:42 am
T.Ashish (6/17/2013)
Provided test data is from my local, so it will not give any issue. Getting data from prod server is not possible for me.I have tried all three options (Two queries I attached and third suggested by you), there is a marginal difference between them. All are taking between 5 - 5.30 minutes to execute and returning 341 rows.
These are the I/O stats :
Table 'worker'. Scan count 1, logical reads 25439, physical reads 0, read-ahead reads 1, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'person'. Scan count 341, logical reads 17962891, physical reads 0, read-ahead reads 10466, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'comp'. Scan count 1, logical reads 1065, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'os_contract'. Scan count 1, logical reads 146, physical reads 0, read-ahead reads 7, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
1) You seem to be scanning the person table. As Chris suggests, this can almost certainly be addressed with proper index.
2) What is the worker table doing? Not sure why that is necessary.
3) We REALLY need the actual query plan!!
4) You probably need OPTION (RECOMPILE) on this statement (even with hard-coded values, but ESPECIALLY if you use variables in your "real" code) due to the extreme data-value-distribution issues you have with the 3 values of comptype. You ABSOLUTELY DO NOT WANT the same plan for each of those 3 values, ESPECIALLY 1 and 3!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 17, 2013 at 8:44 am
Why read the persons table three times when you only have to read it once?
I have seen this type of query SOOO many times at clients over the years. I currently have one that has a DISASTROUS propensity for it!! :Whistling:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 17, 2013 at 8:53 am
TheSQLGuru (6/17/2013)
T.Ashish (6/17/2013)
Provided test data is from my local, so it will not give any issue. Getting data from prod server is not possible for me.I have tried all three options (Two queries I attached and third suggested by you), there is a marginal difference between them. All are taking between 5 - 5.30 minutes to execute and returning 341 rows.
These are the I/O stats :
Table 'worker'. Scan count 1, logical reads 25439, physical reads 0, read-ahead reads 1, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'person'. Scan count 341, logical reads 17962891, physical reads 0, read-ahead reads 10466, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'comp'. Scan count 1, logical reads 1065, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'os_contract'. Scan count 1, logical reads 146, physical reads 0, read-ahead reads 7, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
1) You seem to be scanning the person table. As Chris suggests, this can almost certainly be addressed with proper index.
2) What is the worker table doing? Not sure why that is necessary.
3) We REALLY need the actual query plan!!
4) You probably need OPTION (RECOMPILE) on this statement (even with hard-coded values, but ESPECIALLY if you use variables in your "real" code) due to the extreme data-value-distribution issues you have with the 3 values of comptype. You ABSOLUTELY DO NOT WANT the same plan for each of those 3 values, ESPECIALLY 1 and 3!!
Thanks for the reminder Kevin 🙂
Here's a few indexes which improve performance considerably. I'm not going to claim they are ideal without seeing the plan or playing with the tables:
CREATE NONCLUSTERED INDEX [ix_comp_type] ON [dbo].[comp]
([comp_type] ASC)
CREATE CLUSTERED INDEX [ucx_os_contract_id] ON [dbo].[os_contract]
([owner_code] ASC,[msp_flag] ASC)
CREATE NONCLUSTERED INDEX [ix_comp] ON [dbo].[person]
([comp_code] ASC,[activity_flag] ASC,[reg_flag] ASC)
CREATE NONCLUSTERED INDEX [ix_comp_code] ON [dbo].[worker]
([owner_code] ASC)
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 17, 2013 at 11:20 pm
this is the actual plan from prod server.
June 18, 2013 at 1:23 am
T.Ashish (6/17/2013)
this is the actual plan from prod server.
Thanks for posting.
I'd recommend you implement the indexes I posted earlier and repost the actual plan from prod with the indexes in place. Taken in isolation from all other queries against these tables, they are fine and should give you at least an order of magnitude lift in performance for this query: however, you should examine index usage stats and query activity (most frequent/most expensive) and tweak if necessary.
Here's a slightly modified version of the query you ran in prod. I've removed the eccentric and offputting formatting and corrected a filter in the os_contract subquery:
DECLARE @0 BIT, @1 INT
SELECT @0 = 1, @1 = 2
SELECT
activity_flag = case when c.activity_flag = 1 then 'Active' else 'Inactive' end,
msp_flag = case when bsc.msp_flag = 1 then 'true' else 'false' end,
c.name,
c.comp_code,
p.active_users,
p.new_users,
p.inactive_users,
w.worker,
c.test_comp_flag,
c.parent_comp_flag
FROM dbo.comp c ( NOLOCK )
LEFT JOIN (
SELECT
comp_code,
active_users = SUM(case when activity_flag = 1 and reg_flag = 0 then 1 else 0 end),
new_users = SUM(case when activity_flag = 1 and reg_flag = 1 then 1 else 0 end),
inactive_users = SUM(case when activity_flag = 0 then 1 else 0 end)
FROM dbo.person
GROUP BY comp_code
) p
ON p.comp_code = c.comp_code
LEFT JOIN (
SELECT
COUNT(*) worker,
owner_code
FROM dbo.worker
GROUP BY owner_code
) w
ON w.owner_code = c.comp_code
LEFT JOIN (
SELECT
msp_flag,
owner_code
FROM dbo.os_contract
WHERE msp_flag = @0
GROUP BY msp_flag, owner_code
) bsc
ON bsc.owner_code = c.comp_code
--and bsc . msp_flag = @0
WHERE c.comp_type = @1
ORDER BY c.name
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 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply