|
|
Rank: Newbie
Joined: 3/12/2013
Posts: 5
Location: Poland
|
Hi,
I've read here some topics about increasing performance by directly accessing DB. We've got here a case with a large amount of records in our DB and we know that MapForce has a group-by operation. But it takes forever to proceed...
Our structure looks like this, nothing special:
ID name
11 aaaa
11 bbbb
11 cccc
12 aaaa
12 bbbb etc.
we would like to feed that in our XML structure which looks like this:
<parent id="11">
<child name="aaaa">
<child name="bbbb">
<child name="ccccc">
<parent id="12">
<child name="aaaa">
<child name="bbbb"> etc
but with the build-in MapForce group-by operation we can't see the end of our processing... so we decided to go with DB's group-by.
Ok then, we have our statement which looks like this:
select id, name from table
group by id, name
but now... how do we tell mapforce to take the 'ID' value as parent and the 'name' value as childs in XML, without using MapForce group-by operation again, if we already had one in our SQL statement???...
Please, just don't tell me that this is quite obvious... :)
Greetings,
Rob
|
|
Rank: Advanced Member
Joined: 12/13/2005
Posts: 2,856
Location: Mauritius
|
You can stick to your original solution, but use group-adjacent instead of group-by. The difference is that group-by has to see all rows in order to group them, whereas group-adjacent only reads rows until the key is changing (in your case ID). As long as you deliver your rows sorted by ID (use SQL ORDER BY) - you are allowed to use group-adjacent, which will work VERY MUCH quicker.
|
|
Rank: Newbie
Joined: 3/12/2013
Posts: 5
Location: Poland
|
Oh my goodness... the speed increase is ... enormous!! :O
Vlad, thank you very much... that saves my holiday on 1st of may :)
Very best regards,
Rob
|
|
|
guest |
