Quickly calculate partial amounts in a large SQL Server table

Question

I need to calculate the total number of columns before the specified date in a table that currently has over 400 thousand rows and is ready to grow further. I found that the aggregated function SUM() too slow for my purpose, because I could not get it faster than about 1500 ms for an amount of more than 50 thousand lines.

Please note that the code below is the fastest implementation I've found so far. In particular, filtering data from CustRapport and saving it in a temporary table led to a 3-fold increase in performance. I also experimented with indexes, but they usually did it slower.

I would like the feature to be at least an order of magnitude faster. Any idea on how to achieve this? I came across http://en.wikipedia.org/wiki/Fenwick_tree . However, I would prefer that storage and computation be handled in SQL Server.

CustRapport and CustLeistung are views with the following definition:

 ALTER VIEW [dbo].[CustLeistung] AS SELECT TblLeistung.* FROM TblLeistung WHERE WebKundeID IN (SELECT WebID FROM XBauAdmin.dbo.CustKunde) ALTER VIEW [dbo].[CustRapport] AS SELECT MainRapport.* FROM MainRapport WHERE WebKundeID IN (SELECT WebID FROM XBauAdmin.dbo.CustKunde) 

Thanks for any help or advice!

 ALTER FUNCTION [dbo].[getBaustellenstunden] ( @baustelleID int, @datum date ) RETURNS @ret TABLE ( Summe float ) AS BEGIN declare @rapport table ( id int null ) INSERT INTO @rapport select WebSourceID from CustRapport WHERE RapportBaustelleID = @baustelleID AND RapportDatum <= @datum INSERT INTO @ret SELECT SUM(LeistungArbeit) FROM CustLeistung INNER JOIN @rapport as r ON LeistungRapportID = r.id WHERE LeistungArbeit is not null AND LeistungInventarID is null AND LeistungArbeit > 0 RETURN END 

Execution plan:

http://s23.postimg.org/mxq9ktudn/execplan1.png

http://s23.postimg.org/doo3aplhn/execplan2.png

Answer 1

General recommendations that I can provide until you provide additional information.

My query has been updated as it was pulled from views to pull directly from tables.

 INSERT INTO @ret SELECT SUM(LeistungArbeit) FROM ( SELECT DISTINCT WebID FROM XBauAdmin.dbo.CustKunde ) Web INNER JOIN dbo.TblLeistung ON TblLeistung.WebKundeID=web.webID INNER JOIN dbo.MainRapport ON MainRapport.WebKundeID=web.webID AND TblLeistung.LeistungRapportID=MainRapport.WebSourceID AND MainRapport.RapportBaustelleID = @baustelleID AND MainRapport.RapportDatum <= @datum WHERE TblLeistung.LeistungArbeit is not null AND TblLeistung.LeistungInventarID is null AND TblLeistung.LeistungArbeit > 0 

• Get rid of the table variable. They use them, but I switch to temporary tables when I get 100 records; indexed temporary tables just work better in my experience.
• Update your choice to the above request and check if it works
• Check and make sure there are indexes for each column reference in the query. If you use the actual show execution plan, SQL Server can help determine where indexes will be useful.