II_kol_2017_18_eaukcija

-- Kolokvijum 2017-18

use e_aukcija_indeksi
go


-- Bez indeksa: 1.54
-- Sa ncl_aukcije: 0.28
-- Sa ncl_vremeZatvaranja: 0.039

CREATE NONCLUSTERED INDEX ncl_aukcije
ON ponude(idAukcije)

CREATE NONCLUSTERED INDEX ncl_vremeZatvaranja
ON aukcije(VremeZatvaranja)

create procedure sp_aktuelno
as
begin
    select naziv,
    case when aktuelnaPonuda is null then pocetnaCena else aktuelnaPonuda end as aktuelnaPonuda
    from
    (select naziv, pocetnaCena,
        (
        select max(iznosPonude)
        from ponude p
        where p.idAukcije = a.id
        ) as aktuelnaPonuda
    from aukcije a
    where vremeZatvaranja > GETDATE()
    ) as maxPonude
end

exec sp_aktuelno
go

CREATE NONCLUSTERED INDEX ncl_idAukcije
ON Aukcije(id)

CREATE NONCLUSTERED INDEX ncl_idKorisnika
ON Korisnici(id)

-- Bez indeksa: 0.92
-- Sa ncl_idAukcije: 0.68
-- Sa ncl_idKorisnik: 0.07

create procedure sp_aukcija
(
    @idAukcije int
)
as
begin
    -- DECLARE @idAukcije int
    -- SET @idAukcije = 3
    select a.naziv, a.opis, kat.naziv as kategorija,
    case when DATEDIFF(minute, getdate(), vremeZatvaranja)<0 then 0 else DATEDIFF(minute, getdate(), vremeZatvaranja) end as preostaloMinuta,
    (select count(*) from ponude p where p.idAukcije=a.id ) as brojPonuda,
    (select case when max(iznosPonude) is null then a.pocetnaCena else max(iznosPonude) end from ponude p where p.idAukcije=a.id) as aktuelnaPonuda,
    kor.username
    from aukcije a join kategorije kat
    on a.idKategorije = kat.id
    join korisnici kor on a.idProdavca=kor.id
    where a.id = @idAukcije
end

exec sp_aukcija 18068
go


-- Bez indeksa: 0.01
-- Sa klasterovanim pk na ponude: 0.1 -- ALI MANJE MEMORIJE ZAUZIMA ZBOG RID-a
    -- Sa klasterovanim pk na korisnike: 0.07 -- Jos manje memorije i brze

ALTER TABLE ponude
ADD CONSTRAINT pk_idPonude
PRIMARY KEY (id)

ALTER TABLE korisnici
ADD CONSTRAINT pk_idKorisnika
PRIMARY KEY (id)

ALTER TABLE korisnici DROP CONSTRAINT pk_idKorisnika

create procedure sp_istorija_ponuda
(
    @idAukcije int
)
as
begin
    select kor.username, p.vremePonude, p.iznosPonude
    from ponude p join korisnici kor
    on p.idKupca = kor.id
    where p.idAukcije = @idAukcije
    order by p.vremePonude
end

exec sp_istorija_ponuda 18068
go

-- INSERT

-- Posto je u pitanju INSERT komanda, pozeljno je da nad tabelom ponude imamo samo klasterovane indekse
    -- posto isti ne usporavaju izvrsavanje INSERT naredbe

-- Bez indeksa: 0.01
-- Sa ncl_???: 0.02

create procedure sp_nova_ponuda
(
    @id int, @idKupca int, @idAukcije int, @iznosPonude decimal(8,2), @vremePonude datetime
)
as
begin
    insert into ponude(id, idKupca, idAukcije, iznosPonude, vremePonude)
    values(@id, @idKupca, @idAukcije, @iznosPonude, @vremePonude)
end

exec sp_nova_ponuda 275329, 10000, 17049, 4850, '2017-12-26 08:00:00'
go

-- Bez optimizacije: 3.07
-- Sa cl_idProdavca: 2.85

-- Da bude sortiran prodavac
CREATE CLUSTERED INDEX cl_idProdavca
ON aukcije(idProdavca)

CREATE NONCLUSTERED INDEX ncl_brPozOcena
ON korisnici(brPozOcena)

CREATE NONCLUSTERED INDEX ncl_brNegOcena
ON korisnici(brNegOcena)

DROP INDEX cl_idProdavca

 create procedure sp_najbolji_korisnici
 as
 begin
     select top 100 kor.username, kor.brPozOcena, kor.brNegOcena, count(*) as brAukcija
     from korisnici kor
     join aukcije a
     on kor.id=a.idProdavca
     group by kor.username, kor.brPozOcena, kor.brNegOcena
     order by brPozOcena desc, brNegOcena asc
end

exec sp_najbolji_korisnici