searching Database

Ok, i have one ms access database exported from ActiveDirectory with one table and one filed  (employee Name) and this field has around 30 thousand record.
What I want to do is to create alphabetical field and store each employee name in his related filed according to first character of employee name.
For example employee name is (Molando) will be stored in Filed (M) and (Alex) will be stored in Filed (A) and so.
I Know how to create database on runtime , but I don't know how to search the database  and read all names started with A and store them into new created db with (A) filed.
I want to use this in order to speed up my database search .
Who is Participating?
MrT-RSIConnect With a Mentor Commented:
Try the following SQL:

INSERT INTO "A.mdb" SELECT * FROM "employees.mdb" WHERE (Name LIKE "A%")

A.mdb database should already exist.

Are you sure this will work faster than a single database ? 30.000 aint that much, maybe try to optimize your search code
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.
topkahAuthor Commented:
I don't have any SQL , all what I have is AdoTable and DataSource, I want any way to do it .

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Wim ten BrinkSelf-employed developerCommented:
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...
aikimarkConnect With a Mentor Commented:
SQL example to return all names that begin with the letter "A":

Select *
From MyTable
Where NameField Like "A*"

or (depending on the database)

Select *
From MyTable
Where NameField Like "A%"

I think ADO will allow you to treat the AD data as a standard data source.  This means that you may not need to import the data (snapshot) into your database.  You might get the current AD data with every query.
topkahAuthor Commented:
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
Wim ten BrinkConnect With a Mentor Self-employed developerCommented:
> 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.

You are plain wrong about this! It will be faster if the table has less records but not that much faster. (Unless you're not using any indices...) In general, a database will use the index to do a binary search. And a binary search just splits the table in half for every comparison, disregarding one half every time. So a table

1, 2, 4, 8, 16, 32, 64, 128, 256, 512, 1024, 2048, 4096, 8192, 16384, 32768, 65536, 131072, ...
1, 2, 3, 4, 05, 06, 07, 008, 009, 010, 0011, 0012, 0013, 0014, 00015, 00016, 00017, 000018, ...

If your table has 8 records, (4th in list) the search would execute 4 comparisons to find the record. If it has 128 records, (8th item) it needs 8 comparisons. A table of about 3000 records would require 13 comparisons while a table of 30000 records only takes 16 comparisons. And comparisons can be done very fast...
Now, you could argue that you have to make 3 comparisons less but you forget one thing here... You will still have to determine in which table to look for a certain name. Thus, you first have to do a comparison on the first character and then do a search in the proper table for the name...

Splitting a table into multiple smaller tables is, in general, plain useless since the additional overhead you create nullifies the effect of the speed increase in general.

Ever played a game higher-lower? In this game one person picks a number between 1 and 100 and the other tries to guess it.The first player then tells, "higher", "lower" or "You guessed it". This should take no more than 7 or 8 guesses if you use the binary "guess" method... :)
topkahAuthor Commented:

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 .
  while x[i]<>'@' do begin

  with ADOTable1 do begin
      if not AdoTable1.Locate(Index,s, []) then begin

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.

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

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.
BlackTigerXConnect With a Mentor Commented:
in SQL you can create your query in a string, then execute the string using the EXECUTE statement, not that is recommended in most cases, but is possible

DECLARE @SQLString varchar(4000)
set @SQLString = 'select blablabla from SomeTable'

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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.