Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


searching Database

Posted on 2004-08-25
Medium Priority
Last Modified: 2010-04-05
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 .
Question by:topkah
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 2
  • +2

Accepted Solution

MrT-RSI earned 500 total points
ID: 11897640
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
LVL 46

Expert Comment

ID: 11898038
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.

Author Comment

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 17

Expert Comment

by:Wim ten Brink
ID: 11900664
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...
LVL 46

Assisted Solution

aikimark earned 500 total points
ID: 11901520
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.

Author Comment

ID: 11901598
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
LVL 17

Assisted Solution

by:Wim ten Brink
Wim ten Brink earned 500 total points
ID: 11902485
> 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... :)

Author Comment

ID: 11903013

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)

LVL 46

Expert Comment

ID: 11903018
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.
LVL 46

Expert Comment

ID: 11903174

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.
LVL 13

Expert Comment

ID: 11914600
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
LVL 46

Expert Comment

ID: 11915117

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.
LVL 13

Assisted Solution

BlackTigerX earned 500 total points
ID: 11917712
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'
LVL 46

Expert Comment

ID: 11918632

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.

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question