April 7, 2008 at 8:16 am
Hello everybody,
Can anyone help me optimizing the following query.
The point is that i should treat this points and i use to do it but i´m waiting since two months a go but.... i'm still waiting :doze: and i see so much INNER JOINS that i'm completely lost :blush:
Thanks a lot everyone.
Regards,
JMSM 😉
declare @date datetime
set @date = DATEADD(mi, -1, GetDate())
declare @temp table (oid bigint)
-- produto promoção
select distinct m.ObjectoID
from Modification m WITH (NOLOCK)
INNER JOIN Produto p WITH (NOLOCK)
ON m.ObjectoID = p.ProdutoID
INNER JOIN DefProduto promo WITH (NOLOCK)
ON promo.DefProdutoID = p.DefProdutoID AND promo.TipoDefProdutoID = 4
where m.ModificationEstadoID = 0
AND m.DataCriacao < @date
AND m.TipoObjecto = 8
union
-- produto nivel 1 (simples ou bundle)
select distinct pack.ProdutoID
from Modification m WITH (NOLOCK)
INNER JOIN Produto p WITH (NOLOCK)
ON m.ObjectoID = p.ProdutoID
INNER JOIN ProdutoAssociacao pa WITH (NOLOCK)
ON pa.ProdutoComponenteID = p.ProdutoID
INNER JOIN Produto pack WITH (NOLOCK)
ON pack.ProdutoID = pa.ProdutoCompostoID
INNER JOIN DefProduto promo WITH (NOLOCK)
ON promo.DefProdutoID = pack.DefProdutoID AND promo.TipoDefProdutoID = 4
where m.ModificationEstadoID = 0
AND m.DataCriacao < @date
AND m.TipoObjecto = 8
union
-- produto nivel 2 (simples dentro de bundle)
select distinct pack.ProdutoID
from Modification m WITH (NOLOCK)
INNER JOIN Produto p WITH (NOLOCK)
ON m.ObjectoID = p.ProdutoID
INNER JOIN ProdutoAssociacao pa1 WITH (NOLOCK)
ON pa1.ProdutoComponenteID = p.ProdutoID
INNER JOIN Produto bundle WITH (NOLOCK)
ON bundle.ProdutoID = pa1.ProdutoCompostoID
INNER JOIN ProdutoAssociacao pa2 WITH (NOLOCK)
ON pa2.ProdutoComponenteID = bundle.ProdutoID
INNER JOIN Produto pack WITH (NOLOCK)
ON pack.ProdutoID = pa2.ProdutoCompostoID
INNER JOIN DefProduto promo WITH (NOLOCK)
ON promo.DefProdutoID = pack.DefProdutoID AND promo.TipoDefProdutoID = 4
where m.ModificationEstadoID = 0
AND m.DataCriacao < @date
AND m.TipoObjecto = 8
union
-- produto oportunidade
select distinct p.ProdutoID
from Modification m WITH (NOLOCK)
INNER JOIN Produto p WITH (NOLOCK)
ON p.OportunidadeID = m.ObjectoID
INNER JOIN DefProduto promo WITH (NOLOCK)
ON promo.DefProdutoID = p.DefProdutoID AND promo.TipoDefProdutoID = 4
where m.ModificationEstadoID = 0
AND m.DataCriacao < @date
AND m.TipoObjecto = 9
union
-- produto itemvenda
select distinct pack.ProdutoID
from Modification m WITH (NOLOCK)
INNER JOIN ItemVenda iv WITH (NOLOCK)
ON iv.ItemID = m.ObjectoID
INNER JOIN Produto p WITH (NOLOCK)
ON p.ProdutoID = iv.ProdutoID
INNER JOIN Oportunidade opt WITH (NOLOCK)
ON opt.OportunidadeID = p.OportunidadeID
INNER JOIN Produto pack WITH (NOLOCK)
ON pack.OportunidadeID = opt.OportunidadeID
INNER JOIN DefProduto promo WITH (NOLOCK)
ON promo.DefProdutoID = pack.DefProdutoID AND promo.TipoDefProdutoID = 4
where m.ModificationEstadoID = 0
AND m.DataCriacao < @date
AND m.TipoObjecto = 10
union
-- itemvenda / tarefa - produto simples
select distinct p.ProdutoID
from Modification m WITH (NOLOCK)
INNER JOIN ItemVenda iv WITH (NOLOCK)
ON iv.ItemID = m.ObjectoID
INNER JOIN Produto p WITH (NOLOCK)
ON p.ProdutoID = iv.ProdutoID
INNER JOIN Tarefa t WITH (NOLOCK)
ON t.ObjectoID = p.ProdutoID AND t.TipoObjecto = 8
where m.ModificationEstadoID = 0
AND m.DataCriacao < @date
AND m.TipoObjecto = 10
union
-- tarefa - produto simples
select distinct p.ProdutoID
from Modification m WITH (NOLOCK)
INNER JOIN Tarefa t WITH (NOLOCK)
ON t.TarefaID = m.ObjectoID
INNER JOIN Produto p WITH (NOLOCK)
ON p.ProdutoID = t.ObjectoID AND t.TipoObjecto = 8
where m.ModificationEstadoID = 0
AND m.DataCriacao < @date
AND m.TipoObjecto = 62
union
-- oportunidade
select distinct p.ProdutoID
from Modification m WITH (NOLOCK)
INNER JOIN Produto p WITH (NOLOCK)
ON p.OportunidadeID = m.ObjectoID
INNER JOIN DefProduto promo WITH (NOLOCK)
ON promo.DefProdutoID = p.DefProdutoID AND promo.TipoDefProdutoID = 4
where m.ModificationEstadoID = 0
AND m.DataCriacao < @date
AND m.TipoObjecto = 3
April 7, 2008 at 8:30 am
Just out of curiosity...
I see you are using the WITH (Nolock) Table hint.. Is there a valid business reason for this, because by using this hint you are forcing the select statement to read uncommitted data, which may or may not affect your results and at the very least may lead to inconsistent data.
As for helping to tune your query we might need a goodish bit more information... How about some sample data, table structures, perhaps a decent look at what the execution plans?
Thanks.
-Luke.
April 7, 2008 at 9:02 am
Hi Luke,
How can i send you the execution plan? Can i send the execution plan to a file? How can i do it?
Thanks and regards,
JMSM 😉
April 7, 2008 at 1:01 pm
You could use the following to generate an xml statement which would detail your execution plan. Also, have you attempted to use Profiler or the Database Tuning Advisor to see where your pain point may be?
SET SHOWPLAN_XML ON
GO
Your query goes here...
Go
SET SHOWPLAN_XML OFF
GO
-Luke
April 7, 2008 at 1:37 pm
Is there overlap between the unioned queries, ie, will a row be returned by more than one of them?
If not, change the unions to union All.
Union forces a distinct sort, union all does not.
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
April 8, 2008 at 7:14 am
Hello again,
Here goes de QEP attached, hope this can help.
Once more, thanks and regards,
JMSM 😉
April 9, 2008 at 8:32 am
The first thing I see in your execution plan is that you are doing a clustered index scan on the Modification table.
I'd take a look at which columns are indexed in that table and perhaps create a covering index on [ModificationEstadoID], [TipoObjecto] AND [DataCriacao] so that you can get an index seek instead of a scan, as that table is being scanned numerous times in your query...
Of course, that all comes with the "it depends" bit about the extra processing it would take to handle that index if you have a very high insert to read ratio...
Also, have you tried to run this query through the Tuning advisor and see what it gave you as suggestions?
-Luke.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply