Exists « JUST ATPKING'S BLOGS

今天在做测试的时候，偶尔和同学聊起了这样一个语句

SELECT someColumn

FROM Users t

WHERE

EXISTS(SELECT 1 FROM master_branch mb where mb.MasterCode = ’10000′ and mb.branchCode = t.CompanyCode)

因为Users 比较大，大概几百万条数据，所以很自然的将CompanyCode做了索引。

但是如果一运行的时候，发现其慢无比，用Explain Plan 看了一下，发现索引并没有被用到，而是直接扫描了全表

此处在找Users 的时候没有用到索引

经研究后发现，如果直接在exists 将表再次引用，再次做表连接的话，就ok了

将上面的SQL语句改为如下

SELECT someColumn

FROM Users t

WHERE

EXISTS(SELECT 1 FROM master_branch,Users t1 mb where mb.MasterCode = ’10000′ and mb.branchCode = t1.CompanyCode)

可以看出，走的是索引了,这样，查询在0.1 秒之内就能完成了

Tags: Exists, Oralce, SQL优化

今天在做小项目的时候,有个需求,是比较List 中的对象是否存在过

而比较的对象是通过新建得来的(反序列化)

正当我使用Contains做比较的时候,

就发现问题了.

以下是测试代码

[TestMethod()]
public void RemoveDiscendantPowerTest() {
    Power Parent = new Power
    {
        Alias = "testRoot",
        CanDelete = false,
        Description = "测试根目录",
        id = 100,
        PowerName = "测试根目录",
        Childs = new List<Power>
        {
            new Power{
                Alias = "testBranch1",
                CanDelete = true,
                Description = "枝干节点1",
                id = 100100,
                PowerName = "枝干节点1",
                Childs = new List<Power>{
                    new Power{
                     Alias = "leaf1",
                     CanDelete = true,
                     Description = "叶子节点1",
                     id = 100100100,
                     PowerName = "叶子节点1"
                    },
                    new Power{
                     Alias = "leaf2",
                     CanDelete = true,
                     Description = "叶子节点2",
                     id = 100100101,
                     PowerName = "叶子节点2"
                    }
                }
            },
            new Power{
                Alias ="testBranch2",
                CanDelete = true,
                Description = "枝干节点2",
                id = 100101,
                PowerName = "枝干接点2",
                Childs = new List<Power>{
                    new Power{
                     Alias = "leaf3",
                     CanDelete = true,
                     Description = "叶子节点3",
                     id = 100101100,
                     PowerName = "叶子节点3"
                    },
                    new Power{
                     Alias = "leaf4",
                     CanDelete = true,
                     Description = "叶子节点4",
                     id = 100101101,
                     PowerName = "叶子节点4"
                    }
                }
                
            }
        }
    }; // TODO: 初始化为适当的值
    Power Discendant = new Power{
                     Alias = "leaf4",
                     CanDelete = true,
                     Description = "叶子节点4",
                     id = 100101101,
                     PowerName = "叶子节点4"
                    };
    // TODO: 初始化为适当的值
 
 
    Assert.IsTrue(Parent.Childs[1].Childs.Contains(Discendant), "测试失败,Contains 不能抓出");
 
 
 
 
}

测试结果如下:

结论是,新建的一模一样的对象却在Contains 中返回的false,未能通过测试

为什么会出现这种问题呢?

答案很简单:Contains比较的是存储的内存地址是否相同,而不是值相同.

我们更改下测试,就能发现问题了

