Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 5 of 5 total
Thread Select List(ColName) not optimized for Group By
Fri, Nov 29 2013 12:09 PMPermanent Link

Barry

The following SQL command runs extremely slow, and I suspect the List() is trying to concatenate values from all the rows, instead of the distinct column values.

select list(Cust_Id) from sales group by Cust_Id;  --This takes forever (I had to cancel it)

select list(Distinct Cust_Id) from sales;   --This takes 17 seconds

select list(t1.Cust_id) from (select Cust_Id from sales group by Cust_id) as t1  --Takes 3 seconds

Barry
Fri, Nov 29 2013 12:11 PMPermanent Link

Barry

Oops! I should have mentioned I'm using EDB 2.13 B2

Barry
Sat, Nov 30 2013 4:16 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Barry

>The following SQL command runs extremely slow, and I suspect the List() is trying to concatenate values from all the rows, instead of the distinct column values.
>
>select list(Cust_Id) from sales group by Cust_Id; --This takes forever (I had to cancel it)

Your assumption is correct - that's what you've told it to do stuff ALL Cust_Id into a list. If its taking a long time I suspect Tim hasn't been sufficiently clever with the stringlist's capacity (I assume he uses a string list internally)

>select list(Distinct Cust_Id) from sales; --This takes 17 seconds

Still has to work through, and its now checking to see if a Cust_Id is still in the list

>select list(t1.Cust_id) from (select Cust_Id from sales group by Cust_id) as t1 --Takes 3 seconds

No idea what's going on here to make it so much faster.

Couple of points:

1. You've posted in the suggestions ng and there is no suggestion
2. In your next post you've given us the version of ElevateDB but can we have an idea of the size of the table and its structure as well also wether its unicode or not

Roy Lambert [Team Elevate]
Sat, Nov 30 2013 1:26 PMPermanent Link

Barry

>Your assumption is correct - that's what you've told it to do stuff ALL Cust_Id into a list. If its taking a long time I suspect Tim hasn't been sufficiently clever with the stringlist's capacity (I assume he uses a string list internally)<

You're right. I erroneously thought it would do the Group By first before executing the List(). But now I realize List() is just another aggregate function so it is working the way it is suppose to.

>1. You've posted in the suggestions ng and there is no suggestion<
I posted it as an enhancement request because I thought there was something wrong with List() not being optimized.

>2. In your next post you've given us the version of ElevateDB but can we have an idea of the size of the table and its structure as well also wether its unicode or not<

It is Unicode, the Cust_Id was GUID and the table has approx 330k rows.

So List() and Group By is working correctly after all.
The problem was between the keyboard and chair. Smile

Barry
Sun, Dec 1 2013 7:55 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Barry

>>Your assumption is correct - that's what you've told it to do stuff ALL Cust_Id into a list. If its taking a long time I suspect Tim hasn't been sufficiently clever with the stringlist's capacity (I assume he uses a string list internally)<
>
>You're right. I erroneously thought it would do the Group By first before executing the List(). But now I realize List() is just another aggregate function so it is working the way it is suppose to.

Yup - really useful one as well, especially with its ORDERED and DISTINCT qualifiers.

I'd missed the GROUP BY. That can only make it worse since it gives all the Cust_Ids in the group, which also explains why your third test was so much faster - the subselect reduced the amount of data involved dramatically - it also delivers a totally different result.

>>1. You've posted in the suggestions ng and there is no suggestion<
>I posted it as an enhancement request because I thought there was something wrong with List() not being optimized.

Do not be surprised if implicit suggestions get misunderstood or ignored.

Also being picky I'd regard LIST not being optimised as more of a bug. Just the way I think.

>So List() and Group By is working correctly after all.
>The problem was between the keyboard and chair. Smile

and its name is often Roy Smiley

Roy Lambert [Team Elevate]
Image