On Sun, Jun 12, 2011 at 8:58 PM, Igor Tandetnik <itandet...@mvps.org> wrote:
> iip <iip.umar.ri...@gmail.com> wrote: > > I tried to create Cartesian list from 8 digit text that has 256 row. when > I > > try below statement, the time to fetch the result was very long, I'm > always > > press ctrl+c because its run more than 20 minute: > > > > select a.a,b.b,c.c,d.d, max(length(a.a||b.b||c.c||d.d)) as e from (select > > text8) as a from dict) as a cross join (select text8) as b from dict) as > b > > cross join (select text8) as c from dict) as c cross join (select text8) > as > > d from dict) as d WHERE a.a<>b.b and a.a<>c.c and a.a<>d.d and b.b<>c.c > and > > b.b<>d.d and c.c<>d.d; > > First, this can't be the actual query you are running - it has way more > closing parentheses than it has opening ones. > > Second, this must be the most inefficient way ever of picking four longest > strings. You do realize that you have SQLite manufacture, and then go > through, 4 billion rows, right? See if this works for you: > > select distinct text8, length(text8) from dict > order by length(text8) desc limit 4; > > Yes, you right Igor, since I'm seldom of using sqlite, I thing I miss the "distinct" way as you recommends, and yes, the row will be around 4 billion rows. Thanks to remind me. > > So, above select only use 1 column from the same table, with total row > are > > 256 row. I know that the value of max will be 8*4, I just want to know > the > > speed to get maximum number from column calculation using cartesian list. > > You can't fight combinatorial explosion with brute force. It always wins. > > > Is there a better/fast way to get data combination of column text8, or I > > have to store it physically into table? > > Again, you are contemplating a table of 4 billion records. What do you > think you need it for? What's the ultimate goal of the exercise? > I'm trying to create a dictionary table, I'm still not sure with it, but I will try your statement above, and see if it works...still long way to go. Thanks a lot, -iip- > -- > Igor Tandetnik > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
