dimanche 12 juin 2016

SQL Execution Plan

I have two similar tables,

CREATE TABLE [dbo].[StockPrices] (
    [Id]        INT             IDENTITY (1, 1) NOT NULL,
    [CompanyId] INT             NOT NULL,
    [Date]      DATETIME        NOT NULL,
    [Open]      DECIMAL (18, 2) NOT NULL,
    [Close]     DECIMAL (18, 2) NOT NULL,
    [Low]       DECIMAL (18, 2) NOT NULL,
    [High]      DECIMAL (18, 2) NOT NULL,
    [Volume]    INT             NOT NULL,
    CONSTRAINT [PK_dbo.StockPrices] PRIMARY KEY NONCLUSTERED ([Id] ASC),
    CONSTRAINT [FK_dbo.StockPrices_dbo.Companies_CompanyId] FOREIGN KEY ([CompanyId]) REFERENCES [dbo].[Companies] ([Id]) ON DELETE CASCADE
);

GO
CREATE CLUSTERED INDEX [IX_CompanyId] ON [dbo].[StockPrices]([CompanyId] ASC);

GO
CREATE NONCLUSTERED INDEX [IX_Date] ON [dbo].[StockPrices]([Date] ASC);

and

CREATE TABLE [dbo].[News] (
    [Id]             INT            IDENTITY (1, 1) NOT NULL,
    [NewsProviderId] INT            NOT NULL,
    [CompanyId]      INT            NOT NULL,
    [Date]           DATETIME       NOT NULL,
    [Title]          NVARCHAR (128) NOT NULL,
    [Description]    NVARCHAR (256) NOT NULL,
    [Url]            NVARCHAR (256) NOT NULL,
    CONSTRAINT [PK_dbo.News] PRIMARY KEY NONCLUSTERED ([Id] ASC),
    CONSTRAINT [FK_dbo.News_dbo.Companies_CompanyId] FOREIGN KEY ([CompanyId]) REFERENCES [dbo].[Companies] ([Id]) ON DELETE CASCADE
);
GO

CREATE CLUSTERED INDEX [IX_CompanyId] ON [dbo].[News]([CompanyId] ASC);
GO

CREATE NONCLUSTERED INDEX [IX_Date] ON [dbo].[News]([Date] ASC);
GO

and two similar queries

select *
from news
where companyid = 1
    and date >= '01/01/2010'
    and date <= '01/31/2010'
order by date;

select *
from stockprices
where companyid = 1
    and date >= '01/01/2010'
    and date <= '01/31/2010'
order by date;

and I am getting two completely different actual execution plans

Query1: Relative to the batch: 86% SELECT (COST 0%) <- Nested Loops (Inneer Join)(Cost 0%) <- Index Seek (NonClustered) [News].[IX_Date](Cost 1%) <- Key Lookup (Clustered) [News].[IX_CompanyId](Cost 99%)

Query 2: Relative to the batch: 14% SELECT (Cost0%) <- Sort (Cost 33%) <- Clustered Index Scan (Clustered) [StockPrices]IX_CompanyId

I am not sure why? Can you advice something?

Thank you

Aucun commentaire:

Enregistrer un commentaire