Click here to Skip to main content
15,030,781 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have two tables :
1. D_EMPLOYEE -- Primary key (Employee ID)
2. D_EMPLOYEE_DETAIL -- Foreign key (Employee ID)


D_EMPLYEE contains the employee name and D_EMPLOYEE_DETAIL contains the details of a perticular employee.


I want to perform a search for a perticular employee by providing his name and retrive the employee code that is stored in D_EMPLOYEE_DETAIL table.


This is a simple query but the problem is that the database size is extremely huge and name column has no index due to which it takes larger time than usual.


Please help me any alternative to search from such a huge database in fraction of sec?
Posted
Updated 8-Dec-11 17:29pm
v2
Comments
FastEvo8 8-Dec-11 13:56pm
   
It is very difficult to troubleshoot your performance issue with the limited info you are supplying.
What do you mean by "Huge table"...do you have a 1 billion records?
Indexes are meant to speed up searches, if you do no have one you might want to consider to add it.
What is the query you are running?
Please try to provide more details.
Be Yourself 8-Dec-11 21:47pm
   
Not 1 billion but 30 - 40 thousands of records because of which it takes 3-4 min for search but I want the result in 10 - 20 seconds.
The query is quite simple as you might know.

SELECT ED.EMP_CODE
FROM D_EMPLOYEE E, D_EMPLOYEE_DETAIL ED
WHERE E.EMP_ID = ED.EMP_ID
AND E.EMP_NAME = <<name>>

Can u suggest me what to do?
FastEvo8 8-Dec-11 23:08pm
   
I am surprised to hear that it takes 3-4 minutes to retrieve one record with only 30-40k records. I am starting to think that you might have other issues on your server that are slowing down your query.
Why don't you create the index for the column name?
Did you check the query plan?
Are the data types of the emp_ID fields the same?
Do you have a one to one or a one to many relationship?

Read this my friend.
http://en.wikipedia.org/wiki/Pagination[^]

http://en.wikipedia.org/wiki/Common_table_expression[^]

Just a general idea :)

Regards,
Eduard
   
Use the primary-foreign key relationship between the two tables as the binder. Those are always indexed.
   
Comments
FastEvo8 8-Dec-11 17:01pm
   
I do not agree.
the FK relationship is used for keeping the referential integrity in the database and has nothing to do with indexing. As a matter of fact it may slow down inserts since the DB engine will check if the new values exists in the parent table (check the query plan and you will see it),
Be Yourself 8-Dec-11 21:38pm
   
I agree to you but you cannot index a name column by making it primary key.
and here I have to select the employee code by taking only name as input.
fjdiewornncalwe 8-Dec-11 17:44pm
   
My point was that keys are usually indexed. We are talking about selects anyway, and not inserts or updates. But you are absolutely correct in what you say.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900