I'm hoping that some of you can point me to some information that might be able to help me out with this question.
I'm getting ready to start a consulting gig with a company that monitors meter data. Collects that data and more or less acts as a third party to other companies that need that data.
I'm going to be writing stored procedures and doing some schema modifications for them. Currently they are running on SQL Server 2005 and 2008. One of their complaints is the raw speed of getting the data from the devices into SQL Server. The other complaint they had was the licensing cost.
Apparently the licensing cost is quite steep with SQL Server. Especially since the Enterprise edition is required for some of the things they need to do.
I would like to be able to determine if SQL Anywhere could possibly be a fit in a scenario such as this.
I'm still trying to get my arms wrapped around the entire architecture, but I would love to be able to come in and say "Here is how SQL Anywhere can handle it, and the cost is nowhere near SQL Server.”
The biggest thing that stands out to me though, is the raw number of rows that get fed into their database. It is in the millions of rows and approaches a billion.
Is there any reason that SQL Anywhere wouldn’t be able to physically handle a number of rows like this quickly?? Their tables are very narrow. No more then 15 or 20 columns in a table.
Any thoughts or suggestions would be greatly appreciated!!
Obviously the answer is "it depends" but let me provide a few comments.
answered 06 Jan '12, 14:15
As you are asking for personal experience, we have several productive databases with xx million rows and so far are happy with the performance. The only obstacle we have seen is, that the optimizer might switch out of a sudden its usually choosen strategy and then you will have to influence your statements accordingly (e.g. by introducing a user estimate) Furthermore point 3 of Glenns List (sequential scan) is really a situation which you want to avoid. It led me to the suggestion in the past to introduce an option with which you can prevent any statement from running if a sequential scan is choosen, but so far this option doesn't exists.
answered 09 Jan '12, 04:20
These links might help but I look forward to more complete answers from others:
Personally I am curious as to the details of the situation. When you say millions/billions are fed into the database, is that per second, minute, hour, day etc.? Does the database start with that many and then add that many each period, or is it always growing by that much each period. Is there any cleanup to delete old records? Does the database ever start empty or has it always been growing? I think these will help identify whether it is a storage or performance or both type of labor for the database.
answered 06 Jan '12, 11:15