Re: Getting info from SQL DB

[Armin Zingler]

"Bill Schanks" <ws******@gmail.comschrieb

I have this code, that will take what is in a listview and go get
more info. Howerver on a large number of listview records this will
produce a problem (Say if I had 30,000 Listview entires).

What would be the recommended alternative? Have it load the listview
items to a table and then have my query join to that table, Put in a
check and if there are >1000 listview entires don't allow the sql
lookup?

Try to execute

lvMembers.beginupdate

before filling and

lvMembers.endupdate

afterwards. Better now?
And try this version: (an attempt)

Dim Items As String()

ReDim Items(reader.FieldCount - 1)

Do While reader.Read
Items(0) = CStr(reader(0))

For i = 1 To reader.FieldCount - 1
If reader.IsDBNull(i) Then
Items(i) = "" 'or String.Empty
Else
Items(i) = reader.GetString(i)
End If
Next i

itmListItem = New ListViewItem(Items)
lvmembers.Items.Add(itmListItem)
icount = icount + 1

Loop


Armin

[Bill Schanks]

The error is coming because sSQL is too large, and I am getting the
error on SQL Excecution when there are 30000 entries in the in clause
of the sql command.

On Apr 25, 12:50 pm, "Armin Zingler" <[e-mail]:

"Bill Schanks" <[e-mail]

I have this code, that will take what is in a listview and go get
more info. Howerver on a large number of listview records this will
produce a problem (Say if I had 30,000 Listview entires).

What would be the recommended alternative? Have it load the listview
items to a table and then have my query join to that table, Put in a
check and if there are >1000 listview entires don't allow the sql
lookup?

Try to execute

lvMembers.beginupdate

before filling and

lvMembers.endupdate

afterwards. Better now?

And try this version: (an attempt)

Dim Items As String()

ReDim Items(reader.FieldCount - 1)

Do While reader.Read
Items(0) = CStr(reader(0))

For i = 1 To reader.FieldCount - 1
If reader.IsDBNull(i) Then
Items(i) = "" 'or String.Empty
Else
Items(i) = reader.GetString(i)
End If
Next i

itmListItem = New ListViewItem(Items)
lvmembers.Items.Add(itmListItem)
icount = icount + 1

Loop

Armin

[Lloyd Sheen]

"Bill Schanks" <ws******@gmail.comwrote in message
news:05**********************************@34g2000h sf.googlegroups.com...

The error is coming because sSQL is too large, and I am getting the
error on SQL Excecution when there are 30000 entries in the in clause
of the sql command.

On Apr 25, 12:50 pm, "Armin Zingler" <[e-mail]:

>"Bill Schanks" <[e-mail]

I have this code, that will take what is in a listview and go get
more info. Howerver on a large number of listview records this will
produce a problem (Say if I had 30,000 Listview entires).

What would be the recommended alternative? Have it load the listview
items to a table and then have my query join to that table, Put in a
check and if there are >1000 listview entires don't allow the sql
lookup?

Try to execute

lvMembers.beginupdate

before filling and

lvMembers.endupdate

afterwards. Better now?

And try this version: (an attempt)

Dim Items As String()

ReDim Items(reader.FieldCount - 1)

Do While reader.Read
Items(0) = CStr(reader(0))

For i = 1 To reader.FieldCount - 1
If reader.IsDBNull(i) Then
Items(i) = "" 'or String.Empty
Else
Items(i) = reader.GetString(i)
End If
Next i

itmListItem = New ListViewItem(Items)
lvmembers.Items.Add(itmListItem)
icount = icount + 1

Loop

Armin

What you want to do is if you are using ListView , make it a virtual
listview. I have samples if you need them. Can fill and display 30K items
very quickly.

LS

[Bill Schanks]

The error is not filling the listview, it doesn't even get to that
point. The SQL Command is the problem. Here is the error I get:

system.data.sqlclient.sqlexception: Internal query processor error:
The query processor ran out of stack space during query optimization

