MS SQL query optimization

Hello, please help with query optimization there are 2 tables 

DOCUMENT([DOC_DATE], [SUM], [FIRM_ID])

And

FIRM([FIRM_ID], [NAME]), [ADDRESS])

Made a request:

SELECT @fr1 = '%' + RTRIM(@fr1) + '%';
SELECT YEAR(DOCUMENT.DOC_DATE) AS YEAR,/* FIRM.NAME AS NAME, DOCUMENT.FIRM_ID,*/ SUM(January) AS January, SUM(February) AS February,
        SUM(March) AS March, SUM(April) AS April, SUM(May) AS May,
        SUM(June) AS June, SUM(July) AS July, SUM(August) AS August,
        SUM(September) AS September, SUM(October) AS October,
        SUM(November) AS November, SUM(December) AS December 
        /*into zz_job2*/ FROM
(select DOCUMENT.DOC_DATE, DOCUMENT.FIRM_ID,
case when (MONTH(DOCUMENT.DOC_DATE))=1  then DOCUMENT.SUM else 0 end as January,
case when (MONTH(DOCUMENT.DOC_DATE))=2  then DOCUMENT.SUM else 0 end as February,
case when (MONTH(DOCUMENT.DOC_DATE))=3  then DOCUMENT.SUM else 0 end as March,
case when (MONTH(DOCUMENT.DOC_DATE))=4  then DOCUMENT.SUM else 0 end as April,
case when (MONTH(DOCUMENT.DOC_DATE))=5  then DOCUMENT.SUM else 0 end as May,
case when (MONTH(DOCUMENT.DOC_DATE))=6  then DOCUMENT.SUM else 0 end as June,
case when (MONTH(DOCUMENT.DOC_DATE))=7  then DOCUMENT.SUM else 0 end as July,
case when (MONTH(DOCUMENT.DOC_DATE))=8  then DOCUMENT.SUM else 0 end as August,
case when (MONTH(DOCUMENT.DOC_DATE))=9  then DOCUMENT.SUM else 0 end as September,
case when (MONTH(DOCUMENT.DOC_DATE))=10  then DOCUMENT.SUM else 0 end as October,
case when (MONTH(DOCUMENT.DOC_DATE))=11  then DOCUMENT.SUM else 0 end as November,
case when (MONTH(DOCUMENT.DOC_DATE))=12  then DOCUMENT.SUM else 0 end as December 
from DOCUMENT) DOCUMENT 
INNER JOIN FIRM ON DOCUMENT.FIRM_ID = FIRM.FIRM_ID
where NAME like @fr1
group by DOCUMENT.DOC_DATE

Tell me how to optimize it if the FIRMS table contains 150,000 records, and the DOCUMENTS table weighs ~50 Gb

Author: Kyubey, 2013-06-17
Source

2 answers

If the results are needed in this form, then there is no alternative, although the request should be tweaked:

SELECT  YEAR(d.doc_date),
SUM(CASE WHEN MONTH(d.doc_date) = 1 THEN d.sum ELSE 0 END),
SUM(CASE WHEN MONTH(d.doc_date) = 2 THEN d.sum ELSE 0 END),
SUM(CASE WHEN MONTH(d.doc_date) = 3 THEN d.sum ELSE 0 END),
SUM(CASE WHEN MONTH(d.doc_date) = 4 THEN d.sum ELSE 0 END),
SUM(CASE WHEN MONTH(d.doc_date) = 6 THEN d.sum ELSE 0 END),
SUM(CASE WHEN MONTH(d.doc_date) = 6 THEN d.sum ELSE 0 END),
SUM(CASE WHEN MONTH(d.doc_date) = 7 THEN d.sum ELSE 0 END),
SUM(CASE WHEN MONTH(d.doc_date) = 8 THEN d.sum ELSE 0 END),
SUM(CASE WHEN MONTH(d.doc_date) = 9 THEN d.sum ELSE 0 END),
SUM(CASE WHEN MONTH(d.doc_date) = 10 THEN d.sum ELSE 0 END),
SUM(CASE WHEN MONTH(d.doc_date) = 11 THEN d.sum ELSE 0 END),
SUM(CASE WHEN MONTH(d.doc_date) = 12 THEN d.sum ELSE 0 END)
FROM    document d
    JOIN firm f ON F.firm_id = d.firm_id
    WHERE [Name] LIKE @fr1
GROUP BY YEAR(d.doc_date)
 1
Author: renegator, 2013-06-17 10:11:46

Something like 

select sum([SUM]), extract (month from DOC_DATE) as Month
from DOCUMENTS
group by Month, FIRM_ID
inner join...

It is also possible that the problem is that in the DOCUMENTS table, the values of SUM and FIRM_ID on the hard disk are mixed with the contents of the actual documents. That is, there are only a few bytes of data needed for sampling, then a lot of KB of the document content, then again a few bytes... Then the index by SUM and FIRM_ID will help. If this is not enough, then look in the direction of OLAP

 0
Author: Михаил М, 2013-06-17 06:31:44