Magento can handle any number of products, the problem comes down to server performance and how to manage that many products in a catalog from a business perspective. This is why we are now releasing our parent products as online solutions, SaaS MDM, SaaS ETL and SaaS Core to help small businesses. These are MDM and ETL tools that automate the process from 100s or products to 10s of millions of products as they are built on enterprise technology, pulling/pushing data between erp, crm, pos, xml, http, csv, ftp, other shopping carts, databases, etc. This means that less staff and less time is required to manage an increase in product and order management, this is the reason companies use MDM and ETL.
On a performance note, there are tools such as Magmi however these use direct database updates for product imports, you need to make sure that tools using direct database updates (bypass the Magento API) are perfectly configured and tested thoroughly as issues do not show up initially. For this reason we primarily use internal APIs to guarantee product import and also not be affected by adding extensions, custom code or version upgrades, scaling loads to hosting performance.
Managing the product catalog in Magento with any more than a few hundred products becomes unmanageable, impossible for grouped or configurable products, the more automated product management is the more products that can be managed. Using MDM and ETL tools we have worked with stores having 100s of thousands of products who had limited staff, without these tools you can only manage a few thousand products and still have enough space to handle changes and actually run a business.
To my knowledge, with all the sites I have worked on, Magento has no limit. The limit will be the server capacity, you can look at CDN’s like cacheFly etc. that will help speed things up, but you are probably going to want a VPS with some beefy RAM. Separating the DB is a good option also. There a loads of tuts and info on how to speed Magento out there, but contact your web dev team - they should be able to help.