SQL Server CE optimization help needed

Question

First off, I am not a dba admin and have no formal training in SQL, I only know what I know from googling and some old half forgotten knowledge from high school.

Second, I am of the opinion, that if you can get the specific data with SQL statements then do so, instead of rummaging trough a big set of data in your program.

I want to know if my query can be optimized with the database structure that I have. The database structure cannot be changed cause it is something i inherited.

Relevant table structures (SQL Server CE)

CREATE TABLE [Klanten] //Clients
(
   [ID] INT NOT NULL IDENTITY (14,1),
   [Code] NVARCHAR(5) NOT NULL,
   [Naam] NVARCHAR(100) NOT NULL,
   [Email] NVARCHAR(100),
   [Telefoon] NVARCHAR(30),
   [Bankrekening] NVARCHAR(50),
   [BTWNummer] NVARCHAR(50),
   [BTWRegime] INT,
   [Saldo] MONEY DEFAULT 0,
   [Actief] BIT NOT NULL DEFAULT 1,
   [PrijsNiveau] TINYINT
);

CREATE TABLE [Bestellingen] //Orders
(
   [ID] INT NOT NULL IDENTITY (5235,1),
   [KlantID] INT NOT NULL,
   [LeveringsDatum] DATETIME,
   [OpmaakDatum] DATETIME NOT NULL DEFAULT getdate(),
   [Status] TINYINT NOT NULL DEFAULT 0,
   [Opmerking] NVARCHAR(200),
   [BackOrder] INT,
   [BTWRegime] INT,
   [Referentie] NVARCHAR(50)
);

CREATE TABLE [Facturen] //Invoices
(
   [ID] INT NOT NULL IDENTITY (13200,1),
   [BestelID] INT NOT NULL,
   [OpmaakDatum] DATETIME NOT NULL DEFAULT getdate(),
   [BetaalDatum] DATETIME,
   [Status] TINYINT,
   [BetaalWijze] TINYINT DEFAULT 0
);

CREATE TABLE [FactuurDetails] //Invoice line details
(
   [FactuurID] INT NOT NULL,
   [ArtikelID] INT NOT NULL,
   [Hoeveelheid] SMALLINT NOT NULL,
   [Prijs] MONEY
);

CREATE TABLE [Artikels] //Productes
(
   [ID] INT NOT NULL IDENTITY (156,1),
   [Naam] NVARCHAR(100) NOT NULL,
   [Code] NVARCHAR(5) NOT NULL,
   [GroepID] INT,
   [StandaardWinstMarge] REAL,
   [MinimumWinstMarge] REAL,
   [Voorraad] SMALLINT DEFAULT 0,
   [MinimumVoorraad] SMALLINT DEFAULT 0,
   [DoosInhoud] SMALLINT DEFAULT 1000,
   [LaatsteWijziging] DATETIME NOT NULL DEFAULT getdate(),
   [Prijs1] MONEY DEFAULT 0,
   [Prijs2] MONEY DEFAULT 0,
   [Prijs3] MONEY DEFAULT 0,
   [Prijs4] MONEY DEFAULT 0,
   [Prijs5] MONEY DEFAULT 0,
   [Prijs6] MONEY DEFAULT 0,
   [Actief] BIT NOT NULL DEFAULT 1,
   [KostPrijs] MONEY
);

I wrote a reporting tool for this database (any database really...) that takes data and presents it nicely in reports. I got a request to make a report that shows an overview of all the clients that got invoiced during a certain year and the total amount of kilos they got invoiced for ("hoeveelheid" * product "doosinhoud"). And then in the same report, per client the amount per product.

I cam up with these 2 queries

Select k.Naam, k.ID, COALESCE(sum(q.Kilos),0) as Kilos
From Klanten k
     left join Bestellingen b on b.KlantId = k.ID
     left join Facturen f on f.BestelId = b.Id
     left join (Select a.ID, fd.FactuurID As FactuurID, CAST((a.DoosInhoud / 1000.0 * fd.Hoeveelheid) AS MONEY) As Kilos
                from FactuurDetails fd
                inner join Artikels a on a.ID = fd.ArtikelID) as q on q.FactuurID = f.ID
Where f.Status = 3 AND datepart(year,f.OpmaakDatum) = #Anwser1#
Group by k.Naam, k.ID


Select a.Naam, COALESCE(sum(fd.Hoeveelheid),0) as AantalGeFactureerd
from Artikels a
     left join FactuurDetails fd on fd.ArtikelID = a.ID
     left join Facturen f on fd.FactuurID = f.ID
     left join Bestellingen b on f.BestelID = b.ID
     left join Klanten k on k.ID = b.KlantID
where k.ID = #Identifier# AND f.Status = 3 AND datepart(year,f.OpmaakDatum) = #Anwser1#
group by a.Naam

My reporting program changes the #anwser1# to userinput before the query is run (and its actually parameterized) and runs the second query for every user returned by the first query (changing in this case the #identifier# to the ID).

This is works well and fast (if you think it can be optimized, tell me).

Now the problem. Client: This looks good. but... could you also split it up in months.

So i came up with this for the second query. It gets the job done and it runs in under a second on laptop on a copy of the live database, but it seems a bit... bloated?

When you make a suggestion, could you also tell me why and maybe some names of practices or ways of thinking you are using so i can look it up and actual learn something? Thank you

Select tot.Naam as Naam, tot.Totaal as Totaal, jan.totaal as JAN, feb.totaal as FEB, mar.totaal as MAR,
       apr.totaal as APR, may.totaal as MAY, jun.totaal as JUN, jul.totaal as JUL, aug.totaal as AUG,
       sep.totaal as SEP, okt.totaal as OKT, nov.totaal as NOV, dec.totaal as DEC from
(Select a.Naam as Naam, COALESCE(sum(fd.Hoeveelheid),0) as Totaal
from Artikels a
     left join FactuurDetails fd on fd.ArtikelID = a.ID
     left join Facturen f on fd.FactuurID = f.ID
     left join Bestellingen b on f.BestelID = b.ID
     left join Klanten k on k.ID = b.KlantID
where k.ID = #Identifier# AND f.Status = 3 AND datepart(year,f.OpmaakDatum) = #Anwser1#
group by a.Naam) tot
left join
(Select a.Naam as Naam, COALESCE(sum(fd.Hoeveelheid),0) as Totaal
from Artikels a
     left join FactuurDetails fd on fd.ArtikelID = a.ID
     left join Facturen f on fd.FactuurID = f.ID
     left join Bestellingen b on f.BestelID = b.ID
     left join Klanten k on k.ID = b.KlantID
where k.ID = #Identifier# AND f.Status = 3 AND datepart(year,f.OpmaakDatum) = #Anwser1# And datepart(month,f.OpmaakDatum) = 1
group by a.Naam) jan on tot.Naam = jan.Naam
left join
(Select a.Naam as Naam, COALESCE(sum(fd.Hoeveelheid),0) as Totaal
from Artikels a
     left join FactuurDetails fd on fd.ArtikelID = a.ID
     left join Facturen f on fd.FactuurID = f.ID
     left join Bestellingen b on f.BestelID = b.ID
     left join Klanten k on k.ID = b.KlantID
where k.ID = #Identifier# AND f.Status = 3 AND datepart(year,f.OpmaakDatum) = #Anwser1# And datepart(month,f.OpmaakDatum) = 2
group by a.Naam) feb on tot.Naam = feb.Naam
left join
(Select a.Naam as Naam, COALESCE(sum(fd.Hoeveelheid),0) as Totaal
from Artikels a
     left join FactuurDetails fd on fd.ArtikelID = a.ID
     left join Facturen f on fd.FactuurID = f.ID
     left join Bestellingen b on f.BestelID = b.ID
     left join Klanten k on k.ID = b.KlantID
where k.ID = #Identifier# AND f.Status = 3 AND datepart(year,f.OpmaakDatum) = #Anwser1# And datepart(month,f.OpmaakDatum) = 3
group by a.Naam) mar on tot.Naam = mar.Naam
left join
(Select a.Naam as Naam, COALESCE(sum(fd.Hoeveelheid),0) as Totaal
from Artikels a
     left join FactuurDetails fd on fd.ArtikelID = a.ID
     left join Facturen f on fd.FactuurID = f.ID
     left join Bestellingen b on f.BestelID = b.ID
     left join Klanten k on k.ID = b.KlantID
where k.ID = #Identifier# AND f.Status = 3 AND datepart(year,f.OpmaakDatum) = #Anwser1# And datepart(month,f.OpmaakDatum) = 4
group by a.Naam) apr on tot.Naam = apr.Naam
left join
(Select a.Naam as Naam, COALESCE(sum(fd.Hoeveelheid),0) as Totaal
from Artikels a
     left join FactuurDetails fd on fd.ArtikelID = a.ID
     left join Facturen f on fd.FactuurID = f.ID
     left join Bestellingen b on f.BestelID = b.ID
     left join Klanten k on k.ID = b.KlantID
where k.ID = #Identifier# AND f.Status = 3 AND datepart(year,f.OpmaakDatum) = #Anwser1# And datepart(month,f.OpmaakDatum) = 5
group by a.Naam) may on tot.Naam = may.Naam
left join
(Select a.Naam as Naam, COALESCE(sum(fd.Hoeveelheid),0) as Totaal
from Artikels a
     left join FactuurDetails fd on fd.ArtikelID = a.ID
     left join Facturen f on fd.FactuurID = f.ID
     left join Bestellingen b on f.BestelID = b.ID
     left join Klanten k on k.ID = b.KlantID
where k.ID = #Identifier# AND f.Status = 3 AND datepart(year,f.OpmaakDatum) = #Anwser1# And datepart(month,f.OpmaakDatum) = 6
group by a.Naam) jun on tot.Naam = jun.Naam
left join
(Select a.Naam as Naam, COALESCE(sum(fd.Hoeveelheid),0) as Totaal
from Artikels a
     left join FactuurDetails fd on fd.ArtikelID = a.ID
     left join Facturen f on fd.FactuurID = f.ID
     left join Bestellingen b on f.BestelID = b.ID
     left join Klanten k on k.ID = b.KlantID
where k.ID = #Identifier# AND f.Status = 3 AND datepart(year,f.OpmaakDatum) = #Anwser1# And datepart(month,f.OpmaakDatum) = 7
group by a.Naam) jul on tot.Naam = jul.Naam
left join
(Select a.Naam as Naam, COALESCE(sum(fd.Hoeveelheid),0) as Totaal
from Artikels a
     left join FactuurDetails fd on fd.ArtikelID = a.ID
     left join Facturen f on fd.FactuurID = f.ID
     left join Bestellingen b on f.BestelID = b.ID
     left join Klanten k on k.ID = b.KlantID
where k.ID = #Identifier# AND f.Status = 3 AND datepart(year,f.OpmaakDatum) = #Anwser1# And datepart(month,f.OpmaakDatum) = 8
group by a.Naam) aug on tot.Naam = aug.Naam
left join
(Select a.Naam as Naam, COALESCE(sum(fd.Hoeveelheid),0) as Totaal
from Artikels a
     left join FactuurDetails fd on fd.ArtikelID = a.ID
     left join Facturen f on fd.FactuurID = f.ID
     left join Bestellingen b on f.BestelID = b.ID
     left join Klanten k on k.ID = b.KlantID
where k.ID = #Identifier# AND f.Status = 3 AND datepart(year,f.OpmaakDatum) = #Anwser1# And datepart(month,f.OpmaakDatum) = 9
group by a.Naam) sep on tot.Naam = sep.Naam
left join
(Select a.Naam as Naam, COALESCE(sum(fd.Hoeveelheid),0) as Totaal
from Artikels a
     left join FactuurDetails fd on fd.ArtikelID = a.ID
     left join Facturen f on fd.FactuurID = f.ID
     left join Bestellingen b on f.BestelID = b.ID
     left join Klanten k on k.ID = b.KlantID
where k.ID = #Identifier# AND f.Status = 3 AND datepart(year,f.OpmaakDatum) = #Anwser1# And datepart(month,f.OpmaakDatum) = 10
group by a.Naam) okt on tot.Naam = okt.Naam
left join
(Select a.Naam as Naam, COALESCE(sum(fd.Hoeveelheid),0) as Totaal
from Artikels a
     left join FactuurDetails fd on fd.ArtikelID = a.ID
     left join Facturen f on fd.FactuurID = f.ID
     left join Bestellingen b on f.BestelID = b.ID
     left join Klanten k on k.ID = b.KlantID
where k.ID = #Identifier# AND f.Status = 3 AND datepart(year,f.OpmaakDatum) = #Anwser1# And datepart(month,f.OpmaakDatum) = 11
group by a.Naam) nov on tot.Naam = nov.Naam
left join
(Select a.Naam as Naam, COALESCE(sum(fd.Hoeveelheid),0) as Totaal
from Artikels a
     left join FactuurDetails fd on fd.ArtikelID = a.ID
     left join Facturen f on fd.FactuurID = f.ID
     left join Bestellingen b on f.BestelID = b.ID
     left join Klanten k on k.ID = b.KlantID
where k.ID = #Identifier# AND f.Status = 3 AND datepart(year,f.OpmaakDatum) = #Anwser1# And datepart(month,f.OpmaakDatum) = 12
group by a.Naam) dec on tot.Naam = dec.Naam

Answer 1

When LEFT JOIN, don't have your right side table conditions in the WHERE clause, move them to the ON clause instead to get true left join behavior. (When in WHERE, you get regular inner join result.)

I.e. something like:

Select tot.Naam as Naam, tot.Totaal as Totaal, jan.totaal as JAN, feb.totaal as FEB, mar.totaal as MAR,
       apr.totaal as APR, may.totaal as MAY, jun.totaal as JUN, jul.totaal as JUL, aug.totaal as AUG,
       sep.totaal as SEP, okt.totaal as OKT, nov.totaal as NOV, dec.totaal as DEC from
(Select a.Naam as Naam, COALESCE(sum(fd.Hoeveelheid),0) as Totaal
from Artikels a
     left join FactuurDetails fd on fd.ArtikelID = a.ID
     left join Facturen f on fd.FactuurID = f.ID AND f.Status = 3 AND datepart(year,f.OpmaakDatum) = #Anwser1#
     left join Bestellingen b on f.BestelID = b.ID
     left join Klanten k on k.ID = b.KlantID AND k.ID = #Identifier# 
group by a.Naam) tot
... and so on ...