June 26, 2014 at 11:57 pm
Hi,
I have a performance issue with a simple query and I don't understand why in query plan SQL engine use a index scan instead of index seek.
Query :
DECLARE @pat_ancien_id int,
@pat_ipp varchar(20)
SET @pat_ancien_id = 4035253
SET @pat_ipp = '8807641'
DECLARE @PatientId as int
SET @PatientId = @pat_ancien_id
SELECT
0 AS NombreDossiers,
COUNT(FI.fiche_id) AS NombreFiches,
DOS.dos_libelle AS LibelleMetier,
DOS.dossier_specialite_id AS IdentifiantMetier,
PAT.pat_ancien_id
FROM [DOMINHO].[dominho].FICHE AS FI WITH(NOLOCK)
INNER JOIN [DOMINHO].[dominho].DOSSIER_SPECIALITE AS DOS WITH(NOLOCK) ON DOS.dossier_specialite_id = FI.dossier_specialite_id
INNER JOIN NOYAU.patient.PATIENT AS PAT WITH(NOLOCK) ON PAT.pat_id = FI.patient_id
WHERE
(
(
@pat_ipp IS NOT NULL
AND PAT.pat_ipp IS NOT NULL
AND PAT.pat_ipp = @pat_ipp
)
OR
(
@PatientId IS NOT NULL
AND PAT.pat_ancien_id IS NOT NULL
AND PAT.pat_ancien_id = @PatientId
)
)
AND FI.fiche_id > 30000000 --on exclus les fiches gulper
AND FI.fic_suppr = 0 --on exclu les fiches supprimées
GROUP BY DOS.dos_libelle, DOS.dossier_specialite_id, PAT.pat_ancien_id
Thanks for your help,
Eric
June 27, 2014 at 12:54 am
By the way how much time this query is taking, If you think index seek should be there instead of the index scan and your query is running properly then you do not need to change anything. SQL SERVER choose the best plan for the query.
If its the other way around try to consider the following:
1) Pleas check the fragmentation level of related the indexes. Also check the Statistics are up to date for the related tables.
2) Try to use FORCESEEK Table Hint and check the performance of the query (which i think it wouldn't).
June 27, 2014 at 1:30 am
I ran on NOYAU.patient a full Update Statistics but change nothing.
Query takes 1700ms all the time
June 27, 2014 at 1:52 am
ERIC CRUDELI (6/27/2014)
I ran on NOYAU.patient a full Update Statistics but change nothing.Query takes 1700ms all the time
Your WHERE Clause is causing the scan, if you replace it with below you probably get the Seek.
WHERE
(
(
--@pat_ipp IS NOT NULL
--AND PAT.pat_ipp IS NOT NULL
--AND
PAT.pat_ipp = @pat_ipp
)
OR
(
--@PatientId IS NOT NULL
--AND PAT.pat_ancien_id IS NOT NULL
--AND
PAT.pat_ancien_id = @PatientId
)
)
By the way 1700ms = 1.7 sec Is it that much ?
June 27, 2014 at 4:24 am
Hi,
I changed WHERE Clause like you said and I have always index scan ?
Regards,
EC
June 27, 2014 at 4:42 am
Without the plan itself, not just a picture, it's really hard to say. Can you post the execution plan? An actual plan would be preferable to an estimated one.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
June 27, 2014 at 10:42 am
As asked by Grant Could you please post the actual execution plan. That will help.
Meanwhile you can try removing the OR from Query and use Union All and see if that helps.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply