>>>>

>>>>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.aspx
>
>Boris, 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

Marc,

(1) Concerning the string concatenation, it may be a good idea to use the StringBuilder http://msdn.microsoft.com/en-us/library/system.text.stringbuilder.aspx since we append a few hundred times


(2) If you have all the keys in memory, why not keep the field (you use for the total) in memory as well ?