CODEWITHSUNDEEP
c#asp.netsql-serverasp.net-mvc
Bobby Cannon
Bobby Cannon

Reputation: 6923

Is there a performance hit for using MDF SQL Server files instead of "database"?

Currently my website is written in ASP.NET Webforms using a SQL Server database. I am planning to build a ASP.NET MVC application not because it's better but because I want to learn the technology. My question is more specific to the database. I can create the database and import my SQL table via the import feature of the web interface to the SQL database or I can use a "local" database MDF file. I like the idea of using a MDF file because it will be easier to backup and deal with. My website doesn't get a lot of traffic... matter of fact I could be the only user. :) Now here is the question.

How much of a performance hit will I get by using a local SQL Server MDF file instead for my database?

Upvotes: 0

Views: 1384

Answers (5)

Astra
Astra

Reputation: 11221

The only difference beyond the normal resource limits of the Express version of SQL Server is a negligable startup cost while the SQL Express engine connects to the MDF file, does its routine checks for file integrity and transaction log stuff.

This should only happen on application start up, not for every request.

Upvotes: 0

GalacticCowboy
GalacticCowboy

Reputation: 11759

Also, don't forget that your web host has to support this as well. And since SQL Server Express is the only SKU that supports "user instance" databases, the host will have to have Express installed for you to use it as-is. OTOH, you can develop with it this way and then just deploy your database and change the connection string when you deploy to the web host.

Upvotes: 0

Mitchel Sellers
Mitchel Sellers

Reputation: 63126

Performance wise you should not see a difference.

The biggest issue is with production deployment and management. It is MUCH easier to manage a standard database, than a dynamically attached .mdf.

Upvotes: 1

Tom van Enckevort
Tom van Enckevort

Reputation: 4198

SQL Server uses a MDF file for each database on its server. There is no difference between a MDF file and a 'database' because the 'database' gets stored in a MDF file anyway.

Upvotes: 2

Wyatt Barnett
Wyatt Barnett

Reputation: 15673

Effectively none. Or really none more than having the DB run on the same box as the web site. It is still a Sql Server Express database all the same. Production-wise, you probably want the DB running on a separate box dedicated to serving databases. But code-wise, the only difference will be your connection string.

Upvotes: 3

Related Questions