I have a stored procedure with 20 parameters that searches a highly normalized database, so it needs lots of joins.
我有一个包含20个参数的存储过程,用于搜索高度规范化的数据库,因此需要大量连接。
In any given search, most of the parameters are null.
在任何给定的搜索中,大多数参数都是null。
If a parameter is null, is there any way to suppress the joins that are now unnecessary to return a result?
如果参数为null,是否有任何方法可以抑制现在不必返回结果的连接?
1 个解决方案
#1
1
It sounds like you're using a join to another table for a filter, not to return any columns. You should be using "exists" instead, even outside the original context of this question, to prevent record expansion, which I am guessing you're solving by using "distinct", and for better performance. Then to answer your direct question, the best solution is to make this dynamic sql, and then only add a particular "exists" block if that parameter is not null.
听起来你正在使用连接到另一个表的过滤器,而不是返回任何列。您应该使用“存在”,甚至在此问题的原始上下文之外,以防止记录扩展,我猜您正在通过使用“distinct”来解决,并且为了更好的性能。然后回答你的直接问题,最好的解决方案是制作这个动态的sql,然后只在该参数不为null时添加一个特定的“exists”块。
#1
1
It sounds like you're using a join to another table for a filter, not to return any columns. You should be using "exists" instead, even outside the original context of this question, to prevent record expansion, which I am guessing you're solving by using "distinct", and for better performance. Then to answer your direct question, the best solution is to make this dynamic sql, and then only add a particular "exists" block if that parameter is not null.
听起来你正在使用连接到另一个表的过滤器,而不是返回任何列。您应该使用“存在”,甚至在此问题的原始上下文之外,以防止记录扩展,我猜您正在通过使用“distinct”来解决,并且为了更好的性能。然后回答你的直接问题,最好的解决方案是制作这个动态的sql,然后只在该参数不为null时添加一个特定的“exists”块。