2

I created 2 identical schemas and a jmeter performance test to highlight the differences between an indexed and non-indexed table. All tables have clustered indexes from their primary keys.

Before adding the non-clustered indexes to the tables, I ran the performance tests to make sure the results would currently be equal. 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 cannot think of any reason that these metrics would be so different from each other. Here are the table definitions:

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)
);

I then run a stored procedure to make up a bunch of data for the not-to-be-indexed tables, then run this to make the data in the to-be-indexed table 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 then finally, I run the jmeter tests with 2000 users, 100 ramp-up seconds, and 10 loops. The thread group has 2 JDBC requests (have tried making separate thread groups for each query too) 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 anyone why the "non indexed" queries are running consistently faster than the "to be indexed" ones? I even deleted and rebuilt the database from scratch just in case I did add some indexes and forgot about it (even though I checked and there were no non-clustered indexes in the database).

note The "similar question" thing above is telling me to look at this question: Identical tables, identical query, completely different execution times but it is not the same.

closed as off-topic by Josh Darnell, Colin 't Hart, MDCCL, Michael Green, kevinsky Aug 13 at 11:59

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Josh Darnell, Colin 't Hart, MDCCL, Michael Green, kevinsky
If this question can be reworded to fit the rules in the help center, please edit the question.

  • Are the execution plans the same? Is the space identical (i.e. EXEC sp_spaceused N'dbo.LineLitem' vs. EXEC sp_spaceused N'dbo.iLineLitem')? – Dan Guzman Aug 10 at 15:08
  • @DanGuzman the query plans were the same, but I have not run this stored procedure. The "unused" column in line item is 8 whereas in iLineItem it is 136? and reserved is 2504 vs 2632? I don't know the significance of these results – ewhiting Aug 10 at 15:12
  • The different space is likely due to the different methods of loading the table. Can't say if that's the cause of different performance but I suggest you rebuild the clustered index on all tables prior to testing to level the playing field. Separately, I think you'll get better performance with a clustered primary key index on LineItem(SaleId, Id). If you also query LineItem by Id alone, add a non-clustered unique constraint or index on Id to support those queries. – Dan Guzman Aug 10 at 15:20
  • I rebuilt the indexes like you suggested but the results are still wildly different. I'm not really sure what else to do, I'll add the indexes you suggested and see if that brings the "to-be-indexed" query speeds down – ewhiting Aug 10 at 15:28
  • @DanGuzman if you were curious, it turns out that when I dropped the database and loaded both tables with the data-generating SP instead of selecting on table into another, the problem went away. I couldn't begin to speculate why, but that ended up being the fix. – ewhiting Aug 10 at 16:15