AymanFarhat wroteIt could be due to poorly written queries in your application. Lack of indexes(indexes on some fields hugely affects the speed of retrieval of records).Not using primary keys etc...
Ayman has nailed it right there. 128,000 records is way too little to start thinking about hardware upgrades. At work, we're working on a project that is supposed to start with 2 million records (migrated from the client's old data store) and is expected to have around 10 million records in the upcoming 2 years. We had a query that was taking around 1.5 hours the first time we wrote the query. Then once we've added the proper indexes and did some optimization, the time dropped to around 37 seconds.
AymanFarhat wrotealways make sure your relational database design conforms to the 3rd normal form
This is very important. But one thing to note is that there are certain tables that are actually nothing more than "look-up tables", i.e: they are only used to store static data that will most likely never change, such as gender, nationality, status...etc. I suggest you keep those in a proper non-relational table structure. We usually have 3 tables to cater for this scenario: ListName, ListValues, and LanguageDescriptions. The reason why we do this is to avoid having joins whose only purpose is to display data (such as male, active...etc). Accordingly, what we do is that we get all the look-ups on application start and cache it. And we have some helper class that takes the ListValueId or ListValueCode (ex: Gender_Male) and returns the corresponding localized value (Male).
