Click here to Skip to main content
15,175,838 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
HI,

I am using c#, backend is Mysql db.

I have 3 tables in db, 1st table has 1.5 lakh records, 2nd table has 1.75 lakh records and 3rd table has 21 lakh records. i need the output using above 3 tables, so i joined above 3 table using inner join and i got the output with 1.5 lakh records, but it takes 8 hours of time.

I used primary keys in joining tables, can anyone suggest me to increase the performance.

Thanks!

What I have tried:

select Col1, col2,...coln from table1
JOIN table2 on table1.Id = table2.Id
JOIN table3 on table1.Id = table3.Id
Posted
Updated 25-Aug-17 12:39pm
Comments
Richard MacCutchan 25-Aug-17 9:33am
   
What do you plan to do with 1.5 lakh (150,000) records? Do you think any user is going to be interested in reading through them all? Use proper selection criteria and paging to make your application usable.
sampath1750 25-Aug-17 10:14am
   
We need to display the result set, so we stored this result set in another table and displaying the result set.
Richard MacCutchan 25-Aug-17 10:25am
   
No one needs to display that many records in one go. You need to take a serious look at your design.
[no name] 25-Aug-17 17:58pm
   
Where it was mentioned it has to be displayed? Anyway such a case (select 150k records) can easy occure while make some data mining...
David_Wimbley 25-Aug-17 19:55pm
   
OP clearly says "We need to display the result set" in the comment Richard replied to.
Richard MacCutchan 26-Aug-17 3:38am
   
Read the question.
[no name] 25-Aug-17 17:54pm
   
I don't think 150K records shoud be a Problem (as Long you don't present them to the user). What I learned with MSSQL, a foreign key constraint does not automatically defines an index (e.g. interbase does it Auto). So check first wheter the foreign constraints are indexed ;)

1 solution

I agree with Richard, your first issue with this is your design. There is no requirement that you can have the you would absolutely need to show all 150,000 records all at once.

But lets assume you are determined that it is 150k records or bust.

My first comment would be to look into paginating your data, i think mysql has the concept of LIMIT (I am rusty on my mysql) but would allow you to do something like SELECT * FROM <Table> LIMIT 5,10 which would let you get 5 records starting with the 5th record. I will tell you, none of your users are going to appreciate having to look at 150k records to try and find specifics of what they want. You are better to filter this data to the relevant result set than try and show it all at once, your users will hate you if they have to manually filter that data on their own.

Then you need to look at your servers hardware. 150k records in 8 hours is ridiculous. I can run a similar query in sql server (granted in different db platform) and get 150k records with a three table join in a few seconds. 8 hours for 150k records, are you running your DB on a single core with 500mb of RAM? So look into your hardware problems

Look into what you are querying. 8 hours for that many records, you must be pulling out 10,000 columns or something...are you doing some crazy aggregation that you can maybe offload by storing the data in the required aggregated manner to begin with? For example, If you always add 4 columns to create a total, why not just add a fifth column called "Total" and store that total there so your not having to do the math every time you query it out.

To go along with the last comment, you need to look at your query as well to see what, within your query, can be tuned. Are you doing sub queries unnecessarily? Could you utilize temp tables for some of your joins? Could some of your query that you are doing be unnecessary if you were to add more columns to your schema? I think mysql has the keyword called EXPLAIN that you can use in order to have your query profiled as it runs so you can analyze what is going on.

And I suppose finally, if you want help you need to post a sample schema and your queries so those that want to, can try and replicate your issue your having. Without that all we can do is guess and point you in directions of what you could possibly look at but without that...all you've done is gone to a doctor, tell them your sick, and expect them to diagnose you without having examined you.
   
v2
Comments
[no name] 25-Aug-17 17:56pm
   
Why 150K records should be a Problem? Where OP mentioned it is for GUI? See my comments to the question. No vote from my side.
David_Wimbley 25-Aug-17 19:53pm
   
OP is querying out 150k records and saying it takes 8 hours and only mentions "output".

Where do you think it is or isn't for GUI? It has to go somewhere, whether it is to a file or to a GUI, 8 hours to get 150k records from DB is crazy.

Not lobbying for a vote, just correcting the misconception that my answer only addresses GUI performance. My points to his question address issues with hardware, query performance (explain, asking for schema/query) and options for rendering data in more manageable format whether its to csv/flat file/GUI.
Richard MacCutchan 26-Aug-17 3:40am
   
And table3 is 2,100,000 records which are to be joined to the other two, so I suspect that may affect the timing.

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