2

I have a simple query:

INSERT INTO #Values
SELECT 2, MONTH(f.Datum), SUM(Quant * Price) FROM r_Fakt_Otpr_Data fd INNER JOIN r_Fakture f ON fd.FK_Faktura = f.ID_Faktura 
WHERE f.Type = 2 AND f.Stock = @stock AND f.Year = @year
GROUP BY MONTH(f.Datum)

It has about 10000 records to go through. It takes more than a minute.

If I omit GROUP BY and SUM it runs in less than a second.

Now, funny thing, if I split the query into SELECT and GROUP BY separately, using temp table, it runs in less than a second again:

CREATE TABLE #ValuesBuff
(
    Type int,
    Mnth int,
    Value float
)

INSERT INTO #ValuesBuff
SELECT 2, MONTH(f.Datum), (Quant * Price) FROM r_Fakt_Otpr_Data fd INNER JOIN r_Fakture f ON fd.FK_Faktura = f.ID_Faktura 
WHERE f.Type = 2 AND f.Stock = @stock AND f.Year = @year

INSERT INTO #Values
SELECT 2, Mnth, Sum(Vakue) FROM #ValuesBuff
GROUP BY Mnth

Why would this be the case?

What should I do to instruct SQL Server to perform better. Obviously it is GROUP BY that is the culprit, but it's fairly simple query, and i can't wrap my head around it.

Best Regards,

  • 1
    What sort of indexes do you have? – Mark Sinkinson Apr 23 '14 at 12:52
  • The SUM(Quant * Price) will require sorting - even if these columns are indexed, which will incur some cost. But knowing about the indexes you use will help to diagnose. – user353gre3 Apr 23 '14 at 13:28
  • There are indexes only on FK columns, and on Datum (i added that after the problem occurred). Why would SUM(quant * price) require sorting? – Milos Mijatovic Apr 23 '14 at 14:20
  • I know that i dont't know enough about db guts, but i am wandering how much is group by different from select? Is it totally different approach to fetching data? – Milos Mijatovic Apr 23 '14 at 14:26
2

Sounds like a bad plan to me - and possibly around statistics.

When you insert the data into a temp table, you can get statistics auto created on it.

If your statistics think there aren't many records for that combination of stock and year, it could choose a really bad plan. Try updating your statistics and seeing how it goes. Or put a better indexing strategy together, to help the join as well as the filter and grouping.

You could also post the sqlplans...

  • 2
    Yes man, it was the statistics. I used update statistics on few tables and now it's running in a blink of an eye. Thanks. – Milos Mijatovic Apr 23 '14 at 14:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.