I created 2 identical schemes and a jmeter performance test to highlight the differences between an indexed and non-indexed table. All tables have clustered indexes of their primary keys.
Before adding the ungrouped indexes to the tables, I ran the performance tests to make sure that the results would be the same today. My results were like this:
Queries against Table To Be Indexed:
Average: 97
Min/Max: 33 to 338
Standard Deviation: 48.69
Queries against Table To Not Be Indexed:
Average: 57
Min/Max: 32 to 317
Standard Deviation: 16.43
According to the P test, this is P <0.0001, so the differences are significant.
I can't think of any reason why these metrics would be so Different from each other. Here are the definitions in the table:
CREATE TABLE Flower
(
Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
FlowerName VARCHAR(30) NOT NULL,
UnitPrice DECIMAL(4, 2) NOT NULL
);
CREATE TABLE Sale
(
Id INTEGER NOT NULL IDENTITY(1,1) PRIMARY KEY,
SaleDate DATETIME NOT NULL DEFAULT GETDATE()
);
CREATE TABLE LineItem
(
Id INTEGER NOT NULL IDENTITY(1,1) PRIMARY KEY,
SaleId INT NOT NULL,
FlowerId INT NOT NULL,
Quantity INT NOT NULL,
CONSTRAINT FK_Sale FOREIGN KEY (SaleId) REFERENCES Sale(Id),
CONSTRAINT FK_Flower FOREIGN KEY (FlowerId) REFERENCES Flower(Id)
);
-- To be indexed:
CREATE TABLE iFlower
(
Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
FlowerName VARCHAR(30) NOT NULL,
UnitPrice DECIMAL(4, 2) NOT NULL
);
CREATE TABLE iSale
(
Id INTEGER NOT NULL IDENTITY(1,1) PRIMARY KEY,
SaleDate DATETIME NOT NULL DEFAULT GETDATE()
);
CREATE TABLE iLineItem
(
Id INTEGER NOT NULL IDENTITY(1,1) PRIMARY KEY,
SaleId INT NOT NULL,
FlowerId INT NOT NULL,
Quantity INT NOT NULL,
CONSTRAINT FK_iSale FOREIGN KEY (SaleId) REFERENCES iSale(Id),
CONSTRAINT FK_iFlower FOREIGN KEY (FlowerId) REFERENCES iFlower(Id)
);
Then I execute a stored procedure to create a lot of data for the tables that will not be indexed, then I execute this so that the data in the table that will be indexed are the same:
-- Replicating data between tables
SET IDENTITY_INSERT dbo.iSale ON;
INSERT INTO iSale (Id, SaleDate)
SELECT Id, SaleDate FROM Sale;
SET IDENTITY_INSERT dbo.iSale OFF;
SET IDENTITY_INSERT dbo.iLineItem ON;
INSERT INTO iLineItem (Id, SaleId, FlowerId, Quantity)
SELECT Id, SaleId, FlowerId, Quantity FROM LineItem
SET IDENTITY_INSERT dbo.iLineItem OFF;
And finally, I ran the jmeter tests with 2000 users, 100 seconds of acceleration and 10 loops. The thread group has 2 JDBC requests (you have also tried to create separate thread groups for each query) with the following queries:
-- JDBC Request: Query against non indexed tables:
SELECT
s.Id AS SaleId,
li.Quantity AS Quantity,
f.FlowerName + 's' AS Flowers,
li.Quantity * f.UnitPrice AS LineItemPrice,
s.SaleDate AS SaleDate
FROM Sale s
JOIN LineItem li
ON li.SaleId = s.Id
JOIN Flower f
ON f.Id = li.FlowerId
WHERE s.SaleDate BETWEEN '2019-03-01' AND '2019-04-01'
ORDER BY s.SaleDate, s.Id;
-- JDBC Request: Query against indexed tables (not indexed yet)
SELECT
s.Id AS SaleId,
li.Quantity AS Quantity,
f.FlowerName + 's' AS Flowers,
li.Quantity * f.UnitPrice AS LineItemPrice,
s.SaleDate AS SaleDate
FROM iSale s
JOIN iLineItem li
ON li.SaleId = s.Id
JOIN iFlower f
ON f.Id = li.FlowerId
WHERE s.SaleDate BETWEEN '2019-03-01' AND '2019-04-01'
ORDER BY s.SaleDate, s.Id;
Is it immediately obvious to someone why "non-indexed" queries run faster than "indexed" ones? I even deleted and rebuilt the database from scratch just in case did I added some indexes and forgot it (although I checked it and there were no indexes not grouped in the database).
Note The question of the "similar question" above tells me to examine this question: identical tables, identical queries, completely different execution times, but it is not the same.
