SQL Server and Entity Framework Performance Improvement

Question

I'm using SQL Server and Entity Framework as ORM.

Currently I have large 5 tables with static data. Actually these table used only for data retrieval process.

I ll give good example with my project.

• This is travel related project and I have to store my hotels static data API wise (actually there are hotels booking api. They provide us hotel static data)

• I have to deal with world wide hotels data. That means more than 100,000

• If we get 1 hotel, it includes all hotel details such as facility, history..... image url like wise.

• These data I'm getting city wise

  Let's say Paris XML or Json - I have more than 1000 of hotel details in that single file. It means 10MB or 20MB file size.

  City wise there are more than 40000 rows for one API. Some api having more than 70000.

• Always end user search city wise so right now I'm storing those data city wise in my SQL Server. Because hotel wise saving those data practically slow us down.

Now I will provide my technical side details.

• I'm using Azure SQL Server and ASP.Net MVC 4 project
• Site is hosted in Azure App service
• I'm using database first approach in EF

I'll show you my database structure. It's very simple.

CREATE TABLE [dbo].[SD_HotelsMNS]
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [DestinationCode] [varchar](20) NOT NULL,
    [DestinationXML] [xml] NULL,
 CONSTRAINT [PK_SD_HotelsMNS] PRIMARY KEY NONCLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

My primary key is ID (identity & non clustered), my index is DestinationCode because I always search hotel by destination (City) in code.

These static data updating should be done 10 times a year or less.

In my project side if I want some destination static data what should I do? Is writing a linq query

  string citycode = "paris";
  ExampleEntities entities = new ExampleEntities ();
  var cityData = entities.SD_HotelsMNS.FirstOrDefault(i => i.DestinationCode.Equals(citycode));

  string xml = cityData.DestinationXML;

Now my problem is this static data retrieval part is slow.

After my site is published on Azure app services when user types Paris and start the search, then this process is getting slow.

(Right now I'm doing this for one static data set - one API that includes city wise hotels data rows 50000,each row wise include more than 500 hotels. ex paris city row - 15mb)

My questions to you are:

• Is this a bad design?
• Is there another way of doing it?
• Do I need to to performance improvement on Entity Framework side?

Answer 1

Yes this is very bad design. You are basically using SQL server as a file storage for your XML documents. There is no optimization you can do with Entity Framework that is going to speed this up.

I would suggest that you normalize the data in your XML into regular columns so you can create indexes and relationships as needed. Then you can selecting only the columns that you need and instead of 15mbs of data for 500 rows you might be looking at a few kilobytes.