Solved

searching Database

Posted on 2004-08-25
17
154 Views
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 .
0
Comment
Question by:topkah
  • 6
  • 3
  • 2
  • +2
17 Comments
 
LVL 2

Accepted Solution

by:
MrT-RSI earned 125 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
0
 
LVL 45

Expert Comment

by:aikimark
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.
0
 

Author Comment

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

0
 
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...
0
 
LVL 45

Assisted Solution

by:aikimark
aikimark earned 125 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.
0
 

Author Comment

by:topkah
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
0
 
LVL 17

Assisted Solution

by:Wim ten Brink
Wim ten Brink earned 125 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... :)
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:topkah
ID: 11903013
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)


 
0
 
LVL 45

Expert Comment

by:aikimark
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.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 11903174
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.
0
 
LVL 13

Expert Comment

by:BlackTigerX
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
0
 
LVL 45

Expert Comment

by:aikimark
ID: 11915117
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.
0
 
LVL 13

Assisted Solution

by:BlackTigerX
BlackTigerX earned 125 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'
EXECUTE(@SQLString)
0
 
LVL 45

Expert Comment

by:aikimark
ID: 11918632
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.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

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…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now