Sql-server – Searching from Database having billion records, which database should i use

database-designperformancequery-performancesql server

We are having 1 billion phone numbers and we have to search them against numbers which we send for promotion to filter out which are not promotional registered. That is we have lets say 1 million record which we need to search against 1 billion database records.

What would be best and optimized way to search these records and which database should we use?

We are currently using SQL Server and in a batch of 10000 searches it gives result in 20 minutes.

We want real time searching.

Best Answer

You could use a parallel system such as Azure SQL DW. This would basically split your data up into sixty parts, and should be able to optimise just as well as your regular SQL database, but spreading the work out across sixty databases. You would still need to make sure that your design was optimal, in regard to table structures and indexing, but you should be able to shrink the size of your problem down by a large factor. Your 20 minutes could become half a minute quite easily.

Of course, there are probably good ways to tune your current system.