July 22, 2013 at 7:15 am
Hi,
I have a view like :
alter VIEW View_IdPERSON
as
select em.EMAIL_VALUE as liste_email
,e.PERSON_id as Liste_DMID
,ed.DATA_BIRTHDATE as Liste_DMDATENAISSANCE
,case when ed.CIVILITY_ID=1 then 'M' else 'F' end AS Liste_DMGENRE
,ed.DATA_FIRSTNAME as Liste_DMPRENOM
,ed.DATA_LASTNAME as Liste_DMNOM
,'' as Liste_DMADRESSEID
,a.ADDRESS_1 as Liste_DMADRESSE1
,a.ADDRESS_2 as Liste_DMADRESSE2
,cp.CP_VALUE as Liste_DMCODEPOSTAL
,a.ADDRESS_CITY as Liste_DMVILLE
,ISNULL(a.ADDRESS_COUNTRYISO, ed.DATA_COUNTRYISO) as Liste_DMPAYSISO2
,ed.DATA_TELEPHONE as Liste_DMTELFIXE
,g.GSM_VALUE as Liste_DMTELMOBILE
,Case when ed.CIVILITY_ID IS null then '*' else CIVILITY_CODE end AS Liste_DMCIVILITE
,ed.DATA_INSCRIPTIONDATE as Liste_DMDATEMEMBRE
,ed.DATA_LOGIN as Liste_DMLOGIN
,ed.DATA_PWD as Liste_DMMOTDEPASSE
,'' as Liste_DMIDMEMBREWEB
from dbo.PERSON as e with (nolock, index(IX_PERSON_id_cluster))
INNER JOIN dbo.EMAIL as em with (nolock,index(IX_emailvalue)) on e.EMAIL_ID = em.EMAIL_ID
INNER JOIN (
select e.EMAIL_ID, e.PERSON_Lastmodificationdate, MAX(e.PERSON_ID) AS PERSON_ID
from dbo.PERSON as e with (nolock)
INNER JOIN (
select e.EMAIL_ID, MAX(e.PERSON_Lastmodificationdate) as Lastmodificationdate
from dbo.PERSON as e with (nolock)
INNER JOIN dbo.EMAIL as em with (nolock,index(IX_emailvalue)) on e.EMAIL_ID = em.EMAIL_ID
group by e.EMAIL_ID
)
as k on e.EMAIL_ID = k.EMAIL_ID and e.PERSON_Lastmodificationdate = k.Lastmodificationdate
group by e.EMAIL_ID, e.PERSON_Lastmodificationdate
) as t on e.PERSON_ID = t.PERSON_ID
LEFT OUTER JOIN dbo.Address as a with (nolock)
INNER JOIN dbo.CP as cp with (nolock)
on (a.CP_id=cp.CP_id)
on (e.Address_id=a.Address_id)
LEFT OUTER JOIN dbo.DATA as ed with (nolock)
LEFT OUTER JOIN dbo.Civility as civ with (nolock) on (ed.Civility_id=civ.civility_id)
LEFT OUTER JOIN dbo.Language as l with (nolock) on (ed.Language_id=l.Language_id) on (e.PERSON_id=ed.PERSON_id)
left OUTER JOIN dbo.GSM as g with (nolock) on (e.GSM_ID = g.GSM_ID)
when i try to execute this query i have the result in 3 seconds.
Select Liste_DMADRESSE1,Liste_DMADRESSE2,Liste_DMADRESSEID,Liste_DMCIVILITE,Liste_DMCODEPOSTAL,Liste_DMDATEMEMBRE,Liste_DMDATENAISSANCE,Liste_DMGENRE,Liste_DMIDMEMBREWEB
,Liste_DMLOGIN,Liste_DMMOTDEPASSE,Liste_DMNOM,Liste_DMPAYSISO2,Liste_DMPRENOM,Liste_DMTELFIXE,Liste_DMTELMOBILE,Liste_DMVILLE
From View_IdPERSON AS [IDENTITYVIEW]
where Liste_EMAIL = 'XXX@XXX.FR'
But when i change my query with "top 1" expression i wait at least 5 minutes and i have no result.
(Select top 1 Liste_DMADRESSE1,Liste_DMADRESSE2,Liste_DMADRESSEID,Liste_DMCIVILITE,Liste_DMCODEPOSTAL,Liste_DMDATEMEMBRE,Liste_DMDATENAISSANCE,Liste_DMGENRE,Liste_DMIDMEMBREWEB
,Liste_DMLOGIN,Liste_DMMOTDEPASSE,Liste_DMNOM,Liste_DMPAYSISO2,Liste_DMPRENOM,Liste_DMTELFIXE,Liste_DMTELMOBILE,Liste_DMVILLE
From View_IdPERSON AS [IDENTITYVIEW]
where Liste_EMAIL = 'XXX@XXX.FR' )
Two queries have different execution plans. I tried to index all columns used, update statistics.... But no succes.
HERE ARE THE INDEXES :
-----
IX_emailvalue
IX_Email_id
PERSON
---
IX_PERSON_id_cluster
IX_PERSON_ID include(All columns used in the query)
IX_PERSON_Lastmodificationdate
civility
---
CIVILITY_id
DATA
---
IX_PERSON_ID(non clustered)
IX_PERSON_ID (includes all columns used)
GSM
---
GSM_id
Language
---
Language_id
Address
--
adsress_id
I attached the query plans of two queries.
And three large tables(really not so large, 150 000 lines, the largest one) . The others are really small tables not so much data.
Do you have an idea?
Thanks in advance.
July 22, 2013 at 7:21 am
you must specify an ORDER BY clause with your select query with TOP...
Without specifying ORDER BY sql server will give you any random no which is not correct
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 22, 2013 at 7:37 am
Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 22, 2013 at 8:16 am
The view is a strange mix of naive coding (determined by the very inefficient subquery and suggestions that the view was created using a query designer) and index hints, which are considered to be an advanced technique. I'm sure you could replace the subquery
INNER JOIN (
select e.EMAIL_ID, e.PERSON_Lastmodificationdate, MAX(e.PERSON_ID) AS PERSON_ID
from dbo.PERSON as e with (nolock)
INNER JOIN ( -- dbo.EMAIL not required
select e.EMAIL_ID, MAX(e.PERSON_Lastmodificationdate) as Lastmodificationdate
from dbo.PERSON as e with (nolock)
INNER JOIN dbo.EMAIL as em with (nolock,index(IX_emailvalue)) on e.EMAIL_ID = em.EMAIL_ID
group by e.EMAIL_ID
) as k
on e.EMAIL_ID = k.EMAIL_ID and e.PERSON_Lastmodificationdate = k.Lastmodificationdate
group by e.EMAIL_ID, e.PERSON_Lastmodificationdate
) as t
on e.PERSON_ID = t.PERSON_ID
with a window function such as ROW_NUMBER().
Try querying from the view after removing the index hints - they may very well prevent the optimiser from considering plans which would perform better than the one chosen.
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
July 22, 2013 at 8:43 am
Hi ChrisM@Work,
I think, i didn't really understand howto and why replace with ROW_NUMBER() . Can you just explain a bit?
July 22, 2013 at 9:10 am
nailosuper (7/22/2013)
Hi ChrisM@Work,I think, i didn't really understand howto and why replace with ROW_NUMBER() . Can you just explain a bit?
Your person and email tables are currently read by the view about 7 times each. Once each should be enough.
-- replace this
INNER JOIN (
select e.EMAIL_ID, e.PERSON_Lastmodificationdate, MAX(e.PERSON_ID) AS PERSON_ID
from dbo.PERSON as e
INNER JOIN ( -- dbo.EMAIL not required
select e.EMAIL_ID, MAX(e.PERSON_Lastmodificationdate) as Lastmodificationdate
from dbo.PERSON as e
group by e.EMAIL_ID
) as k
on e.EMAIL_ID = k.EMAIL_ID and e.PERSON_Lastmodificationdate = k.Lastmodificationdate
group by e.EMAIL_ID, e.PERSON_Lastmodificationdate
) as t
-- with this
rn = ROW_NUMBER() OVER(PARTITION BY EMAIL_ID ORDER BY PERSON_Lastmodificationdate DESC, PERSON_ID DESC
There are noncovering indexes and missing clustered indexes and index hints. How much control do you have over indexing, is this something you do or do you have a dba?
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
July 22, 2013 at 11:12 am
p.s. Ditch the nolocks
See - http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 22, 2013 at 11:49 am
Please, get rid of all the query hints, then start tuning. Index hints, especially for a query that is not that complex, shouldn't be necessary. There's no way to validate that you have good indexes in place without seeing a query plan from a query that doesn't use the hints.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 23, 2013 at 2:08 am
Hi ChrisM@Work,
I changed my query and verified(made some modifications) the indexes as the others said. So i could avoid the table spool, and i have the response in 2 seconds.
Thanks much...
July 23, 2013 at 3:15 am
nailosuper (7/23/2013)
Hi ChrisM@Work,I changed my query and verified(made some modifications) the indexes as the others said. So i could avoid the table spool, and i have the response in 2 seconds.
Thanks much...
You're welcome. Any chance of seeing the new Actual plan please?
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 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply