August 30, 2016 at 12:03 pm
Chatter around the office water cooler is that MSSQL is not great for driving search intensive applications, and that something like Postgres is better for some reason.
Something like searching a big customer table, where there will be some character string searches, such as key words in description column. SQL can use full-text search in this case.
Are there any "generalities" about 1 flavor of database being better running a search engine compared to others ??
August 30, 2016 at 12:41 pm
I would say that's generally true. SQL Server, or relational database engines in general, are ideal for applications where the entities are normalized tables and data access patterns are predefined. If you're wanting to build a text keyword search engine or store binary blobs or JSON documents, then there better NoSQL alternatives. Likewise the NoSQL alternatives are no good for transactional applications like CRM, inventory, or accounting.
However, I see you're posting in a SQL Server 2008 forum. Versions 2012, 2014, and 2016 have introduced many new features for working with non-relational data and NoSQL queries.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 30, 2016 at 1:55 pm
Thanks, we're currently running SQL 2005 & 2008 on our servers.
Current licensing costs for SQL have not justified upgrading, since we have a fairly low volume, low traffic web application.
You think a current version of SQL would give better search performance ?
August 30, 2016 at 2:15 pm
I don't have enough Postgres experience to say how it compares to SQL Server but if you know what you're doing you can make a search-intense workload move pretty quick even when there's complex patterned based searches involved. In addition to Full Text search there's some cool tricks you can do with Filtered Indexes and Indexed Views if you know what you're doing.
Based on what you've said there's no clear advantage to upgrading to a newer version of SQL Server. That said, I think 2012 would be an improvement simply because 2008 won't be supported for much longer and T-SQL in 2012 is much more powerful. 2014 introduces in-memory and a new cardinality estimator which can make lots of things faster.
-- Itzik Ben-Gan 2001
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply