asked
Best approach for collecting statistical data from various tables
I am having many tables in my database from where I want to get various statistical data. Below is the simpler version of what I am doing now. SELECT [ID] ,[Sale] ,[Purchase] FROM ( SELECT A.[ID] ,SUM(B.[Sale]) AS [Sale] ,0 AS [Purchase] FROM A INNER JOIN B ON A.[ID] = B.[ID] WHERE B.[SaleDate] BETWEEN @StartDate AND @EndDate GROUP BY A.[ID] UNION ALL SELECT A.[ID] ,0 AS [Sale] ,SUM(C.[Purchase]) AS [Purchase] FROM A INNER JOIN C ON A.[ID] = C.[ID] WHERE B.[PurchaseDate] BETWEEN @StartDate AND @EndDate GROUP BY A.[ID] ) AS T Is this the correct way to perform this operation?
Statistical data is a little misleading for a DBA, it could also be about statistics maintained by MSSQL (which is being used for query operations, not for business analyzing ) Make sure this query is performing well: check if indexes are being used (in this case on Purchasedate) and if there's an index on the [ID] column (probably a primary, clustered index) I don't know about how much data we're talking. If this query runs on a live database and performance is an issue, consider creating a 2nd database for these type of reports. You have various options for this like mirroring with snapshot, logshipping etc.
15 People are following this question.
