December 11, 2011 at 9:51 am
All,
I have a query which is aliasing the same set of tables several times. In all but one case the query optimiser choses a very quick plan but for one it choses a very slow plan. I'm not criticising the optimiser, I'd just like to understand why it's doing what it is doing and what I can do about it.
Firstly I'm sorry for posting such a large query. It's not that I haven't bothered to narrow down the problem, I have tried but I can't find it. It maybe that posting so much makes it impossible to follow, I don't mind posting other information if that helps.
The basic logic is that there are four tables:
tabitem - aliased as it...
tabproperty - aliased as prop...
tabitemproperty - aliased as ip...
tabitempropvalue - aliased as ipv...
The tabitemproperty table has three columns:
uniqueref
itemref - Foreign key from the itemref table
propertyref - foreign key from the propertyref
The tabitempropvalue table has four columns:
uniqueref
itempropref - foreign reference from the tabitemproperty table
heading
data
In all but one cases it joins the tabitemproperty (ip..) table to either the tabitem (it..) or tabproperty (prop...) table before joining it to the ipv... table which is quick. In the case of ipcvmddi and ipvcvmddi it joins the IP table to the IPV table first which results in it pulling a million records from the IP table and executing a merge join/inner join.
Full query:
set dateformat dmy
select ipvcreated.data, ipvddi.data, ipvcli.data, ipvack.data ,itcvm.uniqueref as itemref,
'status' =
case
when CONVERT(VARCHAR,cast(ipvcreated.data as datetime),108)<'09:00:00' and ( (CONVERT(VARCHAR,getdate(),108)>'12:00:00' ) or datediff(d,cast(ipvcreated.data as datetime),getdate()) >1) then '255' -- After 10 and older than 16:00 the previous day
when CONVERT(VARCHAR,cast(ipvcreated.data as datetime),108)>'09:00:00' and CONVERT(VARCHAR,cast(ipvcreated.data as datetime),108)<'11:59:59' and (CONVERT(VARCHAR,getdate(),108)>'15:00:00' or datediff(d,cast(ipvcreated.data as datetime),getdate()) >1) then '255' -- After 10 and older than 16:00 the previous day
when CONVERT(VARCHAR,cast(ipvcreated.data as datetime),108)>'12:00:00' and CONVERT(VARCHAR,cast(ipvcreated.data as datetime),108)<'16:00:00' and ((CONVERT(VARCHAR,getdate(),108)>'10:00:00' and datediff(d,cast(ipvcreated.data as datetime),getdate()) =1) or (datediff(d,cast(ipvcreated.data as datetime),getdate()) >1)) then '255'
when CONVERT(VARCHAR,cast(ipvcreated.data as datetime),108)>'16:00:00' and ((CONVERT(VARCHAR,getdate(),108)>'12:00:00' and datediff(d,cast(ipvcreated.data as datetime),getdate()) =1) or (datediff(d,cast(ipvcreated.data as datetime),getdate()) >1)) then '255' -- After 16 and before 09:00:00
else
'0'
end
from tabitem itcvm, tabitem itddi, tabitem ituser,
tabproperty propcvmddi, tabproperty propcli, tabproperty propcreated, tabproperty propack, tabproperty propddi, tabproperty propstaff, tabproperty propuser,
tabitemproperty ipcvmddi, tabitemproperty ipcli, tabitemproperty ipcreated, tabitemproperty ipack, tabitemproperty ipddi, tabitemproperty ipstaff, tabitemproperty ipuser,
tabitempropvalue ipvcvmddi, tabitempropvalue ipvcli, tabitempropvalue ipvcreated, tabitempropvalue ipvack, tabitempropvalue ipvddi, tabitempropvalue ipvstaff, tabitempropvalue ipvuser
where (itcvm.title ='customer vm - closed' or itcvm.title ='customer vm - open')
and itddi.title='ddi'
and ituser.title='user'
and propcvmddi.title='ddi'
and propcli.title='cli'
and propcreated.title='time'
and propack.title='acknowledged'
and propddi.title='description'
and propstaff.title='VM Monitor Staff'
and propuser.title='details'
and ipvcvmddi.heading='ddiref'
and ipvcli.heading='number'
and ipvcreated.heading='value'
and ipvack.heading='value'
and ipvddi.heading='description'
and ipvstaff.heading='value'
and ipvuser.heading='db ref'
and itcvm.uniqueref=ipcvmddi.itemref
and itcvm.uniqueref=ipcli.itemref
and itcvm.uniqueref=ipcreated.itemref
and itcvm.uniqueref=ipack.itemref
and itddi.uniqueref=ipddi.itemref
and itddi.uniqueref=ipstaff.itemref
and ituser.uniqueref=ipuser.itemref
and propcvmddi.uniqueref=ipcvmddi.propertyref
and propcli.uniqueref=ipcli.propertyref
and propcreated.uniqueref=ipcreated.propertyref
and propack.uniqueref=ipack.propertyref
and propddi.uniqueref=ipddi.propertyref
and propstaff.uniqueref=ipstaff.propertyref
and propuser.uniqueref=ipuser.propertyref
and ipcvmddi.uniqueref=ipvcvmddi.itempropref
and ipcli.uniqueref=ipvcli.itempropref
and ipcreated.uniqueref=ipvcreated.itempropref
and ipack.uniqueref=ipvack.itempropref
and ipddi.uniqueref=ipvddi.itempropref
and ipstaff.uniqueref=ipvstaff.itempropref
and ipuser.uniqueref=ipvuser.itempropref
and (case when dbo.isguid(ipvcvmddi.data)=1 and cast(ipvcvmddi.data as uniqueidentifier)=itddi.uniqueref then 1 else 0 end =1)
and (case when dbo.isguid(ipvstaff.data)=1 and cast(ipvstaff.data as uniqueidentifier)=ituser.uniqueref then 1 else 0 end =1)
and (case when dbo.isguid(ipvuser.data)=1 and cast(ipvuser.data as varchar(40)) ='{BCCDE896-8743-456A-9225-E38BAC7C6FD2}' then 1 else 0 end =1)
and ipvack.data='none'
and isdate(ipvcreated.data)=1
order by cast (ipvcreated.data as datetime) asc
GO
The output from the query optimiser is:
|--Sort(ORDER BY:([Expr1025] ASC))
|--Compute Scalar(DEFINE:([Expr1024]=If (Convert(Convert([ipvcreated].[Data]))<'09:00:00' AND (Convert(getdate())>'12:00:00' OR datediff(day, Convert([ipvcreated].[Data]), getdate())>1)) then '255' else If ((Convert(Convert([ipvcreated].[Data]))>'09
|--Nested Loops(Inner Join, OUTER REFERENCES:([ipcli].[uniqueref]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([ipcli].[PropertyRef]))
| |--Nested Loops(Inner Join, OUTER REFERENCES:([ipack].[ItemRef]))
| | |--Nested Loops(Inner Join, OUTER REFERENCES:([ipcreated].[uniqueref]))
| | | |--Nested Loops(Inner Join, OUTER REFERENCES:([ipcreated].[PropertyRef]))
| | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([ipack].[ItemRef]))
| | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([ipack].[uniqueref]) WITH PREFETCH)
| | | | | | |--Hash Match(Inner Join, HASH:([propack].[uniqueref])=([ipack].[PropertyRef]), RESIDUAL:([propack].[uniqueref]=[ipack].[PropertyRef]))
| | | | | | | |--Index Seek(OBJECT:([CSSystem].[dbo].[tabProperty].[Prop_Title] AS [propack]), SEEK:([propack].[Title]='acknowledged') ORDERED FORWARD)
| | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([ipcvmddi].[ItemRef]) WITH PREFETCH)
| | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([ipcvmddi].[ItemRef]) WITH PREFETCH)
| | | | | | | | |--Hash Match(Inner Join, HASH:([propcvmddi].[uniqueref])=([ipcvmddi].[PropertyRef]), RESIDUAL:([propcvmddi].[uniqueref]=[ipcvmddi].[PropertyRef]))
| | | | | | | | | |--Index Seek(OBJECT:([CSSystem].[dbo].[tabProperty].[Prop_Title] AS [propcvmddi]), SEEK:([propcvmddi].[Title]='ddi') ORDERED FORWARD)
| | | | | | | | | |--Merge Join(Inner Join, MANY-TO-MANY MERGE:([ipvcvmddi].[ItemPropRef])=([ipcvmddi].[uniqueref]), RESIDUAL:([ipcvmddi].[uniqueref]=[ipvcvmddi].[ItemPropRef]))
| | | | | | | | | |--Sort(ORDER BY:([ipvcvmddi].[ItemPropRef] ASC))
| | | | | | | | | | |--Nested Loops(Inner Join, WHERE:(If ([dbo].[isguid](Convert([ipvcvmddi].[Data]))=1 AND Convert([ipvcvmddi].[Data])=[itddi].[uniqueref]) then 1 else 0=1))
| | | | | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([ipuser].[PropertyRef]))
| | | | | | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([ipuser].[uniqueref]) WITH PREFETCH)
| | | | | | | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([ituser].[uniqueref]) WITH PREFETCH)
| | | | | | | | | | | | | |--Nested Loops(Inner Join, WHERE:(If ([dbo].[isguid](Convert([ipvstaff].[Data]))=1 AND Convert([ipvstaff].[Data])=[ituser].[uniqueref]) then 1 else 0=1))
| | | | | | | | | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([ipstaff].[uniqueref]))
| | | | | | | | | | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([ipstaff].[PropertyRef]))
| | | | | | | | | | | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([ipddi].[ItemRef]) WITH PREFETCH)
| | | | | | | | | | | | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([ipddi].[uniqueref]) WITH PREFETCH)
| | | | | | | | | | | | | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([ipddi].[PropertyRef]))
| | | | | | | | | | | | | | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([itddi].[uniqueref]) WITH PREFETCH)
| | | | | | | | | | | | | | | | | | | | |--Index Seek(OBJECT:([CSSystem].[dbo].[tabItem].[Title] AS [itddi]), SEEK:([itddi].[Title]='ddi') ORDERED FORWARD)
| | | | | | | | | | | | | | | | | | | | |--Index Seek(OBJECT:([CSSystem].[dbo].[tabItemProperty].[idxItem] AS [ipddi]), SEEK:([ipddi].[ItemRef]=[itddi].[uniqueref])
| | | | | | | | | | | | | | | | | | | |--Index Seek(OBJECT:([CSSystem].[dbo].[tabProperty].[Prop_Title] AS [propddi]), SEEK:([propddi].[Title]='description' AND [propd
| | | | | | | | | | | | | | | | | | |--Index Seek(OBJECT:([CSSystem].[dbo].[tabItemPropValue].[ItemPropRef] AS [ipvddi]), SEEK:([ipvddi].[ItemPropRef]=[ipddi].[uniqueref]
| | | | | | | | | | | | | | | | | |--Index Seek(OBJECT:([CSSystem].[dbo].[tabItemProperty].[idxItem] AS [ipstaff]), SEEK:([ipstaff].[ItemRef]=[ipddi].[ItemRef]) ORDERED FORW
| | | | | | | | | | | | | | | | |--Index Seek(OBJECT:([CSSystem].[dbo].[tabProperty].[Prop_Title] AS [propstaff]), SEEK:([propstaff].[Title]='VM Monitor Staff' AND [propstaff].
| | | | | | | | | | | | | | | |--Index Seek(OBJECT:([CSSystem].[dbo].[tabItemPropValue].[ItemPropRef] AS [ipvstaff]), SEEK:([ipvstaff].[ItemPropRef]=[ipstaff].[uniqueref]), WHERE
| | | | | | | | | | | | | | |--Index Seek(OBJECT:([CSSystem].[dbo].[tabItem].[Title] AS [ituser]), SEEK:([ituser].[Title]='user') ORDERED FORWARD)
| | | | | | | | | | | | | |--Index Seek(OBJECT:([CSSystem].[dbo].[tabItemProperty].[idxItem] AS [ipuser]), SEEK:([ipuser].[ItemRef]=[ituser].[uniqueref]) ORDERED FORWARD)
| | | | | | | | | | | | |--Filter(WHERE:(If ([dbo].[isguid](Convert([ipvuser].[Data]))=1 AND Convert([ipvuser].[Data])='{BCCDE896-8743-456A-9225-E38BAC7C6FD2}') then 1 else 0=1))
| | | | | | | | | | | | |--Index Seek(OBJECT:([CSSystem].[dbo].[tabItemPropValue].[ItemPropRef] AS [ipvuser]), SEEK:([ipvuser].[ItemPropRef]=[ipuser].[uniqueref]), WHERE:([ipvuser].[
| | | | | | | | | | | |--Index Seek(OBJECT:([CSSystem].[dbo].[tabProperty].[Prop_Title] AS [propuser]), SEEK:([propuser].[Title]='details' AND [propuser].[uniqueref]=[ipuser].[PropertyRef]) O
| | | | | | | | | | |--Index Seek(OBJECT:([CSSystem].[dbo].[tabItemPropValue].[idxHeadingData] AS [ipvcvmddi]), SEEK:([ipvcvmddi].[Heading]='ddiref') ORDERED FORWARD)
| | | | | | | | | |--Index Scan(OBJECT:([CSSystem].[dbo].[tabItemProperty].[idxIPUnique] AS [ipcvmddi]), ORDERED FORWARD)
| | | | | | | | |--Index Seek(OBJECT:([CSSystem].[dbo].[tabItem].[Title] AS [itcvm]), SEEK:([itcvm].[Title]='customer vm - closed' AND [itcvm].[uniqueref]=[ipcvmddi].[ItemRef] OR [itcvm].[Title]='customer vm -
| | | | | | | |--Index Seek(OBJECT:([CSSystem].[dbo].[tabItemProperty].[idxItem] AS [ipack]), SEEK:([ipack].[ItemRef]=[ipcvmddi].[ItemRef]) ORDERED FORWARD)
| | | | | | |--Index Seek(OBJECT:([CSSystem].[dbo].[tabItemPropValue].[idxHeadingData] AS [ipvack]), SEEK:([ipvack].[Heading]='value' AND [ipvack].[Data]='none' AND [ipvack].[ItemPropRef]=[ipack].[uniqueref]) ORDERED FORW
| | | | | |--Index Seek(OBJECT:([CSSystem].[dbo].[tabItemProperty].[idxItem] AS [ipcreated]), SEEK:([ipcreated].[ItemRef]=[ipack].[ItemRef]) ORDERED FORWARD)
| | | | |--Index Seek(OBJECT:([CSSystem].[dbo].[tabProperty].[Prop_Title] AS [propcreated]), SEEK:([propcreated].[Title]='time' AND [propcreated].[uniqueref]=[ipcreated].[PropertyRef]) ORDERED FORWARD)
| | | |--Filter(WHERE:(isdate(Convert([ipvcreated].[Data]))=1))
| | | |--Index Seek(OBJECT:([CSSystem].[dbo].[tabItemPropValue].[ItemPropRef] AS [ipvcreated]), SEEK:([ipvcreated].[ItemPropRef]=[ipcreated].[uniqueref]), WHERE:([ipvcreated].[Heading]='value') ORDERED FORWARD)
| | |--Index Seek(OBJECT:([CSSystem].[dbo].[tabItemProperty].[idxItem] AS [ipcli]), SEEK:([ipcli].[ItemRef]=[ipack].[ItemRef]) ORDERED FORWARD)
| |--Index Seek(OBJECT:([CSSystem].[dbo].[tabProperty].[Prop_Title] AS [propcli]), SEEK:([propcli].[Title]='cli' AND [propcli].[uniqueref]=[ipcli].[PropertyRef]) ORDERED FORWARD)
|--Index Seek(OBJECT:([CSSystem].[dbo].[tabItemPropValue].[ItemPropRef] AS [ipvcli]), SEEK:([ipvcli].[ItemPropRef]=[ipcli].[uniqueref]), WHERE:([ipvcli].[Heading]='number') ORDERED FORWARD)
I can't change the database layout but any other suggestions on the query are welcome whether directly related to the problem or not. I don't know if the text plan is readable? I use the graphical view but a screenshot of that is probably less use due to it's size?
It might be as simple as a missing join or where clause but I've looked for a long time and I can't find one.
Thanks
Andrew
December 12, 2011 at 12:18 am
Im afraid your design is wrong. The EAV (Entity , attribute, value) has been demonstrated time and time again to be inefficent.
You really need to change to a 'correct' normalized design before you *really* find out had bad they can be.
December 12, 2011 at 2:50 am
As Dave pointed out, EAV database design sucks in many ways. Fix it before you go crazy.
A possible way to mitigate the EAV pain is pivoting the attributes as deep as you can in the query plan.
Something like this:
WITH EAVProperties AS (
SELECT *
FROM (
SELECT itemprop.itemref, prop.title, itempropval.data
FROM tabproperty AS prop
INNER JOIN tabitemproperty AS itemprop
ON prop.uniqueref = itemprop.propertyref
INNER JOIN tabitempropvalue AS itempropval
ON prop.uniqueref = itempropval.itempropref
WHERE prop.title IN (
'ddi',
'cli',
'time',
'acknowledged',
'description',
'VM Monitor Staff',
'details')
AND itempropval.heading =
CASE prop.title
WHEN 'ddi' THEN 'ddiref'
WHEN 'cli' THEN 'number'
WHEN 'time' THEN 'value'
WHEN 'acknowledged' THEN 'value'
WHEN 'description' THEN 'description'
WHEN 'VM Monitor Staff' THEN 'value'
WHEN 'details' THEN 'db ref'
END
) AS Props
PIVOT( MIN(data) FOR title IN (
[ddi],
[cli],
[time],
[acknowledged],
[description],
[VM Monitor Staff],
[details])) AS pvt
)
SELECT someColumns ....
FROM tabitem item
INNER JOIN EAVProperties props
ON item.uniqueref = props.itemref
WHERE someFilters...
Hope this helps,
Gianluca
-- Gianluca Sartori
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply