将所有排除条件放在sql查询中还是先获取所有结果然后在内存中执行排除代码？
在我的查询中，我想获取同一城市的所有用户。最终用户也可以使用此查询，以便他们可以看到同一城市的其他用户。 我查询用户表，以便它检索所有用户，甚至运行查询的用户。
现在有 2 个选项：
或者我添加一个条件来查询 user.id != (查询运行用户的 userid)
或者在显示查询结果之前处理查询结果并删除正在运行查询的用户。
我使用哪一种很重要或者有什么显着的影响吗？
注意 - 我的主查询并不像查找同一城市那么简单，而是使用 3 个表连接来访问用户想要显示的数据。为了简洁起见，我只是将城市放在这里。
In my query I want to get all users of the same city. This query will also be available to end users so that they can see other users of same city.
I query user table so it retrieves all the users even who has ran the query.
Now there are 2 options :
Either I add a condition to query user.id != (userid of query running user)
Or process the query result before displaying it and removing user who is running the query.
Does it matter or have any considerable effect which one I use?
Note - My main query is not as simple as finding same city but uses 3 table join to access the data which user wants to display. I just put city here for brevity.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
这取决于，但根据我的一般经验，如果在数据库查询中添加代码和在数据库中进行过滤的参数会导致返回的数据显着减少，这通常意味着数据库实际上能够使用这些东西来做出更好的结果内部工作集较小（不仅仅是通过网络）的执行计划通常更好。
例如，在我最近帮助某人的一个查询中，可以编写该查询来返回所有朋友对。但是，由于从应用程序的角度来看，任何特定页面上只需要特定人的朋友，因此不需要返回被丢弃的额外数据，并且查询计划本身是不同的，因为会有更小的集合在交叉连接的一侧。无论如何，我的观点是，通常你最好为数据库提供尽可能多的信息并让它从那里开始工作。
It depends, but in my general experience, if adding code in the database query and parameters going in to filter at the database results in significant reduction in data coming back, this usually means the database was actually able to use those things to make a better execution plan with a smaller working set internally (not just over the wire) and is generally better.
For instance, in a recent query I helped someone with, the query can be written to return all pairs of friends. But since, from an application point of view, only the friends of a particular person are needed on any particular page, there is no need to return extra data which is just discarded AND the query plan itself is different because there would be a smaller set on one side of a cross join. Anyway, my point is that USUALLY you are better off giving the database as much information as possible and letting it work from there.
我讨厌成为那个给出标准答案的人，但是......对这两个选项进行一些性能测试，然后选择更快的一个。如果差异不确定，请选择对未来开发人员来说更容易的一个。我猜想将其放入查询中对开发人员来说更容易，因为结果可能会在多个地方使用，并且最好在使用时处理代码签入（因此为每次使用复制检查） ）。
I hate to be the guy giving a standard answer but.... Do some performance testing with both options and pick the one that's faster. If the difference is inconclusive, pick the one that is easier for future developers. I'd guess putting it in the query is easier for developers, since the results are likely to be used in more than one place and doing the check in code is probably best handled at the time of use (hence replicating the check for each use).
您可以使用第一个选项，因为 3 个表连接 + 条件不是那么大。
You can use the first option since 3 table joins + condition is not that big.
从提供的信息来看，选择哪个选项应该不会产生任何明显的差异 - 第一个选项可能稍微更受欢迎，因为它需要从数据库检索的数据稍微少一些。
From the information supplied, it shouldn't make any noticeable difference which option is selected - the first option might be slightly preferred, since it requires slightly less data to be retrieved from the database.