This website requires Javascript to function properly. Please go to the setting of your web browser and enable Javascript for this website.

Ad by

最优利率和cashback可以申请特批，好信用好收入offer更好。请点链接扫码加微信咨询，Scotiabank -- Nick Zhang 6478812600。

Ad by

最优利率和cashback可以申请特批，好信用好收入offer更好。请点链接扫码加微信咨询，Scotiabank -- Nick Zhang 6478812600。

探讨一下提高数据搜索性能的方法

madeinchina(强力万能胶)

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.

(#6846824@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)

@Florida

探讨一下提高数据搜索性能的方法

Replies, comments and Discussions:

More Topics