This is due to the IN Clause have 30000 items. My question was should
I have the program load the list view items to a table and then join
to that table to my query, or just not allow the command if the IN
Clause would have more than x number of items.

On Apr 25, 1:04 pm, "Lloyd Sheen" <[e-mail]:

"Bill Schanks" <[e-mail] in message

news:05**********************************@34g2000h sf.googlegroups.com...

The error is coming because sSQL is too large, and I am getting the
error on SQL Excecution when there are 30000 entries in the in clause
of the sql command.

On Apr 25, 12:50 pm, "Armin Zingler" <[e-mail]:

"Bill Schanks" <[e-mail]

I have this code, that will take what is in a listview and go get
more info. Howerver on a large number of listview records this will
produce a problem (Say if I had 30,000 Listview entires).

What would be the recommended alternative? Have it load the listview
items to a table and then have my query join to that table, Put in a
check and if there are >1000 listview entires don't allow the sql
lookup?

Try to execute

lvMembers.beginupdate

before filling and

lvMembers.endupdate

afterwards. Better now?

And try this version: (an attempt)

Dim Items As String()

ReDim Items(reader.FieldCount - 1)

Do While reader.Read
Items(0) = CStr(reader(0))

For i = 1 To reader.FieldCount - 1
If reader.IsDBNull(i) Then
Items(i) = "" 'or String.Empty
Else
Items(i) = reader.GetString(i)
End If
Next i

itmListItem = New ListViewItem(Items)
lvmembers.Items.Add(itmListItem)
icount = icount + 1

Loop

Armin

What you want to do is if you are using ListView , make it a virtual
listview. I have samples if you need them. Can fill and display 30K items
very quickly.

LS

[Armin Zingler]

"Bill Schanks" <ws******@gmail.comschrieb

The error is coming because sSQL is too large, and I am getting the
error on SQL Excecution when there are 30000 entries in the in clause
of the sql command.

Reading this statement

>>Howerver on a large number of listview records this will produce a
problem (Say if I had 30,000 Listview entires)."

I thought it takes too long.
What does "SQL is too large" mean? Do you get an exception? Which one?

Armin

[Bill Schanks]

This is the error:
system.data.sqlclient.sqlexception: Internal query processor error:
The query processor ran out of stack space during query optimization

This is because the in clause would have 30,000+ items in it.

On Apr 25, 1:53 pm, "Armin Zingler" <[e-mail]:

"Bill Schanks" <[e-mail]

The error is coming because sSQL is too large, and I am getting the
error on SQL Excecution when there are 30000 entries in the in clause
of the sql command.

Reading this statement

>Howerver on a large number of listview records this will produce a
problem (Say if I had 30,000 Listview entires)."

I thought it takes too long.

What does "SQL is too large" mean? Do you get an exception? Which one?

Armin

[Armin Zingler]

"Bill Schanks" <ws******@gmail.comschrieb

This is the error:
system.data.sqlclient.sqlexception: Internal query processor error:
The query processor ran out of stack space during query optimization

This is because the in clause would have 30,000+ items in it.

Ok, I see. Just read your other message. Wondered why you posted the
code to fill the listview if it has nothing to do with the problem.
Anyways, yes, you should insert the keys into a table and have the DBMS
do the join.
Armin

[Bill Schanks]

Thanks. Sorry I should have been more clear in my question.

On Apr 25, 5:00 pm, "Armin Zingler" <[e-mail]:

"Bill Schanks" <[e-mail]

This is the error:
system.data.sqlclient.sqlexception: Internal query processor error:
The query processor ran out of stack space during query optimization

This is because the in clause would have 30,000+ items in it.

Ok, I see. Just read your other message. Wondered why you posted the
code to fill the listview if it has nothing to do with the problem.
Anyways, yes, you should insert the keys into a table and have the DBMS
do the join.

Armin

[Steve Gerrard]

Bill Schanks wrote:

This is the error:
system.data.sqlclient.sqlexception: Internal query processor error:
The query processor ran out of stack space during query optimization

This is because the in clause would have 30,000+ items in it.

I have to ask, where do the 30,000 items come from? Assuming they haven't been
added manually by the user, it seems like they might already be in the DB
somewhere.

I'm surprised you could go as high as 30,000, that's a pretty big IN clause. :)

[Bill Schanks]

The application pulls users that are in an Active Directory Group. And
allows users to pull add'l data from the HR Records that is not avail
from Active Directory.

And it doesn't allow 30,000 records in the in clause it errors out. I
don't know what the limit is. Now 30,000 is an extreme example. Users
most likely won't want to pull info on 30,000 People. But I'd like the
app to work regardless.

On Apr 25, 8:19 pm, "Steve Gerrard" <[e-mail]:

Bill Schanks wrote:

This is the error:
system.data.sqlclient.sqlexception: Internal query processor error:
The query processor ran out of stack space during query optimization

This is because the in clause would have 30,000+ items in it.

I have to ask, where do the 30,000 items come from? Assuming they haven't been
added manually by the user, it seems like they might already be in the DB
somewhere.

I'm surprised you could go as high as 30,000, that's a pretty big IN clause. :)

[Steve Gerrard]

Bill Schanks wrote:

The application pulls users that are in an Active Directory Group. And
allows users to pull add'l data from the HR Records that is not avail
from Active Directory.

And it doesn't allow 30,000 records in the in clause it errors out. I
don't know what the limit is. Now 30,000 is an extreme example. Users
most likely won't want to pull info on 30,000 People. But I'd like the
app to work regardless.

Got it. So they aren't in the DB. I don't know what the limit in on an IN clause
might be, but I suspect it is variable, depending on configuration and possibly
load on the server as well.

I would pick a number for the limit of one query, somewhere in the 20 to 50
range maybe. You can then run the query repeatedly, getting 20 to 50 results
each time. If you made the query with parameters, you can get pretty good
results this way; after the first one, the server caches the statement, and can
crank them out. It is a little bit like using a keyset cursor, something some of
the database drivers do - or used to do. It will help if PreferredID is indexed.

The SQL with just 3 parameters might look like
... Where PreferredID IN (@P1, @P2, @P3) ...

you would add the parameters to your command:
command.Parameters.Add("@P1", SqlDbType.VarChar, 80)
command.Parameters.Add("@P2", SqlDbType.VarChar, 80)
command.Parameters.Add("@P3", SqlDbType.VarChar, 80)

for each batch, you set the parameter values instead of building a string:
command.Parameters("@P1").Value = item.Text

and clear any unused parameters if the last batch has less than a full set.

It would take some fiddling to get it to work right, but once you have it,
something like that could handle any volume, and give a reasonable response time
as well.

The alternative, uploading the list of items to the server first, may or may not
be useful. Inserting several 1000 records is not particularly fast, but it might
work well, depending on - well, lots of things, I guess.

[Mike Williams]

"Lloyd Sheen" <[e-mail] in message
news:OX**************@TK2MSFTNGP05.phx.gbl...

What you want to do is if you are using ListView , make it a virtual
listview. I have samples if you need them. Can fill and display 30K
items very quickly.

I don't know whether anyone from Microsoft regularly reads the Visual Basic
groups but if they do then I am very surprised that they permit one of their
own MVPs to engage in such outrageous long term trolling activities in one
of their own public newsgroups, such as the activity that the person who
purports to be Bill McCarthy has engaged in on the
microsoft.public.vb.general.discussion group for many months. If this man
belongs to you:

https://mvp.support.microsoft.com/pr...9-25680609CCB8

.. . . then perhaps you might like to look at his activity in that group.
Here for example is one of his very latest offerings:

"Bill McCarthy" <Bi**@N0SPAM.comwrote in message
news:19**********************************@microsof t.com...

Yeh, still a way to go. I think given the warm reaction today,
and given some folks have learning difficulties around here
(obviously why they are still *stuck* in VB6 *only*), might
just have to ramp things up a notch or two, and make it more
toasty :D