48V 6,5Ah Li-Ion baterie

CREATE INDEX idx_store_idStore ON store (idStore);
CREATE INDEX idx_store_residence ON store (residence);
CREATE INDEX idx_store_idStore_residence ON store (idStore, residence);
CREATE INDEX idx_store_idStore_residencexd
ON store (idStore)
INCLUDE (residence);

CREATE INDEX idx_order_idStore_idOrder ON "order" (idStore, idOrder);
CREATE INDEX idx_order_order_datetime ON "order" (order_datetime);
CREATE INDEX idx_order_idStore_order_datetime
ON "order" (idStore, order_datetime)
INCLUDE (idOrder);

CREATE INDEX idx_orderitem_idOrder ON OrderItem (idOrder);
CREATE INDEX idx_orderitem_idProduct ON OrderItem (idProduct);
CREATE INDEX idx_orderitem_quantity_unit_price ON OrderItem (quantity, unit_price);
CREATE INDEX idx_orderitem_idOrder_idProduct_quantity_unit_price
ON OrderItem (idOrder, idProduct)
INCLUDE (quantity, unit_price);

CREATE INDEX idx_product_idProduct ON Product (idProduct);
CREATE INDEX idx_product_producer ON Product (producer);
CREATE INDEX idx_product_idProduct_producer
ON Product (idProduct, producer)
INCLUDE (name);

select p.idProduct, p.name, sum(oi.quantity * oi.unit_price)
from "order" o
join store s on s.idStore = o.idStore
join OrderItem oi on oi.idOrder = o.idOrder
join Product p on p.idProduct = oi.idProduct
where s.residence = 'Praha' and year(o.order_datetime) IN (2021, 2022) and p.producer = 'AEG'
group by p.idProduct, p.name
option (maxdop 1);

SET STATISTICS TIME ON;
SET STATISTICS TIME OFF;

CREATE INDEX idx_store_idStore ON store (idStore);
CREATE INDEX idx_store_residence ON store (residence);

CREATE INDEX idx_order_idStore_idOrder ON "order" (idStore, idOrder);

CREATE INDEX idx_orderitem_idOrder ON OrderItem (idOrder);
CREATE INDEX idx_orderitem_quantity ON OrderItem (quantity);

CREATE INDEX idx_customer_idCustomer ON Customer (idCustomer);
CREATE INDEX idx_customer_birthday ON Customer (birthday);

select c.idCustomer, c.lName, c.fName, sum(oi.quantity)
from "order" o
join store s on s.idStore = o.idStore
join OrderItem oi on oi.idOrder = o.idOrder
join Customer c on c.idCustomer = o.idCustomer
where s.residence = 'Znojmo' and year(c.birthday) = 1957
group by c.idCustomer, c.lName, c.fName
option (maxdop 1);

SELECT
t.name AS TableName,
i.name AS IndexName,
i.type_desc AS IndexType,
c.name AS ColumnName,
ic.key_ordinal AS ColumnOrder
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.object_id = i.object_id
INNER JOIN
sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN
sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE
i.type > 0 -- Ignore heap (type = 0)
ORDER BY
t.name, i.name, ic.key_ordinal;

CREATE INDEX idx_store_idStore ON store (idStore);
CREATE INDEX idx_store_residence ON store (residence);
CREATE INDEX idx_store_idStore_residence ON store (idStore, residence);
CREATE INDEX idx_store_idStore_residencexd
ON store (idStore)
INCLUDE (residence);

CREATE INDEX idx_order_idStore_idOrder ON "order" (idStore, idOrder);
CREATE INDEX idx_order_order_datetime ON "order" (order_datetime);
CREATE INDEX idx_order_idStore_order_datetime
ON "order" (idStore, order_datetime)
INCLUDE (idOrder);

CREATE INDEX idx_orderitem_idOrder ON OrderItem (idOrder);
CREATE INDEX idx_orderitem_idProduct ON OrderItem (idProduct);
CREATE INDEX idx_orderitem_quantity_unit_price ON OrderItem (quantity, unit_price);
CREATE INDEX idx_orderitem_idOrder_idProduct_quantity_unit_price
ON OrderItem (idOrder, idProduct)
INCLUDE (quantity, unit_price);

SELECT * INTO OrderItem_copy FROM OrderItem;
CREATE clustered INDEX idx_orderitem_idOrderxdddd ON OrderItem_copy (idOrder);

CREATE INDEX idx_orderitem_idOrder_idProduct_quantity_unit_pricesdfsdf
ON OrderItem_copy (idOrder, idProduct)
INCLUDE (quantity, unit_price);

CREATE INDEX idx_product_idProduct ON Product (idProduct);
CREATE INDEX idx_product_producer ON Product (producer);
CREATE INDEX idx_product_idProduct_producer
ON Product (idProduct, producer)
INCLUDE (name);
CREATE INDEX idx_product_producer_name ON Product (producer, name);

SELECT * INTO store_copy FROM store;

drop INDEX idx_store_copy_idStore ON store_copy;
CREATE clustered INDEX idx_store_copy_residence2 ON store_copy (residence);

select p.idProduct, p.name, sum(oi.quantity * oi.unit_price)
from "order" o
join store_copy s on s.idStore = o.idStore
join OrderItem_copy oi on oi.idOrder = o.idOrder
join Product p on p.idProduct = oi.idProduct
where s.residence = 'Praha' and year(o.order_datetime) IN (2021, 2022) and p.producer = 'AEG'
group by p.idProduct, p.name
option (maxdop 1);

SET STATISTICS TIME ON;
SET STATISTICS IO ON;
SET STATISTICS TIME OFF;

CREATE INDEX idx_store_idStore ON store (idStore);
CREATE INDEX idx_store_residence ON store (residence);

CREATE INDEX idx_order_idStore_idOrder ON "order" (idStore, idOrder);

CREATE INDEX idx_orderitem_idOrder ON OrderItem (idOrder);
CREATE INDEX idx_orderitem_quantity ON OrderItem (quantity);

CREATE INDEX idx_customer_idCustomer ON Customer (idCustomer);
CREATE INDEX idx_customer_birthday ON Customer (birthday);

select c.idCustomer, c.lName, c.fName, sum(oi.quantity)
from "order" o
join store s on s.idStore = o.idStore
join OrderItem oi on oi.idOrder = o.idOrder
join Customer c on c.idCustomer = o.idCustomer
where s.residence = 'Znojmo' and year(c.birthday) = 1957
group by c.idCustomer, c.lName, c.fName
option (maxdop 1);

SELECT
t.name AS TableName,
i.name AS IndexName,
i.type_desc AS IndexType,
c.name AS ColumnName,
ic.key_ordinal AS ColumnOrder
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.object_id = i.object_id
INNER JOIN
sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN
sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE
i.type > 0 -- Ignore heap (type = 0)
ORDER BY
t.name, i.name, ic.key_ordinal;