query large list of values

[Ruslan Zasukhin]

On 2/19/11 4:48 PM, "Wolfgang Colsman" <wolfgang.colsman at osthus.de> wrote:

Hi Wolfgang ,
  
> I have a scenario where I have a very long list of values (1 Mio) as an
> IEnumerable<string> in memory (using VNET).
>  
> What is the recommended way to use this list as query parameter?
>  
> To my understanding it is not possible to bind the list to a sql like "WHERE x
> IN (:1)", since this would compare the array object, not the values inside the
> array.
>  
> I tried the following approach: create a temporary table, populate it with the
> values, and finally join this table with the data to be queried.
>  
> In order to populate the temporary table I created a dump file with 500,000
> rows which I loaded via LoadDump().
>  
> LoadDump() took 1.5 minutes to load the data!!
>  
> What is the most performing way to implement this? This performance is not
> acceptable for the users.

What exactly dump you made?  SQL?

I think the fastest way and best for you,
Create text file as  Coma-separated, or such


==========
500K records...
How many fields ?


In V4RB we have bench of adding 100K  into table with 19 fields of each
Valentina type  and best possible time  is 3.5 sec on regular MAC.

So 500K  will be best possible time 18 sec ...
But this is 19 fields...

Of course during massive load, should not be any indexes on table ...


-- 
Best regards,

Ruslan Zasukhin
VP Engineering and New Technology
Paradigma Software, Inc

Valentina - Joining Worlds of Information
http://www.paradigmasoft.com

[I feel the need: the need for speed]