Database Reference
In-Depth Information
Currently the performance looks like this:
Table 'CountryRegion'. Scan count 1, logical reads 4
CPU time = 16 ms, elapsed time = 118 ms.
Figure 23-6 shows the execution plan.
Figure 23-6. Query results in an execution plan that has table scans
While an in-memory table scan is certainly going to be faster than the same scan on a table stored on disk, it's
still not a good situation. Plus considering the extra work resulting from the Filter operation and the Sort operation
in order to satisfy the Merge Join that the optimizer felt it needed, this is a problematic query. So, you should add an
index to the table to speed it up.
But, you can't just run CREATE INDEX on the dbo.Address table. Instead, you'll have to drop the table, re-create it,
and then reload it with data. The table creation script now looks like this:
CREATE TABLE dbo.Address(
AddressID int IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=50000),
AddressLine1 nvarchar(60) NOT NULL,
AddressLine2 nvarchar(60) NULL,
City nvarchar(30) COLLATE Latin1_General_100_BIN2 NOT NULL,
StateProvinceID int NOT NULL,
PostalCode nvarchar(15) NOT NULL,
ModifiedDate datetime NOT NULL CONSTRAINT DF_Address_ModifiedDate DEFAULT (getdate()),
INDEX nci NONCLUSTERED (City)
) WITH (MEMORY_OPTIMIZED=ON);
Please note that I had to add a collation to the City column in order to create the index. This is because indexes
on character columns within in-memory databases support only *_BIN2 collations. You either need to change the
entire collation of your database or set the collation on a spot basis as I did earlier.
After reloading the data into the newly created table, you can try the query again. This time it ran in 15ms on my
system, much faster than it ran previously. Figure 23-7 shows the execution plan.
Search WWH ::




Custom Search