×

Loading...
Ad by
  • 技多不压身，工到自然成：安省技工证书特训班，点击咨询报名！
Ad by
  • 技多不压身，工到自然成：安省技工证书特训班，点击咨询报名！

@Calgary

今天work from home，中午上网打了一小时COD，死了无数遍。接着又上网买了200股suncor，将rolia刷了几遍，忽然让我想起很多年前有同样类似的问题 - 还是table join table 比 in 来的快。

madeinchina(强力万能胶)
本文发表在 rolia.net 枫下论坛不要以为我懒和工作轻松，昨晚可是工作到凌晨才睡觉的。

i have to create a inline table with help of a home brew function

select a.*
from fat_table a, (select * from dbo.[ConvertCommaSeparatedStringToIntTable]('399451, 403177, 386076, xxx,xxxx,xxx')) b
where a.currencycode='USD'
and a.dealtypecode='PE'
and a.id = b.item
order by a.companyName asc, a.InvestmentDate desc

/* function */
CREATE FUNCTION [dbo].[ConvertCommaSeparatedStringToIntTable]
(
@commaSeparatedString varchar(max)
)
RETURNS @TempList TABLE
(
item int
)
AS
BEGIN
DECLARE @item varchar(50), @Pos int
SET @commaSeparatedString = LTRIM(RTRIM(@commaSeparatedString))+ ','
SET @Pos = CHARINDEX(',', @commaSeparatedString, 1)
IF REPLACE(@commaSeparatedString, ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @item = LTRIM(RTRIM(LEFT(@commaSeparatedString, @Pos - 1)))
IF isnumeric(@item)=1
BEGIN
INSERT INTO @TempList (item) VALUES (cast(@item as int))
END
SET @commaSeparatedString = RIGHT(@commaSeparatedString, LEN(@commaSeparatedString) - @Pos)
SET @Pos = CHARINDEX(',', @commaSeparatedString, 1)
END
END
RETURN
END更多精彩文章及讨论，请光临枫下论坛 rolia.net
(#6847530@0)
Last Updated: 2011-8-11
This post has been archived. It cannot be replied.
Report

Replies, comments and Discussions:

  • 工作学习 / 学科技术讨论 / 探讨一下提高数据搜索性能的方法 -madeinchina(强力万能胶); 2011-8-11 {914} (#6846824@0)
    I have a big fat table having 10 million rows. This table has three major columns as CurrencyCode, DealType and ID. There are 9 distinct CurrencyCode, 5 distinct DealType and 1 million distinct Ids.I created a clustered index on CurrencyCode,DealType, ID.

    I have a SQL query as "select xxx,xxx,xxx... from this_table where CurrencyCode='USD' and DealType='PE' and ID in (1,2,3,xxx...)". Usually, number of IDs will go up to 10000 as a cap.With 10000 IDs in the query, i start to get data back in 6 seconds and get all data back in 28 seconds.In the execution plan, it shows 98% time is spent on table scanning.

    Is it possible to make the query run faster? like getting data from 2nd second? Is the order of index right.Is there other way to do the select? When i load data in this table, it is done by currency in 9 patches, but i don't really know this will help or not.

    thank you for your inputs.
    • Check www.infobright.org. -ynxie2000(ynxie2000); 2011-8-11 {154} (#6846900@0)
      我在这个公司上班，性能应该满足你的要求。
      几点注意：
      - currencycode, dealtype defined as comment 'lookup'
      - replaceIN (... 10K of them) with subquery
    • Is this Oracle? Do you have PK or UK? -handd(孩子带给我欢笑); 2011-8-11 (#6846919@0)
      • Oracle没有clustered index这种东西 -newkid(newkid); 2011-8-11 {130} (#6846977@0)
        如果是ORACLE, 这个表应该按前两列的组合做LIST分区，在ID建本地索引，而1万个随机ID做查询条件我觉得不太可能，应该想办法转成RANGE SCAN.
        • Based on my reading. It seems to clustered index the column ID would be more effecient in this case. -handd(孩子带给我欢笑); 2011-8-11 {168} (#6847010@0)
          Because the clustered index works will on the columns that contain a large number of distinct values, and if columns that are accessed sequentially, it would be better.
          • 按你这样每个ID读出来还要再读表数据再筛选其他两个条件，效率更低。 -newkid(newkid); 2011-8-11 (#6847081@0)
    • try three separate indexes instead of one... -ssy214(村长); 2011-8-11 (#6846967@0)
      • 觉的做两个index 好点，前两个条件一个，然后id单独一个index. -c1xwy(洪兴罩俺去战斗); 2011-8-11 (#6847019@0)
        • 好在哪？前两个所有组合才45种，能起什么作用？楼主的索引顺序（针对这个查询而言）是正确的。我猜测他用SQL SERVER不知道能否像ORACLE那样分区，也就是切成几大块，前两列就没必要包含在索引里面。 -newkid(newkid); 2011-8-11 (#6847091@0)
          • 是MS SQL。曾经想过把它分成9个小的table或view。但有同样类似的另一table，最后要18个，这也太难搞了。table中文怎样翻译？ -madeinchina(强力万能胶); 2011-8-11 (#6847389@0)
            • TABLE就是“表”。你在这学的IT? -newkid(newkid); 2011-8-11 (#6847398@0)
              • 不是的，只是忘记了。现在连写中文都有问题。 -madeinchina(强力万能胶); 2011-8-11 (#6847539@0)
    • 你这up to 10000的1,2,3,xxx...是从哪里获得的？建议把这个弄成subquery跟主table join。 -bdbs(不多不少); 2011-8-11 (#6847021@0)
      • 不成。这些ID是随机的，不连续。不知道把它们排列一下再搜索会不会提高速度。主要是不清楚MS SQL 是怎样用这些ID作搜索的。 -madeinchina(强力万能胶); 2011-8-11 (#6847350@0)
        • 再随机也得有个来头吧。不然你这个query怎么来呢？ -bdbs(不多不少); 2011-8-11 (#6847390@0)
          • 这些ID是OLAP drill-through来的。 -madeinchina(强力万能胶); 2011-8-11 (#6847394@0)
            • 插入到临时表再JOIN. 要是ORACLE只允许最多1000个在IN里面。 -newkid(newkid); 2011-8-11 (#6847399@0)
      • 谢谢你，table join 确实是比 in 快。想偷懒还是不行的。 -madeinchina(强力万能胶); 2011-8-11 (#6847533@0)
    • 哈哈，让我想到一个方法，现在是1秒钟就开始有数据返回。优化一下再公开。 -madeinchina(强力万能胶); 2011-8-11 (#6847456@0)
      • 如果你是要追求快速响应而不是所有数据都返回，缩小列表分成多批就行了。 -newkid(newkid); 2011-8-11 (#6847467@0)
      • 今天work from home，中午上网打了一小时COD，死了无数遍。接着又上网买了200股suncor，将rolia刷了几遍，忽然让我想起很多年前有同样类似的问题 - 还是table join table 比 in 来的快。 -madeinchina(强力万能胶); 2011-8-11 {1184} (#6847530@0)
        本文发表在 rolia.net 枫下论坛不要以为我懒和工作轻松，昨晚可是工作到凌晨才睡觉的。

        i have to create a inline table with help of a home brew function

        select a.*
        from fat_table a, (select * from dbo.[ConvertCommaSeparatedStringToIntTable]('399451, 403177, 386076, xxx,xxxx,xxx')) b
        where a.currencycode='USD'
        and a.dealtypecode='PE'
        and a.id = b.item
        order by a.companyName asc, a.InvestmentDate desc

        /* function */
        CREATE FUNCTION [dbo].[ConvertCommaSeparatedStringToIntTable]
        (
        @commaSeparatedString varchar(max)
        )
        RETURNS @TempList TABLE
        (
        item int
        )
        AS
        BEGIN
        DECLARE @item varchar(50), @Pos int
        SET @commaSeparatedString = LTRIM(RTRIM(@commaSeparatedString))+ ','
        SET @Pos = CHARINDEX(',', @commaSeparatedString, 1)
        IF REPLACE(@commaSeparatedString, ',', '') <> ''
        BEGIN
        WHILE @Pos > 0
        BEGIN
        SET @item = LTRIM(RTRIM(LEFT(@commaSeparatedString, @Pos - 1)))
        IF isnumeric(@item)=1
        BEGIN
        INSERT INTO @TempList (item) VALUES (cast(@item as int))
        END
        SET @commaSeparatedString = RIGHT(@commaSeparatedString, LEN(@commaSeparatedString) - @Pos)
        SET @Pos = CHARINDEX(',', @commaSeparatedString, 1)
        END
        END
        RETURN
        END更多精彩文章及讨论，请光临枫下论坛 rolia.net
        • 类似ORACLE里面的TABLE()函数。但ORACLE的优化器会假设返回的集合为某个尺寸（一般8K行）有时候会产生不好的计划。 -newkid(newkid); 2011-8-11 (#6847563@0)
    • Go cloud. Map reduce. -majorhomedepot(马甲后的炮); 2011-8-11 (#6848059@0)

More Topics

  • 现在社区游泳池招救生员巡逻，过一会还要不停的点人头，这工作是否很快就被AI代替了，装一个摄像头后面是AI处理系统，这样只招一个等着救人就行了，人头沉下水一定时间立马强光照射，还可以分析水下人的状况来判断是否溺水等
  • 新冠疫苗引发心肌炎? 最新研究这样看
  • 这道几何题有解吗?
  • SAM 2 Demo
  • 如何在 AWS EC2 实例上构建 WordPress 应用程序?
    • 枫下论坛主坛 /  工作学习 /  学科技术讨论