How to speed up data retrieval from Mysql database

Question

I am working with c# windows form application and using mysql as back end. I have a table employee. In that I have more 2000 rows its just a example likely more record. When I'm retrieving and filtering the data from database my application is getting slow. Is there another way to retrieve database in good speed? Shall I achieve the speed by using Linq?

Answer 1

add index to your fields participating in JOINS and WHERE clauses.

Answer 2

You can get a speed up by using lazy loading (there are several tutorials over the internet) or just select those ones that interest you.

Answer 3

2000 records is a relatively small table. What is the size of each row?

Retrieving / Filtering from such a small table should be very fast atleast at a DB level.

• View the Query Plan of your query and Optimize your query based on it.

• Especially check your indexes and see how they are being used in your query and if you need to add / change any for them to be optimally used.

If you post your query and its execution plan, people might be able to point out some specifics as to how you may be able to improve on it!

Answer 4

How do you retrieve data now? are you using ado.net? but Stored procedures with ado.net is the fastest way I guess, are you sure that data retrieve is the slowest,2000 records is not so big table, so I guess your UI components are working slower?

Answer 5

Just make sure that you perform filtering in the database using WHERE (if you are using SQL for data access).

If you are using linq or not does not matter much as long as the SQL that is executed in the database is good (proper filtering using WHERE and indexes).