Click here to monitor SSC
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
Right there with Babe
Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)

Group: General Forum Members
Points: 767 Visits: 582
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 (7 views, 2.00 KB)
testdata.txt (6 views, 86.00 KB)
sqlnaive
sqlnaive
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3683 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
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8975 Visits: 19028
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
Right there with Babe
Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)

Group: General Forum Members
Points: 767 Visits: 582
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
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8975 Visits: 19028
-- 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
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8975 Visits: 19028
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
Right there with Babe
Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)

Group: General Forum Members
Points: 767 Visits: 582
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
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 5963 Visits: 8312
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 at GMail
TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 5963 Visits: 8312
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 at GMail
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8975 Visits: 19028
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