June 6, 2013 at 12:37 am
Hello,
I have the following query which takes lot of CPU (4 sec) and Duration (4 sec) all the time. I created different index but i have the same problem. I have on noyau.patient 3.4 millions lines and on noyau.LIEU_NAISSANCE 39000 lines.
DECLARE @p__linq__0 nvarchar(4000),
@p__linq__1 varchar(8000),
@p__linq__2 nvarchar(4000),
@p__linq__3 varchar(8000)
SET @p__linq__0=N'ARD%'
SET @p__linq__1='CHU-LYON'
SET @p__linq__2=N'REI%'
SET @p__linq__3='CHU-LYON'
SELECT TOP (100)
[Project1].[pat_id] AS [pat_id],
[Project1].[pat_ipp] AS [pat_ipp],
[Project1].[pat_nom] AS [pat_nom],
[Project1].[pat_nom_naissance] AS [pat_nom_naissance],
[Project1].[pat_prenom] AS [pat_prenom],
[Project1].[pat_date_naissance] AS [pat_date_naissance],
[Project1].[pat_sexe] AS [pat_sexe],
[Project1].[pat_date_deces] AS [pat_date_deces],
[Project1].[pat_nationalite_id] AS [pat_nationalite_id],
[Project1].[pat_categorie_socio_professionnelle] AS [pat_categorie_socio_professionnelle],
[Project1].[pat_activite_socio_professionnelle] AS [pat_activite_socio_professionnelle],
[Project1].[pat_site_code] AS [pat_site_code],
[Project1].[pat_situation] AS [pat_situation],
[Project1].[pat_identite_definitive] AS [pat_identite_definitive],
[Project1].[lnai_id] AS [lnai_id],
[Project1].[pat_ancien_id] AS [pat_ancien_id],
[Project1].[pat_portail_id] AS [pat_portail_id],
[Project1].[pat_confidentiel] AS [pat_confidentiel],
[Project1].[pat_insa] AS [pat_insa],
[Project1].[pat_insc] AS [pat_insc],
[Project1].[photo_id] AS [photo_id],
[Project1].[lnai_id1] AS [lnai_id1],
[Project1].[lnai_cp] AS [lnai_cp],
[Project1].[lnai_ville] AS [lnai_ville],
[Project1].[lnai_pays_id] AS [lnai_pays_id]
FROM ( SELECT
[Extent1].[pat_id] AS [pat_id],
[Extent1].[pat_situation] AS [pat_situation],
[Extent1].[pat_ancien_id] AS [pat_ancien_id],
[Extent1].[pat_portail_id] AS [pat_portail_id],
[Extent1].[pat_ipp] AS [pat_ipp],
[Extent1].[pat_nom] AS [pat_nom],
[Extent1].[pat_nom_naissance] AS [pat_nom_naissance],
[Extent1].[pat_prenom] AS [pat_prenom],
[Extent1].[pat_date_naissance] AS [pat_date_naissance],
[Extent1].[pat_site_code] AS [pat_site_code],
[Extent1].[pat_sexe] AS [pat_sexe],
[Extent1].[pat_date_deces] AS [pat_date_deces],
[Extent1].[pat_categorie_socio_professionnelle] AS [pat_categorie_socio_professionnelle],
[Extent1].[pat_activite_socio_professionnelle] AS [pat_activite_socio_professionnelle],
[Extent1].[pat_nationalite_id] AS [pat_nationalite_id],
[Extent1].[lnai_id] AS [lnai_id],
[Extent1].[pat_identite_definitive] AS [pat_identite_definitive],
[Extent1].[pat_confidentiel] AS [pat_confidentiel],
[Extent1].[pat_insa] AS [pat_insa],
[Extent1].[pat_insc] AS [pat_insc],
[Extent1].[photo_id] AS [photo_id],
[Extent2].[lnai_id] AS [lnai_id1],
[Extent2].[lnai_cp] AS [lnai_cp],
[Extent2].[lnai_ville] AS [lnai_ville],
[Extent2].[lnai_pays_id] AS [lnai_pays_id]
FROM [noyau].[PATIENT] AS [Extent1]
LEFT OUTER JOIN [noyau].[LIEU_NAISSANCE] AS [Extent2] ON [Extent1].[lnai_id] = [Extent2].[lnai_id]
WHERE ([Extent1].[pat_nom] IS NOT NULL)
AND ([Extent1].[pat_nom] LIKE @p__linq__0 ESCAPE '~')
AND ([Extent1].[pat_site_code] = @p__linq__1)
AND ([Extent1].[pat_nom_naissance] IS NOT NULL)
AND ([Extent1].[pat_nom_naissance] LIKE @p__linq__2 ESCAPE '~')
AND ([Extent1].[pat_site_code] = @p__linq__3)
) AS [Project1]
ORDER BY [Project1].[pat_nom] ASC, [Project1].[pat_nom_naissance] ASC, [project1].[pat_prenom] ASC
I tried to find which part of query takes time and the part taking problem is :
DECLARE @p__linq__0 nvarchar(4000),
@p__linq__1 varchar(8000),
@p__linq__2 nvarchar(4000),
@p__linq__3 varchar(8000)
SET @p__linq__0=N'ARD%'
SET @p__linq__1='CHU-LYON'
SET @p__linq__2=N'REI%'
SET @p__linq__3='CHU-LYON'
SELECT
[Extent1].[pat_id] AS [pat_id],
[Extent1].[pat_situation] AS [pat_situation],
[Extent1].[pat_ancien_id] AS [pat_ancien_id],
[Extent1].[pat_portail_id] AS [pat_portail_id],
[Extent1].[pat_ipp] AS [pat_ipp],
[Extent1].[pat_nom] AS [pat_nom],
[Extent1].[pat_nom_naissance] AS [pat_nom_naissance],
[Extent1].[pat_prenom] AS [pat_prenom],
[Extent1].[pat_date_naissance] AS [pat_date_naissance],
[Extent1].[pat_site_code] AS [pat_site_code],
[Extent1].[pat_sexe] AS [pat_sexe],
[Extent1].[pat_date_deces] AS [pat_date_deces],
[Extent1].[pat_categorie_socio_professionnelle] AS [pat_categorie_socio_professionnelle],
[Extent1].[pat_activite_socio_professionnelle] AS [pat_activite_socio_professionnelle],
[Extent1].[pat_nationalite_id] AS [pat_nationalite_id],
[Extent1].[lnai_id] AS [lnai_id],
[Extent1].[pat_identite_definitive] AS [pat_identite_definitive],
[Extent1].[pat_confidentiel] AS [pat_confidentiel],
[Extent1].[pat_insa] AS [pat_insa],
[Extent1].[pat_insc] AS [pat_insc],
[Extent1].[photo_id] AS [photo_id],
[Extent2].[lnai_id] AS [lnai_id1],
[Extent2].[lnai_cp] AS [lnai_cp],
[Extent2].[lnai_ville] AS [lnai_ville],
[Extent2].[lnai_pays_id] AS [lnai_pays_id]
FROM [noyau].[PATIENT] AS [Extent1] WITH (NOLOCK)
LEFT OUTER JOIN [noyau].[LIEU_NAISSANCE] AS [Extent2] WITH (NOLOCK) ON [Extent1].[lnai_id] = [Extent2].[lnai_id]
WHERE ([Extent1].[pat_nom] IS NOT NULL)
AND ([Extent1].[pat_nom] LIKE @p__linq__0 ESCAPE '~')
AND ([Extent1].[pat_site_code] = @p__linq__1)
AND ([Extent1].[pat_nom_naissance] IS NOT NULL)
AND ([Extent1].[pat_nom_naissance] LIKE @p__linq__2 ESCAPE '~')
AND ([Extent1].[pat_site_code] = @p__linq__3)
I don't know I can improve it.:hehe:
Thanks for your help,
EC
June 6, 2013 at 1:21 am
You can make a significant improvement to the readability of the query by eliminating the unnecessary subselect, removing unnecessary column aliases and using sensible table aliases:
SELECT
p.[pat_id],
p.[pat_situation],
p.[pat_ancien_id],
p.[pat_portail_id],
p.[pat_ipp],
p.[pat_nom],
p.[pat_nom_naissance],
p.[pat_prenom],
p.[pat_date_naissance],
p.[pat_site_code],
p.[pat_sexe],
p.[pat_date_deces],
p.[pat_categorie_socio_professionnelle],
p.[pat_activite_socio_professionnelle],
p.[pat_nationalite_id],
p.[lnai_id],
p.[pat_identite_definitive],
p.[pat_confidentiel],
p.[pat_insa],
p.[pat_insc],
p.[photo_id],
n.[lnai_id],
n.[lnai_cp],
n.[lnai_ville]],
n.[lnai_pays_id]
FROM [noyau].[PATIENT] p
LEFT OUTER JOIN [noyau].[LIEU_NAISSANCE] n
ON p.[lnai_id] = n.[lnai_id]
WHERE (p.[pat_nom] IS NOT NULL)
AND (p.[pat_nom] LIKE @p__linq__0 ESCAPE '~')
AND (p.[pat_site_code] = @p__linq__1)
AND (p.[pat_site_code] = @p__linq__3)
AND (p.[pat_nom_naissance] IS NOT NULL)
AND (p.[pat_nom_naissance] LIKE @p__linq__2 ESCAPE '~')
ORDER BY p.[pat_nom] ASC, p.[pat_nom_naissance] ASC, p.[pat_prenom] ASC
In terms of performance improvement, 4 seconds isn't so bad if you're returning a significant proportion of rows from [noyau].[PATIENT]. If you're returning one row then it's poor. 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 6, 2013 at 1:58 am
Hello,
In this case, I return one line but anyway if the query returns one line or more the duration and CPU keep the same value.
Cheers,
Eric
June 6, 2013 at 2:22 am
ERIC CRUDELI (6/6/2013)
Hello,In this case, I return one line but anyway if the query returns one line or more the duration and CPU keep the same value.
Cheers,
Eric
Can you post the execution plan?
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 6, 2013 at 2:34 am
Query plan see attachment
June 6, 2013 at 2:47 am
ERIC CRUDELI (6/6/2013)
Query plan see attachment
No, not a picture of it - that's like ordering a taxi to the airport and someone turning up with a picture of a taxi. The actual plan, saved 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 6, 2013 at 2:51 am
Jesus !!!
June 6, 2013 at 3:09 am
ERIC CRUDELI (6/6/2013)
Jesus !!!
He's gone home, there's just us geeks eating fish sandwiches and slugging Bordeaux.
The existing index isn't well matched to this query - you're getting an index scan for a single row. Try an index on pat_nom, pat_nom_naissance, pat_site_code. I wouldn't recommend at this stage including the outputted columns from [patient] as it's a hefty list and a single key lookup should be cheap.
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 6, 2013 at 3:35 am
Also, you should match the datatype of your variables to your column datatypes:
DECLARE
@p__linq__0 nvarchar(?), -- pat_nom
@p__linq__1 varchar(?), -- pat_site_code
@p__linq__2 nvarchar(?), -- pat_nom_naissance
@p__linq__3 varchar(?) -- pat_site_code (again)
- to get rid of some of those implicit conversions.
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 6, 2013 at 3:56 am
I adjusted datatype of myr variables to my column datatypes
DECLARE @p__linq__0 varchar(25),
@p__linq__1 varchar(9),
@p__linq__2 varchar(25),
@p__linq__3 varchar(9)
And created index
USE [NOYAU_PATIENT]
GO
/****** Object: Index [IX_PATIENT_EC] Script Date: 06/06/2013 11:53:15 ******/
CREATE NONCLUSTERED INDEX [IX_PATIENT_EC] ON [noyau].[PATIENT]
(
[pat_nom] ASC,
[pat_nom_naissance] ASC,
[pat_site_code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
I got the same value for duration and CPU :sick:
June 6, 2013 at 4:24 am
There's still a huge memory grant :ermm:
Try this very simplified version:
SELECT
pat_nom, pat_site_code, pat_nom_naissance, pat_id
FROM [noyau].[PATIENT]
WHERE ([pat_nom] LIKE @p__linq__0 ESCAPE '~')
AND ([pat_site_code] = @p__linq__1)
--AND ([Extent1].[pat_site_code] = @p__linq__3)
--AND ([Extent1].[pat_nom_naissance] IS NOT NULL)
AND ([pat_nom_naissance] LIKE @p__linq__2 ESCAPE '~')
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 6, 2013 at 5:13 am
Hello,
I tried this :
DECLARE @p__linq__0 varchar(25),
@p__linq__1 varchar(9),
@p__linq__2 varchar(25),
@p__linq__3 varchar(9)
SET @p__linq__0=N'ARD%'
SET @p__linq__1='CHU-LYON'
SET @p__linq__2=N'REI%'
SET @p__linq__3='CHU-LYON'
SELECT
P.pat_nom,
P.pat_site_code,
P.pat_nom_naissance,
P.pat_id,
L.[lnai_id] AS [lnai_id1],
L.[lnai_cp] AS [lnai_cp],
L.[lnai_ville] AS [lnai_ville],
L.[lnai_pays_id] AS [lnai_pays_id]
FROM [noyau].[PATIENT] P
LEFT OUTER JOIN [noyau].[LIEU_NAISSANCE] AS L ON P.[lnai_id] = L.[lnai_id]
WHERE (P.[pat_nom] LIKE @p__linq__0 ESCAPE '~')
AND (P.[pat_site_code] = @p__linq__1)
AND (P.[pat_site_code] = @p__linq__3)
AND (P.[pat_nom_naissance] IS NOT NULL)
AND (P.[pat_nom_naissance] LIKE @p__linq__2 ESCAPE '~')
and the duration and cpu are near 0.
What is the problem with the original query (too many columns inside select)
June 6, 2013 at 5:33 am
ERIC CRUDELI (6/6/2013)
Hello,I tried this :
DECLARE @p__linq__0 varchar(25),
@p__linq__1 varchar(9),
@p__linq__2 varchar(25),
@p__linq__3 varchar(9)
SET @p__linq__0=N'ARD%'
SET @p__linq__1='CHU-LYON'
SET @p__linq__2=N'REI%'
SET @p__linq__3='CHU-LYON'
SELECT
P.pat_nom,
P.pat_site_code,
P.pat_nom_naissance,
P.pat_id,
L.[lnai_id] AS [lnai_id1],
L.[lnai_cp] AS [lnai_cp],
L.[lnai_ville] AS [lnai_ville],
L.[lnai_pays_id] AS [lnai_pays_id]
FROM [noyau].[PATIENT] P
LEFT OUTER JOIN [noyau].[LIEU_NAISSANCE] AS L ON P.[lnai_id] = L.[lnai_id]
WHERE (P.[pat_nom] LIKE @p__linq__0 ESCAPE '~')
AND (P.[pat_site_code] = @p__linq__1)
AND (P.[pat_site_code] = @p__linq__3)
AND (P.[pat_nom_naissance] IS NOT NULL)
AND (P.[pat_nom_naissance] LIKE @p__linq__2 ESCAPE '~')
and the duration and cpu are near 0.
What is the problem with the original query (too many columns inside select)
Not sure yet, Eric. The memory grant is far more reasonable now, the seek is more reasonable too - a range for nom rather than nom is not null. I wonder...try putting AND P.[pat_nom] IS NOT NULL back into the WHERE clause...
There's quite a big difference between estimated and actual row counts which often signals stale stats.
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 6, 2013 at 5:37 am
Hello,
I just found the problem just before to get your email.
The problen is in clause WHERE with P.[pat_nom] IS NOT NULL. If I remove it the reponse time of query is immediate.
Why ?
Cheers,
Eric
June 6, 2013 at 5:56 am
ERIC CRUDELI (6/6/2013)
Hello,I just found the problem just before to get your email.
The problen is in clause WHERE with P.[pat_nom] IS NOT NULL. If I remove it the reponse time of query is immediate.
Why ?
Cheers,
Eric
If it's in the WHERE clause, then it's used as the seek predicate - I don't know why because it's not very selective at all. Could be stale stats. You don't need it because the other predicate for pat_nom excludes NULLs. I'm hoping someone like Gail or Grant will step in at this point - they may know the answer to this.
Try updating your stats (UPDATE STATISTICS Patient WITH FULLSCAN) and rerunning the original 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
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply