Same data for different key in table takes lots of time

Question

I have a table...

FactorID, col1 col2     Factor Value
1         a     2        1231
1         b     3        2342
2         a     2        1233
2         b     3        2344

That mean ... For factor Id 1, I have 1500 records...and factor Id 2 I have same number of records.

For Factor Ids 1 &2 every values are same except "Factor Value".

When I join above table with some other tables to fetch data... FactorID 1 gives me quick results...but when I query for "2" ...the execution never ends.

This seems to be weird eror to me. Is it something related to row-lock ? Please helpme.

score 1 · Accepted Answer · answered Mar 25 '11 at 00:48

1

It could be that the join is using a table scan. Check whether FactorID has got an index. The results you are seeing might be because FactorID 1 records are first in the table and when it comes to FactorID 2, it goes on skipping records for FactorID1 one by one until it comes to FactorID 2.

answered Mar 25 '11 at 00:48

Rasika

1,980
13
19

how to avoid table scan ?. I dont have index on "FactorID". I have index on combination of "FactorId/Col1/Col2". – Relativity Mar 25 '11 at 00:56
Just create an index on FactorID coulum and it will be picked by the DB engine. – Rasika Mar 25 '11 at 00:58
FacorId is non unique..So, is it okay to create an index in that ? – Relativity Mar 25 '11 at 01:01

score 0 · Answer 2 · answered Mar 25 '11 at 01:09

When I join above table with some other tables to fetch data... FactorID 1 gives me quick results... but when I query for "2" ...the execution never ends.

Wow, that's saying very little.

The answer depends on three things.

How are your tables defined?
- Do you have primary keys? Which columns are keys? If there are only two "FactorID" values in the table shown above, that would be a poor choice for a primary key.
- Are any other columns indexed?
- Do you actually have columns named "col1" and "col2"? Or are you just saying, "the names don't matter"?
It matters how the OTHER tables are defined too. Do they have any keys?
What type of data is in the tables? How many rows?
The most important thing: What do you mean by "when I join table with some other tables"? How many tables are being joined? How are you doing the joins?

Plus, you say that your query "never ends" - how long did you wait?

Sorry, but we need a LOT more information before a meaningful answer can be given.

Same data for different key in table takes lots of time

2 Answers2