Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Query running slow on prod. Expand / Collapse
Author
Message
Posted Monday, June 17, 2013 1:18 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, November 3, 2014 1:52 AM
Points: 720, Visits: 553
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





  Post Attachments 
sqlquery.txt (6 views, 2.40 KB)
testdata.txt (5 views, 86.08 KB)
Post #1464008
Posted Monday, June 17, 2013 1:46 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, November 20, 2014 4:29 AM
Points: 3,559, Visits: 2,671
For me, it's giving 32 records each and it's almost in a flash. Where is the issue ?
Post #1464016
Posted Monday, June 17, 2013 2:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:43 AM
Points: 6,890, Visits: 14,254
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
Post #1464024
Posted Monday, June 17, 2013 3:51 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, November 3, 2014 1:52 AM
Points: 720, Visits: 553
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.


Post #1464048
Posted Monday, June 17, 2013 4:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:43 AM
Points: 6,890, Visits: 14,254
-- 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
Post #1464054
Posted Monday, June 17, 2013 4:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:43 AM
Points: 6,890, Visits: 14,254
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
Post #1464060
Posted Monday, June 17, 2013 4:36 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, November 3, 2014 1:52 AM
Points: 720, Visits: 553
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
Post #1464065
Posted Monday, June 17, 2013 8:42 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:20 PM
Points: 4,468, Visits: 6,397
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
Post #1464185
Posted Monday, June 17, 2013 8:44 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:20 PM
Points: 4,468, Visits: 6,397
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!!


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1464186
Posted Monday, June 17, 2013 8:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:43 AM
Points: 6,890, Visits: 14,254
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
Post #1464197
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse