August 2008 - users-list

[MonetDB-users] Proofreading your professional documents
by Richard Hounslow 24 Feb '24

24 Feb '24

Dear Professor/Lecturer Please excuse us for contacting you directly. We are offering an efficient service to help you ensure your academic and professional work is written in perfect English. We will check the grammar and style of your work and return it to you to meet your requirements and deadlines. Proof-Reading-Service.com can provide you with a professional proofreading service at a very reasonable rate. All our proofreaders are highly qualified native English speakers. Many work as leading academics in their fields and all have extensive experience of proofreading to the highest standards. If you are interested in our service, please take a look at our website: www.proof-reading-service.com All you have to do is send us your document as a word attachment with the deadline and we will guarantee delivery of a perfectly written document to give you complete confidence when you submit your work. The fee is worked out on a flat rate (£6.95 per thousand words or 0.695 pence per word), so you know exactly how much the proofreading will cost in advance. Once we have received your document(s) we will confirm the word count, the price and the deadline. If you have not heard from us within 2 hours during normal business hours after you have sent your work please resend it from a different email account or send the file to proofreadings(a)gmail.com. We look forward to hearing from you! Yours faithfully Proof-Reading-Service.com

2 2

[MonetDB-users] batcalc.ifThenElse:requires bats of identical size
by Rick Spickelmier 28 Aug '08

28 Aug '08

Hi! I am running into this error: batcalc.ifThenElse:requires bats of identical size My server (running on Windows XP 64): # MonetDB server v5.6.0, based on kernel v1.24.0 # Serving database 'demo' # Compiled for i686-pc-win32/64bit with 32bit OIDs dynamically linked The following recreates the problem: CREATE TABLE test1 (col1 INTEGER); INSERT INTO test1 VALUES (null); INSERT INTO test1 VALUES (null); SELECT CASE WHEN col1=1 THEN 'ONE' END AS "pc1" FROM test1 GROUP BY "pc1"; Any ideas? Obviously the above isn't too interesting, but it a simplified test case from a more complex query on a much larger dataset. Seems to be related to GROUP BY on a CASE... Thanks! Rick +----------------------------------------------------------------------- ------+ | mal | +======================================================================= ======+ | function user.s0_3():void; | | _1:bat[:oid,:int]{rows=1:lng} := sql.bind("voc","test2","col1",0); | | _6:bat[:oid,:int]{rows=1:lng} := sql.bind("voc","test2","col1",1); | | _8{rows=2:lng} := algebra.kunion(_1,_6); | | _1:bat[:oid,:int]{rows=1:lng} := nil; | | _6:bat[:oid,:int]{rows=1:lng} := nil; | | _9:bat[:oid,:int]{rows=1:lng} := sql.bind("voc","test2","col1",2); | | _11{rows=2:lng} := algebra.kdifference(_8,_9); | | _8{rows=2:lng} := nil; | | _12{rows=3:lng} := algebra.kunion(_11,_9); | | _11{rows=2:lng} := nil; | | _9:bat[:oid,:int]{rows=1:lng} := nil; | | _13:bat[:oid,:oid]{rows=1:lng} := sql.bind_dbat("voc","test2",1); | | _14{rows=1:lng} := bat.reverse(_13); | | _13:bat[:oid,:oid]{rows=1:lng} := nil; | | _15{rows=3:lng} := algebra.kdifference(_12,_14); | | _14{rows=1:lng} := nil; | | _17{rows=3:lng,tlb>=0@0:oid,tub<1@0:oid} := algebra.markT(_15,0@0); | | _15{rows=3:lng} := nil; | | _18{rows=3:lng,hlb>=0@0:oid,hub<1@0:oid} := bat.reverse(_17); | | _17{rows=3:lng,tlb>=0@0:oid,tub<1@0:oid} := nil; | | _19{rows=3:lng,hlb>=0@0:oid,hub<1@0:oid} := algebra.join(_18,_12); | | _12{rows=3:lng} := nil; | | _20:bat[:oid,:bit]{rows=3:lng,hlb>=0@0:oid,hub<1@0:oid} := | : batcalc.==(_19,1); | | _23{rows=3:lng,hlb>=0@0:oid,hub<1@0:oid} := algebra.project(_18,nil:str); | | _24:bat[:oid,:str]{rows=3:lng,hlb>=0@0:oid,hub<1@0:oid} := | : batcalc.ifthenelse(_20,"ONE",_23); | | _20:bat[:oid,:bit]{rows=3:lng,hlb>=0@0:oid,hub<1@0:oid} := nil; | | _23{rows=3:lng,hlb>=0@0:oid,hub<1@0:oid} := nil; | | (ext36,grp34):= group.new(_24); | | _24:bat[:oid,:str]{rows=3:lng,hlb>=0@0:oid,hub<1@0:oid} := nil; | | _27 := bat.mirror(ext36); | | ext36 := nil; | | _28 := algebra.join(_27,_19); | | _27 := nil; | | _19{rows=3:lng,hlb>=0@0:oid,hub<1@0:oid} := nil; | | _29:bat[:oid,:bit] := batcalc.==(_28,1); | | _28 := nil; | | _30{rows=3:lng,hlb>=0@0:oid,hub<1@0:oid} := algebra.project(_18,nil:str); | | _18{rows=3:lng,hlb>=0@0:oid,hub<1@0:oid} := nil; | | _31:bat[:oid,:str] := batcalc.ifthenelse(_29,"ONE",_30); | | _29:bat[:oid,:bit] := nil; | | _30{rows=3:lng,hlb>=0@0:oid,hub<1@0:oid} := nil; | | _32 := sql.resultSet(1,1,_31); | | sql.rsColumn(_32,"voc.","pc1","char",0,0,_31); | | _31:bat[:oid,:str] := nil; | | _37 := io.stdout(); | | sql.exportResult(_37,_32,""); | | end s0_3; | +----------------------------------------------------------------------- ------+ Rick Spickelmier | CTO | Success Metrics, Inc. | w (415) 738-2078 | ricks(a)successmetricsinc.com

3 3

[MonetDB-users] upgrading ...
by ewilde 24 Aug '08

24 Aug '08

hi, i have updated my monet installtion from "june 2008" to "current" on Aug 19, using YAM on fedora 9. i tried to run monet-server: mserver5 --dbinit="include sql;" --set gdk_vm_minsize=1000000000000 and i have got: # MonetDB server v5.7.0, based on kernel v1.25.0 # Serving database 'demo', using 2 threads # Compiled for x86_64-redhat-linux-gnu/64bit with 64bit OIDs dynamically linked # Copyright (c) 1993-2008 CWI, all rights reserved # Visit http://monetdb.cwi.nl/ for further information #warning: please don't forget to set your vault key! #(see /etc/monetdb5.conf) # Listening for connection requests on mapi:monetdb://127.0.0.1:50000/ #WARNING: LoaderException:loadLibrary:sql: loader error lib_logger.so.0: cannot open shared object file: No such file or directory !MALException:MAL.getAddress:address of 'sql.SQLsession' not found !MALException:MAL.getAddress:address of 'sql.SQLsession2' not found !MALException:MAL.getAddress:address of 'sql.SQLinitEnvironment' not found !MALException:MAL.getAddress:address of 'sql.SQLcommit' not found !MALException:MAL.getAddress:address of 'sql.SQLrollback' not found !MALException:MAL.getAddress:address of 'sql.SQLstatement' not found !MALException:MAL.getAddress:address of 'sql.SQLstatementOut' not found !MALException:MAL.getAddress:address of 'sql.SQLassert' not found !MALException:MAL.getAddress:address of 'sql.SQLassertInt' not found !MALException:MAL.getAddress:address of 'sql.SQLassertLng' not found !MALException:MAL.getAddress:address of 'sql.setVariable' not found !MALException:MAL.getAddress:address of 'sql.getVariable' not found !MALException:MAL.getAddress:address of 'sql.mvc_logfile' not found !MALException:MAL.getAddress:address of 'sql.mvc_next_value' not found !MALException:MAL.getAddress:address of 'batsql.mvc_bat_next_value' not found !MALException:MAL.getAddress:address of 'sql.mvc_get_value' not found !MALException:MAL.getAddress:address of 'sql.mvc_restart_seq' not found !MALException:MAL.getAddress:address of 'sql.mvc_bind_dbat_wrap' not found !MALException:MAL.getAddress:address of 'sql.mvc_bind_idxbat_wrap' not found !MALException:MAL.getAddress:address of 'sql.mvc_bind_wrap' not found . . .[lines were removed because the message would not been sent otherwise] . . !MALException:MAL.getAddress:address of 'mat.mat_bind_idxbat_wrap' not found !MALException:MAL.getAddress:address of 'mat.inc_parts_wrap' not found !MALException:MAL.getAddress:address of 'mat.send_part_wrap' not found !MALException:MAL.getAddress:address of 'mat.get_part_wrap' not found !MALException:MAL.getAddress:address of 'mat.print_part_wrap' not found !MALException:MAL.getAddress:address of 'constraints.Cparts' not found !TypeException:user.main[1]:'sql.prelude' undefined in: _1:void := sql.prelude() > this file lib_logger.so.0 (which is actually a link to lib_logger.so.0.0.0) exists in: /usr/lib64/MonetDB5/lib thanks in advanced for any help, ewilde.

3 2

[MonetDB-users] test 2
by ewilde 24 Aug '08

24 Aug '08

1 0

[MonetDB-users] C# odbc connection
by Aviad Harell 21 Aug '08

21 Aug '08

Hi there, I'm playing here with MonetDB. I've downloaded the monetDb sql DB and the odbc driver. With the console I succeeded to connect and query the voc sample db, which is great. I also succeeded to import data to excel from the MonetDb via odbc as shown on the website. Now, I want to connect to the monetDB via C# and odbc driver. How can I do that? Thanks, Aviad

2 1

[MonetDB-users] C# odbc connection
by Aviad Harell 21 Aug '08

21 Aug '08

Hi there, I'm playing here with MonetDB. I've downloaded the monetDb sql DB and the odbc driver. With the console I succeeded to connect and query the voc sample db, which is great. I also succeeded to import data to excel from the MonetDb via odbc as shown on the website. Now, I want to connect to the monetDB via C# and odbc driver. How can I do that? Thanks, Aviad

1 0

[MonetDB-users] C# odbc connection
by Aviad 20 Aug '08

20 Aug '08

Hi there, I'm playing here with MonetDB. I’ve downloaded the monetDb sql DB and the odbc driver. With the console I succeeded to connect and query the voc sample db, which is great. I also succeeded to import data to excel from the MonetDb via odbc as shown on the website. Now, I want to connect to the monetDB via C# and odbc driver. How can I do that? Thanks, Aviad -- View this message in context: http://www.nabble.com/C--odbc-connection-tp19073114p19073114.html Sent from the monetdb-users mailing list archive at Nabble.com.

1 0

Re: [MonetDB-users] Order of WHERE clause has significant performance impact
by Ross Bates 15 Aug '08

15 Aug '08

