>>>
>>>String cStr = "SELECT ..... FROM MainTable WHERE Key IN ("
>>>loop for each key you have in memory
>>> cStr = cStr + Key.ToString() + ","
>>>endloop
>>>cStr = LEFT(cStr, LEN(cStr)-1)+")" && This is a VFP code, you should translate it to VB.NET :-)
>>>
>>>execute cStr
>>>
>>
>>Not the most elegant solution, but definitely a solution to my problem. Thank you very much. :)
>>
>>
>>
>>>>>>I'm looking for a way to speed up a calculation routine that computes the total of a document that can contain up to a few hundred lines. The routine is slow now because for each line I access the db. I'm looking for a way to implement this in a more data oriented way.
>>>>>>
>>>>>>In VFP I would have dumped the relevant keys into a cursor and then join the tables to the cursor in an sql statement.
>>>>>>
>>>>>>I could of course simulate this by dumping the keys into a temporary table (which if I'm right would not limit the amount of times I access the db), but I was wondering if there is not a more "elegant" way to do this.
>>>>>>
>>>>>>TIA.
>>>>>
>>>>>I'm not sure I understand you right, but you could use derived tables in your SELECT.
>>>>>
>>>>>SELECT .....
>>>>>FROM MainTable
>>>>>INNER JOIN (SELECT Keys From SomeTable WHERE ...) DympedKeys
>>>>> ON ....
>>>>>
>>>>
>>>>Right Borislav, I'm sorry, I was not explicit. I'm not working in VFP, but in vb.net. And my keys are for the time being stored in some sort of datatable in memory.
>>>>The problem of speed is relevant because I need to recompute the totals each time the user enters or changes a field in the document.
>>>
>>>I saw you posted in NET forum, so I expected that.
>>>But the code I gave you is pure SQL code, it is not Front end related, and it can be executed no matter what DB is used.
>>>The only drawback is that you have these keys in memory.
>>>Could you build your SELECT on the fly and concatenate these keys?
>>>(pseudo code)
>>>
>
>I see that you use ACCESS.
>If you use SQL Server 2008 you could pass a Table valued parameter to SP.
>
http://msdn.microsoft.com/en-us/library/bb675163.aspxBoris, thank you for this. When I grow up I will definitely implement SQL Server :). I thought this would have a rather "evident" solution, but in fact, the one you suggest, i.e. building the select on the fly is perfectly implementable. I have googled around and my first reaction of "do people actually do this?" got a definite "yup" answer.
Thanks again and kind regards,
Marc
If things have the tendency to go your way, do not worry. It won't last. Jules Renard.