SQL Server Compact - Will it perform well with heavier traffic sites? I'm using Orchard CMS, and on initial setup, the default setting is to use SQL Server Compact. I'm not too knowledgeable about different database types and performance.

So, will SQL Server Compact be able to handle a website getting 1,000+ hits? Assuming all else with the hosting provider is exceptional (bandwidth, up-time, etc).
I have not used SQL Server Compact, but because of its design, there are warnings in the SQL Server 2012 documentation about concurrency issues.

See: http://technet.microsoft.com/en-us/library/ms171845(v=sql.110).aspx  for warnings on:


Lost updates.
Inconsistent analysis (nonrepeatable reads).
Phantom reads.


If you need a free and much more functional version of SQL Server, consider using SQL Server Express.  Its limits are considerable above the Compact edition.  You can lookup its limitations.