Re: [sqlite] cross join very slow, which is better select virtually using cross join, or create physically data output from cross join?

[iip]

On Sun, Jun 12, 2011 at 8:58 PM, Igor Tandetnik  wrote:

> iip  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

Re: [sqlite] cross join very slow, which is better select virtually using cross join, or create physically data output from cross join?

[Igor Tandetnik]

iip  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;

> 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?
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users