Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB Enhancement Requests and Suggestions » View Thread |
Messages 1 to 5 of 5 total |
Select List(ColName) not optimized for Group By |
Fri, Nov 29 2013 12:09 PM | Permanent 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 PM | Permanent Link |
Barry | Oops! I should have mentioned I'm using EDB 2.13 B2
Barry |
Sat, Nov 30 2013 4:16 AM | Permanent Link |
Roy Lambert NLH Associates 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 PM | Permanent 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. Barry |
Sun, Dec 1 2013 7:55 AM | Permanent Link |
Roy Lambert NLH Associates 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. and its name is often Roy Roy Lambert [Team Elevate] |
This web page was last updated on Friday, March 29, 2024 at 03:30 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |