sql query performance

Question

I am with a performance problem in my query I'm need some tips for gain performance i tried with the UNION ALL but only won one seconds

S

ELECT    GE.Id, V.Gid, V.EventOn, V.[Type], GE.SiteId, S.[$Refex] SiteRefex, V.Quantity, GE.IsIgnored
FROM         GoodsEvent GE INNER JOIN
                          (/* 2. SI que gerem Quantity  < 0*/ SELECT GE_SI_SO.Gid, GE_SI_SO.[Type], max(GE.EventOn) EventOn, GE_SI_SO.Quantity
                            FROM          GoodsEvent GE INNER JOIN
                                                       (SELECT     GGE.Gid, CASE WHEN SUM(GGE.Qtd) < 0 THEN 'SO' ELSE 'SI' END [Type], SUM(GGE.Qtd) Quantity
                                                         FROM          (SELECT     GE.Gid, CASE WHEN GE.[Type] = 'SI' THEN COUNT(GE.[Type]) ELSE 0 END nSI, 
                                                                                                        CASE WHEN GE.[Type] = 'SO' THEN COUNT(GE.[Type]) ELSE 0 END nSO, CASE WHEN GE.[Type] = 'SI' THEN SUM(GE.Quantity) 
                                                                                                        ELSE SUM(GE.Quantity) * - 1 END Qtd
                                                                                 FROM          GoodsEvent GE
                                                                                 WHERE      GE.IsDeleted = 0 AND GE.[Type] IN ('SI', 'SO')
                                                                                 GROUP BY GE.Gid, GE.[Type]) GGE
                                                         GROUP BY GGE.Gid
                                                         HAVING      SUM(GGE.nSI) > SUM(GGE.nSO) + 1 OR
                                                                                SUM(GGE.Qtd) < 0) GE_SI_SO ON GE.Gid = GE_SI_SO.Gid AND GE.[Type] = GE_SI_SO.[Type]
                            WHERE      GE.IsDeleted = 0
                            GROUP BY GE_SI_SO.Gid, GE_SI_SO.Quantity, GE_SI_SO.[Type]
                            UNION 
                            /* 1. Vários SI c/ ou s/ LO no meio*/ SELECT GE_BASE.Gid, 'SI' AS [Type], GE_Base.EventOn, 0 AS Quantity
                            FROM         (SELECT     ROW_NUMBER() OVER (ORDER BY GE.Gid, GE.EventOn) RowNumber, GE.Gid, GE.[Type], GE.EventOn
                            FROM         GoodsEvent GE
                            WHERE     GE.IsDeleted = 0) GE_BASE INNER JOIN
                          (SELECT     ROW_NUMBER() OVER (ORDER BY GE.Gid, GE.EventOn) RowNumber, GE.Gid, GE.[Type]
FROM         GoodsEvent GE
WHERE     GE.IsDeleted = 0) GE_O ON GE_BASE.Gid = GE_O.Gid AND 
GE_O.RowNumber = CASE GE_BASE.RowNumber WHEN 1 THEN 1 ELSE GE_BASE.RowNumber - 1 END
WHERE     GE_BASE.RowNumber <> GE_O.RowNumber AND GE_BASE.[Type] = GE_O.[Type] AND GE_BASE.[Type] = 'SI' AND GE_O.[Type] = 'SI'
UNION
/* 3. LO sem SI a preceder*/ SELECT GE.Gid, 'LO' [Type], GE.EventOn, 0 Quantity
FROM         GoodsEvent GE INNER JOIN
                          (SELECT     GE.Gid, MIN(GE.EventOn) EventOn
                            FROM          GoodsEvent GE
                            WHERE      GE.IsDeleted = 0
                            GROUP BY GE.Gid) GGE ON GE.Gid = GGE.Gid AND GE.EventOn = GGE.EventOn
/*WHERE     GE.[Type] = 'LO' AND GE.IsDeleted = 0*/ WHERE GE.[Type] <> 'SI' AND GE.IsDeleted = 0
UNION 
/*4. IG  Gids com Eventos de 'SI' Apos fecho de SiteIn */ SELECT GE.Gid, 'IG' [Type], GE.EventOn, 0 Quantity
FROM         GoodsEvent GE INNER JOIN
                          (SELECT     Gid, MIN(EventOn) AS EventOn
                            FROM          GoodsEvent AS GE
                            WHERE      GE.IsDeleted = 0
                            GROUP BY Gid) GGE ON GE.Gid = GGE.Gid AND GGE.EventOn = Ge.EventOn INNER JOIN
                      Goods G ON G.Gid = Ge.Gid INNER JOIN
                      SiteIn SI ON G.SiteIn = SI.[$Id] AND SI.Closed = 1 AND SI.ClosedOn < GE.EventOn
WHERE     GE.IsDeleted = 0) V ON GE.Gid = V.Gid AND GE.EventOn = V.EventOn AND GE.IsDeleted = 0 INNER JOIN
[Site] S ON S.[$Id] = GE.SiteId

Answer 1

Execute it while you're running SQL Server profiler. Then save the output, and insert it into the Database Engine Tuning Advisor. That will give you ideas for indexes and statistics

Answer 2

it's offcourse not simple to tune a query without knowing the details (size of your table, indexes, primary key, ...).

But looking at your query it's just way too complicated. You should just start from scratch I guess. But here are some tips that can help you:

• you have many subqueries and join in your query, but you only use one table called GoodsEvent. Think about it, you really need those joins and subqueries? If you do think you need then, try putting the result in a temp table and join with the temp table. You can reuse that temp table then in other parts of your query

• part 3 & 4 of your query: you do an inner join with the table GoodEvents where you calculate the min EventOn, but you never use this result in the main query. So the use of the subquery seems not to be useful

• Try to find different alliases for your tables, naming all of them the same makes it complicated

• you use the table GGE all through your query. This GGE table is made in the first part of the query but you reuse it in part 3 & 4, rethink that. Can't you put it in a temp table?

I hope these tips can help you a little bit, but as said before, without more details it's nearly impossible to rework this query. Maybe you can post your query execution plan?

Or if you send me you create script of the goodsEvent table and the result you want, then I can maybe try to write it.

regards guy