On Tue, Aug 12, 2008 at 08:31:22PM -0500, Ross Bates wrote: > Hi All - I have 2 queries which run very different based on how I > order the WHERE clause. > > When I put the larger filter first, it runs for 3.5 seconds. > > Query 1: > > select c5, c6, sum(sale) from sobi.sample > where > c5 in (5253,5110,5375,5268,5257,5266,5243,5256,5252,5361,5379,5363,5386,5367,5240,5241,5261,5249,5245,5260,5247,5264,5354,5250,5373,5366,5246,5356,5365,5255,5262,5259,5251,5369,5357,5269,5364,5380,5244,5248,5242,5254,5358,5360,5263,5362,5374,5378,5370,5294,5339,5272,5277,5275,5295,5340,5273,5276,5270,5061,5081,5079,5080,5066,5056,5073,5060,5070,5099,5101,5100,5067,5063,5077,5072,5062,5075,5083,5086,5071,5095,5058,5087,5090,5102,5084,5098,5076,5059,5074,5094,5091,5065,5069,5068,5104,5109,5107,5108,5013,5019,5022,5010,5009,5023,5011,5024,5012,5021,5007,5008,5025,5020,5055,5006,5036,5038,5054,5032,5033,5031,5039,5035,5026,5027,5030,5200,5043,5005,5041,5049,5042,5052,5040,5053,5045,5047,5048,5050,5051,5044,5046) > and c6 in (5182) > group by c5, c6; > > > When I reverse the order of the WHERE clause, it runs in about .4 seconds. > > > Query 2: > > c5, c6, sum(sale) from sobi.sample > where > c6 in (5182) > and > c5 in (5253,5110,5375,5268,5257,5266,5243,5256,5252,5361,5379,5363,5386,5367,5240,5241,5261,5249,5245,5260,5247,5264,5354,5250,5373,5366,5246,5356,5365,5255,5262,5259,5251,5369,5357,5269,5364,5380,5244,5248,5242,5254,5358,5360,5263,5362,5374,5378,5370,5294,5339,5272,5277,5275,5295,5340,5273,5276,5270,5061,5081,5079,5080,5066,5056,5073,5060,5070,5099,5101,5100,5067,5063,5077,5072,5062,5075,5083,5086,5071,5095,5058,5087,5090,5102,5084,5098,5076,5059,5074,5094,5091,5065,5069,5068,5104,5109,5107,5108,5013,5019,5022,5010,5009,5023,5011,5024,5012,5021,5007,5008,5025,5020,5055,5006,5036,5038,5054,5032,5033,5031,5039,5035,5026,5027,5030,5200,5043,5005,5041,5049,5042,5052,5040,5053,5045,5047,5048,5050,5051,5044,5046) > group by c5, c6; > > This in on MonetDB server v5.6.0, based on kernel v1.24.0 which I > built from the super tarball. > > I can attach the entire trace files for each query if appropriate for the list. > > While I am still learning to read the trace, it looks like most of the > time spent in the first query on a series of algebra.semijoin > operations. Here are the excerpted lines: > > Query 1: > > [2320345 usec # _461 := > algebra.semijoin(_167=<~tmp_2116>bat[:str,:oid]{8280020}, > _460=<~tmp_2016>bat[:str,:oid]{142}) ] > [776017 usec # _463 := > algebra.semijoin(_158=<tmp_2130>bat[:oid,:str]{8280020}, > _462=<~tmp_1572>bat[:oid,:str]{5135708}) ] > [253551 usec # _470 := > algebra.semijoin(_464=<~tmp_2116>bat[:str,:oid]{5135708}, > _469=<~tmp_2016>bat[:str,:oid]{1}) ] > > > Query 2: > > [412122 usec # _179 := > algebra.semijoin(_167=<~tmp_2076>bat[:str,:oid]{8280020}, > _178=<~tmp_2016>bat[:str,:oid]{1}) ] > [ 194 usec # _181 := > algebra.semijoin(_158=<tmp_2070>bat[:oid,:str]{8280020}, > _180=<~tmp_1572>bat[:oid,:str]{304}) ] > [ 0 usec # _470 := > algebra.semijoin(_182=<~tmp_2076>bat[:str,:oid]{304}, > _469=<~tmp_2016>bat[:str,:oid]{142}) ] > > > Any assistance would be greatly appreciated. Indeed the optimizer isn't smart enough to use the correct (most selective) in first. Could you send the create statement for the sobi.sample table? If possible could you try this on the 'current' and with mclient -lsql -G. That last -G enables the new implementation which first translates sql into a relational algebra and then in binary algebra. Niels > > -Ross Niels - thanks for the response. I tried using the -G switch and the performance time was the same for both versions of the query. The table is pretty simple (see below). I've tried the query against the table using both int and varchar for the columns being used in the WHERE clause. When c6 and C7 are declared as ints the queries run faster, but the order of the WHERE clause still creates a difference. 3.3 vs .4 when varchar 1.9 vs .2 when int CREATE TABLE "sobi"."sample" ( c1 char(1), c2 varchar(24), c3 varchar(12), c4 varchar(12), c5 varchar(12), c6 int, c7 int, c8 varchar(12), c9 varchar(12), sale int ) CREATE TABLE "sobi"."sample" ( c1 char(1), c2 varchar(24), c3 varchar(12), c4 varchar(12), c5 varchar(12), c6 varchar(12), c7 varchar(12), c8 varchar(12), c9 varchar(12), sale int ) Would it introduce any issues or risks to have the optimizer reorder the execution of each WHERE clause based on the number of predicates specified? I am still learning about the optimizer, but would changing any steps in the default SQL optimization sequence help in this situation? Thanks again, Ross

4 9

[MonetDB-users] Newbie question about XML / SQL capabilities
by Bill Walton 14 Aug '08

14 Aug '08

Greetings all, I just became aware of MonetDB via a post to the Ruby on Rails list about a new connection adapter for MonetDB (thanks Michalis). I'm intrigued by what looks like a very impressive capability. If I'm reading the site right, MonetDB can import an XML file using the XQuery client and then query that same data using the SQL client. Is that a correct reading? TIA, Bill

2 1

[MonetDB-users] Order of WHERE clause has significant performance impact
by Ross Bates 13 Aug '08

13 Aug '08

Hi All - I have 2 queries which run very different based on how I order the WHERE clause. When I put the larger filter first, it runs for 3.5 seconds. Query 1: select c5, c6, sum(sale) from sobi.sample where c5 in (5253,5110,5375,5268,5257,5266,5243,5256,5252,5361,5379,5363,5386,5367,5240,5241,5261,5249,5245,5260,5247,5264,5354,5250,5373,5366,5246,5356,5365,5255,5262,5259,5251,5369,5357,5269,5364,5380,5244,5248,5242,5254,5358,5360,5263,5362,5374,5378,5370,5294,5339,5272,5277,5275,5295,5340,5273,5276,5270,5061,5081,5079,5080,5066,5056,5073,5060,5070,5099,5101,5100,5067,5063,5077,5072,5062,5075,5083,5086,5071,5095,5058,5087,5090,5102,5084,5098,5076,5059,5074,5094,5091,5065,5069,5068,5104,5109,5107,5108,5013,5019,5022,5010,5009,5023,5011,5024,5012,5021,5007,5008,5025,5020,5055,5006,5036,5038,5054,5032,5033,5031,5039,5035,5026,5027,5030,5200,5043,5005,5041,5049,5042,5052,5040,5053,5045,5047,5048,5050,5051,5044,5046) and c6 in (5182) group by c5, c6; When I reverse the order of the WHERE clause, it runs in about .4 seconds. Query 2: c5, c6, sum(sale) from sobi.sample where c6 in (5182) and c5 in (5253,5110,5375,5268,5257,5266,5243,5256,5252,5361,5379,5363,5386,5367,5240,5241,5261,5249,5245,5260,5247,5264,5354,5250,5373,5366,5246,5356,5365,5255,5262,5259,5251,5369,5357,5269,5364,5380,5244,5248,5242,5254,5358,5360,5263,5362,5374,5378,5370,5294,5339,5272,5277,5275,5295,5340,5273,5276,5270,5061,5081,5079,5080,5066,5056,5073,5060,5070,5099,5101,5100,5067,5063,5077,5072,5062,5075,5083,5086,5071,5095,5058,5087,5090,5102,5084,5098,5076,5059,5074,5094,5091,5065,5069,5068,5104,5109,5107,5108,5013,5019,5022,5010,5009,5023,5011,5024,5012,5021,5007,5008,5025,5020,5055,5006,5036,5038,5054,5032,5033,5031,5039,5035,5026,5027,5030,5200,5043,5005,5041,5049,5042,5052,5040,5053,5045,5047,5048,5050,5051,5044,5046) group by c5, c6; This in on MonetDB server v5.6.0, based on kernel v1.24.0 which I built from the super tarball. I can attach the entire trace files for each query if appropriate for the list. While I am still learning to read the trace, it looks like most of the time spent in the first query on a series of algebra.semijoin operations. Here are the excerpted lines: Query 1: [2320345 usec # _461 := algebra.semijoin(_167=<~tmp_2116>bat[:str,:oid]{8280020}, _460=<~tmp_2016>bat[:str,:oid]{142}) ] [776017 usec # _463 := algebra.semijoin(_158=<tmp_2130>bat[:oid,:str]{8280020}, _462=<~tmp_1572>bat[:oid,:str]{5135708}) ] [253551 usec # _470 := algebra.semijoin(_464=<~tmp_2116>bat[:str,:oid]{5135708}, _469=<~tmp_2016>bat[:str,:oid]{1}) ] Query 2: [412122 usec # _179 := algebra.semijoin(_167=<~tmp_2076>bat[:str,:oid]{8280020}, _178=<~tmp_2016>bat[:str,:oid]{1}) ] [ 194 usec # _181 := algebra.semijoin(_158=<tmp_2070>bat[:oid,:str]{8280020}, _180=<~tmp_1572>bat[:oid,:str]{304}) ] [ 0 usec # _470 := algebra.semijoin(_182=<~tmp_2076>bat[:str,:oid]{304}, _469=<~tmp_2016>bat[:str,:oid]{142}) ] Any assistance would be greatly appreciated. -Ross

2 1