多次联接的性能 [英] Performance of Multiple Joins

问题描述

问候过花，

我需要查询具有许多/复杂空间条件的对象. 在关系数据库中，该数据库已转换为许多联接(可能是10个以上). 我是这个行业的新手，想知道是否要使用MS SQL Server 2008 R2或Oracle 11g或基于文档的解决方案(例如RavenDB)还是仅使用某些空间数据库(GIS)...

I need to query against objects with many/complex spacial conditions. In relational databases that is translated to many joins (possibly 10+). I'm new to this business and wondering whether to go with MS SQL Server 2008 R2 or Oracle 11g or document-based solutions such as RavenDB or simply go with some spacial database (GIS)...

有什么想法吗?

致谢

更新:谢谢大家的回答.有人会选择文档/空间数据库吗?我的数据库将包含数千万至数十亿的记录.通常是只读的.除非输入错误，否则几乎没有更新.隔夜插入，而不是那么频繁.联接表是预先预测的，但自联接(多次联接自身的表)的数目却没有.这些查询的小页面结果将在高度交互的网站上查看，因此响应时间至关重要.关于如何在MS SQL Server 2008 R2或Oracle 11g上执行的任何预测?我还担心通过添加更多服务器来提高性能，哪种服务器可扩展性更好? PostgresQL怎么样?

UPDATE: Thank you all for your answers. Would anybody opt for document/spatial databases ? My database would consist of tens of millions to few billion records. Mostly read-only. Almost no updates unless in case of mistakes in input. Overnight inserts and not that frequent. The join tables are predicted beforehand but the number of self joins (tables joining themselves multiple times) is not. Small pages of results from such queries are going to be viewed on an highly interactive website so response time is critical. Any predictions on how this can perform on MS SQL Server 2008 R2 or Oracle 11g ? I'm also concerned about boosting performance by adding more servers, which one scales better ? How about PostgresQL ?

推荐答案

构建和测试.

那是知道您的想法是否可行的唯一方法.有免费版本的Oracle，SQL Server和Teradata可下载. PostgreSQL是免费的.

That's the only way to know whether your idea is going to work. There are free versions of Oracle, SQL Server, and Teradata available for downloading. PostgreSQL is free, period.

数据库设计帮助可能不是免费的. SQL性能比其他任何单一原因更容易受到不良设计的影响.

Database design help might not be free. SQL performance suffers from bad design more than any other single cause.

昨天我在20个5000万行的表，自然键(无id号)，20个左联接，中位访问时间为40毫秒的20个表中进行了一次测试(概念验证) .使用具有慢速磁盘和2 GB RAM的商用台式计算机.

I did a test (proof of concept) yesterday (?? days are running together in my head) on 20 tables of 50 million rows, natural keys (no id numbers), 20 left joins, median access time of 40 milliseconds. Using a commodity desktop computer with slow disks and 2 gigs of RAM.

:似乎还有一个免费的Greenplum单服务器版本，仅限于两个CPU插槽，对CPU内核没有限制.对数据库大小也没有限制.我觉得需要玩几个TB.

It seems there's also a free, single-server version of Greenplum that's only constrained to two CPU sockets, no limitation on CPU cores. No limitation on database size, either. I'm feelin' the need to play with a couple of terabytes.

这篇关于多次联接的性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！