searching Database

1. Is there an index on the name column?
2. What does your Select SQL statement look like?  

It is likely you can get better performance through database tuning and SQL optimization.

I don't have any SQL , all what I have is AdoTable and DataSource, I want any way to do it .

If you have an ADOTable then you can also add an ADOQuery... Duh! (And an ADOQuery is the SQL you need...)

But I just wonder why you're not moving the data from Active Directory straight into the appropiate tables in the first place. Why this in-between step of filling one table with all records, then splitting the table up?

And I don't even understand why you need the tables to be split up based on the first character.

And I don't even understand why you're searching through the table in the first place. All you have to do is walk through your input table, read the name and then based on the first letter write the record to one of the other 26 tables that you have open. Means you just walk through the whole table once, which should be quite fast...

Yes Workshope Alex ,  I can't move data from Active Directory straight into the appropriate tables , because I'm using  Active Directory tools to dump user name in tables. I believe there is away in Delphi using ADOConnection to connect to LDAP to search for user container  but I didn't use it because it's little bit hard to me .

I need the tables to be split up based on the first character in order to speed up the search , because it will be more faster if I search in table has 2 thousands or 3 thousands name than searching in Table with more than 30 thousands.

Please if any one can help me to write this code I will be much appreciated.
Thank you

Workshop_Alex

Yes  this what I did …… determine in which table to look for a certain name before I start searching and that's why I want to make A-Z tables  to make search more faster .
=============
var
  e,x,y,s,Index:string;
  i:Integer;
begin
  x:=EMailAddress;
  i:=pos('',x);
  i:=i+1;
  while x[i]<>'@' do begin
      s:=s+x[i];
      inc(i);
    end;
  y:=AnsiLeftStr(s,1);
  e:=AnsiUpperCase(y);
  Index:=e;

  with ADOTable1 do begin
      if not AdoTable1.Locate(Index,s, []) then begin
          ListBoxRejected.Items.Add(EMailAddress);
          Accept:=False
        end
      else
        ListBoxAccepted.Items.Add(EMailAddress);
      Accept:=True;
    end;
end;
==================

Now I'm determining the right Table to search for user name .

All what I want now is to convert /create new database  with  (A-Z) tables , after that I will search the current/old Database username and store the username into the right tables belong to him .
(note: this conversation / creation of new database I will make it just once I receive the updated AD username database)


 

my thoughts:

1. splitting up the names into separate tables is a bad idea (in general)
2. multi-table data distribution prevents you from using stored procs/stored queries, since table names can not be parameterized.
3. while there is some justification for denormalization of relation data to improve performance, this data-splitting isn't what the experts are talking about.

====================
Performance-improving alternatives:

P1. Create a new CHAR(1) column in your table that contains the first character of the name.  Create an index on this column.
P2. Create a new INT(2)/BYTE column in your table that contains a numerical equivalence of the first character of the name.  Create an index on this column.  This is even faster than the P1 alternative due to the speed of numeric vs. character comparison operations.  However, the searches might become a bit cryptic to the maintenance programmer or DBA.
P3. Create a new table in your database that contains the first character of the name and the record ID of the row.  Create an index on the character column.  Ideally, the entries in this table would constitute a clustered index with the first characters sorted prior to insertion in the new table.

========================
I think we experts need a better understanding of your use of this data in order to help you better.

topkah,

More suggestions:

* Drop the use of the ADOTable in favor of an ADOCommand object.
* point the ADOCommand object to a stored query or stored proc.  You can parameterize the character you are using as a limiting factor rather than use dynamic SQL.
* consider using a "bound" listbox or combobox.  "Bound" controls are tied directly to a datasource object and do not require a programmatic Items.Add method.
* Make sure you are turning off screen/control refreshing and repainting while you are adding items.

just some comments:
on aikimark comment:
"2. multi-table data distribution prevents you from using stored procs/stored queries, since table names can not be parameterized."
this is not true, you can still use stored procedures, however in this case you are using MSACCEss, so it doesn't apply

the other thing is 30,000 records is a very small amount of data even for an MSACCESS database, just adding an index to the Name field should do the job, the queries should be instantaneus, you shouldn't have to worry about splitting data and creating extra columns, etc

BlackTigerX,

Which (non-MSAccess) database engines provide parameterized tablenames for use with stored queries, stored procedures, views, or user-defined functions?  I'm not aware of any, so I hope to expand my own knowledgebase with your reply.

BlackTigerX,

That is an example of dynamic SQL.  Since performance is an important criterion for our solution, static SQL (stored query, stored proc, view, user-defined function) would be a much better performer than dynamic SQL.