SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query running slow on prod.


Query running slow on prod.

Author
Message
T.Ashish
T.Ashish
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1329 Visits: 607
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
(select top (1) 1
from dbo.os_contract (nolock)
where os_contract.owner_code = comp.comp_code
and os_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
Attachments
sqlquery.txt (17 views, 2.00 KB)
testdata.txt (19 views, 86.00 KB)
sqlnaive
sqlnaive
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6291 Visits: 2774
For me, it's giving 32 records each and it's almost in a flash. Where is the issue ?
ChrisM@Work
ChrisM@Work
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39966 Visits: 20000
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.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
T.Ashish
T.Ashish
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1329 Visits: 607
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.
ChrisM@Work
ChrisM@Work
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39966 Visits: 20000
-- 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



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
ChrisM@Work
ChrisM@Work
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39966 Visits: 20000
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.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
T.Ashish
T.Ashish
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1329 Visits: 607
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
TheSQLGuru
TheSQLGuru
SSC-Dedicated
SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)

Group: General Forum Members
Points: 31077 Visits: 8669
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
TheSQLGuru
TheSQLGuru
SSC-Dedicated
SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)

Group: General Forum Members
Points: 31077 Visits: 8669
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
ChrisM@Work
ChrisM@Work
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39966 Visits: 20000
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)



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search