Optimizing database search.

Question

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 8-Dec-11 6:57am

Be Yourself

Updated 8-Dec-11 17:29pm

Eduard Lu

v2

Add a Solution

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?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Eduard Lu · Accepted Answer · 2011-12-08T17:30:00

Solution 2

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

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

Just a general idea :)

Regards,
Eduard

Posted 8-Dec-11 17:30pm

Eduard Lu

fjdiewornncalwe · Accepted Answer · 2011-12-08T08:14:00

Solution 1

Use the primary-foreign key relationship between the two tables as the binder. Those are always indexed.

Posted 8-Dec-11 8:14am

fjdiewornncalwe

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.