Hiya
I have a DataTable containing thousands of records. Each record has a
primary key field called "ID" and another field called "PRODUCT" I want to
retrieve the rows that satisy the following criteria:
I have a list of about 100 numbers which correspond to the ID field and also
another 40 say numbers corresponding to the numbers in the PRODUCT field. I
want to show the rows that correspond to both these criteria.
My oringinal idea was to create a DataView and then generate a string an SQL
string, which I could then use with RowFilter to get the rows I want.
However, I'm uncertain of the SQL syntax to use and, more importantly, if
this is the best way to do it.
Can anybody help?
Geoff
33  2286 
Hi Geoff,
By the way, if you already have a 'larger' datatable, you can use the method
I showed you earlier in a dataview select.
Bernie
"Geoff Jones" <no********@email.com> wrote in message
news:42**********************@news.dial.pipex.com. .. Hiya
I have a DataTable containing thousands of records. Each record has a
primary key field called "ID" and another field called "PRODUCT" I want to
retrieve the rows that satisy the following criteria:
I have a list of about 100 numbers which correspond to the ID field and
also another 40 say numbers corresponding to the numbers in the PRODUCT
field. I want to show the rows that correspond to both these criteria.
My oringinal idea was to create a DataView and then generate a string an
SQL string, which I could then use with RowFilter to get the rows I want.
However, I'm uncertain of the SQL syntax to use and, more importantly, if
this is the best way to do it.
Can anybody help?
Geoff
Hi Geoff,
Select * from ttable where id in ('324','17', '934'...etc) and product in
('a1234','3434'...etc)
Now, if these are variable and selected from, say, a listbox by the user,
then you have to use a form of dynamic sql - not of the traditional kind,
but rather the vb .net kind. If that is the case, let me know - I can show
you how to do this easily by reconstructing the sql string from a variable
array or arraylist.
HTH,
Bernie Yaeger
"Geoff Jones" <no********@email.com> wrote in message
news:42**********************@news.dial.pipex.com. .. Hiya
I have a DataTable containing thousands of records. Each record has a
primary key field called "ID" and another field called "PRODUCT" I want to
retrieve the rows that satisy the following criteria:
I have a list of about 100 numbers which correspond to the ID field and
also another 40 say numbers corresponding to the numbers in the PRODUCT
field. I want to show the rows that correspond to both these criteria.
My oringinal idea was to create a DataView and then generate a string an
SQL string, which I could then use with RowFilter to get the rows I want.
However, I'm uncertain of the SQL syntax to use and, more importantly, if
this is the best way to do it.
Can anybody help?
Geoff
Hi Bernie
Yes, the variables are indeed selected from a list box. Two in fact. One
list box generates the ID's and the other generates the product keys. So, in
effect, I'm trying to get an AND. The rows in the table that correspond to
the ID "AND" the PRODUCT id.
The dynamic SQL sounds like exactly what I'm looking for :)
I look forward to hearing your idea.
Thanks in advance
Geoff
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:Ox**************@TK2MSFTNGP10.phx.gbl... Hi Geoff,
Select * from ttable where id in ('324','17', '934'...etc) and product in
('a1234','3434'...etc)
Now, if these are variable and selected from, say, a listbox by the user,
then you have to use a form of dynamic sql - not of the traditional kind,
but rather the vb .net kind. If that is the case, let me know - I can
show you how to do this easily by reconstructing the sql string from a
variable array or arraylist.
HTH,
Bernie Yaeger
"Geoff Jones" <no********@email.com> wrote in message
news:42**********************@news.dial.pipex.com. .. Hiya
I have a DataTable containing thousands of records. Each record has a
primary key field called "ID" and another field called "PRODUCT" I want
to retrieve the rows that satisy the following criteria:
I have a list of about 100 numbers which correspond to the ID field and
also another 40 say numbers corresponding to the numbers in the PRODUCT
field. I want to show the rows that correspond to both these criteria.
My oringinal idea was to create a DataView and then generate a string an
SQL string, which I could then use with RowFilter to get the rows I want.
However, I'm uncertain of the SQL syntax to use and, more importantly, if
this is the best way to do it.
Can anybody help?
Geoff
Hi Geoff,
Here's essentially how I do this (and I've done so literally in more than
100 different routines):
1. I generally have a 'frombox' and 'tobox' listboxes; the user moves the
selected items from the from box to the to box;
2. create an arraylist containing the selections in the tobox when they
click 'process' or whatever; make a string - 'longstring' - from this array,
such that it looks like "('123','556','329'...etc)" by using a simple for
loop - here's an example (where i = tobox.items.count)
longstring = "('"
For j = 0 To i - 1
longstring = longstring & RTrim(titlearray(j)) & RTrim(issuecodearray(j)) &
Chr(39) & ",'"
Next
longstring = Mid(longstring, 1, longstring.Length - 2)
longstring = longstring & ")"
3. do this for both the id and the product columns, so that you will have
longstring1 and longstring2
4. construct your .select of the dataview like this:
For Each irow In dv.Table.Select("bipad in (" & longstring & ")")
In this example I didn't build longstring to have the open and close parens,
but you can do it either way.
Let me know if you have any problems with this.
Bernie
"Geoff Jones" <no********@email.com> wrote in message
news:42**********************@news.dial.pipex.com. .. Hi Bernie
Yes, the variables are indeed selected from a list box. Two in fact. One
list box generates the ID's and the other generates the product keys. So,
in effect, I'm trying to get an AND. The rows in the table that correspond
to the ID "AND" the PRODUCT id.
The dynamic SQL sounds like exactly what I'm looking for :)
I look forward to hearing your idea.
Thanks in advance
Geoff
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:Ox**************@TK2MSFTNGP10.phx.gbl... Hi Geoff,
Select * from ttable where id in ('324','17', '934'...etc) and product in
('a1234','3434'...etc)
Now, if these are variable and selected from, say, a listbox by the user,
then you have to use a form of dynamic sql - not of the traditional kind,
but rather the vb .net kind. If that is the case, let me know - I can
show you how to do this easily by reconstructing the sql string from a
variable array or arraylist.
HTH,
Bernie Yaeger
"Geoff Jones" <no********@email.com> wrote in message
news:42**********************@news.dial.pipex.com. .. Hiya
I have a DataTable containing thousands of records. Each record has a
primary key field called "ID" and another field called "PRODUCT" I want
to retrieve the rows that satisy the following criteria:
I have a list of about 100 numbers which correspond to the ID field and
also another 40 say numbers corresponding to the numbers in the PRODUCT
field. I want to show the rows that correspond to both these criteria.
My oringinal idea was to create a DataView and then generate a string an
SQL string, which I could then use with RowFilter to get the rows I
want.
However, I'm uncertain of the SQL syntax to use and, more importantly,
if this is the best way to do it.
Can anybody help?
Geoff
Bernie,
I break a little bit in, about the majority of your answer I see no need to
discuss, however my eye did fall on this. Here are you using in fact the
datatable select from the original table. For Each irow In dv.Table.Select("bipad in (" & longstring & ")")
dv.Table tells which datatable the dataview is using and is not a dataview
method, however a property.
I would use in this case the original datatable direct. Although I would
probably choose to read a new dataset from the database and create a small
dataset for selections.
Just to inform you.
Cor
Hi Bernie
Thanks very much. I wasn't familiar with the syntax for "And"ing the
numbers. Interesting.
I take it that Chr(39) is a way to generate ' ? Could I also use "'"?
In addition, I assume that I'd write:
For Each irow In dv.Table.Select("ID in (" & longstring & ")")
i.e. bipad has been changed to ID (I'm "bipad" is not a keyword but rather
the name of the field I'm searching?)
Could I also trouble you further with a bit of the syntax I need? The line
above will gengerate the rows I require for one criteria. What if I wanted
to filter this selection down further by, for example, finding all the rows
where the "PRODUCT" field was equal to, say, (3,565,3457)?
Would it be something like, in pseudo code:
For Each irow In dv.Table.Select("ID in (" & longstring1 & ")" & " AND " &
"PRODUCT in (" & longstring2 & ")"
Thanks in advance
Geoff
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:el**************@TK2MSFTNGP10.phx.gbl... Hi Geoff,
Here's essentially how I do this (and I've done so literally in more than
100 different routines):
1. I generally have a 'frombox' and 'tobox' listboxes; the user moves the
selected items from the from box to the to box;
2. create an arraylist containing the selections in the tobox when they
click 'process' or whatever; make a string - 'longstring' - from this
array, such that it looks like "('123','556','329'...etc)" by using a
simple for loop - here's an example (where i = tobox.items.count)
longstring = "('"
For j = 0 To i - 1
longstring = longstring & RTrim(titlearray(j)) & RTrim(issuecodearray(j))
& Chr(39) & ",'"
Next
longstring = Mid(longstring, 1, longstring.Length - 2)
longstring = longstring & ")"
3. do this for both the id and the product columns, so that you will have
longstring1 and longstring2
4. construct your .select of the dataview like this:
For Each irow In dv.Table.Select("bipad in (" & longstring & ")")
In this example I didn't build longstring to have the open and close
parens, but you can do it either way.
Let me know if you have any problems with this.
Bernie
"Geoff Jones" <no********@email.com> wrote in message
news:42**********************@news.dial.pipex.com. .. Hi Bernie
Yes, the variables are indeed selected from a list box. Two in fact. One
list box generates the ID's and the other generates the product keys. So,
in effect, I'm trying to get an AND. The rows in the table that
correspond to the ID "AND" the PRODUCT id.
The dynamic SQL sounds like exactly what I'm looking for :)
I look forward to hearing your idea.
Thanks in advance
Geoff
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:Ox**************@TK2MSFTNGP10.phx.gbl... Hi Geoff,
Select * from ttable where id in ('324','17', '934'...etc) and product
in ('a1234','3434'...etc)
Now, if these are variable and selected from, say, a listbox by the
user, then you have to use a form of dynamic sql - not of the
traditional kind, but rather the vb .net kind. If that is the case, let
me know - I can show you how to do this easily by reconstructing the sql
string from a variable array or arraylist.
HTH,
Bernie Yaeger
"Geoff Jones" <no********@email.com> wrote in message
news:42**********************@news.dial.pipex.com. ..
Hiya
I have a DataTable containing thousands of records. Each record has a
primary key field called "ID" and another field called "PRODUCT" I want
to retrieve the rows that satisy the following criteria:
I have a list of about 100 numbers which correspond to the ID field and
also another 40 say numbers corresponding to the numbers in the PRODUCT
field. I want to show the rows that correspond to both these criteria.
My oringinal idea was to create a DataView and then generate a string
an SQL string, which I could then use with RowFilter to get the rows I
want.
However, I'm uncertain of the SQL syntax to use and, more importantly,
if this is the best way to do it.
Can anybody help?
Geoff
Hi Geoff,
Yes to every question: chr(39) is an apostrophe and yes you can call it with
"'"; 'bipad' is my field (column) name; you're pseudocode for using 'and' is
correct, although the term 'anding' is usually used in a different context
(re hex notation).
Glad to help,
Bernie
"Geoff Jones" <no********@email.com> wrote in message
news:42***********************@news.dial.pipex.com ... Hi Bernie
Thanks very much. I wasn't familiar with the syntax for "And"ing the
numbers. Interesting.
I take it that Chr(39) is a way to generate ' ? Could I also use "'"?
In addition, I assume that I'd write:
For Each irow In dv.Table.Select("ID in (" & longstring & ")")
i.e. bipad has been changed to ID (I'm "bipad" is not a keyword but rather
the name of the field I'm searching?)
Could I also trouble you further with a bit of the syntax I need? The line
above will gengerate the rows I require for one criteria. What if I wanted
to filter this selection down further by, for example, finding all the
rows where the "PRODUCT" field was equal to, say, (3,565,3457)?
Would it be something like, in pseudo code:
For Each irow In dv.Table.Select("ID in (" & longstring1 & ")" & " AND " &
"PRODUCT in (" & longstring2 & ")"
Thanks in advance
Geoff
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:el**************@TK2MSFTNGP10.phx.gbl... Hi Geoff,
Here's essentially how I do this (and I've done so literally in more than
100 different routines):
1. I generally have a 'frombox' and 'tobox' listboxes; the user moves
the selected items from the from box to the to box;
2. create an arraylist containing the selections in the tobox when they
click 'process' or whatever; make a string - 'longstring' - from this
array, such that it looks like "('123','556','329'...etc)" by using a
simple for loop - here's an example (where i = tobox.items.count)
longstring = "('"
For j = 0 To i - 1
longstring = longstring & RTrim(titlearray(j)) & RTrim(issuecodearray(j))
& Chr(39) & ",'"
Next
longstring = Mid(longstring, 1, longstring.Length - 2)
longstring = longstring & ")"
3. do this for both the id and the product columns, so that you will
have longstring1 and longstring2
4. construct your .select of the dataview like this:
For Each irow In dv.Table.Select("bipad in (" & longstring & ")")
In this example I didn't build longstring to have the open and close
parens, but you can do it either way.
Let me know if you have any problems with this.
Bernie
"Geoff Jones" <no********@email.com> wrote in message
news:42**********************@news.dial.pipex.com. .. Hi Bernie
Yes, the variables are indeed selected from a list box. Two in fact. One
list box generates the ID's and the other generates the product keys.
So, in effect, I'm trying to get an AND. The rows in the table that
correspond to the ID "AND" the PRODUCT id.
The dynamic SQL sounds like exactly what I'm looking for :)
I look forward to hearing your idea.
Thanks in advance
Geoff
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:Ox**************@TK2MSFTNGP10.phx.gbl...
Hi Geoff,
Select * from ttable where id in ('324','17', '934'...etc) and product
in ('a1234','3434'...etc)
Now, if these are variable and selected from, say, a listbox by the
user, then you have to use a form of dynamic sql - not of the
traditional kind, but rather the vb .net kind. If that is the case,
let me know - I can show you how to do this easily by reconstructing
the sql string from a variable array or arraylist.
HTH,
Bernie Yaeger
"Geoff Jones" <no********@email.com> wrote in message
news:42**********************@news.dial.pipex.com. ..
> Hiya
>
> I have a DataTable containing thousands of records. Each record has a
> primary key field called "ID" and another field called "PRODUCT" I
> want to retrieve the rows that satisy the following criteria:
>
> I have a list of about 100 numbers which correspond to the ID field
> and also another 40 say numbers corresponding to the numbers in the
> PRODUCT field. I want to show the rows that correspond to both these
> criteria.
>
> My oringinal idea was to create a DataView and then generate a string
> an SQL string, which I could then use with RowFilter to get the rows I
> want.
>
> However, I'm uncertain of the SQL syntax to use and, more importantly,
> if this is the best way to do it.
>
> Can anybody help?
>
> Geoff
>
Hi Cor,
You are correct, but either approach will work. Geoff mentioned that he had
a datatable already created from which he wanted to drill down further.
Bernie
"Cor Ligthert" <no************@planet.nl> wrote in message
news:ON**************@TK2MSFTNGP10.phx.gbl... Bernie,
I break a little bit in, about the majority of your answer I see no need
to discuss, however my eye did fall on this. Here are you using in fact
the datatable select from the original table.
For Each irow In dv.Table.Select("bipad in (" & longstring & ")")
dv.Table tells which datatable the dataview is using and is not a dataview
method, however a property.
I would use in this case the original datatable direct. Although I would
probably choose to read a new dataset from the database and create a small
dataset for selections.
Just to inform you.
Cor
Many thanks for your help Bernie. It is most appreciated.
Geoff
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:ua***************@TK2MSFTNGP09.phx.gbl... Hi Geoff,
Yes to every question: chr(39) is an apostrophe and yes you can call it
with "'"; 'bipad' is my field (column) name; you're pseudocode for using
'and' is correct, although the term 'anding' is usually used in a
different context (re hex notation).
Glad to help,
Bernie
"Geoff Jones" <no********@email.com> wrote in message
news:42***********************@news.dial.pipex.com ... Hi Bernie
Thanks very much. I wasn't familiar with the syntax for "And"ing the
numbers. Interesting.
I take it that Chr(39) is a way to generate ' ? Could I also use "'"?
In addition, I assume that I'd write:
For Each irow In dv.Table.Select("ID in (" & longstring & ")")
i.e. bipad has been changed to ID (I'm "bipad" is not a keyword but
rather the name of the field I'm searching?)
Could I also trouble you further with a bit of the syntax I need? The
line above will gengerate the rows I require for one criteria. What if I
wanted to filter this selection down further by, for example, finding all
the rows where the "PRODUCT" field was equal to, say, (3,565,3457)?
Would it be something like, in pseudo code:
For Each irow In dv.Table.Select("ID in (" & longstring1 & ")" & " AND "
& "PRODUCT in (" & longstring2 & ")"
Thanks in advance
Geoff
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:el**************@TK2MSFTNGP10.phx.gbl... Hi Geoff,
Here's essentially how I do this (and I've done so literally in more
than 100 different routines):
1. I generally have a 'frombox' and 'tobox' listboxes; the user moves
the selected items from the from box to the to box;
2. create an arraylist containing the selections in the tobox when they
click 'process' or whatever; make a string - 'longstring' - from this
array, such that it looks like "('123','556','329'...etc)" by using a
simple for loop - here's an example (where i = tobox.items.count)
longstring = "('"
For j = 0 To i - 1
longstring = longstring & RTrim(titlearray(j)) &
RTrim(issuecodearray(j)) & Chr(39) & ",'"
Next
longstring = Mid(longstring, 1, longstring.Length - 2)
longstring = longstring & ")"
3. do this for both the id and the product columns, so that you will
have longstring1 and longstring2
4. construct your .select of the dataview like this:
For Each irow In dv.Table.Select("bipad in (" & longstring & ")")
In this example I didn't build longstring to have the open and close
parens, but you can do it either way.
Let me know if you have any problems with this.
Bernie
"Geoff Jones" <no********@email.com> wrote in message
news:42**********************@news.dial.pipex.com. ..
Hi Bernie
Yes, the variables are indeed selected from a list box. Two in fact.
One list box generates the ID's and the other generates the product
keys. So, in effect, I'm trying to get an AND. The rows in the table
that correspond to the ID "AND" the PRODUCT id.
The dynamic SQL sounds like exactly what I'm looking for :)
I look forward to hearing your idea.
Thanks in advance
Geoff
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:Ox**************@TK2MSFTNGP10.phx.gbl...
> Hi Geoff,
>
> Select * from ttable where id in ('324','17', '934'...etc) and product
> in ('a1234','3434'...etc)
>
> Now, if these are variable and selected from, say, a listbox by the
> user, then you have to use a form of dynamic sql - not of the
> traditional kind, but rather the vb .net kind. If that is the case,
> let me know - I can show you how to do this easily by reconstructing
> the sql string from a variable array or arraylist.
>
> HTH,
>
> Bernie Yaeger
>
> "Geoff Jones" <no********@email.com> wrote in message
> news:42**********************@news.dial.pipex.com. ..
>> Hiya
>>
>> I have a DataTable containing thousands of records. Each record has a
>> primary key field called "ID" and another field called "PRODUCT" I
>> want to retrieve the rows that satisy the following criteria:
>>
>> I have a list of about 100 numbers which correspond to the ID field
>> and also another 40 say numbers corresponding to the numbers in the
>> PRODUCT field. I want to show the rows that correspond to both these
>> criteria.
>>
>> My oringinal idea was to create a DataView and then generate a string
>> an SQL string, which I could then use with RowFilter to get the rows
>> I want.
>>
>> However, I'm uncertain of the SQL syntax to use and, more
>> importantly, if this is the best way to do it.
>>
>> Can anybody help?
>>
>> Geoff
>>
>
>
Hi Goeff,
Last point - if the select ever errors out, look at it in messagebox.show to
make sure all of the apostrophes and parens are in the right place, not
doubled, not missing, etc.
Bernie
"Geoff Jones" <no********@email.com> wrote in message
news:42***********************@news.dial.pipex.com ... Many thanks for your help Bernie. It is most appreciated.
Geoff
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:ua***************@TK2MSFTNGP09.phx.gbl... Hi Geoff,
Yes to every question: chr(39) is an apostrophe and yes you can call it
with "'"; 'bipad' is my field (column) name; you're pseudocode for using
'and' is correct, although the term 'anding' is usually used in a
different context (re hex notation).
Glad to help,
Bernie
"Geoff Jones" <no********@email.com> wrote in message
news:42***********************@news.dial.pipex.com ... Hi Bernie
Thanks very much. I wasn't familiar with the syntax for "And"ing the
numbers. Interesting.
I take it that Chr(39) is a way to generate ' ? Could I also use "'"?
In addition, I assume that I'd write:
For Each irow In dv.Table.Select("ID in (" & longstring & ")")
i.e. bipad has been changed to ID (I'm "bipad" is not a keyword but
rather the name of the field I'm searching?)
Could I also trouble you further with a bit of the syntax I need? The
line above will gengerate the rows I require for one criteria. What if I
wanted to filter this selection down further by, for example, finding
all the rows where the "PRODUCT" field was equal to, say, (3,565,3457)?
Would it be something like, in pseudo code:
For Each irow In dv.Table.Select("ID in (" & longstring1 & ")" & " AND "
& "PRODUCT in (" & longstring2 & ")"
Thanks in advance
Geoff
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:el**************@TK2MSFTNGP10.phx.gbl...
Hi Geoff,
Here's essentially how I do this (and I've done so literally in more
than 100 different routines):
1. I generally have a 'frombox' and 'tobox' listboxes; the user moves
the selected items from the from box to the to box;
2. create an arraylist containing the selections in the tobox when
they click 'process' or whatever; make a string - 'longstring' - from
this array, such that it looks like "('123','556','329'...etc)" by
using a simple for loop - here's an example (where i =
tobox.items.count)
longstring = "('"
For j = 0 To i - 1
longstring = longstring & RTrim(titlearray(j)) &
RTrim(issuecodearray(j)) & Chr(39) & ",'"
Next
longstring = Mid(longstring, 1, longstring.Length - 2)
longstring = longstring & ")"
3. do this for both the id and the product columns, so that you will
have longstring1 and longstring2
4. construct your .select of the dataview like this:
For Each irow In dv.Table.Select("bipad in (" & longstring & ")")
In this example I didn't build longstring to have the open and close
parens, but you can do it either way.
Let me know if you have any problems with this.
Bernie
"Geoff Jones" <no********@email.com> wrote in message
news:42**********************@news.dial.pipex.com. ..
> Hi Bernie
>
> Yes, the variables are indeed selected from a list box. Two in fact.
> One list box generates the ID's and the other generates the product
> keys. So, in effect, I'm trying to get an AND. The rows in the table
> that correspond to the ID "AND" the PRODUCT id.
>
> The dynamic SQL sounds like exactly what I'm looking for :)
>
> I look forward to hearing your idea.
>
> Thanks in advance
>
> Geoff
>
> "Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
> news:Ox**************@TK2MSFTNGP10.phx.gbl...
>> Hi Geoff,
>>
>> Select * from ttable where id in ('324','17', '934'...etc) and
>> product in ('a1234','3434'...etc)
>>
>> Now, if these are variable and selected from, say, a listbox by the
>> user, then you have to use a form of dynamic sql - not of the
>> traditional kind, but rather the vb .net kind. If that is the case,
>> let me know - I can show you how to do this easily by reconstructing
>> the sql string from a variable array or arraylist.
>>
>> HTH,
>>
>> Bernie Yaeger
>>
>> "Geoff Jones" <no********@email.com> wrote in message
>> news:42**********************@news.dial.pipex.com. ..
>>> Hiya
>>>
>>> I have a DataTable containing thousands of records. Each record has
>>> a primary key field called "ID" and another field called "PRODUCT" I
>>> want to retrieve the rows that satisy the following criteria:
>>>
>>> I have a list of about 100 numbers which correspond to the ID field
>>> and also another 40 say numbers corresponding to the numbers in the
>>> PRODUCT field. I want to show the rows that correspond to both these
>>> criteria.
>>>
>>> My oringinal idea was to create a DataView and then generate a
>>> string an SQL string, which I could then use with RowFilter to get
>>> the rows I want.
>>>
>>> However, I'm uncertain of the SQL syntax to use and, more
>>> importantly, if this is the best way to do it.
>>>
>>> Can anybody help?
>>>
>>> Geoff
>>>
>>
>>
>
>
Bernie,
Therefore it was only to point you attention on it, I hope you understood
that was all I wanted to do.
Cor
Hi Cor,
Of course; you are always helpful - tx
Bernie
"Cor Ligthert" <no************@planet.nl> wrote in message
news:u2******************@TK2MSFTNGP09.phx.gbl... Bernie,
Therefore it was only to point you attention on it, I hope you understood
that was all I wanted to do.
Cor
Hi Bernie
I've tried the "IN" scheme and it works but it is very slow. Am I doing
something wrong or can it be slow in searching?
Geoff
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:eC**************@TK2MSFTNGP10.phx.gbl... Hi Geoff,
By the way, if you already have a 'larger' datatable, you can use the
method I showed you earlier in a dataview select.
Bernie
"Geoff Jones" <no********@email.com> wrote in message
news:42**********************@news.dial.pipex.com. .. Hiya
I have a DataTable containing thousands of records. Each record has a
primary key field called "ID" and another field called "PRODUCT" I want
to retrieve the rows that satisy the following criteria:
I have a list of about 100 numbers which correspond to the ID field and
also another 40 say numbers corresponding to the numbers in the PRODUCT
field. I want to show the rows that correspond to both these criteria.
My oringinal idea was to create a DataView and then generate a string an
SQL string, which I could then use with RowFilter to get the rows I want.
However, I'm uncertain of the SQL syntax to use and, more importantly, if
this is the best way to do it.
Can anybody help?
Geoff
Hi Goeff,
It shouldn't be slow. Can you send me a code snippet and an idea of the
tables you are using - number of rows, number of columns, kind of database
(sql, ms access, etc).
I would like to test it on my system to see what I experience.
Bernie
"Geoff Jones" <no********@email.com> wrote in message
news:42***********************@news.dial.pipex.com ... Hi Bernie
I've tried the "IN" scheme and it works but it is very slow. Am I doing
something wrong or can it be slow in searching?
Geoff
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:eC**************@TK2MSFTNGP10.phx.gbl... Hi Geoff,
By the way, if you already have a 'larger' datatable, you can use the
method I showed you earlier in a dataview select.
Bernie
"Geoff Jones" <no********@email.com> wrote in message
news:42**********************@news.dial.pipex.com. .. Hiya
I have a DataTable containing thousands of records. Each record has a
primary key field called "ID" and another field called "PRODUCT" I want
to retrieve the rows that satisy the following criteria:
I have a list of about 100 numbers which correspond to the ID field and
also another 40 say numbers corresponding to the numbers in the PRODUCT
field. I want to show the rows that correspond to both these criteria.
My oringinal idea was to create a DataView and then generate a string an
SQL string, which I could then use with RowFilter to get the rows I
want.
However, I'm uncertain of the SQL syntax to use and, more importantly,
if this is the best way to do it.
Can anybody help?
Geoff
Thanks for the kind offer Bernie. Unfortunately the database is massive i.e.
in excess of 200,000 rows!
The code I am using is something like:
Dim f As String = "ID IN ('543', '2345', '435', etc - goes on for about
10,000 numbers) AND PRODUCT IN ('345', '456', '3445')"
Dim vue As DataView = New DataView(theBigTable)
vue.RowFilter = f ' The program appears to hang here i.e. very slow
Obviously, the table is VERY large, so I'd expect some delay. However, the
first bit of code I wrote used a "Find" routine of a DataTable to find the
"ID" numbers (could do this because they were the primary keys). I then,
after finding the rows that satisifed the ID condition, I simply iterated
through them to find the required rows satisfying the second condition i.e.
PRODUCT.
This method worked well i.e. relatively quickly. Is it possible then that
I'm seeing a big increase in filtering time because I'm not searching on the
primary key fields?
Geoff
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:eC*************@TK2MSFTNGP09.phx.gbl... Hi Goeff,
It shouldn't be slow. Can you send me a code snippet and an idea of the
tables you are using - number of rows, number of columns, kind of database
(sql, ms access, etc).
I would like to test it on my system to see what I experience.
Bernie
"Geoff Jones" <no********@email.com> wrote in message
news:42***********************@news.dial.pipex.com ... Hi Bernie
I've tried the "IN" scheme and it works but it is very slow. Am I doing
something wrong or can it be slow in searching?
Geoff
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:eC**************@TK2MSFTNGP10.phx.gbl... Hi Geoff,
By the way, if you already have a 'larger' datatable, you can use the
method I showed you earlier in a dataview select.
Bernie
"Geoff Jones" <no********@email.com> wrote in message
news:42**********************@news.dial.pipex.com. ..
Hiya
I have a DataTable containing thousands of records. Each record has a
primary key field called "ID" and another field called "PRODUCT" I want
to retrieve the rows that satisy the following criteria:
I have a list of about 100 numbers which correspond to the ID field and
also another 40 say numbers corresponding to the numbers in the PRODUCT
field. I want to show the rows that correspond to both these criteria.
My oringinal idea was to create a DataView and then generate a string
an SQL string, which I could then use with RowFilter to get the rows I
want.
However, I'm uncertain of the SQL syntax to use and, more importantly,
if this is the best way to do it.
Can anybody help?
Geoff
"not searching on the primary key fields" i.e. not using "Find"
Geoff
"Geoff Jones" <no********@email.com> wrote in message
news:42***********************@news.dial.pipex.com ... Thanks for the kind offer Bernie. Unfortunately the database is massive
i.e. in excess of 200,000 rows!
The code I am using is something like:
Dim f As String = "ID IN ('543', '2345', '435', etc - goes on for about
10,000 numbers) AND PRODUCT IN ('345', '456', '3445')"
Dim vue As DataView = New DataView(theBigTable)
vue.RowFilter = f ' The program appears to hang here i.e. very slow
Obviously, the table is VERY large, so I'd expect some delay. However, the
first bit of code I wrote used a "Find" routine of a DataTable to find the
"ID" numbers (could do this because they were the primary keys). I then,
after finding the rows that satisifed the ID condition, I simply iterated
through them to find the required rows satisfying the second condition
i.e. PRODUCT.
This method worked well i.e. relatively quickly. Is it possible then that
I'm seeing a big increase in filtering time because I'm not searching on
the primary key fields?
Geoff
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:eC*************@TK2MSFTNGP09.phx.gbl... Hi Goeff,
It shouldn't be slow. Can you send me a code snippet and an idea of the
tables you are using - number of rows, number of columns, kind of
database (sql, ms access, etc).
I would like to test it on my system to see what I experience.
Bernie
"Geoff Jones" <no********@email.com> wrote in message
news:42***********************@news.dial.pipex.com ... Hi Bernie
I've tried the "IN" scheme and it works but it is very slow. Am I doing
something wrong or can it be slow in searching?
Geoff
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:eC**************@TK2MSFTNGP10.phx.gbl...
Hi Geoff,
By the way, if you already have a 'larger' datatable, you can use the
method I showed you earlier in a dataview select.
Bernie
"Geoff Jones" <no********@email.com> wrote in message
news:42**********************@news.dial.pipex.com. ..
> Hiya
>
> I have a DataTable containing thousands of records. Each record has a
> primary key field called "ID" and another field called "PRODUCT" I
> want to retrieve the rows that satisy the following criteria:
>
> I have a list of about 100 numbers which correspond to the ID field
> and also another 40 say numbers corresponding to the numbers in the
> PRODUCT field. I want to show the rows that correspond to both these
> criteria.
>
> My oringinal idea was to create a DataView and then generate a string
> an SQL string, which I could then use with RowFilter to get the rows I
> want.
>
> However, I'm uncertain of the SQL syntax to use and, more importantly,
> if this is the best way to do it.
>
> Can anybody help?
>
> Geoff
>
Geoff,
Can you try this piece of code.
\\\Needs one datagrid and a button the first part is to build the table.
Dim dt As New DataTable
Private Sub Form1_Load(ByVal sender _
As System.Object, ByVal e As System.EventArgs) _
Handles MyBase.Load
dt.Columns.Add("number")
For i As Integer = 0 To 200000
dt.Rows.Add(dt.NewRow)
dt.Rows(i)(0) = i.ToString
Next
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click
Dim str() As String = {"1", "3", "100", "100000", "150", "199999"}
Dim dtnew As DataTable = dt.Clone
For Each st As String In str
Dim dr As DataRow() = dt.Select("number = '" & st & "'")
If Not dr Is Nothing Then
dtnew.ImportRow(dr(0)) _
'assuming there can only be one otherwise a loop
End If
Next
DataGrid1.DataSource = dtnew
End Sub
///
(I think that it can be faster by looping through the sorted dataview and
make a clever check about the as well sorted part what has to be checked
everytime. That is a complete different approach by the way).
However try this one first because that other one is more difficult code.
I hope this helps.
Cor
Hi Geoff,
OK; I've got your code.
First, let me tell you that we will get this working effectively - 200,000
rows is not daunting. But I need more information. Am I correct in
assuming that you first specify the 10,000 rows and then you want to 'find'
rows that are among those 10,000 but also meet a certain product id
condition? If this is so, let me know - I can construct a dataview of the
10,000 and show you how to search it very easily, with or without using the
PK.
Get back to me asap. I will work on this approach until I hear from you.
Bernie
"Geoff Jones" <no********@email.com> wrote in message
news:42***********************@news.dial.pipex.com ... Thanks for the kind offer Bernie. Unfortunately the database is massive
i.e. in excess of 200,000 rows!
The code I am using is something like:
Dim f As String = "ID IN ('543', '2345', '435', etc - goes on for about
10,000 numbers) AND PRODUCT IN ('345', '456', '3445')"
Dim vue As DataView = New DataView(theBigTable)
vue.RowFilter = f ' The program appears to hang here i.e. very slow
Obviously, the table is VERY large, so I'd expect some delay. However, the
first bit of code I wrote used a "Find" routine of a DataTable to find the
"ID" numbers (could do this because they were the primary keys). I then,
after finding the rows that satisifed the ID condition, I simply iterated
through them to find the required rows satisfying the second condition
i.e. PRODUCT.
This method worked well i.e. relatively quickly. Is it possible then that
I'm seeing a big increase in filtering time because I'm not searching on
the primary key fields?
Geoff
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:eC*************@TK2MSFTNGP09.phx.gbl... Hi Goeff,
It shouldn't be slow. Can you send me a code snippet and an idea of the
tables you are using - number of rows, number of columns, kind of
database (sql, ms access, etc).
I would like to test it on my system to see what I experience.
Bernie
"Geoff Jones" <no********@email.com> wrote in message
news:42***********************@news.dial.pipex.com ... Hi Bernie
I've tried the "IN" scheme and it works but it is very slow. Am I doing
something wrong or can it be slow in searching?
Geoff
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:eC**************@TK2MSFTNGP10.phx.gbl...
Hi Geoff,
By the way, if you already have a 'larger' datatable, you can use the
method I showed you earlier in a dataview select.
Bernie
"Geoff Jones" <no********@email.com> wrote in message
news:42**********************@news.dial.pipex.com. ..
> Hiya
>
> I have a DataTable containing thousands of records. Each record has a
> primary key field called "ID" and another field called "PRODUCT" I
> want to retrieve the rows that satisy the following criteria:
>
> I have a list of about 100 numbers which correspond to the ID field
> and also another 40 say numbers corresponding to the numbers in the
> PRODUCT field. I want to show the rows that correspond to both these
> criteria.
>
> My oringinal idea was to create a DataView and then generate a string
> an SQL string, which I could then use with RowFilter to get the rows I
> want.
>
> However, I'm uncertain of the SQL syntax to use and, more importantly,
> if this is the best way to do it.
>
> Can anybody help?
>
> Geoff
>
Geof,
I was curious if the select with a OR would be faster than looping, I think
not. However this is your problem more complete in my opinion using a Select
and concatinated OR's (that stringbuilding can better using a for index
however in that part I did not wanted to change it anymore)
\\\
Dim dt As New DataTable
Private Sub Form1_Load(ByVal sender _
As System.Object, ByVal e As System.EventArgs) _
Handles MyBase.Load
dt.Columns.Add("ID")
dt.Columns.Add("PRODUCT")
For i As Integer = 0 To 200000
dt.Rows.Add(dt.NewRow)
dt.Rows(i)(0) = (i \ 10).ToString
dt.Rows(i)(1) = (i \ 100).ToString
Next
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click
Dim str1() As String = {"10", "3", "100", "150", "19999"}
Dim str2() As String = {"1", "10", "150", "19990"}
Dim dtnew1 As DataTable = dt.Clone
Dim sb1 As New System.Text.StringBuilder
For Each st As String In str1
sb1.Append("ID = '" & st & "'" & " OR ")
Next
sb1.Append("ID = 'NotExist'")
Dim dr1 As DataRow() = dt.Select(sb1.ToString)
If Not dr1 Is Nothing Then
For Each drs As DataRow In dr1
dtnew1.ImportRow(drs)
Next
End If
Dim dtnew2 As DataTable = dtnew1.Clone
Dim sb2 As New System.Text.StringBuilder
For Each st As String In str2
sb2.Append("PRODUCT = '" & st & "'" & " OR ")
Next
sb2.Append("PRODUCT = 'NotExist'")
Dim dr2 As DataRow() = dtnew1.Select(sb2.ToString)
If Not dr2 Is Nothing Then
For Each drs As DataRow In dr2
dtnew2.ImportRow(drs)
Next
End If
DataGrid1.DataSource = dtnew2
End Sub
///
I hope this helps?
Cor
Hi Cor
Very clever ideas! I especially like the cloning.
Thanks again
Geoff
"Cor Ligthert" <no************@planet.nl> wrote in message
news:%2****************@tk2msftngp13.phx.gbl... Geof,
I was curious if the select with a OR would be faster than looping, I
think not. However this is your problem more complete in my opinion using
a Select and concatinated OR's (that stringbuilding can better using a for
index however in that part I did not wanted to change it anymore)
\\\
Dim dt As New DataTable
Private Sub Form1_Load(ByVal sender _
As System.Object, ByVal e As System.EventArgs) _
Handles MyBase.Load
dt.Columns.Add("ID")
dt.Columns.Add("PRODUCT")
For i As Integer = 0 To 200000
dt.Rows.Add(dt.NewRow)
dt.Rows(i)(0) = (i \ 10).ToString
dt.Rows(i)(1) = (i \ 100).ToString
Next
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click
Dim str1() As String = {"10", "3", "100", "150", "19999"}
Dim str2() As String = {"1", "10", "150", "19990"}
Dim dtnew1 As DataTable = dt.Clone
Dim sb1 As New System.Text.StringBuilder
For Each st As String In str1
sb1.Append("ID = '" & st & "'" & " OR ")
Next
sb1.Append("ID = 'NotExist'")
Dim dr1 As DataRow() = dt.Select(sb1.ToString)
If Not dr1 Is Nothing Then
For Each drs As DataRow In dr1
dtnew1.ImportRow(drs)
Next
End If
Dim dtnew2 As DataTable = dtnew1.Clone
Dim sb2 As New System.Text.StringBuilder
For Each st As String In str2
sb2.Append("PRODUCT = '" & st & "'" & " OR ")
Next
sb2.Append("PRODUCT = 'NotExist'")
Dim dr2 As DataRow() = dtnew1.Select(sb2.ToString)
If Not dr2 Is Nothing Then
For Each drs As DataRow In dr2
dtnew2.ImportRow(drs)
Next
End If
DataGrid1.DataSource = dtnew2
End Sub
///
I hope this helps?
Cor
Hi Bernie
I hope I'm not putting you to too much trouble. You've been invaluable
already!
At the moment, I'm getting a list of approximately 10,000 ID's i.e. primary
keys from another process which give me the rows in the 200,000 total. As I
said previously, I was originally getting these rows by using a
Table.Rows.Find. From these 10,000 or so rows, I'm trying to filter them
down to those which satisfy a PRODUCT field criteria e.g. PRODUCT IN
('3454', '324', '7855', etc.)
Indeed, my next step may be to do something like (forgive the pseudo code):
WHERE PRODUCT = '2345' AND COST = '3.45'
where COST is another field.
I had considered the possibility of using multiple tables i.e. searching a
table for rows, creating a new table, searching this new table with a new
criteria etc. etc. in the same way that Cor has suggested. I'm interested in
both techniques i.e. cloning and SQL type coding. What are the advantages
and disadvantages of both?
Thanks again for your continuing help.
Geoff
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:uw**************@TK2MSFTNGP09.phx.gbl... Hi Geoff,
OK; I've got your code.
First, let me tell you that we will get this working effectively - 200,000
rows is not daunting. But I need more information. Am I correct in
assuming that you first specify the 10,000 rows and then you want to
'find' rows that are among those 10,000 but also meet a certain product id
condition? If this is so, let me know - I can construct a dataview of the
10,000 and show you how to search it very easily, with or without using
the PK.
Get back to me asap. I will work on this approach until I hear from you.
Bernie
"Geoff Jones" <no********@email.com> wrote in message
news:42***********************@news.dial.pipex.com ... Thanks for the kind offer Bernie. Unfortunately the database is massive
i.e. in excess of 200,000 rows!
The code I am using is something like:
Dim f As String = "ID IN ('543', '2345', '435', etc - goes on for about
10,000 numbers) AND PRODUCT IN ('345', '456', '3445')"
Dim vue As DataView = New DataView(theBigTable)
vue.RowFilter = f ' The program appears to hang here i.e. very slow
Obviously, the table is VERY large, so I'd expect some delay. However,
the first bit of code I wrote used a "Find" routine of a DataTable to
find the "ID" numbers (could do this because they were the primary keys).
I then, after finding the rows that satisifed the ID condition, I simply
iterated through them to find the required rows satisfying the second
condition i.e. PRODUCT.
This method worked well i.e. relatively quickly. Is it possible then that
I'm seeing a big increase in filtering time because I'm not searching on
the primary key fields?
Geoff
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:eC*************@TK2MSFTNGP09.phx.gbl... Hi Goeff,
It shouldn't be slow. Can you send me a code snippet and an idea of the
tables you are using - number of rows, number of columns, kind of
database (sql, ms access, etc).
I would like to test it on my system to see what I experience.
Bernie
"Geoff Jones" <no********@email.com> wrote in message
news:42***********************@news.dial.pipex.com ...
Hi Bernie
I've tried the "IN" scheme and it works but it is very slow. Am I doing
something wrong or can it be slow in searching?
Geoff
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:eC**************@TK2MSFTNGP10.phx.gbl...
> Hi Geoff,
>
> By the way, if you already have a 'larger' datatable, you can use the
> method I showed you earlier in a dataview select.
>
> Bernie
>
> "Geoff Jones" <no********@email.com> wrote in message
> news:42**********************@news.dial.pipex.com. ..
>> Hiya
>>
>> I have a DataTable containing thousands of records. Each record has a
>> primary key field called "ID" and another field called "PRODUCT" I
>> want to retrieve the rows that satisy the following criteria:
>>
>> I have a list of about 100 numbers which correspond to the ID field
>> and also another 40 say numbers corresponding to the numbers in the
>> PRODUCT field. I want to show the rows that correspond to both these
>> criteria.
>>
>> My oringinal idea was to create a DataView and then generate a string
>> an SQL string, which I could then use with RowFilter to get the rows
>> I want.
>>
>> However, I'm uncertain of the SQL syntax to use and, more
>> importantly, if this is the best way to do it.
>>
>> Can anybody help?
>>
>> Geoff
>>
>
>
>
Hi Geoff,
Not putting me out at all; glad to help.
But I'm still confused. You originally said this:
I have a list of about 100 numbers which correspond to the ID field
and also another 40 say numbers corresponding to the numbers in the
PRODUCT field.
Now you mentioned 10000 - quite a difference.
In any event, that may not be an issue. You say now you have these 10,000
rows - in a dataset/datatable? I have a code sample below that's very fast
and might serve as a template for you. It fills a dataset and then makes a
dataview from the datatable and then filters the rows as necessary, using 2
criteria (replace these with product and cost).
Let me know if we're getting somewhere.
Regards,
Bernie
Dim oconn As New SqlConnection("data source=d5z0071;initial
catalog=imc;integrated security=sspi;")
Dim ocmd As New SqlCommand("select * from prod order by bipad", oconn)
Dim oda As New SqlDataAdapter(ocmd)
Dim ods As New DataSet("prod")
Try
oconn.Open()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
oda.Fill(ods, "prod")
Dim vue As New DataView(ods.Tables(0))
vue.Sort = "bipad"
vue.RowFilter = "bipad in ('18772','18770','01043') and issuecode =
'200304'"
Dim irow As DataRow
Dim irows As DataRow()
Dim i As Integer
For i = 0 To vue.Count - 1
MessageBox.Show(vue(i)("bipad"))
MessageBox.Show(vue(i)("issuecode"))
MessageBox.Show(vue(i)("usprice"))
Next
oconn.Close()
"Geoff Jones" <no********@email.com> wrote in message
news:42***********************@news.dial.pipex.com ... Hi Bernie
I hope I'm not putting you to too much trouble. You've been invaluable
already!
At the moment, I'm getting a list of approximately 10,000 ID's i.e.
primary keys from another process which give me the rows in the 200,000
total. As I said previously, I was originally getting these rows by using
a Table.Rows.Find. From these 10,000 or so rows, I'm trying to filter them
down to those which satisfy a PRODUCT field criteria e.g. PRODUCT IN
('3454', '324', '7855', etc.)
Indeed, my next step may be to do something like (forgive the pseudo
code):
WHERE PRODUCT = '2345' AND COST = '3.45'
where COST is another field.
I had considered the possibility of using multiple tables i.e. searching a
table for rows, creating a new table, searching this new table with a new
criteria etc. etc. in the same way that Cor has suggested. I'm interested
in both techniques i.e. cloning and SQL type coding. What are the
advantages and disadvantages of both?
Thanks again for your continuing help.
Geoff
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:uw**************@TK2MSFTNGP09.phx.gbl... Hi Geoff,
OK; I've got your code.
First, let me tell you that we will get this working effectively -
200,000 rows is not daunting. But I need more information. Am I correct
in assuming that you first specify the 10,000 rows and then you want to
'find' rows that are among those 10,000 but also meet a certain product
id condition? If this is so, let me know - I can construct a dataview of
the 10,000 and show you how to search it very easily, with or without
using the PK.
Get back to me asap. I will work on this approach until I hear from you.
Bernie
"Geoff Jones" <no********@email.com> wrote in message
news:42***********************@news.dial.pipex.com ... Thanks for the kind offer Bernie. Unfortunately the database is massive
i.e. in excess of 200,000 rows!
The code I am using is something like:
Dim f As String = "ID IN ('543', '2345', '435', etc - goes on for about
10,000 numbers) AND PRODUCT IN ('345', '456', '3445')"
Dim vue As DataView = New DataView(theBigTable)
vue.RowFilter = f ' The program appears to hang here i.e. very slow
Obviously, the table is VERY large, so I'd expect some delay. However,
the first bit of code I wrote used a "Find" routine of a DataTable to
find the "ID" numbers (could do this because they were the primary
keys). I then, after finding the rows that satisifed the ID condition, I
simply iterated through them to find the required rows satisfying the
second condition i.e. PRODUCT.
This method worked well i.e. relatively quickly. Is it possible then
that I'm seeing a big increase in filtering time because I'm not
searching on the primary key fields?
Geoff
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:eC*************@TK2MSFTNGP09.phx.gbl...
Hi Goeff,
It shouldn't be slow. Can you send me a code snippet and an idea of
the tables you are using - number of rows, number of columns, kind of
database (sql, ms access, etc).
I would like to test it on my system to see what I experience.
Bernie
"Geoff Jones" <no********@email.com> wrote in message
news:42***********************@news.dial.pipex.com ...
> Hi Bernie
>
> I've tried the "IN" scheme and it works but it is very slow. Am I
> doing something wrong or can it be slow in searching?
>
> Geoff
>
> "Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
> news:eC**************@TK2MSFTNGP10.phx.gbl...
>> Hi Geoff,
>>
>> By the way, if you already have a 'larger' datatable, you can use the
>> method I showed you earlier in a dataview select.
>>
>> Bernie
>>
>> "Geoff Jones" <no********@email.com> wrote in message
>> news:42**********************@news.dial.pipex.com. ..
>>> Hiya
>>>
>>> I have a DataTable containing thousands of records. Each record has
>>> a primary key field called "ID" and another field called "PRODUCT" I
>>> want to retrieve the rows that satisy the following criteria:
>>>
>>> I have a list of about 100 numbers which correspond to the ID field
>>> and also another 40 say numbers corresponding to the numbers in the
>>> PRODUCT field. I want to show the rows that correspond to both these
>>> criteria.
>>>
>>> My oringinal idea was to create a DataView and then generate a
>>> string an SQL string, which I could then use with RowFilter to get
>>> the rows I want.
>>>
>>> However, I'm uncertain of the SQL syntax to use and, more
>>> importantly, if this is the best way to do it.
>>>
>>> Can anybody help?
>>>
>>> Geoff
>>>
>>
>>
>>
>
>
Hiya Bernie
The difference i.e. 100 as opposed to 10000 was merely the range of
possibilities. Sorry for the confusion.
The rows are in a DataSet; but also in a table - which is stored in the
DataSet.
The code looks excellent. I'll give it a try tomorrow.
Geoff
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:u3**************@TK2MSFTNGP09.phx.gbl... Hi Geoff,
Not putting me out at all; glad to help.
But I'm still confused. You originally said this:
I have a list of about 100 numbers which correspond to the ID field
and also another 40 say numbers corresponding to the numbers in the
PRODUCT field.
Now you mentioned 10000 - quite a difference.
In any event, that may not be an issue. You say now you have these 10,000
rows - in a dataset/datatable? I have a code sample below that's very
fast and might serve as a template for you. It fills a dataset and then
makes a dataview from the datatable and then filters the rows as
necessary, using 2 criteria (replace these with product and cost).
Let me know if we're getting somewhere.
Regards,
Bernie
Dim oconn As New SqlConnection("data source=d5z0071;initial
catalog=imc;integrated security=sspi;")
Dim ocmd As New SqlCommand("select * from prod order by bipad", oconn)
Dim oda As New SqlDataAdapter(ocmd)
Dim ods As New DataSet("prod")
Try
oconn.Open()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
oda.Fill(ods, "prod")
Dim vue As New DataView(ods.Tables(0))
vue.Sort = "bipad"
vue.RowFilter = "bipad in ('18772','18770','01043') and issuecode =
'200304'"
Dim irow As DataRow
Dim irows As DataRow()
Dim i As Integer
For i = 0 To vue.Count - 1
MessageBox.Show(vue(i)("bipad"))
MessageBox.Show(vue(i)("issuecode"))
MessageBox.Show(vue(i)("usprice"))
Next
oconn.Close()
"Geoff Jones" <no********@email.com> wrote in message
news:42***********************@news.dial.pipex.com ...
Hi Bernie
I hope I'm not putting you to too much trouble. You've been invaluable
already!
At the moment, I'm getting a list of approximately 10,000 ID's i.e.
primary keys from another process which give me the rows in the 200,000
total. As I said previously, I was originally getting these rows by using
a Table.Rows.Find. From these 10,000 or so rows, I'm trying to filter
them down to those which satisfy a PRODUCT field criteria e.g. PRODUCT IN
('3454', '324', '7855', etc.)
Indeed, my next step may be to do something like (forgive the pseudo
code):
WHERE PRODUCT = '2345' AND COST = '3.45'
where COST is another field.
I had considered the possibility of using multiple tables i.e. searching
a table for rows, creating a new table, searching this new table with a
new criteria etc. etc. in the same way that Cor has suggested. I'm
interested in both techniques i.e. cloning and SQL type coding. What are
the advantages and disadvantages of both?
Thanks again for your continuing help.
Geoff
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:uw**************@TK2MSFTNGP09.phx.gbl... Hi Geoff,
OK; I've got your code.
First, let me tell you that we will get this working effectively -
200,000 rows is not daunting. But I need more information. Am I
correct in assuming that you first specify the 10,000 rows and then you
want to 'find' rows that are among those 10,000 but also meet a certain
product id condition? If this is so, let me know - I can construct a
dataview of the 10,000 and show you how to search it very easily, with
or without using the PK.
Get back to me asap. I will work on this approach until I hear from
you.
Bernie
"Geoff Jones" <no********@email.com> wrote in message
news:42***********************@news.dial.pipex.com ...
Thanks for the kind offer Bernie. Unfortunately the database is massive
i.e. in excess of 200,000 rows!
The code I am using is something like:
Dim f As String = "ID IN ('543', '2345', '435', etc - goes on for about
10,000 numbers) AND PRODUCT IN ('345', '456', '3445')"
Dim vue As DataView = New DataView(theBigTable)
vue.RowFilter = f ' The program appears to hang here i.e. very slow
Obviously, the table is VERY large, so I'd expect some delay. However,
the first bit of code I wrote used a "Find" routine of a DataTable to
find the "ID" numbers (could do this because they were the primary
keys). I then, after finding the rows that satisifed the ID condition,
I simply iterated through them to find the required rows satisfying the
second condition i.e. PRODUCT.
This method worked well i.e. relatively quickly. Is it possible then
that I'm seeing a big increase in filtering time because I'm not
searching on the primary key fields?
Geoff
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:eC*************@TK2MSFTNGP09.phx.gbl...
> Hi Goeff,
>
> It shouldn't be slow. Can you send me a code snippet and an idea of
> the tables you are using - number of rows, number of columns, kind of
> database (sql, ms access, etc).
>
> I would like to test it on my system to see what I experience.
>
> Bernie
>
> "Geoff Jones" <no********@email.com> wrote in message
> news:42***********************@news.dial.pipex.com ...
>> Hi Bernie
>>
>> I've tried the "IN" scheme and it works but it is very slow. Am I
>> doing something wrong or can it be slow in searching?
>>
>> Geoff
>>
>> "Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
>> news:eC**************@TK2MSFTNGP10.phx.gbl...
>>> Hi Geoff,
>>>
>>> By the way, if you already have a 'larger' datatable, you can use
>>> the method I showed you earlier in a dataview select.
>>>
>>> Bernie
>>>
>>> "Geoff Jones" <no********@email.com> wrote in message
>>> news:42**********************@news.dial.pipex.com. ..
>>>> Hiya
>>>>
>>>> I have a DataTable containing thousands of records. Each record has
>>>> a primary key field called "ID" and another field called "PRODUCT"
>>>> I want to retrieve the rows that satisy the following criteria:
>>>>
>>>> I have a list of about 100 numbers which correspond to the ID field
>>>> and also another 40 say numbers corresponding to the numbers in the
>>>> PRODUCT field. I want to show the rows that correspond to both
>>>> these criteria.
>>>>
>>>> My oringinal idea was to create a DataView and then generate a
>>>> string an SQL string, which I could then use with RowFilter to get
>>>> the rows I want.
>>>>
>>>> However, I'm uncertain of the SQL syntax to use and, more
>>>> importantly, if this is the best way to do it.
>>>>
>>>> Can anybody help?
>>>>
>>>> Geoff
>>>>
>>>
>>>
>>>
>>
>>
>
>
HI Bernie
Thanks for the code. I've learned a lot in the last few days with your and
others help. I do like the idea of using "IN" but unfortunately it does seem
to be slower than my original idea. I've written some code based on Cor's
which demonstrates the difference:
The first bit of code filters the dataset using "IN" and the last bit does
it using a "Find". When I ran it this morning, the SQL code took 41 seconds
whilst the Find took 0.03 seconds. Quite a difference.
Obviously, I couldn't use the same technique i.e. "Find", from the outset,
using a non-primary key (because it needs a primary key to work).
Maybe the best approach is a mixture of both techniques?
Geoff
P.S. Code is below:
Dim dt As New DataTable
Private Sub Form1_Load(ByVal sender _
As System.Object, ByVal e As System.EventArgs) _
Handles MyBase.Load
dt.Columns.Add("ID")
dt.Columns.Add("PRODUCT")
dt.Columns.Add("COST")
For i As Integer = 0 To 200000
dt.Rows.Add(dt.NewRow)
dt.Rows(i)(0) = i.ToString
Dim rg As Random = New Random(i)
Dim rn As Integer = CInt(rg.Next(0, 200000))
dt.Rows(i)(1) = rn.ToString
rg = New Random(rn)
rn = CInt(rg.Next(0, 200000))
dt.Rows(i)(2) = rn.ToString
Next
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click
Dim start As DateTime = DateTime.Now
Dim numOfRows As Integer = dt.Rows.Count
Dim vue As DataView = New DataView(dt)
vue.Sort = "ID"
vue.RowFilter = "ID IN
('139211','139215','139214','139213','139212','139 211','139210','139259','139258','139257','139251', '139255','139254','139253','139252','139251','1392 50','139249','139248','139247','139241','139245',' 139244','139243','139242','139241','139240','13923 9','139238','139237','139231','139235','139234','1 39233','139232','139231','139230','139229','139228 ','139227','139221','139225','139224','139223','13 9222','139221','139220','139219','139218','139217' ,'139211','139215','139214','139213','139212','139 211','139210','139209','139208','139207','139201', '139205','139204','139203','139202','139201','1392 00','139199','139198','139197','139191','139195',' 139194','139193','139192','139191','139190','13918 9','139188','139187','139181','139185','139184','1 39183','139182','139181','139180','139179','139178 ','139177','139171','139175','139174','139173','13 9172','139171','139170','139119','139118','139117' ,'139111','139115','139114','139113','139112','139 111','139110','139159','139158','139157','139151', '139155','139154','139153','139152','139151','1391 50','139149','139148','139147','139141','139145',' 139144','139143','139142','139141','139140','13913 9','139138','139137','139131','139135','139134','1 39133','139132','139131','139130','139129','139128 ','139127','139121','139125','139124','139123','13 9122','139121','139120','139119','139118','139117' ,'139111','139115','139114','139113','139112','139 111','139110','139109','139108','139107','139101', '139105','139104','139103','139102','139101','1391 00','139099','139098','139097','139091','139095',' 139094','139093','139092','139091','139090','13908 9','139088','139087','139081','139085','139084','1 39083','139082','139081','139080','139079','139078 ','139077','139071','139075','139074','139073','13 9072','139071','139070','139019','139018','139017' ,'139011','139015','139014','139013','139012','139 011','139010','139059','139058','139057','139051', '139055','139054','139053','139052','139051','1390 50','139049','139048','139047','139041','139045',' 139044','139043','139042','139041','139040','13903 9','139038','139037','139031','139035','139034','1 39033','139032','139031','139030','139029','139028 ','139027','139021','139025','139024','139023','13 9022','139021','139020','139019','139018','139017' ,'139011','139015','139014','139013','139012','139 011','139010','139009','139008','139007','139001', '139005','139004','139003','139002','139001','1390 00','138999','138998','138997','138991','138995',' 138994','138993','138992','138991','138990','13898 9','138988','138987','138981','138985','138984','1 38983','138982','138981','138980','138979','138978 ','138977','138971','138975','138974','138973','13 8972','139275','139274','139273','139272','139271' ,'139270','139219','139218','139217')"
DataGrid1.DataSource = dt
Dim finish As DateTime = DateTime.Now
Dim time As TimeSpan = finish.Subtract(start)
Debug.WriteLine("Time taken = " & time.TotalSeconds)
DataGrid1.DataSource = dt
dt.PrimaryKey = New DataColumn() {dt.Columns("ID")}
start = DateTime.Now
Dim newTable As DataTable = dt.Clone
Dim str1() As String = {"139211", "139215", "139214", "139213", "139212",
"139211", "139210", "139259", "139258", "139257", "139251", "139255",
"139254", "139253", "139252", "139251", "139250", "139249", "139248",
"139247", "139241", "139245", "139244", "139243", "139242", "139241",
"139240", "139239", "139238", "139237", "139231", "139235", "139234",
"139233", "139232", "139231", "139230", "139229", "139228", "139227",
"139221", "139225", "139224", "139223", "139222", "139221", "139220",
"139219", "139218", "139217", "139211", "139215", "139214", "139213",
"139212", "139211", "139210", "139209", "139208", "139207", "139201",
"139205", "139204", "139203", "139202", "139201", "139200", "139199",
"139198", "139197", "139191", "139195", "139194", "139193", "139192",
"139191", "139190", "139189", "139188", "139187", "139181", "139185",
"139184", "139183", "139182", "139181", "139180", "139179", "139178",
"139177", "139171", "139175", "139174", "139173", "139172", "139171",
"139170", "139119", "139118", "139117", "139111", "139115", "139114",
"139113", "139112", "139111", "139110", "139159", "139158", "139157",
"139151", "139155", "139154", "139153", "139152", "139151", "139150",
"139149", "139148", "139147", "139141", "139145", "139144", "139143",
"139142", "139141", "139140", "139139", "139138", "139137", "139131",
"139135", "139134", "139133", "139132", "139131", "139130", "139129",
"139128", "139127", "139121", "139125", "139124", "139123", "139122",
"139121", "139120", "139119", "139118", "139117", "139111", "139115",
"139114", "139113", "139112", "139111", "139110", "139109", "139108",
"139107", "139101", "139105", "139104", "139103", "139102", "139101",
"139100", "139099", "139098", "139097", "139091", "139095", "139094",
"139093", "139092", "139091", "139090", "139089", "139088", "139087",
"139081", "139085", "139084", "139083", "139082", "139081", "139080",
"139079", "139078", "139077", "139071", "139075", "139074", "139073",
"139072", "139071", "139070", "139019", "139018", "139017", "139011",
"139015", "139014", "139013", "139012", "139011", "139010", "139059",
"139058", "139057", "139051", "139055", "139054", "139053", "139052",
"139051", "139050", "139049", "139048", "139047", "139041", "139045",
"139044", "139043", "139042", "139041", "139040", "139039", "139038",
"139037", "139031", "139035", "139034", "139033", "139032", "139031",
"139030", "139029", "139028", "139027", "139021", "139025", "139024",
"139023", "139022", "139021", "139020", "139019", "139018", "139017",
"139011", "139015", "139014", "139013", "139012", "139011", "139010",
"139009", "139008", "139007", "139001", "139005", "139004", "139003",
"139002", "139001", "139000", "138999", "138998", "138997", "138991",
"138995", "138994", "138993", "138992", "138991", "138990", "138989",
"138988", "138987", "138981", "138985", "138984", "138983", "138982",
"138981", "138980", "138979", "138978", "138977", "138971", "138975",
"138974", "138973", "138972", "139275", "139274", "139273", "139272",
"139271", "139270", "139219", "139218", "139217"}
For Each st As String In str1
Dim row As DataRow = dt.Rows.Find(st)
If newTable.Rows.Find(st) Is Nothing Then
newTable.ImportRow(row)
End If
Next
finish = DateTime.Now
time = finish.Subtract(start)
Debug.WriteLine("Time taken = " & time.TotalSeconds)
DataGrid1.DataSource = newTable
End Sub
"Geoff Jones" <no********@email.com> wrote in message
news:42***********************@news.dial.pipex.com ... Hiya Bernie
The difference i.e. 100 as opposed to 10000 was merely the range of
possibilities. Sorry for the confusion.
The rows are in a DataSet; but also in a table - which is stored in the
DataSet.
The code looks excellent. I'll give it a try tomorrow.
Geoff
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:u3**************@TK2MSFTNGP09.phx.gbl... Hi Geoff,
Not putting me out at all; glad to help.
But I'm still confused. You originally said this:
I have a list of about 100 numbers which correspond to the ID field
and also another 40 say numbers corresponding to the numbers in the
PRODUCT field.
Now you mentioned 10000 - quite a difference.
In any event, that may not be an issue. You say now you have these
10,000 rows - in a dataset/datatable? I have a code sample below that's
very fast and might serve as a template for you. It fills a dataset and
then makes a dataview from the datatable and then filters the rows as
necessary, using 2 criteria (replace these with product and cost).
Let me know if we're getting somewhere.
Regards,
Bernie
Dim oconn As New SqlConnection("data source=d5z0071;initial
catalog=imc;integrated security=sspi;")
Dim ocmd As New SqlCommand("select * from prod order by bipad", oconn)
Dim oda As New SqlDataAdapter(ocmd)
Dim ods As New DataSet("prod")
Try
oconn.Open()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
oda.Fill(ods, "prod")
Dim vue As New DataView(ods.Tables(0))
vue.Sort = "bipad"
vue.RowFilter = "bipad in ('18772','18770','01043') and issuecode =
'200304'"
Dim irow As DataRow
Dim irows As DataRow()
Dim i As Integer
For i = 0 To vue.Count - 1
MessageBox.Show(vue(i)("bipad"))
MessageBox.Show(vue(i)("issuecode"))
MessageBox.Show(vue(i)("usprice"))
Next
oconn.Close()
"Geoff Jones" <no********@email.com> wrote in message
news:42***********************@news.dial.pipex.com ...
Hi Bernie
I hope I'm not putting you to too much trouble. You've been invaluable
already!
At the moment, I'm getting a list of approximately 10,000 ID's i.e.
primary keys from another process which give me the rows in the 200,000
total. As I said previously, I was originally getting these rows by
using a Table.Rows.Find. From these 10,000 or so rows, I'm trying to
filter them down to those which satisfy a PRODUCT field criteria e.g.
PRODUCT IN ('3454', '324', '7855', etc.)
Indeed, my next step may be to do something like (forgive the pseudo
code):
WHERE PRODUCT = '2345' AND COST = '3.45'
where COST is another field.
I had considered the possibility of using multiple tables i.e. searching
a table for rows, creating a new table, searching this new table with a
new criteria etc. etc. in the same way that Cor has suggested. I'm
interested in both techniques i.e. cloning and SQL type coding. What are
the advantages and disadvantages of both?
Thanks again for your continuing help.
Geoff
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:uw**************@TK2MSFTNGP09.phx.gbl...
Hi Geoff,
OK; I've got your code.
First, let me tell you that we will get this working effectively -
200,000 rows is not daunting. But I need more information. Am I
correct in assuming that you first specify the 10,000 rows and then you
want to 'find' rows that are among those 10,000 but also meet a certain
product id condition? If this is so, let me know - I can construct a
dataview of the 10,000 and show you how to search it very easily, with
or without using the PK.
Get back to me asap. I will work on this approach until I hear from
you.
Bernie
"Geoff Jones" <no********@email.com> wrote in message
news:42***********************@news.dial.pipex.com ...
> Thanks for the kind offer Bernie. Unfortunately the database is
> massive i.e. in excess of 200,000 rows!
>
> The code I am using is something like:
>
> Dim f As String = "ID IN ('543', '2345', '435', etc - goes on for
> about 10,000 numbers) AND PRODUCT IN ('345', '456', '3445')"
>
> Dim vue As DataView = New DataView(theBigTable)
>
> vue.RowFilter = f ' The program appears to hang here i.e. very slow
>
> Obviously, the table is VERY large, so I'd expect some delay. However,
> the first bit of code I wrote used a "Find" routine of a DataTable to
> find the "ID" numbers (could do this because they were the primary
> keys). I then, after finding the rows that satisifed the ID condition,
> I simply iterated through them to find the required rows satisfying
> the second condition i.e. PRODUCT.
>
> This method worked well i.e. relatively quickly. Is it possible then
> that I'm seeing a big increase in filtering time because I'm not
> searching on the primary key fields?
>
> Geoff
>
>
> "Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
> news:eC*************@TK2MSFTNGP09.phx.gbl...
>> Hi Goeff,
>>
>> It shouldn't be slow. Can you send me a code snippet and an idea of
>> the tables you are using - number of rows, number of columns, kind of
>> database (sql, ms access, etc).
>>
>> I would like to test it on my system to see what I experience.
>>
>> Bernie
>>
>> "Geoff Jones" <no********@email.com> wrote in message
>> news:42***********************@news.dial.pipex.com ...
>>> Hi Bernie
>>>
>>> I've tried the "IN" scheme and it works but it is very slow. Am I
>>> doing something wrong or can it be slow in searching?
>>>
>>> Geoff
>>>
>>> "Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
>>> news:eC**************@TK2MSFTNGP10.phx.gbl...
>>>> Hi Geoff,
>>>>
>>>> By the way, if you already have a 'larger' datatable, you can use
>>>> the method I showed you earlier in a dataview select.
>>>>
>>>> Bernie
>>>>
>>>> "Geoff Jones" <no********@email.com> wrote in message
>>>> news:42**********************@news.dial.pipex.com. ..
>>>>> Hiya
>>>>>
>>>>> I have a DataTable containing thousands of records. Each record
>>>>> has a primary key field called "ID" and another field called
>>>>> "PRODUCT" I want to retrieve the rows that satisy the following
>>>>> criteria:
>>>>>
>>>>> I have a list of about 100 numbers which correspond to the ID
>>>>> field and also another 40 say numbers corresponding to the numbers
>>>>> in the PRODUCT field. I want to show the rows that correspond to
>>>>> both these criteria.
>>>>>
>>>>> My oringinal idea was to create a DataView and then generate a
>>>>> string an SQL string, which I could then use with RowFilter to get
>>>>> the rows I want.
>>>>>
>>>>> However, I'm uncertain of the SQL syntax to use and, more
>>>>> importantly, if this is the best way to do it.
>>>>>
>>>>> Can anybody help?
>>>>>
>>>>> Geoff
>>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>
Hi Goeff,
You are correct; Cor's approach is much faster.
Good luck,
Bernie
"Geoff Jones" <no********@email.com> wrote in message
news:42***********************@news.dial.pipex.com ... HI Bernie
Thanks for the code. I've learned a lot in the last few days with your and
others help. I do like the idea of using "IN" but unfortunately it does
seem to be slower than my original idea. I've written some code based on
Cor's which demonstrates the difference:
The first bit of code filters the dataset using "IN" and the last bit does
it using a "Find". When I ran it this morning, the SQL code took 41
seconds whilst the Find took 0.03 seconds. Quite a difference.
Obviously, I couldn't use the same technique i.e. "Find", from the outset,
using a non-primary key (because it needs a primary key to work).
Maybe the best approach is a mixture of both techniques?
Geoff
P.S. Code is below:
Dim dt As New DataTable
Private Sub Form1_Load(ByVal sender _
As System.Object, ByVal e As System.EventArgs) _
Handles MyBase.Load
dt.Columns.Add("ID")
dt.Columns.Add("PRODUCT")
dt.Columns.Add("COST")
For i As Integer = 0 To 200000
dt.Rows.Add(dt.NewRow)
dt.Rows(i)(0) = i.ToString
Dim rg As Random = New Random(i)
Dim rn As Integer = CInt(rg.Next(0, 200000))
dt.Rows(i)(1) = rn.ToString
rg = New Random(rn)
rn = CInt(rg.Next(0, 200000))
dt.Rows(i)(2) = rn.ToString
Next
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click
Dim start As DateTime = DateTime.Now
Dim numOfRows As Integer = dt.Rows.Count
Dim vue As DataView = New DataView(dt)
vue.Sort = "ID"
vue.RowFilter = "ID IN
('139211','139215','139214','139213','139212','139 211','139210','139259','139258','139257','139251', '139255','139254','139253','139252','139251','1392 50','139249','139248','139247','139241','139245',' 139244','139243','139242','139241','139240','13923 9','139238','139237','139231','139235','139234','1 39233','139232','139231','139230','139229','139228 ','139227','139221','139225','139224','139223','13 9222','139221','139220','139219','139218','139217' ,'139211','139215','139214','139213','139212','139 211','139210','139209','139208','139207','139201', '139205','139204','139203','139202','139201','1392 00','139199','139198','139197','139191','139195',' 139194','139193','139192','139191','139190','13918 9','139188','139187','139181','139185','139184','1 39183','139182','139181','139180','139179','139178 ','139177','139171','139175','139174','139173','13 9172','139171','139170','139119','139118','139117' ,'139111','139115','139114','139113','139112','139 111','139110','139159','139158','139157','139151', '139155','139154','139153','139152','139151','1391 50','139149','139148','139147','139141','139145',' 139144','139143','139142','139141','139140','13913 9','139138','139137','139131','139135','139134','1 39133','139132','139131','139130','139129','139128 ','139127','139121','139125','139124','139123','13 9122','139121','139120','139119','139118','139117' ,'139111','139115','139114','139113','139112','139 111','139110','139109','139108','139107','139101', '139105','139104','139103','139102','139101','1391 00','139099','139098','139097','139091','139095',' 139094','139093','139092','139091','139090','13908 9','139088','139087','139081','139085','139084','1 39083','139082','139081','139080','139079','139078 ','139077','139071','139075','139074','139073','13 9072','139071','139070','139019','139018','139017' ,'139011','139015','139014','139013','139012','139 011','139010','139059','139058','139057','139051', '139055','139054','139053','139052','139051','1390 50','139049','139048','139047','139041','139045',' 139044','139043','139042','139041','139040','13903 9','139038','139037','139031','139035','139034','1 39033','139032','139031','139030','139029','139028 ','139027','139021','139025','139024','139023','13 9022','139021','139020','139019','139018','139017' ,'139011','139015','139014','139013','139012','139 011','139010','139009','139008','139007','139001', '139005','139004','139003','139002','139001','1390 00','138999','138998','138997','138991','138995',' 138994','138993','138992','138991','138990','13898 9','138988','138987','138981','138985','138984','1 38983','138982','138981','138980','138979','138978 ','138977','138971','138975','138974','138973','13 8972','139275','139274','139273','139272','139271' ,'139270','139219','139218','139217')"
DataGrid1.DataSource = dt
Dim finish As DateTime = DateTime.Now
Dim time As TimeSpan = finish.Subtract(start)
Debug.WriteLine("Time taken = " & time.TotalSeconds)
DataGrid1.DataSource = dt
dt.PrimaryKey = New DataColumn() {dt.Columns("ID")}
start = DateTime.Now
Dim newTable As DataTable = dt.Clone
Dim str1() As String = {"139211", "139215", "139214", "139213", "139212",
"139211", "139210", "139259", "139258", "139257", "139251", "139255",
"139254", "139253", "139252", "139251", "139250", "139249", "139248",
"139247", "139241", "139245", "139244", "139243", "139242", "139241",
"139240", "139239", "139238", "139237", "139231", "139235", "139234",
"139233", "139232", "139231", "139230", "139229", "139228", "139227",
"139221", "139225", "139224", "139223", "139222", "139221", "139220",
"139219", "139218", "139217", "139211", "139215", "139214", "139213",
"139212", "139211", "139210", "139209", "139208", "139207", "139201",
"139205", "139204", "139203", "139202", "139201", "139200", "139199",
"139198", "139197", "139191", "139195", "139194", "139193", "139192",
"139191", "139190", "139189", "139188", "139187", "139181", "139185",
"139184", "139183", "139182", "139181", "139180", "139179", "139178",
"139177", "139171", "139175", "139174", "139173", "139172", "139171",
"139170", "139119", "139118", "139117", "139111", "139115", "139114",
"139113", "139112", "139111", "139110", "139159", "139158", "139157",
"139151", "139155", "139154", "139153", "139152", "139151", "139150",
"139149", "139148", "139147", "139141", "139145", "139144", "139143",
"139142", "139141", "139140", "139139", "139138", "139137", "139131",
"139135", "139134", "139133", "139132", "139131", "139130", "139129",
"139128", "139127", "139121", "139125", "139124", "139123", "139122",
"139121", "139120", "139119", "139118", "139117", "139111", "139115",
"139114", "139113", "139112", "139111", "139110", "139109", "139108",
"139107", "139101", "139105", "139104", "139103", "139102", "139101",
"139100", "139099", "139098", "139097", "139091", "139095", "139094",
"139093", "139092", "139091", "139090", "139089", "139088", "139087",
"139081", "139085", "139084", "139083", "139082", "139081", "139080",
"139079", "139078", "139077", "139071", "139075", "139074", "139073",
"139072", "139071", "139070", "139019", "139018", "139017", "139011",
"139015", "139014", "139013", "139012", "139011", "139010", "139059",
"139058", "139057", "139051", "139055", "139054", "139053", "139052",
"139051", "139050", "139049", "139048", "139047", "139041", "139045",
"139044", "139043", "139042", "139041", "139040", "139039", "139038",
"139037", "139031", "139035", "139034", "139033", "139032", "139031",
"139030", "139029", "139028", "139027", "139021", "139025", "139024",
"139023", "139022", "139021", "139020", "139019", "139018", "139017",
"139011", "139015", "139014", "139013", "139012", "139011", "139010",
"139009", "139008", "139007", "139001", "139005", "139004", "139003",
"139002", "139001", "139000", "138999", "138998", "138997", "138991",
"138995", "138994", "138993", "138992", "138991", "138990", "138989",
"138988", "138987", "138981", "138985", "138984", "138983", "138982",
"138981", "138980", "138979", "138978", "138977", "138971", "138975",
"138974", "138973", "138972", "139275", "139274", "139273", "139272",
"139271", "139270", "139219", "139218", "139217"}
For Each st As String In str1
Dim row As DataRow = dt.Rows.Find(st)
If newTable.Rows.Find(st) Is Nothing Then
newTable.ImportRow(row)
End If
Next
finish = DateTime.Now
time = finish.Subtract(start)
Debug.WriteLine("Time taken = " & time.TotalSeconds)
DataGrid1.DataSource = newTable
End Sub
"Geoff Jones" <no********@email.com> wrote in message
news:42***********************@news.dial.pipex.com ... Hiya Bernie
The difference i.e. 100 as opposed to 10000 was merely the range of
possibilities. Sorry for the confusion.
The rows are in a DataSet; but also in a table - which is stored in the
DataSet.
The code looks excellent. I'll give it a try tomorrow.
Geoff
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:u3**************@TK2MSFTNGP09.phx.gbl... Hi Geoff,
Not putting me out at all; glad to help.
But I'm still confused. You originally said this:
I have a list of about 100 numbers which correspond to the ID field
and also another 40 say numbers corresponding to the numbers in the
PRODUCT field.
Now you mentioned 10000 - quite a difference.
In any event, that may not be an issue. You say now you have these
10,000 rows - in a dataset/datatable? I have a code sample below that's
very fast and might serve as a template for you. It fills a dataset and
then makes a dataview from the datatable and then filters the rows as
necessary, using 2 criteria (replace these with product and cost).
Let me know if we're getting somewhere.
Regards,
Bernie
Dim oconn As New SqlConnection("data source=d5z0071;initial
catalog=imc;integrated security=sspi;")
Dim ocmd As New SqlCommand("select * from prod order by bipad", oconn)
Dim oda As New SqlDataAdapter(ocmd)
Dim ods As New DataSet("prod")
Try
oconn.Open()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
oda.Fill(ods, "prod")
Dim vue As New DataView(ods.Tables(0))
vue.Sort = "bipad"
vue.RowFilter = "bipad in ('18772','18770','01043') and issuecode =
'200304'"
Dim irow As DataRow
Dim irows As DataRow()
Dim i As Integer
For i = 0 To vue.Count - 1
MessageBox.Show(vue(i)("bipad"))
MessageBox.Show(vue(i)("issuecode"))
MessageBox.Show(vue(i)("usprice"))
Next
oconn.Close()
"Geoff Jones" <no********@email.com> wrote in message
news:42***********************@news.dial.pipex.com ...
Hi Bernie
I hope I'm not putting you to too much trouble. You've been invaluable
already!
At the moment, I'm getting a list of approximately 10,000 ID's i.e.
primary keys from another process which give me the rows in the 200,000
total. As I said previously, I was originally getting these rows by
using a Table.Rows.Find. From these 10,000 or so rows, I'm trying to
filter them down to those which satisfy a PRODUCT field criteria e.g.
PRODUCT IN ('3454', '324', '7855', etc.)
Indeed, my next step may be to do something like (forgive the pseudo
code):
WHERE PRODUCT = '2345' AND COST = '3.45'
where COST is another field.
I had considered the possibility of using multiple tables i.e.
searching a table for rows, creating a new table, searching this new
table with a new criteria etc. etc. in the same way that Cor has
suggested. I'm interested in both techniques i.e. cloning and SQL type
coding. What are the advantages and disadvantages of both?
Thanks again for your continuing help.
Geoff
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:uw**************@TK2MSFTNGP09.phx.gbl...
> Hi Geoff,
>
> OK; I've got your code.
>
> First, let me tell you that we will get this working effectively -
> 200,000 rows is not daunting. But I need more information. Am I
> correct in assuming that you first specify the 10,000 rows and then
> you want to 'find' rows that are among those 10,000 but also meet a
> certain product id condition? If this is so, let me know - I can
> construct a dataview of the 10,000 and show you how to search it very
> easily, with or without using the PK.
>
> Get back to me asap. I will work on this approach until I hear from
> you.
>
> Bernie
>
> "Geoff Jones" <no********@email.com> wrote in message
> news:42***********************@news.dial.pipex.com ...
>> Thanks for the kind offer Bernie. Unfortunately the database is
>> massive i.e. in excess of 200,000 rows!
>>
>> The code I am using is something like:
>>
>> Dim f As String = "ID IN ('543', '2345', '435', etc - goes on for
>> about 10,000 numbers) AND PRODUCT IN ('345', '456', '3445')"
>>
>> Dim vue As DataView = New DataView(theBigTable)
>>
>> vue.RowFilter = f ' The program appears to hang here i.e. very slow
>>
>> Obviously, the table is VERY large, so I'd expect some delay.
>> However, the first bit of code I wrote used a "Find" routine of a
>> DataTable to find the "ID" numbers (could do this because they were
>> the primary keys). I then, after finding the rows that satisifed the
>> ID condition, I simply iterated through them to find the required
>> rows satisfying the second condition i.e. PRODUCT.
>>
>> This method worked well i.e. relatively quickly. Is it possible then
>> that I'm seeing a big increase in filtering time because I'm not
>> searching on the primary key fields?
>>
>> Geoff
>>
>>
>> "Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
>> news:eC*************@TK2MSFTNGP09.phx.gbl...
>>> Hi Goeff,
>>>
>>> It shouldn't be slow. Can you send me a code snippet and an idea of
>>> the tables you are using - number of rows, number of columns, kind
>>> of database (sql, ms access, etc).
>>>
>>> I would like to test it on my system to see what I experience.
>>>
>>> Bernie
>>>
>>> "Geoff Jones" <no********@email.com> wrote in message
>>> news:42***********************@news.dial.pipex.com ...
>>>> Hi Bernie
>>>>
>>>> I've tried the "IN" scheme and it works but it is very slow. Am I
>>>> doing something wrong or can it be slow in searching?
>>>>
>>>> Geoff
>>>>
>>>> "Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
>>>> news:eC**************@TK2MSFTNGP10.phx.gbl...
>>>>> Hi Geoff,
>>>>>
>>>>> By the way, if you already have a 'larger' datatable, you can use
>>>>> the method I showed you earlier in a dataview select.
>>>>>
>>>>> Bernie
>>>>>
>>>>> "Geoff Jones" <no********@email.com> wrote in message
>>>>> news:42**********************@news.dial.pipex.com. ..
>>>>>> Hiya
>>>>>>
>>>>>> I have a DataTable containing thousands of records. Each record
>>>>>> has a primary key field called "ID" and another field called
>>>>>> "PRODUCT" I want to retrieve the rows that satisy the following
>>>>>> criteria:
>>>>>>
>>>>>> I have a list of about 100 numbers which correspond to the ID
>>>>>> field and also another 40 say numbers corresponding to the
>>>>>> numbers in the PRODUCT field. I want to show the rows that
>>>>>> correspond to both these criteria.
>>>>>>
>>>>>> My oringinal idea was to create a DataView and then generate a
>>>>>> string an SQL string, which I could then use with RowFilter to
>>>>>> get the rows I want.
>>>>>>
>>>>>> However, I'm uncertain of the SQL syntax to use and, more
>>>>>> importantly, if this is the best way to do it.
>>>>>>
>>>>>> Can anybody help?
>>>>>>
>>>>>> Geoff
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>
Geoff, Obviously, I couldn't use the same technique i.e. "Find", from the outset,
using a non-primary key (because it needs a primary key to work).
Actually you could have!
You should be able to use DataView.Find or DataView.FindRows on non-primary
keys with about the same performance as DataRowCollection.Find.
When you create the DataView, set the DataView.Sort property to the
non-primary key columns you are searching on, do not need to set the
RowFilter property.
Then rather then using DataTable.Rows.Find, use DataView.Find or
DataView.FindRows! DataView.Find returns an integer index into the DataView,
while DataView.FindRows returns DataRowView objects...
Something like:
Dim newTable As DataTable = dt.Clone
For Each st As String In str1
Dim row As DataRow = dt.Rows.Find(st)
If newTable.Rows.Find(st) Is Nothing Then
newTable.ImportRow(row)
End If
Next
dt.PrimaryKey = Nothing
Dim newTable As DataTable = dt.Clone
newTable.PrimaryKey = New DataColumn() {newTable.Columns("ID")}
Dim view As New DataView(dt)
view.Sort = "ID"
For Each key As String In str1
For Each row As DataRowView In view.FindRows(key)
If Not newTable.Rows.Contains(key) Then
newTable.ImportRow(row.Row)
End If
Next
Next
I don't believe the newTable.Rows.Contains is really needed. I left it in as
you had it in your original code.
Hope this helps
Jay
"Geoff Jones" <no********@email.com> wrote in message
news:42***********************@news.dial.pipex.com ... HI Bernie
Thanks for the code. I've learned a lot in the last few days with your and
others help. I do like the idea of using "IN" but unfortunately it does
seem to be slower than my original idea. I've written some code based on
Cor's which demonstrates the difference:
The first bit of code filters the dataset using "IN" and the last bit does
it using a "Find". When I ran it this morning, the SQL code took 41
seconds whilst the Find took 0.03 seconds. Quite a difference.
Obviously, I couldn't use the same technique i.e. "Find", from the outset,
using a non-primary key (because it needs a primary key to work).
Maybe the best approach is a mixture of both techniques?
Geoff
P.S. Code is below:
Dim dt As New DataTable
Private Sub Form1_Load(ByVal sender _
As System.Object, ByVal e As System.EventArgs) _
Handles MyBase.Load
dt.Columns.Add("ID")
dt.Columns.Add("PRODUCT")
dt.Columns.Add("COST")
For i As Integer = 0 To 200000
dt.Rows.Add(dt.NewRow)
dt.Rows(i)(0) = i.ToString
Dim rg As Random = New Random(i)
Dim rn As Integer = CInt(rg.Next(0, 200000))
dt.Rows(i)(1) = rn.ToString
rg = New Random(rn)
rn = CInt(rg.Next(0, 200000))
dt.Rows(i)(2) = rn.ToString
Next
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click
Dim start As DateTime = DateTime.Now
Dim numOfRows As Integer = dt.Rows.Count
Dim vue As DataView = New DataView(dt)
vue.Sort = "ID"
vue.RowFilter = "ID IN
('139211','139215','139214','139213','139212','139 211','139210','139259','139258','139257','139251', '139255','139254','139253','139252','139251','1392 50','139249','139248','139247','139241','139245',' 139244','139243','139242','139241','139240','13923 9','139238','139237','139231','139235','139234','1 39233','139232','139231','139230','139229','139228 ','139227','139221','139225','139224','139223','13 9222','139221','139220','139219','139218','139217' ,'139211','139215','139214','139213','139212','139 211','139210','139209','139208','139207','139201', '139205','139204','139203','139202','139201','1392 00','139199','139198','139197','139191','139195',' 139194','139193','139192','139191','139190','13918 9','139188','139187','139181','139185','139184','1 39183','139182','139181','139180','139179','139178 ','139177','139171','139175','139174','139173','13 9172','139171','139170','139119','139118','139117' ,'139111','139115','139114','139113','139112','139 111','139110','139159','139158','139157','139151', '139155','139154','139153','139152','139151','1391 50','139149','139148','139147','139141','139145',' 139144','139143','139142','139141','139140','13913 9','139138','139137','139131','139135','139134','1 39133','139132','139131','139130','139129','139128 ','139127','139121','139125','139124','139123','13 9122','139121','139120','139119','139118','139117' ,'139111','139115','139114','139113','139112','139 111','139110','139109','139108','139107','139101', '139105','139104','139103','139102','139101','1391 00','139099','139098','139097','139091','139095',' 139094','139093','139092','139091','139090','13908 9','139088','139087','139081','139085','139084','1 39083','139082','139081','139080','139079','139078 ','139077','139071','139075','139074','139073','13 9072','139071','139070','139019','139018','139017' ,'139011','139015','139014','139013','139012','139 011','139010','139059','139058','139057','139051', '139055','139054','139053','139052','139051','1390 50','139049','139048','139047','139041','139045',' 139044','139043','139042','139041','139040','13903 9','139038','139037','139031','139035','139034','1 39033','139032','139031','139030','139029','139028 ','139027','139021','139025','139024','139023','13 9022','139021','139020','139019','139018','139017' ,'139011','139015','139014','139013','139012','139 011','139010','139009','139008','139007','139001', '139005','139004','139003','139002','139001','1390 00','138999','138998','138997','138991','138995',' 138994','138993','138992','138991','138990','13898 9','138988','138987','138981','138985','138984','1 38983','138982','138981','138980','138979','138978 ','138977','138971','138975','138974','138973','13 8972','139275','139274','139273','139272','139271' ,'139270','139219','139218','139217')"
DataGrid1.DataSource = dt
Dim finish As DateTime = DateTime.Now
Dim time As TimeSpan = finish.Subtract(start)
Debug.WriteLine("Time taken = " & time.TotalSeconds)
DataGrid1.DataSource = dt
dt.PrimaryKey = New DataColumn() {dt.Columns("ID")}
start = DateTime.Now
Dim newTable As DataTable = dt.Clone
Dim str1() As String = {"139211", "139215", "139214", "139213", "139212",
"139211", "139210", "139259", "139258", "139257", "139251", "139255",
"139254", "139253", "139252", "139251", "139250", "139249", "139248",
"139247", "139241", "139245", "139244", "139243", "139242", "139241",
"139240", "139239", "139238", "139237", "139231", "139235", "139234",
"139233", "139232", "139231", "139230", "139229", "139228", "139227",
"139221", "139225", "139224", "139223", "139222", "139221", "139220",
"139219", "139218", "139217", "139211", "139215", "139214", "139213",
"139212", "139211", "139210", "139209", "139208", "139207", "139201",
"139205", "139204", "139203", "139202", "139201", "139200", "139199",
"139198", "139197", "139191", "139195", "139194", "139193", "139192",
"139191", "139190", "139189", "139188", "139187", "139181", "139185",
"139184", "139183", "139182", "139181", "139180", "139179", "139178",
"139177", "139171", "139175", "139174", "139173", "139172", "139171",
"139170", "139119", "139118", "139117", "139111", "139115", "139114",
"139113", "139112", "139111", "139110", "139159", "139158", "139157",
"139151", "139155", "139154", "139153", "139152", "139151", "139150",
"139149", "139148", "139147", "139141", "139145", "139144", "139143",
"139142", "139141", "139140", "139139", "139138", "139137", "139131",
"139135", "139134", "139133", "139132", "139131", "139130", "139129",
"139128", "139127", "139121", "139125", "139124", "139123", "139122",
"139121", "139120", "139119", "139118", "139117", "139111", "139115",
"139114", "139113", "139112", "139111", "139110", "139109", "139108",
"139107", "139101", "139105", "139104", "139103", "139102", "139101",
"139100", "139099", "139098", "139097", "139091", "139095", "139094",
"139093", "139092", "139091", "139090", "139089", "139088", "139087",
"139081", "139085", "139084", "139083", "139082", "139081", "139080",
"139079", "139078", "139077", "139071", "139075", "139074", "139073",
"139072", "139071", "139070", "139019", "139018", "139017", "139011",
"139015", "139014", "139013", "139012", "139011", "139010", "139059",
"139058", "139057", "139051", "139055", "139054", "139053", "139052",
"139051", "139050", "139049", "139048", "139047", "139041", "139045",
"139044", "139043", "139042", "139041", "139040", "139039", "139038",
"139037", "139031", "139035", "139034", "139033", "139032", "139031",
"139030", "139029", "139028", "139027", "139021", "139025", "139024",
"139023", "139022", "139021", "139020", "139019", "139018", "139017",
"139011", "139015", "139014", "139013", "139012", "139011", "139010",
"139009", "139008", "139007", "139001", "139005", "139004", "139003",
"139002", "139001", "139000", "138999", "138998", "138997", "138991",
"138995", "138994", "138993", "138992", "138991", "138990", "138989",
"138988", "138987", "138981", "138985", "138984", "138983", "138982",
"138981", "138980", "138979", "138978", "138977", "138971", "138975",
"138974", "138973", "138972", "139275", "139274", "139273", "139272",
"139271", "139270", "139219", "139218", "139217"}
For Each st As String In str1
Dim row As DataRow = dt.Rows.Find(st)
If newTable.Rows.Find(st) Is Nothing Then
newTable.ImportRow(row)
End If
Next
finish = DateTime.Now
time = finish.Subtract(start)
Debug.WriteLine("Time taken = " & time.TotalSeconds)
DataGrid1.DataSource = newTable
End Sub
"Geoff Jones" <no********@email.com> wrote in message
news:42***********************@news.dial.pipex.com ... Hiya Bernie
The difference i.e. 100 as opposed to 10000 was merely the range of
possibilities. Sorry for the confusion.
The rows are in a DataSet; but also in a table - which is stored in the
DataSet.
The code looks excellent. I'll give it a try tomorrow.
Geoff
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:u3**************@TK2MSFTNGP09.phx.gbl... Hi Geoff,
Not putting me out at all; glad to help.
But I'm still confused. You originally said this:
I have a list of about 100 numbers which correspond to the ID field
and also another 40 say numbers corresponding to the numbers in the
PRODUCT field.
Now you mentioned 10000 - quite a difference.
In any event, that may not be an issue. You say now you have these
10,000 rows - in a dataset/datatable? I have a code sample below that's
very fast and might serve as a template for you. It fills a dataset and
then makes a dataview from the datatable and then filters the rows as
necessary, using 2 criteria (replace these with product and cost).
Let me know if we're getting somewhere.
Regards,
Bernie
Dim oconn As New SqlConnection("data source=d5z0071;initial
catalog=imc;integrated security=sspi;")
Dim ocmd As New SqlCommand("select * from prod order by bipad", oconn)
Dim oda As New SqlDataAdapter(ocmd)
Dim ods As New DataSet("prod")
Try
oconn.Open()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
oda.Fill(ods, "prod")
Dim vue As New DataView(ods.Tables(0))
vue.Sort = "bipad"
vue.RowFilter = "bipad in ('18772','18770','01043') and issuecode =
'200304'"
Dim irow As DataRow
Dim irows As DataRow()
Dim i As Integer
For i = 0 To vue.Count - 1
MessageBox.Show(vue(i)("bipad"))
MessageBox.Show(vue(i)("issuecode"))
MessageBox.Show(vue(i)("usprice"))
Next
oconn.Close()
"Geoff Jones" <no********@email.com> wrote in message
news:42***********************@news.dial.pipex.com ...
Hi Bernie
I hope I'm not putting you to too much trouble. You've been invaluable
already!
At the moment, I'm getting a list of approximately 10,000 ID's i.e.
primary keys from another process which give me the rows in the 200,000
total. As I said previously, I was originally getting these rows by
using a Table.Rows.Find. From these 10,000 or so rows, I'm trying to
filter them down to those which satisfy a PRODUCT field criteria e.g.
PRODUCT IN ('3454', '324', '7855', etc.)
Indeed, my next step may be to do something like (forgive the pseudo
code):
WHERE PRODUCT = '2345' AND COST = '3.45'
where COST is another field.
I had considered the possibility of using multiple tables i.e.
searching a table for rows, creating a new table, searching this new
table with a new criteria etc. etc. in the same way that Cor has
suggested. I'm interested in both techniques i.e. cloning and SQL type
coding. What are the advantages and disadvantages of both?
Thanks again for your continuing help.
Geoff
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:uw**************@TK2MSFTNGP09.phx.gbl...
> Hi Geoff,
>
> OK; I've got your code.
>
> First, let me tell you that we will get this working effectively -
> 200,000 rows is not daunting. But I need more information. Am I
> correct in assuming that you first specify the 10,000 rows and then
> you want to 'find' rows that are among those 10,000 but also meet a
> certain product id condition? If this is so, let me know - I can
> construct a dataview of the 10,000 and show you how to search it very
> easily, with or without using the PK.
>
> Get back to me asap. I will work on this approach until I hear from
> you.
>
> Bernie
>
> "Geoff Jones" <no********@email.com> wrote in message
> news:42***********************@news.dial.pipex.com ...
>> Thanks for the kind offer Bernie. Unfortunately the database is
>> massive i.e. in excess of 200,000 rows!
>>
>> The code I am using is something like:
>>
>> Dim f As String = "ID IN ('543', '2345', '435', etc - goes on for
>> about 10,000 numbers) AND PRODUCT IN ('345', '456', '3445')"
>>
>> Dim vue As DataView = New DataView(theBigTable)
>>
>> vue.RowFilter = f ' The program appears to hang here i.e. very slow
>>
>> Obviously, the table is VERY large, so I'd expect some delay.
>> However, the first bit of code I wrote used a "Find" routine of a
>> DataTable to find the "ID" numbers (could do this because they were
>> the primary keys). I then, after finding the rows that satisifed the
>> ID condition, I simply iterated through them to find the required
>> rows satisfying the second condition i.e. PRODUCT.
>>
>> This method worked well i.e. relatively quickly. Is it possible then
>> that I'm seeing a big increase in filtering time because I'm not
>> searching on the primary key fields?
>>
>> Geoff
>>
>>
>> "Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
>> news:eC*************@TK2MSFTNGP09.phx.gbl...
>>> Hi Goeff,
>>>
>>> It shouldn't be slow. Can you send me a code snippet and an idea of
>>> the tables you are using - number of rows, number of columns, kind
>>> of database (sql, ms access, etc).
>>>
>>> I would like to test it on my system to see what I experience.
>>>
>>> Bernie
>>>
>>> "Geoff Jones" <no********@email.com> wrote in message
>>> news:42***********************@news.dial.pipex.com ...
>>>> Hi Bernie
>>>>
>>>> I've tried the "IN" scheme and it works but it is very slow. Am I
>>>> doing something wrong or can it be slow in searching?
>>>>
>>>> Geoff
>>>>
>>>> "Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
>>>> news:eC**************@TK2MSFTNGP10.phx.gbl...
>>>>> Hi Geoff,
>>>>>
>>>>> By the way, if you already have a 'larger' datatable, you can use
>>>>> the method I showed you earlier in a dataview select.
>>>>>
>>>>> Bernie
>>>>>
>>>>> "Geoff Jones" <no********@email.com> wrote in message
>>>>> news:42**********************@news.dial.pipex.com. ..
>>>>>> Hiya
>>>>>>
>>>>>> I have a DataTable containing thousands of records. Each record
>>>>>> has a primary key field called "ID" and another field called
>>>>>> "PRODUCT" I want to retrieve the rows that satisy the following
>>>>>> criteria:
>>>>>>
>>>>>> I have a list of about 100 numbers which correspond to the ID
>>>>>> field and also another 40 say numbers corresponding to the
>>>>>> numbers in the PRODUCT field. I want to show the rows that
>>>>>> correspond to both these criteria.
>>>>>>
>>>>>> My oringinal idea was to create a DataView and then generate a
>>>>>> string an SQL string, which I could then use with RowFilter to
>>>>>> get the rows I want.
>>>>>>
>>>>>> However, I'm uncertain of the SQL syntax to use and, more
>>>>>> importantly, if this is the best way to do it.
>>>>>>
>>>>>> Can anybody help?
>>>>>>
>>>>>> Geoff
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>
Hi Bernie and Jay
Many thanks for all your help. As always it has been very invaluable to
learn from you guys.
Jay - the reason I used Contains was because otherwise I got an error saying
that the row already existed. If you comment it out, you'll see that it
causes an exception in the code I posted.
Geoff
"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message
news:Og*************@TK2MSFTNGP12.phx.gbl... Geoff, Obviously, I couldn't use the same technique i.e. "Find", from the
outset, using a non-primary key (because it needs a primary key to work).
Actually you could have!
You should be able to use DataView.Find or DataView.FindRows on
non-primary keys with about the same performance as
DataRowCollection.Find.
When you create the DataView, set the DataView.Sort property to the
non-primary key columns you are searching on, do not need to set the
RowFilter property.
Then rather then using DataTable.Rows.Find, use DataView.Find or
DataView.FindRows! DataView.Find returns an integer index into the
DataView, while DataView.FindRows returns DataRowView objects...
Something like:
Dim newTable As DataTable = dt.Clone
For Each st As String In str1
Dim row As DataRow = dt.Rows.Find(st)
If newTable.Rows.Find(st) Is Nothing Then
newTable.ImportRow(row)
End If
Next
dt.PrimaryKey = Nothing
Dim newTable As DataTable = dt.Clone
newTable.PrimaryKey = New DataColumn() {newTable.Columns("ID")}
Dim view As New DataView(dt)
view.Sort = "ID"
For Each key As String In str1
For Each row As DataRowView In view.FindRows(key)
If Not newTable.Rows.Contains(key) Then
newTable.ImportRow(row.Row)
End If
Next
Next
I don't believe the newTable.Rows.Contains is really needed. I left it in
as you had it in your original code.
Hope this helps
Jay
"Geoff Jones" <no********@email.com> wrote in message
news:42***********************@news.dial.pipex.com ... HI Bernie
Thanks for the code. I've learned a lot in the last few days with your
and others help. I do like the idea of using "IN" but unfortunately it
does seem to be slower than my original idea. I've written some code
based on Cor's which demonstrates the difference:
The first bit of code filters the dataset using "IN" and the last bit
does it using a "Find". When I ran it this morning, the SQL code took 41
seconds whilst the Find took 0.03 seconds. Quite a difference.
Obviously, I couldn't use the same technique i.e. "Find", from the
outset, using a non-primary key (because it needs a primary key to work).
Maybe the best approach is a mixture of both techniques?
Geoff
P.S. Code is below:
Dim dt As New DataTable
Private Sub Form1_Load(ByVal sender _
As System.Object, ByVal e As System.EventArgs) _
Handles MyBase.Load
dt.Columns.Add("ID")
dt.Columns.Add("PRODUCT")
dt.Columns.Add("COST")
For i As Integer = 0 To 200000
dt.Rows.Add(dt.NewRow)
dt.Rows(i)(0) = i.ToString
Dim rg As Random = New Random(i)
Dim rn As Integer = CInt(rg.Next(0, 200000))
dt.Rows(i)(1) = rn.ToString
rg = New Random(rn)
rn = CInt(rg.Next(0, 200000))
dt.Rows(i)(2) = rn.ToString
Next
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click
Dim start As DateTime = DateTime.Now
Dim numOfRows As Integer = dt.Rows.Count
Dim vue As DataView = New DataView(dt)
vue.Sort = "ID"
vue.RowFilter = "ID IN
('139211','139215','139214','139213','139212','139 211','139210','139259','139258','139257','139251', '139255','139254','139253','139252','139251','1392 50','139249','139248','139247','139241','139245',' 139244','139243','139242','139241','139240','13923 9','139238','139237','139231','139235','139234','1 39233','139232','139231','139230','139229','139228 ','139227','139221','139225','139224','139223','13 9222','139221','139220','139219','139218','139217' ,'139211','139215','139214','139213','139212','139 211','139210','139209','139208','139207','139201', '139205','139204','139203','139202','139201','1392 00','139199','139198','139197','139191','139195',' 139194','139193','139192','139191','139190','13918 9','139188','139187','139181','139185','139184','1 39183','139182','139181','139180','139179','139178 ','139177','139171','139175','139174','139173','13 9172','139171','139170','139119','139118','139117' ,'139111','139115','139114','139113','139112','139 111','139110','139159','139158','139157','139151', '139155','139154','139153','139152','139151','1391 50','139149','139148','139147','139141','139145',' 139144','139143','139142','139141','139140','13913 9','139138','139137','139131','139135','139134','1 39133','139132','139131','139130','139129','139128 ','139127','139121','139125','139124','139123','13 9122','139121','139120','139119','139118','139117' ,'139111','139115','139114','139113','139112','139 111','139110','139109','139108','139107','139101', '139105','139104','139103','139102','139101','1391 00','139099','139098','139097','139091','139095',' 139094','139093','139092','139091','139090','13908 9','139088','139087','139081','139085','139084','1 39083','139082','139081','139080','139079','139078 ','139077','139071','139075','139074','139073','13 9072','139071','139070','139019','139018','139017' ,'139011','139015','139014','139013','139012','139 011','139010','139059','139058','139057','139051', '139055','139054','139053','139052','139051','1390 50','139049','139048','139047','139041','139045',' 139044','139043','139042','139041','139040','13903 9','139038','139037','139031','139035','139034','1 39033','139032','139031','139030','139029','139028 ','139027','139021','139025','139024','139023','13 9022','139021','139020','139019','139018','139017' ,'139011','139015','139014','139013','139012','139 011','139010','139009','139008','139007','139001', '139005','139004','139003','139002','139001','1390 00','138999','138998','138997','138991','138995',' 138994','138993','138992','138991','138990','13898 9','138988','138987','138981','138985','138984','1 38983','138982','138981','138980','138979','138978 ','138977','138971','138975','138974','138973','13 8972','139275','139274','139273','139272','139271' ,'139270','139219','139218','139217')"
DataGrid1.DataSource = dt
Dim finish As DateTime = DateTime.Now
Dim time As TimeSpan = finish.Subtract(start)
Debug.WriteLine("Time taken = " & time.TotalSeconds)
DataGrid1.DataSource = dt
dt.PrimaryKey = New DataColumn() {dt.Columns("ID")}
start = DateTime.Now
Dim newTable As DataTable = dt.Clone
Dim str1() As String = {"139211", "139215", "139214", "139213", "139212",
"139211", "139210", "139259", "139258", "139257", "139251", "139255",
"139254", "139253", "139252", "139251", "139250", "139249", "139248",
"139247", "139241", "139245", "139244", "139243", "139242", "139241",
"139240", "139239", "139238", "139237", "139231", "139235", "139234",
"139233", "139232", "139231", "139230", "139229", "139228", "139227",
"139221", "139225", "139224", "139223", "139222", "139221", "139220",
"139219", "139218", "139217", "139211", "139215", "139214", "139213",
"139212", "139211", "139210", "139209", "139208", "139207", "139201",
"139205", "139204", "139203", "139202", "139201", "139200", "139199",
"139198", "139197", "139191", "139195", "139194", "139193", "139192",
"139191", "139190", "139189", "139188", "139187", "139181", "139185",
"139184", "139183", "139182", "139181", "139180", "139179", "139178",
"139177", "139171", "139175", "139174", "139173", "139172", "139171",
"139170", "139119", "139118", "139117", "139111", "139115", "139114",
"139113", "139112", "139111", "139110", "139159", "139158", "139157",
"139151", "139155", "139154", "139153", "139152", "139151", "139150",
"139149", "139148", "139147", "139141", "139145", "139144", "139143",
"139142", "139141", "139140", "139139", "139138", "139137", "139131",
"139135", "139134", "139133", "139132", "139131", "139130", "139129",
"139128", "139127", "139121", "139125", "139124", "139123", "139122",
"139121", "139120", "139119", "139118", "139117", "139111", "139115",
"139114", "139113", "139112", "139111", "139110", "139109", "139108",
"139107", "139101", "139105", "139104", "139103", "139102", "139101",
"139100", "139099", "139098", "139097", "139091", "139095", "139094",
"139093", "139092", "139091", "139090", "139089", "139088", "139087",
"139081", "139085", "139084", "139083", "139082", "139081", "139080",
"139079", "139078", "139077", "139071", "139075", "139074", "139073",
"139072", "139071", "139070", "139019", "139018", "139017", "139011",
"139015", "139014", "139013", "139012", "139011", "139010", "139059",
"139058", "139057", "139051", "139055", "139054", "139053", "139052",
"139051", "139050", "139049", "139048", "139047", "139041", "139045",
"139044", "139043", "139042", "139041", "139040", "139039", "139038",
"139037", "139031", "139035", "139034", "139033", "139032", "139031",
"139030", "139029", "139028", "139027", "139021", "139025", "139024",
"139023", "139022", "139021", "139020", "139019", "139018", "139017",
"139011", "139015", "139014", "139013", "139012", "139011", "139010",
"139009", "139008", "139007", "139001", "139005", "139004", "139003",
"139002", "139001", "139000", "138999", "138998", "138997", "138991",
"138995", "138994", "138993", "138992", "138991", "138990", "138989",
"138988", "138987", "138981", "138985", "138984", "138983", "138982",
"138981", "138980", "138979", "138978", "138977", "138971", "138975",
"138974", "138973", "138972", "139275", "139274", "139273", "139272",
"139271", "139270", "139219", "139218", "139217"}
For Each st As String In str1
Dim row As DataRow = dt.Rows.Find(st)
If newTable.Rows.Find(st) Is Nothing Then
newTable.ImportRow(row)
End If
Next
finish = DateTime.Now
time = finish.Subtract(start)
Debug.WriteLine("Time taken = " & time.TotalSeconds)
DataGrid1.DataSource = newTable
End Sub
"Geoff Jones" <no********@email.com> wrote in message
news:42***********************@news.dial.pipex.com ... Hiya Bernie
The difference i.e. 100 as opposed to 10000 was merely the range of
possibilities. Sorry for the confusion.
The rows are in a DataSet; but also in a table - which is stored in the
DataSet.
The code looks excellent. I'll give it a try tomorrow.
Geoff
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:u3**************@TK2MSFTNGP09.phx.gbl...
Hi Geoff,
Not putting me out at all; glad to help.
But I'm still confused. You originally said this:
I have a list of about 100 numbers which correspond to the ID field
and also another 40 say numbers corresponding to the numbers in the
PRODUCT field.
Now you mentioned 10000 - quite a difference.
In any event, that may not be an issue. You say now you have these
10,000 rows - in a dataset/datatable? I have a code sample below
that's very fast and might serve as a template for you. It fills a
dataset and then makes a dataview from the datatable and then filters
the rows as necessary, using 2 criteria (replace these with product and
cost).
Let me know if we're getting somewhere.
Regards,
Bernie
Dim oconn As New SqlConnection("data source=d5z0071;initial
catalog=imc;integrated security=sspi;")
Dim ocmd As New SqlCommand("select * from prod order by bipad", oconn)
Dim oda As New SqlDataAdapter(ocmd)
Dim ods As New DataSet("prod")
Try
oconn.Open()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
oda.Fill(ods, "prod")
Dim vue As New DataView(ods.Tables(0))
vue.Sort = "bipad"
vue.RowFilter = "bipad in ('18772','18770','01043') and issuecode =
'200304'"
Dim irow As DataRow
Dim irows As DataRow()
Dim i As Integer
For i = 0 To vue.Count - 1
MessageBox.Show(vue(i)("bipad"))
MessageBox.Show(vue(i)("issuecode"))
MessageBox.Show(vue(i)("usprice"))
Next
oconn.Close()
"Geoff Jones" <no********@email.com> wrote in message
news:42***********************@news.dial.pipex.com ...
> Hi Bernie
>
> I hope I'm not putting you to too much trouble. You've been invaluable
> already!
>
> At the moment, I'm getting a list of approximately 10,000 ID's i.e.
> primary keys from another process which give me the rows in the
> 200,000 total. As I said previously, I was originally getting these
> rows by using a Table.Rows.Find. From these 10,000 or so rows, I'm
> trying to filter them down to those which satisfy a PRODUCT field
> criteria e.g. PRODUCT IN ('3454', '324', '7855', etc.)
>
> Indeed, my next step may be to do something like (forgive the pseudo
> code):
>
> WHERE PRODUCT = '2345' AND COST = '3.45'
>
> where COST is another field.
>
> I had considered the possibility of using multiple tables i.e.
> searching a table for rows, creating a new table, searching this new
> table with a new criteria etc. etc. in the same way that Cor has
> suggested. I'm interested in both techniques i.e. cloning and SQL type
> coding. What are the advantages and disadvantages of both?
>
> Thanks again for your continuing help.
>
> Geoff
>
> "Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
> news:uw**************@TK2MSFTNGP09.phx.gbl...
>> Hi Geoff,
>>
>> OK; I've got your code.
>>
>> First, let me tell you that we will get this working effectively -
>> 200,000 rows is not daunting. But I need more information. Am I
>> correct in assuming that you first specify the 10,000 rows and then
>> you want to 'find' rows that are among those 10,000 but also meet a
>> certain product id condition? If this is so, let me know - I can
>> construct a dataview of the 10,000 and show you how to search it very
>> easily, with or without using the PK.
>>
>> Get back to me asap. I will work on this approach until I hear from
>> you.
>>
>> Bernie
>>
>> "Geoff Jones" <no********@email.com> wrote in message
>> news:42***********************@news.dial.pipex.com ...
>>> Thanks for the kind offer Bernie. Unfortunately the database is
>>> massive i.e. in excess of 200,000 rows!
>>>
>>> The code I am using is something like:
>>>
>>> Dim f As String = "ID IN ('543', '2345', '435', etc - goes on for
>>> about 10,000 numbers) AND PRODUCT IN ('345', '456', '3445')"
>>>
>>> Dim vue As DataView = New DataView(theBigTable)
>>>
>>> vue.RowFilter = f ' The program appears to hang here i.e. very slow
>>>
>>> Obviously, the table is VERY large, so I'd expect some delay.
>>> However, the first bit of code I wrote used a "Find" routine of a
>>> DataTable to find the "ID" numbers (could do this because they were
>>> the primary keys). I then, after finding the rows that satisifed the
>>> ID condition, I simply iterated through them to find the required
>>> rows satisfying the second condition i.e. PRODUCT.
>>>
>>> This method worked well i.e. relatively quickly. Is it possible then
>>> that I'm seeing a big increase in filtering time because I'm not
>>> searching on the primary key fields?
>>>
>>> Geoff
>>>
>>>
>>> "Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
>>> news:eC*************@TK2MSFTNGP09.phx.gbl...
>>>> Hi Goeff,
>>>>
>>>> It shouldn't be slow. Can you send me a code snippet and an idea
>>>> of the tables you are using - number of rows, number of columns,
>>>> kind of database (sql, ms access, etc).
>>>>
>>>> I would like to test it on my system to see what I experience.
>>>>
>>>> Bernie
>>>>
>>>> "Geoff Jones" <no********@email.com> wrote in message
>>>> news:42***********************@news.dial.pipex.com ...
>>>>> Hi Bernie
>>>>>
>>>>> I've tried the "IN" scheme and it works but it is very slow. Am I
>>>>> doing something wrong or can it be slow in searching?
>>>>>
>>>>> Geoff
>>>>>
>>>>> "Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
>>>>> news:eC**************@TK2MSFTNGP10.phx.gbl...
>>>>>> Hi Geoff,
>>>>>>
>>>>>> By the way, if you already have a 'larger' datatable, you can use
>>>>>> the method I showed you earlier in a dataview select.
>>>>>>
>>>>>> Bernie
>>>>>>
>>>>>> "Geoff Jones" <no********@email.com> wrote in message
>>>>>> news:42**********************@news.dial.pipex.com. ..
>>>>>>> Hiya
>>>>>>>
>>>>>>> I have a DataTable containing thousands of records. Each record
>>>>>>> has a primary key field called "ID" and another field called
>>>>>>> "PRODUCT" I want to retrieve the rows that satisy the following
>>>>>>> criteria:
>>>>>>>
>>>>>>> I have a list of about 100 numbers which correspond to the ID
>>>>>>> field and also another 40 say numbers corresponding to the
>>>>>>> numbers in the PRODUCT field. I want to show the rows that
>>>>>>> correspond to both these criteria.
>>>>>>>
>>>>>>> My oringinal idea was to create a DataView and then generate a
>>>>>>> string an SQL string, which I could then use with RowFilter to
>>>>>>> get the rows I want.
>>>>>>>
>>>>>>> However, I'm uncertain of the SQL syntax to use and, more
>>>>>>> importantly, if this is the best way to do it.
>>>>>>>
>>>>>>> Can anybody help?
>>>>>>>
>>>>>>> Geoff
>>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>
Geoff,
The values in your keys array (str1) are not unique, hence the duplicates...
I would consider ensuring that your keys array is distinct before creating
the newTable. Alternatively I would only process unique values in str1 (by
sorting it first)...
Array.Sort(str1)
Dim lastkey As String = Nothing
For Each key As String In str1
If key <> lastkey Then
For Each row As DataRowView In view.FindRows(key)
newTable.ImportRow(row.Row)
Next
lastkey = key
End If
Next
However! I am not saying using Contains is a bad thing either :-) Which
method to use would require profiling to decide, naturally it may change
over the life of your program.
Of course if you can ensure that your keys array is unique to begin with,
then that might be "best"...
Hope this helps
Jay
"Geoff Jones" <no********@email.com> wrote in message
news:42***********************@news.dial.pipex.com ... Hi Bernie and Jay
Many thanks for all your help. As always it has been very invaluable to
learn from you guys.
Jay - the reason I used Contains was because otherwise I got an error
saying that the row already existed. If you comment it out, you'll see
that it causes an exception in the code I posted.
Geoff
"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message
news:Og*************@TK2MSFTNGP12.phx.gbl... Geoff, Obviously, I couldn't use the same technique i.e. "Find", from the
outset, using a non-primary key (because it needs a primary key to
work).
Actually you could have!
You should be able to use DataView.Find or DataView.FindRows on
non-primary keys with about the same performance as
DataRowCollection.Find.
When you create the DataView, set the DataView.Sort property to the
non-primary key columns you are searching on, do not need to set the
RowFilter property.
Then rather then using DataTable.Rows.Find, use DataView.Find or
DataView.FindRows! DataView.Find returns an integer index into the
DataView, while DataView.FindRows returns DataRowView objects...
Something like:
Dim newTable As DataTable = dt.Clone
For Each st As String In str1
Dim row As DataRow = dt.Rows.Find(st)
If newTable.Rows.Find(st) Is Nothing Then
newTable.ImportRow(row)
End If
Next
dt.PrimaryKey = Nothing
Dim newTable As DataTable = dt.Clone
newTable.PrimaryKey = New DataColumn()
{newTable.Columns("ID")}
Dim view As New DataView(dt)
view.Sort = "ID"
For Each key As String In str1
For Each row As DataRowView In view.FindRows(key)
If Not newTable.Rows.Contains(key) Then
newTable.ImportRow(row.Row)
End If
Next
Next
I don't believe the newTable.Rows.Contains is really needed. I left it in
as you had it in your original code.
Hope this helps
Jay
<<snip>>
Bernie,
I have the idea that you did not understand why my first message was for in
this thread and why it was so slow with the first attempts of Geoff.
There is no dataview *select* function.
What probably (that deep did I not look for it) is happening in the mix from
Geoff and your first code, (it is not that consistent that I know where it
is) is that there is created a dataview (defaultview) with a rowfilter.
That is a rowfilter for a datatable that is referenced in that dataview.
So when you filter 100 rows you can loop through that very fast.
However, when you use this.
Dataview.table.select you are selecting again from that datatable from
200.000 rows.
Therefore I showed it with that cloned and copied table.
I hope this makes it more clear why I wrote this in this thread.
Cor
"Bernie Yaeger" <be*****@cherwellinc.com> schreef in bericht
news:%2****************@TK2MSFTNGP14.phx.gbl... Hi Goeff,
You are correct; Cor's approach is much faster.
Good luck,
Bernie
"Geoff Jones" <no********@email.com> wrote in message
news:42***********************@news.dial.pipex.com ... HI Bernie
Thanks for the code. I've learned a lot in the last few days with your
and others help. I do like the idea of using "IN" but unfortunately it
does seem to be slower than my original idea. I've written some code
based on Cor's which demonstrates the difference:
The first bit of code filters the dataset using "IN" and the last bit
does it using a "Find". When I ran it this morning, the SQL code took 41
seconds whilst the Find took 0.03 seconds. Quite a difference.
Obviously, I couldn't use the same technique i.e. "Find", from the
outset, using a non-primary key (because it needs a primary key to work).
Maybe the best approach is a mixture of both techniques?
Geoff
P.S. Code is below:
Dim dt As New DataTable
Private Sub Form1_Load(ByVal sender _
As System.Object, ByVal e As System.EventArgs) _
Handles MyBase.Load
dt.Columns.Add("ID")
dt.Columns.Add("PRODUCT")
dt.Columns.Add("COST")
For i As Integer = 0 To 200000
dt.Rows.Add(dt.NewRow)
dt.Rows(i)(0) = i.ToString
Dim rg As Random = New Random(i)
Dim rn As Integer = CInt(rg.Next(0, 200000))
dt.Rows(i)(1) = rn.ToString
rg = New Random(rn)
rn = CInt(rg.Next(0, 200000))
dt.Rows(i)(2) = rn.ToString
Next
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click
Dim start As DateTime = DateTime.Now
Dim numOfRows As Integer = dt.Rows.Count
Dim vue As DataView = New DataView(dt)
vue.Sort = "ID"
vue.RowFilter = "ID IN
('139211','139215','139214','139213','139212','139 211','139210','139259','139258','139257','139251', '139255','139254','139253','139252','139251','1392 50','139249','139248','139247','139241','139245',' 139244','139243','139242','139241','139240','13923 9','139238','139237','139231','139235','139234','1 39233','139232','139231','139230','139229','139228 ','139227','139221','139225','139224','139223','13 9222','139221','139220','139219','139218','139217' ,'139211','139215','139214','139213','139212','139 211','139210','139209','139208','139207','139201', '139205','139204','139203','139202','139201','1392 00','139199','139198','139197','139191','139195',' 139194','139193','139192','139191','139190','13918 9','139188','139187','139181','139185','139184','1 39183','139182','139181','139180','139179','139178 ','139177','139171','139175','139174','139173','13 9172','139171','139170','139119','139118','139117' ,'139111','139115','139114','139113','139112','139 111','139110','139159','139158','139157','139151', '139155','139154','139153','139152','139151','1391 50','139149','139148','139147','139141','139145',' 139144','139143','139142','139141','139140','13913 9','139138','139137','139131','139135','139134','1 39133','139132','139131','139130','139129','139128 ','139127','139121','139125','139124','139123','13 9122','139121','139120','139119','139118','139117' ,'139111','139115','139114','139113','139112','139 111','139110','139109','139108','139107','139101', '139105','139104','139103','139102','139101','1391 00','139099','139098','139097','139091','139095',' 139094','139093','139092','139091','139090','13908 9','139088','139087','139081','139085','139084','1 39083','139082','139081','139080','139079','139078 ','139077','139071','139075','139074','139073','13 9072','139071','139070','139019','139018','139017' ,'139011','139015','139014','139013','139012','139 011','139010','139059','139058','139057','139051', '139055','139054','139053','139052','139051','1390 50','139049','139048','139047','139041','139045',' 139044','139043','139042','139041','139040','13903 9','139038','139037','139031','139035','139034','1 39033','139032','139031','139030','139029','139028 ','139027','139021','139025','139024','139023','13 9022','139021','139020','139019','139018','139017' ,'139011','139015','139014','139013','139012','139 011','139010','139009','139008','139007','139001', '139005','139004','139003','139002','139001','1390 00','138999','138998','138997','138991','138995',' 138994','138993','138992','138991','138990','13898 9','138988','138987','138981','138985','138984','1 38983','138982','138981','138980','138979','138978 ','138977','138971','138975','138974','138973','13 8972','139275','139274','139273','139272','139271' ,'139270','139219','139218','139217')"
DataGrid1.DataSource = dt
Dim finish As DateTime = DateTime.Now
Dim time As TimeSpan = finish.Subtract(start)
Debug.WriteLine("Time taken = " & time.TotalSeconds)
DataGrid1.DataSource = dt
dt.PrimaryKey = New DataColumn() {dt.Columns("ID")}
start = DateTime.Now
Dim newTable As DataTable = dt.Clone
Dim str1() As String = {"139211", "139215", "139214", "139213", "139212",
"139211", "139210", "139259", "139258", "139257", "139251", "139255",
"139254", "139253", "139252", "139251", "139250", "139249", "139248",
"139247", "139241", "139245", "139244", "139243", "139242", "139241",
"139240", "139239", "139238", "139237", "139231", "139235", "139234",
"139233", "139232", "139231", "139230", "139229", "139228", "139227",
"139221", "139225", "139224", "139223", "139222", "139221", "139220",
"139219", "139218", "139217", "139211", "139215", "139214", "139213",
"139212", "139211", "139210", "139209", "139208", "139207", "139201",
"139205", "139204", "139203", "139202", "139201", "139200", "139199",
"139198", "139197", "139191", "139195", "139194", "139193", "139192",
"139191", "139190", "139189", "139188", "139187", "139181", "139185",
"139184", "139183", "139182", "139181", "139180", "139179", "139178",
"139177", "139171", "139175", "139174", "139173", "139172", "139171",
"139170", "139119", "139118", "139117", "139111", "139115", "139114",
"139113", "139112", "139111", "139110", "139159", "139158", "139157",
"139151", "139155", "139154", "139153", "139152", "139151", "139150",
"139149", "139148", "139147", "139141", "139145", "139144", "139143",
"139142", "139141", "139140", "139139", "139138", "139137", "139131",
"139135", "139134", "139133", "139132", "139131", "139130", "139129",
"139128", "139127", "139121", "139125", "139124", "139123", "139122",
"139121", "139120", "139119", "139118", "139117", "139111", "139115",
"139114", "139113", "139112", "139111", "139110", "139109", "139108",
"139107", "139101", "139105", "139104", "139103", "139102", "139101",
"139100", "139099", "139098", "139097", "139091", "139095", "139094",
"139093", "139092", "139091", "139090", "139089", "139088", "139087",
"139081", "139085", "139084", "139083", "139082", "139081", "139080",
"139079", "139078", "139077", "139071", "139075", "139074", "139073",
"139072", "139071", "139070", "139019", "139018", "139017", "139011",
"139015", "139014", "139013", "139012", "139011", "139010", "139059",
"139058", "139057", "139051", "139055", "139054", "139053", "139052",
"139051", "139050", "139049", "139048", "139047", "139041", "139045",
"139044", "139043", "139042", "139041", "139040", "139039", "139038",
"139037", "139031", "139035", "139034", "139033", "139032", "139031",
"139030", "139029", "139028", "139027", "139021", "139025", "139024",
"139023", "139022", "139021", "139020", "139019", "139018", "139017",
"139011", "139015", "139014", "139013", "139012", "139011", "139010",
"139009", "139008", "139007", "139001", "139005", "139004", "139003",
"139002", "139001", "139000", "138999", "138998", "138997", "138991",
"138995", "138994", "138993", "138992", "138991", "138990", "138989",
"138988", "138987", "138981", "138985", "138984", "138983", "138982",
"138981", "138980", "138979", "138978", "138977", "138971", "138975",
"138974", "138973", "138972", "139275", "139274", "139273", "139272",
"139271", "139270", "139219", "139218", "139217"}
For Each st As String In str1
Dim row As DataRow = dt.Rows.Find(st)
If newTable.Rows.Find(st) Is Nothing Then
newTable.ImportRow(row)
End If
Next
finish = DateTime.Now
time = finish.Subtract(start)
Debug.WriteLine("Time taken = " & time.TotalSeconds)
DataGrid1.DataSource = newTable
End Sub
"Geoff Jones" <no********@email.com> wrote in message
news:42***********************@news.dial.pipex.com ... Hiya Bernie
The difference i.e. 100 as opposed to 10000 was merely the range of
possibilities. Sorry for the confusion.
The rows are in a DataSet; but also in a table - which is stored in the
DataSet.
The code looks excellent. I'll give it a try tomorrow.
Geoff
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:u3**************@TK2MSFTNGP09.phx.gbl...
Hi Geoff,
Not putting me out at all; glad to help.
But I'm still confused. You originally said this:
I have a list of about 100 numbers which correspond to the ID field
and also another 40 say numbers corresponding to the numbers in the
PRODUCT field.
Now you mentioned 10000 - quite a difference.
In any event, that may not be an issue. You say now you have these
10,000 rows - in a dataset/datatable? I have a code sample below
that's very fast and might serve as a template for you. It fills a
dataset and then makes a dataview from the datatable and then filters
the rows as necessary, using 2 criteria (replace these with product and
cost).
Let me know if we're getting somewhere.
Regards,
Bernie
Dim oconn As New SqlConnection("data source=d5z0071;initial
catalog=imc;integrated security=sspi;")
Dim ocmd As New SqlCommand("select * from prod order by bipad", oconn)
Dim oda As New SqlDataAdapter(ocmd)
Dim ods As New DataSet("prod")
Try
oconn.Open()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
oda.Fill(ods, "prod")
Dim vue As New DataView(ods.Tables(0))
vue.Sort = "bipad"
vue.RowFilter = "bipad in ('18772','18770','01043') and issuecode =
'200304'"
Dim irow As DataRow
Dim irows As DataRow()
Dim i As Integer
For i = 0 To vue.Count - 1
MessageBox.Show(vue(i)("bipad"))
MessageBox.Show(vue(i)("issuecode"))
MessageBox.Show(vue(i)("usprice"))
Next
oconn.Close()
"Geoff Jones" <no********@email.com> wrote in message
news:42***********************@news.dial.pipex.com ...
> Hi Bernie
>
> I hope I'm not putting you to too much trouble. You've been invaluable
> already!
>
> At the moment, I'm getting a list of approximately 10,000 ID's i.e.
> primary keys from another process which give me the rows in the
> 200,000 total. As I said previously, I was originally getting these
> rows by using a Table.Rows.Find. From these 10,000 or so rows, I'm
> trying to filter them down to those which satisfy a PRODUCT field
> criteria e.g. PRODUCT IN ('3454', '324', '7855', etc.)
>
> Indeed, my next step may be to do something like (forgive the pseudo
> code):
>
> WHERE PRODUCT = '2345' AND COST = '3.45'
>
> where COST is another field.
>
> I had considered the possibility of using multiple tables i.e.
> searching a table for rows, creating a new table, searching this new
> table with a new criteria etc. etc. in the same way that Cor has
> suggested. I'm interested in both techniques i.e. cloning and SQL type
> coding. What are the advantages and disadvantages of both?
>
> Thanks again for your continuing help.
>
> Geoff
>
> "Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
> news:uw**************@TK2MSFTNGP09.phx.gbl...
>> Hi Geoff,
>>
>> OK; I've got your code.
>>
>> First, let me tell you that we will get this working effectively -
>> 200,000 rows is not daunting. But I need more information. Am I
>> correct in assuming that you first specify the 10,000 rows and then
>> you want to 'find' rows that are among those 10,000 but also meet a
>> certain product id condition? If this is so, let me know - I can
>> construct a dataview of the 10,000 and show you how to search it very
>> easily, with or without using the PK.
>>
>> Get back to me asap. I will work on this approach until I hear from
>> you.
>>
>> Bernie
>>
>> "Geoff Jones" <no********@email.com> wrote in message
>> news:42***********************@news.dial.pipex.com ...
>>> Thanks for the kind offer Bernie. Unfortunately the database is
>>> massive i.e. in excess of 200,000 rows!
>>>
>>> The code I am using is something like:
>>>
>>> Dim f As String = "ID IN ('543', '2345', '435', etc - goes on for
>>> about 10,000 numbers) AND PRODUCT IN ('345', '456', '3445')"
>>>
>>> Dim vue As DataView = New DataView(theBigTable)
>>>
>>> vue.RowFilter = f ' The program appears to hang here i.e. very slow
>>>
>>> Obviously, the table is VERY large, so I'd expect some delay.
>>> However, the first bit of code I wrote used a "Find" routine of a
>>> DataTable to find the "ID" numbers (could do this because they were
>>> the primary keys). I then, after finding the rows that satisifed the
>>> ID condition, I simply iterated through them to find the required
>>> rows satisfying the second condition i.e. PRODUCT.
>>>
>>> This method worked well i.e. relatively quickly. Is it possible then
>>> that I'm seeing a big increase in filtering time because I'm not
>>> searching on the primary key fields?
>>>
>>> Geoff
>>>
>>>
>>> "Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
>>> news:eC*************@TK2MSFTNGP09.phx.gbl...
>>>> Hi Goeff,
>>>>
>>>> It shouldn't be slow. Can you send me a code snippet and an idea
>>>> of the tables you are using - number of rows, number of columns,
>>>> kind of database (sql, ms access, etc).
>>>>
>>>> I would like to test it on my system to see what I experience.
>>>>
>>>> Bernie
>>>>
>>>> "Geoff Jones" <no********@email.com> wrote in message
>>>> news:42***********************@news.dial.pipex.com ...
>>>>> Hi Bernie
>>>>>
>>>>> I've tried the "IN" scheme and it works but it is very slow. Am I
>>>>> doing something wrong or can it be slow in searching?
>>>>>
>>>>> Geoff
>>>>>
>>>>> "Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
>>>>> news:eC**************@TK2MSFTNGP10.phx.gbl...
>>>>>> Hi Geoff,
>>>>>>
>>>>>> By the way, if you already have a 'larger' datatable, you can use
>>>>>> the method I showed you earlier in a dataview select.
>>>>>>
>>>>>> Bernie
>>>>>>
>>>>>> "Geoff Jones" <no********@email.com> wrote in message
>>>>>> news:42**********************@news.dial.pipex.com. ..
>>>>>>> Hiya
>>>>>>>
>>>>>>> I have a DataTable containing thousands of records. Each record
>>>>>>> has a primary key field called "ID" and another field called
>>>>>>> "PRODUCT" I want to retrieve the rows that satisy the following
>>>>>>> criteria:
>>>>>>>
>>>>>>> I have a list of about 100 numbers which correspond to the ID
>>>>>>> field and also another 40 say numbers corresponding to the
>>>>>>> numbers in the PRODUCT field. I want to show the rows that
>>>>>>> correspond to both these criteria.
>>>>>>>
>>>>>>> My oringinal idea was to create a DataView and then generate a
>>>>>>> string an SQL string, which I could then use with RowFilter to
>>>>>>> get the rows I want.
>>>>>>>
>>>>>>> However, I'm uncertain of the SQL syntax to use and, more
>>>>>>> importantly, if this is the best way to do it.
>>>>>>>
>>>>>>> Can anybody help?
>>>>>>>
>>>>>>> Geoff
>>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>
Hi Jay
Good advice as always.
You mention "profiling". Is this some way of deciding which routines are the
quickest? If so, I'd be most interested in knowing how the experts do this!
I, in my naive way, have probably been doing it the slow way i.e. I wrap a
routine in between two time variables and use TimeSpan to work out how many
seconds has passed. Is there an easier way to do this using the Visual
Studio environment?
Geoff
"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message
news:ur****************@tk2msftngp13.phx.gbl... Geoff,
The values in your keys array (str1) are not unique, hence the
duplicates...
I would consider ensuring that your keys array is distinct before creating
the newTable. Alternatively I would only process unique values in str1 (by
sorting it first)...
Array.Sort(str1)
Dim lastkey As String = Nothing
For Each key As String In str1
If key <> lastkey Then
For Each row As DataRowView In view.FindRows(key)
newTable.ImportRow(row.Row)
Next
lastkey = key
End If
Next
However! I am not saying using Contains is a bad thing either :-) Which
method to use would require profiling to decide, naturally it may change
over the life of your program.
Of course if you can ensure that your keys array is unique to begin with,
then that might be "best"...
Hope this helps
Jay
"Geoff Jones" <no********@email.com> wrote in message
news:42***********************@news.dial.pipex.com ... Hi Bernie and Jay
Many thanks for all your help. As always it has been very invaluable to
learn from you guys.
Jay - the reason I used Contains was because otherwise I got an error
saying that the row already existed. If you comment it out, you'll see
that it causes an exception in the code I posted.
Geoff
"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message
news:Og*************@TK2MSFTNGP12.phx.gbl... Geoff,
Obviously, I couldn't use the same technique i.e. "Find", from the
outset, using a non-primary key (because it needs a primary key to
work).
Actually you could have!
You should be able to use DataView.Find or DataView.FindRows on
non-primary keys with about the same performance as
DataRowCollection.Find.
When you create the DataView, set the DataView.Sort property to the
non-primary key columns you are searching on, do not need to set the
RowFilter property.
Then rather then using DataTable.Rows.Find, use DataView.Find or
DataView.FindRows! DataView.Find returns an integer index into the
DataView, while DataView.FindRows returns DataRowView objects...
Something like:
Dim newTable As DataTable = dt.Clone
For Each st As String In str1
Dim row As DataRow = dt.Rows.Find(st)
If newTable.Rows.Find(st) Is Nothing Then
newTable.ImportRow(row)
End If
Next
dt.PrimaryKey = Nothing
Dim newTable As DataTable = dt.Clone
newTable.PrimaryKey = New DataColumn()
{newTable.Columns("ID")}
Dim view As New DataView(dt)
view.Sort = "ID"
For Each key As String In str1
For Each row As DataRowView In view.FindRows(key)
If Not newTable.Rows.Contains(key) Then
newTable.ImportRow(row.Row)
End If
Next
Next
I don't believe the newTable.Rows.Contains is really needed. I left it
in as you had it in your original code.
Hope this helps
Jay
<<snip>>
Hi Cor,
It is more clear to me now and I do understand that the 'double' select is
the cause of the delay.
Tx,
Bernie
"Cor Ligthert" <no************@planet.nl> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl... Bernie,
I have the idea that you did not understand why my first message was for
in this thread and why it was so slow with the first attempts of Geoff.
There is no dataview *select* function.
What probably (that deep did I not look for it) is happening in the mix
from Geoff and your first code, (it is not that consistent that I know
where it is) is that there is created a dataview (defaultview) with a
rowfilter.
That is a rowfilter for a datatable that is referenced in that dataview.
So when you filter 100 rows you can loop through that very fast.
However, when you use this.
Dataview.table.select you are selecting again from that datatable from
200.000 rows.
Therefore I showed it with that cloned and copied table.
I hope this makes it more clear why I wrote this in this thread.
Cor
"Bernie Yaeger" <be*****@cherwellinc.com> schreef in bericht
news:%2****************@TK2MSFTNGP14.phx.gbl... Hi Goeff,
You are correct; Cor's approach is much faster.
Good luck,
Bernie
"Geoff Jones" <no********@email.com> wrote in message
news:42***********************@news.dial.pipex.com ... HI Bernie
Thanks for the code. I've learned a lot in the last few days with your
and others help. I do like the idea of using "IN" but unfortunately it
does seem to be slower than my original idea. I've written some code
based on Cor's which demonstrates the difference:
The first bit of code filters the dataset using "IN" and the last bit
does it using a "Find". When I ran it this morning, the SQL code took 41
seconds whilst the Find took 0.03 seconds. Quite a difference.
Obviously, I couldn't use the same technique i.e. "Find", from the
outset, using a non-primary key (because it needs a primary key to
work).
Maybe the best approach is a mixture of both techniques?
Geoff
P.S. Code is below:
Dim dt As New DataTable
Private Sub Form1_Load(ByVal sender _
As System.Object, ByVal e As System.EventArgs) _
Handles MyBase.Load
dt.Columns.Add("ID")
dt.Columns.Add("PRODUCT")
dt.Columns.Add("COST")
For i As Integer = 0 To 200000
dt.Rows.Add(dt.NewRow)
dt.Rows(i)(0) = i.ToString
Dim rg As Random = New Random(i)
Dim rn As Integer = CInt(rg.Next(0, 200000))
dt.Rows(i)(1) = rn.ToString
rg = New Random(rn)
rn = CInt(rg.Next(0, 200000))
dt.Rows(i)(2) = rn.ToString
Next
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click
Dim start As DateTime = DateTime.Now
Dim numOfRows As Integer = dt.Rows.Count
Dim vue As DataView = New DataView(dt)
vue.Sort = "ID"
vue.RowFilter = "ID IN
('139211','139215','139214','139213','139212','139 211','139210','139259','139258','139257','139251', '139255','139254','139253','139252','139251','1392 50','139249','139248','139247','139241','139245',' 139244','139243','139242','139241','139240','13923 9','139238','139237','139231','139235','139234','1 39233','139232','139231','139230','139229','139228 ','139227','139221','139225','139224','139223','13 9222','139221','139220','139219','139218','139217' ,'139211','139215','139214','139213','139212','139 211','139210','139209','139208','139207','139201', '139205','139204','139203','139202','139201','1392 00','139199','139198','139197','139191','139195',' 139194','139193','139192','139191','139190','13918 9','139188','139187','139181','139185','139184','1 39183','139182','139181','139180','139179','139178 ','139177','139171','139175','139174','139173','13 9172','139171','139170','139119','139118','139117' ,'139111','139115','139114','139113','139112','139 111','139110','139159','139158','139157','139151', '139155','139154','139153','139152','139151','1391 50','139149','139148','139147','139141','139145',' 139144','139143','139142','139141','139140','13913 9','139138','139137','139131','139135','139134','1 39133','139132','139131','139130','139129','139128 ','139127','139121','139125','139124','139123','13 9122','139121','139120','139119','139118','139117' ,'139111','139115','139114','139113','139112','139 111','139110','139109','139108','139107','139101', '139105','139104','139103','139102','139101','1391 00','139099','139098','139097','139091','139095',' 139094','139093','139092','139091','139090','13908 9','139088','139087','139081','139085','139084','1 39083','139082','139081','139080','139079','139078 ','139077','139071','139075','139074','139073','13 9072','139071','139070','139019','139018','139017' ,'139011','139015','139014','139013','139012','139 011','139010','139059','139058','139057','139051', '139055','139054','139053','139052','139051','1390 50','139049','139048','139047','139041','139045',' 139044','139043','139042','139041','139040','13903 9','139038','139037','139031','139035','139034','1 39033','139032','139031','139030','139029','139028 ','139027','139021','139025','139024','139023','13 9022','139021','139020','139019','139018','139017' ,'139011','139015','139014','139013','139012','139 011','139010','139009','139008','139007','139001', '139005','139004','139003','139002','139001','1390 00','138999','138998','138997','138991','138995',' 138994','138993','138992','138991','138990','13898 9','138988','138987','138981','138985','138984','1 38983','138982','138981','138980','138979','138978 ','138977','138971','138975','138974','138973','13 8972','139275','139274','139273','139272','139271' ,'139270','139219','139218','139217')"
DataGrid1.DataSource = dt
Dim finish As DateTime = DateTime.Now
Dim time As TimeSpan = finish.Subtract(start)
Debug.WriteLine("Time taken = " & time.TotalSeconds)
DataGrid1.DataSource = dt
dt.PrimaryKey = New DataColumn() {dt.Columns("ID")}
start = DateTime.Now
Dim newTable As DataTable = dt.Clone
Dim str1() As String = {"139211", "139215", "139214", "139213",
"139212", "139211", "139210", "139259", "139258", "139257", "139251",
"139255", "139254", "139253", "139252", "139251", "139250", "139249",
"139248", "139247", "139241", "139245", "139244", "139243", "139242",
"139241", "139240", "139239", "139238", "139237", "139231", "139235",
"139234", "139233", "139232", "139231", "139230", "139229", "139228",
"139227", "139221", "139225", "139224", "139223", "139222", "139221",
"139220", "139219", "139218", "139217", "139211", "139215", "139214",
"139213", "139212", "139211", "139210", "139209", "139208", "139207",
"139201", "139205", "139204", "139203", "139202", "139201", "139200",
"139199", "139198", "139197", "139191", "139195", "139194", "139193",
"139192", "139191", "139190", "139189", "139188", "139187", "139181",
"139185", "139184", "139183", "139182", "139181", "139180", "139179",
"139178", "139177", "139171", "139175", "139174", "139173", "139172",
"139171", "139170", "139119", "139118", "139117", "139111", "139115",
"139114", "139113", "139112", "139111", "139110", "139159", "139158",
"139157", "139151", "139155", "139154", "139153", "139152", "139151",
"139150", "139149", "139148", "139147", "139141", "139145", "139144",
"139143", "139142", "139141", "139140", "139139", "139138", "139137",
"139131", "139135", "139134", "139133", "139132", "139131", "139130",
"139129", "139128", "139127", "139121", "139125", "139124", "139123",
"139122", "139121", "139120", "139119", "139118", "139117", "139111",
"139115", "139114", "139113", "139112", "139111", "139110", "139109",
"139108", "139107", "139101", "139105", "139104", "139103", "139102",
"139101", "139100", "139099", "139098", "139097", "139091", "139095",
"139094", "139093", "139092", "139091", "139090", "139089", "139088",
"139087", "139081", "139085", "139084", "139083", "139082", "139081",
"139080", "139079", "139078", "139077", "139071", "139075", "139074",
"139073", "139072", "139071", "139070", "139019", "139018", "139017",
"139011", "139015", "139014", "139013", "139012", "139011", "139010",
"139059", "139058", "139057", "139051", "139055", "139054", "139053",
"139052", "139051", "139050", "139049", "139048", "139047", "139041",
"139045", "139044", "139043", "139042", "139041", "139040", "139039",
"139038", "139037", "139031", "139035", "139034", "139033", "139032",
"139031", "139030", "139029", "139028", "139027", "139021", "139025",
"139024", "139023", "139022", "139021", "139020", "139019", "139018",
"139017", "139011", "139015", "139014", "139013", "139012", "139011",
"139010", "139009", "139008", "139007", "139001", "139005", "139004",
"139003", "139002", "139001", "139000", "138999", "138998", "138997",
"138991", "138995", "138994", "138993", "138992", "138991", "138990",
"138989", "138988", "138987", "138981", "138985", "138984", "138983",
"138982", "138981", "138980", "138979", "138978", "138977", "138971",
"138975", "138974", "138973", "138972", "139275", "139274", "139273",
"139272", "139271", "139270", "139219", "139218", "139217"}
For Each st As String In str1
Dim row As DataRow = dt.Rows.Find(st)
If newTable.Rows.Find(st) Is Nothing Then
newTable.ImportRow(row)
End If
Next
finish = DateTime.Now
time = finish.Subtract(start)
Debug.WriteLine("Time taken = " & time.TotalSeconds)
DataGrid1.DataSource = newTable
End Sub
"Geoff Jones" <no********@email.com> wrote in message
news:42***********************@news.dial.pipex.com ...
Hiya Bernie
The difference i.e. 100 as opposed to 10000 was merely the range of
possibilities. Sorry for the confusion.
The rows are in a DataSet; but also in a table - which is stored in the
DataSet.
The code looks excellent. I'll give it a try tomorrow.
Geoff
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:u3**************@TK2MSFTNGP09.phx.gbl...
> Hi Geoff,
>
> Not putting me out at all; glad to help.
>
> But I'm still confused. You originally said this:
> I have a list of about 100 numbers which correspond to the ID field
> and also another 40 say numbers corresponding to the numbers in the
> PRODUCT field.
> Now you mentioned 10000 - quite a difference.
>
> In any event, that may not be an issue. You say now you have these
> 10,000 rows - in a dataset/datatable? I have a code sample below
> that's very fast and might serve as a template for you. It fills a
> dataset and then makes a dataview from the datatable and then filters
> the rows as necessary, using 2 criteria (replace these with product
> and cost).
>
> Let me know if we're getting somewhere.
>
> Regards,
>
> Bernie
> Dim oconn As New SqlConnection("data source=d5z0071;initial
> catalog=imc;integrated security=sspi;")
>
> Dim ocmd As New SqlCommand("select * from prod order by bipad", oconn)
>
> Dim oda As New SqlDataAdapter(ocmd)
>
> Dim ods As New DataSet("prod")
>
> Try
>
> oconn.Open()
>
> Catch ex As Exception
>
> MessageBox.Show(ex.Message)
>
> End Try
>
> oda.Fill(ods, "prod")
>
> Dim vue As New DataView(ods.Tables(0))
>
> vue.Sort = "bipad"
>
> vue.RowFilter = "bipad in ('18772','18770','01043') and issuecode =
> '200304'"
>
> Dim irow As DataRow
>
> Dim irows As DataRow()
>
> Dim i As Integer
>
> For i = 0 To vue.Count - 1
>
> MessageBox.Show(vue(i)("bipad"))
>
> MessageBox.Show(vue(i)("issuecode"))
>
> MessageBox.Show(vue(i)("usprice"))
>
> Next
>
> oconn.Close()
>
> "Geoff Jones" <no********@email.com> wrote in message
> news:42***********************@news.dial.pipex.com ...
>
>> Hi Bernie
>>
>> I hope I'm not putting you to too much trouble. You've been
>> invaluable already!
>>
>> At the moment, I'm getting a list of approximately 10,000 ID's i.e.
>> primary keys from another process which give me the rows in the
>> 200,000 total. As I said previously, I was originally getting these
>> rows by using a Table.Rows.Find. From these 10,000 or so rows, I'm
>> trying to filter them down to those which satisfy a PRODUCT field
>> criteria e.g. PRODUCT IN ('3454', '324', '7855', etc.)
>>
>> Indeed, my next step may be to do something like (forgive the pseudo
>> code):
>>
>> WHERE PRODUCT = '2345' AND COST = '3.45'
>>
>> where COST is another field.
>>
>> I had considered the possibility of using multiple tables i.e.
>> searching a table for rows, creating a new table, searching this new
>> table with a new criteria etc. etc. in the same way that Cor has
>> suggested. I'm interested in both techniques i.e. cloning and SQL
>> type coding. What are the advantages and disadvantages of both?
>>
>> Thanks again for your continuing help.
>>
>> Geoff
>>
>> "Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
>> news:uw**************@TK2MSFTNGP09.phx.gbl...
>>> Hi Geoff,
>>>
>>> OK; I've got your code.
>>>
>>> First, let me tell you that we will get this working effectively -
>>> 200,000 rows is not daunting. But I need more information. Am I
>>> correct in assuming that you first specify the 10,000 rows and then
>>> you want to 'find' rows that are among those 10,000 but also meet a
>>> certain product id condition? If this is so, let me know - I can
>>> construct a dataview of the 10,000 and show you how to search it
>>> very easily, with or without using the PK.
>>>
>>> Get back to me asap. I will work on this approach until I hear from
>>> you.
>>>
>>> Bernie
>>>
>>> "Geoff Jones" <no********@email.com> wrote in message
>>> news:42***********************@news.dial.pipex.com ...
>>>> Thanks for the kind offer Bernie. Unfortunately the database is
>>>> massive i.e. in excess of 200,000 rows!
>>>>
>>>> The code I am using is something like:
>>>>
>>>> Dim f As String = "ID IN ('543', '2345', '435', etc - goes on for
>>>> about 10,000 numbers) AND PRODUCT IN ('345', '456', '3445')"
>>>>
>>>> Dim vue As DataView = New DataView(theBigTable)
>>>>
>>>> vue.RowFilter = f ' The program appears to hang here i.e. very slow
>>>>
>>>> Obviously, the table is VERY large, so I'd expect some delay.
>>>> However, the first bit of code I wrote used a "Find" routine of a
>>>> DataTable to find the "ID" numbers (could do this because they were
>>>> the primary keys). I then, after finding the rows that satisifed
>>>> the ID condition, I simply iterated through them to find the
>>>> required rows satisfying the second condition i.e. PRODUCT.
>>>>
>>>> This method worked well i.e. relatively quickly. Is it possible
>>>> then that I'm seeing a big increase in filtering time because I'm
>>>> not searching on the primary key fields?
>>>>
>>>> Geoff
>>>>
>>>>
>>>> "Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
>>>> news:eC*************@TK2MSFTNGP09.phx.gbl...
>>>>> Hi Goeff,
>>>>>
>>>>> It shouldn't be slow. Can you send me a code snippet and an idea
>>>>> of the tables you are using - number of rows, number of columns,
>>>>> kind of database (sql, ms access, etc).
>>>>>
>>>>> I would like to test it on my system to see what I experience.
>>>>>
>>>>> Bernie
>>>>>
>>>>> "Geoff Jones" <no********@email.com> wrote in message
>>>>> news:42***********************@news.dial.pipex.com ...
>>>>>> Hi Bernie
>>>>>>
>>>>>> I've tried the "IN" scheme and it works but it is very slow. Am I
>>>>>> doing something wrong or can it be slow in searching?
>>>>>>
>>>>>> Geoff
>>>>>>
>>>>>> "Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
>>>>>> news:eC**************@TK2MSFTNGP10.phx.gbl...
>>>>>>> Hi Geoff,
>>>>>>>
>>>>>>> By the way, if you already have a 'larger' datatable, you can
>>>>>>> use the method I showed you earlier in a dataview select.
>>>>>>>
>>>>>>> Bernie
>>>>>>>
>>>>>>> "Geoff Jones" <no********@email.com> wrote in message
>>>>>>> news:42**********************@news.dial.pipex.com. ..
>>>>>>>> Hiya
>>>>>>>>
>>>>>>>> I have a DataTable containing thousands of records. Each record
>>>>>>>> has a primary key field called "ID" and another field called
>>>>>>>> "PRODUCT" I want to retrieve the rows that satisy the following
>>>>>>>> criteria:
>>>>>>>>
>>>>>>>> I have a list of about 100 numbers which correspond to the ID
>>>>>>>> field and also another 40 say numbers corresponding to the
>>>>>>>> numbers in the PRODUCT field. I want to show the rows that
>>>>>>>> correspond to both these criteria.
>>>>>>>>
>>>>>>>> My oringinal idea was to create a DataView and then generate a
>>>>>>>> string an SQL string, which I could then use with RowFilter to
>>>>>>>> get the rows I want.
>>>>>>>>
>>>>>>>> However, I'm uncertain of the SQL syntax to use and, more
>>>>>>>> importantly, if this is the best way to do it.
>>>>>>>>
>>>>>>>> Can anybody help?
>>>>>>>>
>>>>>>>> Geoff
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>
Geoff, I wrap a routine in between two time variables and use TimeSpan to work
out how many seconds has passed.
For general stuff like this that is how I normally do it.
For more specific profiling I use CLR Profiler (or other similiar tools).
Just Remember the 80/20 rule. That is 80% of the execution time of your
program is spent in 20% of your code. I will optimize (worry about
performance, memory consumption) the 20% once that 20% has been identified &
proven to be a performance problem via profiling (CLR Profiler is one
profiling tool).
For info on the 80/20 rule & optimizing only the 20% see Martin Fowler's
article "Yet Another Optimization Article" at
http://martinfowler.com/ieeeSoftware...timization.pdf
Info on the CLR Profiler:
http://msdn.microsoft.com/library/de...nethowto13.asp
http://msdn.microsoft.com/library/de...anagedapps.asp
Hope this helps
Jay
"Geoff Jones" <no********@email.com> wrote in message
news:42***********************@news.dial.pipex.com ... Hi Jay
Good advice as always.
You mention "profiling". Is this some way of deciding which routines are
the quickest? If so, I'd be most interested in knowing how the experts do
this! I, in my naive way, have probably been doing it the slow way i.e. I
wrap a routine in between two time variables and use TimeSpan to work out
how many seconds has passed. Is there an easier way to do this using the
Visual Studio environment?
Geoff
"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message
news:ur****************@tk2msftngp13.phx.gbl... Geoff,
The values in your keys array (str1) are not unique, hence the
duplicates...
I would consider ensuring that your keys array is distinct before
creating the newTable. Alternatively I would only process unique values
in str1 (by sorting it first)...
Array.Sort(str1)
Dim lastkey As String = Nothing
For Each key As String In str1
If key <> lastkey Then
For Each row As DataRowView In view.FindRows(key)
newTable.ImportRow(row.Row)
Next
lastkey = key
End If
Next
However! I am not saying using Contains is a bad thing either :-) Which
method to use would require profiling to decide, naturally it may change
over the life of your program.
Of course if you can ensure that your keys array is unique to begin with,
then that might be "best"...
Hope this helps
Jay
"Geoff Jones" <no********@email.com> wrote in message
news:42***********************@news.dial.pipex.com ... Hi Bernie and Jay
Many thanks for all your help. As always it has been very invaluable to
learn from you guys.
Jay - the reason I used Contains was because otherwise I got an error
saying that the row already existed. If you comment it out, you'll see
that it causes an exception in the code I posted.
Geoff
"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in
message news:Og*************@TK2MSFTNGP12.phx.gbl...
Geoff,
> Obviously, I couldn't use the same technique i.e. "Find", from the
> outset, using a non-primary key (because it needs a primary key to
> work).
Actually you could have!
You should be able to use DataView.Find or DataView.FindRows on
non-primary keys with about the same performance as
DataRowCollection.Find.
When you create the DataView, set the DataView.Sort property to the
non-primary key columns you are searching on, do not need to set the
RowFilter property.
Then rather then using DataTable.Rows.Find, use DataView.Find or
DataView.FindRows! DataView.Find returns an integer index into the
DataView, while DataView.FindRows returns DataRowView objects...
Something like:
> Dim newTable As DataTable = dt.Clone
>
> For Each st As String In str1
>
> Dim row As DataRow = dt.Rows.Find(st)
>
> If newTable.Rows.Find(st) Is Nothing Then
>
> newTable.ImportRow(row)
>
> End If
>
> Next
dt.PrimaryKey = Nothing
Dim newTable As DataTable = dt.Clone
newTable.PrimaryKey = New DataColumn()
{newTable.Columns("ID")}
Dim view As New DataView(dt)
view.Sort = "ID"
For Each key As String In str1
For Each row As DataRowView In view.FindRows(key)
If Not newTable.Rows.Contains(key) Then
newTable.ImportRow(row.Row)
End If
Next
Next
I don't believe the newTable.Rows.Contains is really needed. I left it
in as you had it in your original code.
Hope this helps
Jay
<<snip>>
Excellent. Thanks Jay
Geoff
"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl... Geoff, I wrap a routine in between two time variables and use TimeSpan to work
out how many seconds has passed.
For general stuff like this that is how I normally do it.
For more specific profiling I use CLR Profiler (or other similiar tools).
Just Remember the 80/20 rule. That is 80% of the execution time of your
program is spent in 20% of your code. I will optimize (worry about
performance, memory consumption) the 20% once that 20% has been identified
&
proven to be a performance problem via profiling (CLR Profiler is one
profiling tool).
For info on the 80/20 rule & optimizing only the 20% see Martin Fowler's
article "Yet Another Optimization Article" at
http://martinfowler.com/ieeeSoftware...timization.pdf
Info on the CLR Profiler:
http://msdn.microsoft.com/library/de...nethowto13.asp
http://msdn.microsoft.com/library/de...anagedapps.asp
Hope this helps
Jay
"Geoff Jones" <no********@email.com> wrote in message
news:42***********************@news.dial.pipex.com ... Hi Jay
Good advice as always.
You mention "profiling". Is this some way of deciding which routines are
the quickest? If so, I'd be most interested in knowing how the experts do
this! I, in my naive way, have probably been doing it the slow way i.e. I
wrap a routine in between two time variables and use TimeSpan to work out
how many seconds has passed. Is there an easier way to do this using the
Visual Studio environment?
Geoff
"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message
news:ur****************@tk2msftngp13.phx.gbl... Geoff,
The values in your keys array (str1) are not unique, hence the
duplicates...
I would consider ensuring that your keys array is distinct before
creating the newTable. Alternatively I would only process unique values
in str1 (by sorting it first)...
Array.Sort(str1)
Dim lastkey As String = Nothing
For Each key As String In str1
If key <> lastkey Then
For Each row As DataRowView In view.FindRows(key)
newTable.ImportRow(row.Row)
Next
lastkey = key
End If
Next
However! I am not saying using Contains is a bad thing either :-) Which
method to use would require profiling to decide, naturally it may change
over the life of your program.
Of course if you can ensure that your keys array is unique to begin
with, then that might be "best"...
Hope this helps
Jay
"Geoff Jones" <no********@email.com> wrote in message
news:42***********************@news.dial.pipex.com ...
Hi Bernie and Jay
Many thanks for all your help. As always it has been very invaluable to
learn from you guys.
Jay - the reason I used Contains was because otherwise I got an error
saying that the row already existed. If you comment it out, you'll see
that it causes an exception in the code I posted.
Geoff
"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in
message news:Og*************@TK2MSFTNGP12.phx.gbl...
> Geoff,
>> Obviously, I couldn't use the same technique i.e. "Find", from the
>> outset, using a non-primary key (because it needs a primary key to
>> work).
> Actually you could have!
>
> You should be able to use DataView.Find or DataView.FindRows on
> non-primary keys with about the same performance as
> DataRowCollection.Find.
>
> When you create the DataView, set the DataView.Sort property to the
> non-primary key columns you are searching on, do not need to set the
> RowFilter property.
>
> Then rather then using DataTable.Rows.Find, use DataView.Find or
> DataView.FindRows! DataView.Find returns an integer index into the
> DataView, while DataView.FindRows returns DataRowView objects...
>
> Something like:
>
>> Dim newTable As DataTable = dt.Clone
>>
>> For Each st As String In str1
>>
>> Dim row As DataRow = dt.Rows.Find(st)
>>
>> If newTable.Rows.Find(st) Is Nothing Then
>>
>> newTable.ImportRow(row)
>>
>> End If
>>
>> Next
>
> dt.PrimaryKey = Nothing
>
> Dim newTable As DataTable = dt.Clone
> newTable.PrimaryKey = New DataColumn()
> {newTable.Columns("ID")}
>
> Dim view As New DataView(dt)
> view.Sort = "ID"
>
> For Each key As String In str1
> For Each row As DataRowView In view.FindRows(key)
> If Not newTable.Rows.Contains(key) Then
> newTable.ImportRow(row.Row)
> End If
> Next
> Next
>
> I don't believe the newTable.Rows.Contains is really needed. I left it
> in as you had it in your original code.
>
> Hope this helps
> Jay
>
>
<<snip>>
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Michi |
last post by:
I was wondering what the best solution is for making large numbers of
TEXT (or BLOB?) fields searchable. For example, if I have a forum,
what is the best way to be able to search for specific...
|
by: Stuart Clark |
last post by:
Hello,
I haven't done much with ASP but now I'm giving it a go! However, I'd
like to ask if anybody knows if what I need to do is possible.
I have a database with a lot of tables. Each table...
|
by: justobservant |
last post by:
When more than one keyword is typed into a search-query, most of the
search-results displayed indicate specified keywords scattered
throughout an entire website of content i.e., this is shown as...
|
by: john |
last post by:
In my form I have a master table and a details table linked 1xM. I can
search through the whole parent table but I also like to be able to search
through the child table fields to find parent...
|
by: Aaron |
last post by:
I'm trying to parse a table on a webpage to pull down some data I
need. The page is based off of information entered into a form. when
you submit the data from the form it displays a...
|
by: db2admin |
last post by:
Hello all,
I have table with CLOB column length of ~65K. application is searching
from this table by CLOB and i can not create index on it.
I know IBM recommends using DB2 Net Search Extender...
|
by: Alexnb |
last post by:
This is similar to my last post, but a little different. Here is what I would
like to do.
Lets say I have a text file. The contents look like this, only there is A
LOT of the same thing.
() A...
|
by: tkip |
last post by:
Hello everyone. I have been working on this DB for quite some time and still can't figure out a way to do what I want to do. It's a database that keep track of drawings and engineering change etc....
|
by: repath |
last post by:
Hi,
I use DCount function to count the number of entries for the existing customer by name of the customer in the database table.
Table Structure like: CustRegion, CustName, OrdersPlaced.
...
|
by: Kemmylinns12 |
last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and efficiency. While initially associated with cryptocurrencies...
|
by: WisdomUfot |
last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific technical details, Gmail likely implements measures...
|
by: Carina712 |
last post by:
Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand. Background colors can be used to highlight important...
|
by: BLUEPANDA |
last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS starter kit that's not only easy to use but also...
|
by: Ricardo de Mila |
last post by:
Dear people, good afternoon...
I have a form in msAccess with lots of controls and a specific routine must be triggered if the mouse_down event happens in any control.
Than I need to discover what...
|
by: Johno34 |
last post by:
I have this click event on my form. It speaks to a Datasheet Subform
Private Sub Command260_Click()
Dim r As DAO.Recordset
Set r = Form_frmABCD.Form.RecordsetClone
r.MoveFirst
Do
If...
|
by: ezappsrUS |
last post by:
Hi,
I wonder if someone knows where I am going wrong below. I have a continuous form and two labels where only one would be visible depending on the checkbox being checked or not. Below is the...
|
by: jack2019x |
last post by:
hello, Is there code or static lib for hook swapchain present?
I wanna hook dxgi swapchain present for dx11 and dx9.
|
by: DizelArs |
last post by:
Hi all)
Faced with a problem, element.click() event doesn't work in Safari browser.
Tried various tricks like emulating touch event through a function:
let clickEvent = new Event('click', {...
| |
