Has somebody measured performance differences between "real" SQL Server database and a SQL Server Express user instances (attached .mdf database file)?
What was the conclusion?
Was the .mdf database file fast enough for your application?
views:
399answers:
1
+3
A:
.MDF type IS SQL Server...........
OK, so you want to know about how attaching a .MDF file as a user instance behaves in SQL Server Express. I've never really used that feature myself, but from all I understand, the only difference is that SQL Server Express must attach the .MDF file before first using it during a session.
So you will experience a certain delay when the first request comes in - how much that is really hard to quantify and depends on your hardware, mostly (disk speed, available RAM). It should definitely not be more than a few seconds.
Once attached, as far as I understand it, there's no difference between a SQL Server Express user instance database and a "proper" real-life SQL Server Express database. So I don't think this is a major issue in terms of performance.
marc_s
2009-09-07 14:03:18
...but don't let that stop you.
Eric
2009-09-07 14:07:28
Yep but there is a difference between a "real" SQL Server and Sql Server User Instances (.mdf files).I'm interested in performance differences.
Sad0w1nL1ght
2009-09-07 14:09:03
The attach operation also needs to start a 'child instance', and that is the real bummer. The 'parent' Express instance needs to copy a set of master/model/msdb files into the user profile, then launch a SQL server process under the user context, wait for it to start (create tempdb etc), and then communicate with it. Once started, the child instance lasts up to one hour after last access. http://msdn.microsoft.com/en-us/library/bb264564%28SQL.90%29.aspx. After the child instance is running and is connected, it behaves juts like any other instance with express restrictions.
Remus Rusanu
2009-09-07 17:13:49
@Remus: OK, so the startup delay might be more significant than I first imagined. But you agree - once the attach is done, the performance should be the same as if it's a stand-alone, full-fledged SQL Server (Express) database, right?
marc_s
2009-09-07 19:07:04