/// <summary>
///RemoveDiscendantPower 的测试
///</summary>
[TestMethod()]
public void RemoveDiscendantPowerTest() {
    Power Parent = new Power
    {
        Alias = "testRoot",
        CanDelete = false,
        Description = "测试根目录",
        id = 100,
        PowerName = "测试根目录",
        Childs = new List<Power>
        {
            new Power{
                Alias = "testBranch1",
                CanDelete = true,
                Description = "枝干节点1",
                id = 100100,
                PowerName = "枝干节点1",
                Childs = new List<Power>{
                    new Power{
                     Alias = "leaf1",
                     CanDelete = true,
                     Description = "叶子节点1",
                     id = 100100100,
                     PowerName = "叶子节点1"
                    },
                    new Power{
                     Alias = "leaf2",
                     CanDelete = true,
                     Description = "叶子节点2",
                     id = 100100101,
                     PowerName = "叶子节点2"
                    }
                }
            },
            new Power{
                Alias ="testBranch2",
                CanDelete = true,
                Description = "枝干节点2",
                id = 100101,
                PowerName = "枝干接点2",
                Childs = new List<Power>{
                    new Power{
                     Alias = "leaf3",
                     CanDelete = true,
                     Description = "叶子节点3",
                     id = 100101100,
                     PowerName = "叶子节点3"
                    },
                    new Power{
                     Alias = "leaf4",
                     CanDelete = true,
                     Description = "叶子节点4",
                     id = 100101101,
                     PowerName = "叶子节点4"
                    }
                }
                
            }
        }
    }; // TODO: 初始化为适当的值
    Power Discendant = new Power{
                     Alias = "leaf4",
                     CanDelete = true,
                     Description = "叶子节点4",
                     id = 100101101,
                     PowerName = "叶子节点4"
                    };
    // TODO: 初始化为适当的值
 
 
    //Assert.IsTrue(Parent.Childs[1].Childs.Contains(Discendant), "测试失败,Contains不能抓出");
 
 
 
    Power test = Parent.Childs[1].Childs[1];
 
    Assert.IsTrue(Parent.Childs[1].Childs.Contains(test), "测试失败,甚至连内存引用都扯淡");
 
 
}

结果为

很显然,当我直接从Parent中取得一个对象的时候,Contains就会抓住这个对象

那么,如果说我需要值的比较的话,怎样做到呢?

很简单,我们可以定义一个predicate 委托来指定怎样进行比较:

/// <summary>
///RemoveDiscendantPower 的测试
///</summary>
[TestMethod()]
public void RemoveDiscendantPowerTest() {
    Power Parent = new Power
    {
        Alias = "testRoot",
        CanDelete = false,
        Description = "测试根目录",
        id = 100,
        PowerName = "测试根目录",
        Childs = new List<Power>
        {
            new Power{
                Alias = "testBranch1",
                CanDelete = true,
                Description = "枝干节点1",
                id = 100100,
                PowerName = "枝干节点1",
                Childs = new List<Power>{
                    new Power{
                     Alias = "leaf1",
                     CanDelete = true,
                     Description = "叶子节点1",
                     id = 100100100,
                     PowerName = "叶子节点1"
                    },
                    new Power{
                     Alias = "leaf2",
                     CanDelete = true,
                     Description = "叶子节点2",
                     id = 100100101,
                     PowerName = "叶子节点2"
                    }
                }
            },
            new Power{
                Alias ="testBranch2",
                CanDelete = true,
                Description = "枝干节点2",
                id = 100101,
                PowerName = "枝干接点2",
                Childs = new List<Power>{
                    new Power{
                     Alias = "leaf3",
                     CanDelete = true,
                     Description = "叶子节点3",
                     id = 100101100,
                     PowerName = "叶子节点3"
                    },
                    new Power{
                     Alias = "leaf4",
                     CanDelete = true,
                     Description = "叶子节点4",
                     id = 100101101,
                     PowerName = "叶子节点4"
                    }
                }
                
            }
        }
    }; // TODO: 初始化为适当的值
    Power Discendant = new Power{
                     Alias = "leaf4",
                     CanDelete = true,
                     Description = "叶子节点4",
                     id = 100101101,
                     PowerName = "叶子节点4"
                    };
    // TODO: 初始化为适当的值
 
 
    //Assert.IsTrue(Parent.Childs[1].Childs.Contains(Discendant), "测试失败,Contains不能抓出");
 
 
 
    //Power test = Parent.Childs[1].Childs[1];
 
    //Assert.IsTrue(Parent.Childs[1].Childs.Contains(test), "测试失败,甚至连内存引用都扯淡");
    Assert.IsTrue(Parent.Childs[1].Childs.Exists(delegate(Power t) {
        return t.id == 100101101;
    }));
 
}

(此处使用匿名委托代替了显式声明一个Predicate 委托)

测试轻松通过

ok,委托的比较执行成功.

结论

================================

List<T> 中的Contains 是对对象的"内存检测",

如果想查看List<T> 中是否有个对象的值跟你声明的对象的值相同

则我们需要Exists 方法,且实现一个Predicate 委托来指定比较的方式

================================

Tags: C#, Contains, Exists, List

一	二	三	四	五	六	日
« 八
		1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29	30

JUST ATPKING'S BLOGS

Tech && Story

Posts Tagged Exists

关于Exists 里使用外面的表的索引问题

标签云

搜索河蟹社会

近期评论

分类目录

友情链接

功能