performance : table with 6 million rows

hi guys

I have a situtation where i need to improve the sql performance
My sql is

select A.ID,A.Date,B.Description,C.PID
from EDetail A
INNER JOIN
EMand B
ON A.MainID =  B.StaffID
INNER JOIN People_Detail C
ON C.PID = A.MainID


EDetail has 100,000 rows
EMand has 6 million rows
People_Detail has 50000 rows,

Its taking for ever (> 20 minutes and still running) to execute the sql although i have indexes on A.MainID and B.StaffID.

Any ideas how i can improve the performance?
Any help would be really apprecaited
Thanks
royjaydAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<this isn't my strong suit, but since no one else has replied I'll give it a whack>

If you're going the indexing route, you'll need indexes on these three tables that cover not only the JOIN columns, but all columns in the SELECT clause as well.  (aka a covering index).

Are the JOIN columns related with a foreign key?
What's the data type of the JOIN columns, and can it be made smaller?

Do you need to execute this SQL on the entire table(s), or can a WHERE clause be inserted to limit the rows returned?

what's the purpose of the JOIN on People_Detail c, if you're selecting c.PID, which is the same as A.MainID?

Are you certain that the JOIN columns are correct, meaning that the tables are joined by only one column and not multiple columns, where if you execute SQL joining only one column it would return duplicate rows?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
virtuadeptCommented:
Can you post the table schemas for all 3 tables in the join including index definitions?

How many rows are there for each unique EMand.StaffID?

is StaffID the primary key of EMand and/or the clustered index?  

If not, what is the primary key?
0
Scott PletcherSenior DBACommented:
Agree with virtuadept.  In particular, are A.MainID and B.StaffID exactly the same datatype?  Int, bigint, whatever.

Also, would you post the estimated query plan -- actual is much better but estimated is OK since the query takes so long :-).
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

royjaydAuthor Commented:
thanks for all the replies. This is what i find:

select A.ID,A.Date,B.Description,C.PID
from EDetail A
INNER JOIN
EMand B
ON A.MainID =  B.StaffID
INNER JOIN People_Detail C
ON C.PID = A.MainID
GROUP BY
A.ID,A.Date,B.Description,C.PID

is very fast and executes less than 30 seconds.
Now i am curious what magic did GROUP BY do here.
0
Scott PletcherSenior DBACommented:
Reduced the number of rows.  GROUP BY would produce only one row per unique value, whereas a normal SELECT could output millions (potentially) of rows with exactly the same values.
0
royjaydAuthor Commented:
AAh, got it , so GROUP BY eliminated the duplicates rows here, correct ?

Thx
0
virtuadeptCommented:
Yes, also group by could have changed the way the optimizer tried to use indexes which could also have improved performance.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Yes, and if you would like some leisure reading on that, check out my article on SQL Server GROUP BY Solutions, and hit the 'Yes' button if you liked it.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.