October 17, 2016 at 9:09 am
The indexes are okay. In any case the db is the same in both tests...
October 17, 2016 at 9:20 am
The only index in your sample table was on the identity field, which is not an appropriate index for this approach. You need to create an index like below.
CREATE INDEX ix_exchangerates_currency_ddate ON exchangerates( currency, ddate DESC)
INCLUDE (rate)
The index will work best with the ddate sorted in descending order, but that is not mandatory if most of your queries will work best with it sorted ascending.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 17, 2016 at 11:39 am
On the sample data their were no indexes indeed, but on the db I tested the solutions, there are indexes on the currency id and on the date...
October 17, 2016 at 11:49 am
marc.corbeel (10/17/2016)
On the sample data their were no indexes indeed, but on the db I tested the solutions, there are indexes on the currency id and on the date...
That's very interesting Mark - can you post an execution plan representing this, please?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 17, 2016 at 12:03 pm
okay, I will, but if okay with you I will use my live db table and field names (sorry for the cryptic names).
If you prefer the earlier sample names, I can rewrite this.
Also if you want sample data I can give you this.
explanation:
table036 is the table with currencies, t036_f002 is the ISO code
table033 is the one the rates are downloaded to, with t033_f002 being the date, t033_f003 being the currency id (link with t036_f001), and t033_f004 being the rate.
CREATE TABLE [dbo].[table036](
[t036_f001] [int] NOT NULL CONSTRAINT [DF_table036_t036_f001] DEFAULT ((0)),
[t036_name_en] [varchar](50) NULL,
[t036_f002] [varchar](3) NULL,
CONSTRAINT [PK__table036__05F14B0642E2BA55] PRIMARY KEY CLUSTERED
(
[t036_f001] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[table033](
[t033_f001] [int] IDENTITY(1,1) NOT NULL,
[t033_f002] [smalldatetime] NOT NULL,
[t033_f003] [int] NOT NULL,
[t033_f004] [numeric](19, 6) NOT NULL,
PRIMARY KEY CLUSTERED
(
[t033_f001] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[table033] ADD DEFAULT (getdate()) FOR [t033_f002]
GO
ALTER TABLE [dbo].[table033] ADD DEFAULT ((0)) FOR [t033_f003]
GO
ALTER TABLE [dbo].[table033] ADD DEFAULT ((0)) FOR [t033_f004]
GO
October 17, 2016 at 12:27 pm
marc.corbeel (10/17/2016)
On the sample data their were no indexes indeed, but on the db I tested the solutions, there are indexes on the currency id and on the date...
This sounds like you have separate indexes. You need a single index and you need to include the rate.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply