Table data storage and retrieval optimization

Hello Everybody,

I have a small tricky problem here...need your help.

Let me explain in detail. I have three tables

1. Emp Table: Columns-> EMPID and DeptID
2. Dept Table: Columns-> DeptName and DeptID
3. Team table : Columns -> Date, EmpID1, EmpID2, DeptNo.

There is a stored procedure which runs every day, and for "EVERY" deptID that exists in the dept table, selects two employee from emp table and puts them in the team table. Now assuming that there are several thousands of departments in the dept table, the amount of data entered in Team table is tremendous every day.
If I continue to run the stored proc for 1 month, the team table will have lots of rows in it.

The real problem is when I want to retrive data for a employee(empid1 or empid2) from Team table and view the related details like date, deptno and empid1 to empid2 from emp table.

How do we optimise the data retrieval and storage for the table Team. Please help me to optimize the query and data retrieval time from Team table.


Thanks,
Ganesh

I think you got everything wrong (as far as I can see from your description of course, your problem might be more complicated).
Anyway, a single query solution to get two random employees for each department is

Consider that I had to put the long UNION ALL chain to build an equivalent of your emp table, you won't need this.
Then you'll be able to join the result of this query with any table you want.

You posted the same question in the MsSQL forum, what's the good one?

Starting from your schema and accepting that all you are interested in is optimizing queries by employee on the Team Table.

You need an index on the empId1 column and one on the empId2 column
(you imply that the query might be based upon either column).

If you want the results ordered by date and there will be a lot of records in the result set consider adding the date column to each index.
Make sure the statistics are kept up to date.

You might also want to consider adding a surrogate PK column to the table.

Clive

Clive and Pabloj - I really appreciate you helping me out and for the solutions. I think I need to reframe my problem.

Consider this table with columns "Date Emp1 Emp2"
Date Emp1 Emp2
1-feb 1 2
2-feb 3 4
3-feb 6 1
4-feb 1 2
5-feb 1 5
6-Feb 5 7


Now, I want search for matches to employee with empid 1 in a way that if column emp1 contains 1 I want the data in column emp2, and if column emp2 contains 1, then I want data in column emp1.

The output has to be a single column with no duplicate values. In the above example, for employee id 1, the output would be, 2,5 and 6 only.

I have to scan both columns for a given employee id. Assuming this table has lakhs of records, what will be the most optimized way to retrieve the data faster. Also, do I need to restructure this table for faster data retrieval?
I have indexes on emp1 and emp2 columns.

Thanks again..looking for a reply .