Re: Getting info from SQL DB

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.




.

Relevant Pages

  • Re: Localizing SQL Server Data
    ... The best way to limit the data transferred through the wire is to built ... if you do the join on the Server, that means that the same repeated data ... make a local query implying the local tables. ... to pull the changed records for those tables. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Record source at runtime
    ... If you have defined a primary key, always include that in your query. ... The way Jet works is to go to the server, find the tableand pull the ... it will pull the entire table of data. ... Limiting the fields doesn't matter much unless they are OLE ...
    (microsoft.public.access.formscoding)
  • Re: Pulling remote server files, placing them in a central location
    ... pull one file from about 100 servers. ... > The file is in the same location on each server. ... > The script would pull these files into a central server after creating ... Whether you start learning with batch or wsh is up to you. ...
    (microsoft.public.scripting.wsh)
  • Query from Server1, View from Server 2
    ... I have to pull a view over from Server ... and then run a query that references the view and a db on Server 1. ...
    (microsoft.public.sqlserver.dts)
  • Dynamic Table Names in DTS
    ... I am new to DTS packages and not familiar with the object ... I want to simply pull the results of a query on ... one Server to another and save the results in a table ...
    (microsoft.public.sqlserver.dts)