I am seeing something in Sql 2008 (Express and R2) that I do not totally understand, I am hoping someone can elaborate.
BACKGROUND: 18 months ago I was given the project of "band-aid" fixing a reconciliation application that was outgrowing Microsoft Access. The database where the main table lived continually outgrew 2GB in size, causing them to split into separate files and made things increasingly difficult.
There was quite an extensive front end in access with various queries that queried queries that dumped files to process the information this department needed to process on a weekly basis. To get around this size limitation, I re-wrote the import process so that the file they receive daily gets imported into a SQL database. Then, each user has an ODBC link to the SQL Express database, and the Access database (with a few modifications) links to this 1 large table. I knew this was a quick fix kind of thing, and that was what was expected of me at the time.
This was running on a Windows XP workstation, SQL 2008 Express with 4GB RAM.
FAST FORWARD: We recently upgraded our VMWare environment, and I am looking to move this database onto a virtual machine with more resources. Yesterday I built the VM, Windows 2008 R2 OS, 8GB RAM, 4 vCPUs. I took a restore of the SQL Express database to the 2008 R2 VM machine.
I thought I would do some quick comparisons. So the largest table in the database has 13.8M rows (16 Columns). I just did select * from mydb.dbo.bigtable.
The SQL Express, Win XP machine finished 5:18 sec and the Win2k8R2, 2008R2 SQL, 8GB RAM VM returned in 5:40 sec. I would have guessed the VM would have performed better.
Does this sound correct??
Thanks in advance for any replies