[MonetDB-users] monetdb--query is very slow on table about 1 billion rows
hi, guys, i create a simple table , and the table is up to 1 billion rows, its structure as follow: create table test(Month_ID int not null, C_NAME varchar(10) not null, C_ID int not null, MSISDN varchar(15) not null, C_FLUX double not null); and the data looks like: “201201，name1, 123213, 13723213213, 12.12321" when i execute the query "select c_name,c_flux from test where c_id=**** order by c_flux desc", it takes more than 3 minute to get the result(more than 100 thousands rows returned, if limit 100 is used, the execute time is about 40s), is there any ways to optimize? Hardwares: intel xeon 2.12*8, 32G memory, 4*1T SATA(raid 0)
Hi,
could you please share the profiling output of
"TRACE select c_name,c_flux from test where c_id=**** order by c_flux desc;" and "TRACE select c_name,c_flux from test where c_id=**** order by c_flux desc limit 100;"
such that we could analyse where the time is spent?
Thanks, Stefan
On Wed, Jun 13, 2012 at 10:38:35PM +0800, liangmeng wrote:
hi, guys, i create a simple table , and the table is up to 1 billion rows, its structure as follow: create table test(Month_ID int not null, C_NAME varchar(10) not null, C_ID int not null, MSISDN varchar(15) not null, C_FLUX double not null); and the data looks like: “201201，name1, 123213, 13723213213, 12.12321" when i execute the query "select c_name,c_flux from test where c_id=**** order by c_flux desc", it takes more than 3 minute to get the result(more than 100 thousands rows returned, if limit 100 is used, the execute time is about 40s), is there any ways to optimize? Hardwares: intel xeon 2.12*8, 32G memory, 4*1T SATA(raid 0)
Live Security Virtual Conference Exclusive live event will cover all the ways today's security and threat landscape has changed and how IT managers can respond. Discussions will include endpoint security, mobile security and the latest in malware threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
hi, i found this problem while copy into monetdb tables(the same table or different tables) parallelly; only the first finished 'copy into' successed, the others failed and rollbacked;
Hi,
this is the expected behavior with concurrent conflicting access under optimistic concurrency control.
Stefan
On Sat, Jun 16, 2012 at 11:15:37AM +0800, liangmeng wrote:
hi, i found this problem while copy into monetdb tables(the same table or different tables) parallelly; only the first finished 'copy into' successed, the others failed and rollbacked;
Live Security Virtual Conference Exclusive live event will cover all the ways today's security and threat landscape has changed and how IT managers can respond. Discussions will include endpoint security, mobile security and the latest in malware threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
participants (2)
-
liangmeng -
Stefan Manegold