Sql optimization Techniques

Question

I want to know optimization techniques for databases that has nearly 80,000 records, list of possibilities for optimizing

i am using for my mobile project in android platform i use sqlite,i takes lot of time to retreive the data

Thanks

Answer 1

• Create indexes

• Delete indexes

• Normalize

• DeNormalize

Answer 2

80k rows isn't many rows these days. Clever index(es) with queries that utlise these indexes will serve you right.

Answer 3

Learn how to display query execution maps, then learn to understand what they mean, then optimize your indices, tables, queries accordingly.

Answer 4

Well, with only 80,000 records and assuming your database is well designed and normalized, just adding indexes on the columns that you frequently use in your WHERE or ORDER BY clauses should be sufficient.

There are other more sophisticated techniques you can use (such as denormalizing certain tables, partitioning, etc.) but those normally only start to come into play when you have millions of records to deal with.

ETA:

I see you updated the question to mention that this is on a mobile platform - that could change things a bit.

Assuming you can't pare down the data set at all, one thing you might be able to do would be to try to partition the database a bit. The idea here is to take your one large table and split it into several smaller identical tables that each hold a subset of the data.

Which of those tables a given row would go into would depend on how you choose to partition it. For example, if you had a "customer_id" field that could range from 0 to 10,000 you might put customers 0 - 2500 in table1, 2,500 - 5,000 in table2, etc. splitting the one large table into 4 smaller ones. You would then have logic in your app that would figure out which table (or tables) to query to retrieve a given record.

You would want to partition your data in such a way that you generally only need to query one of the partitions at a time. Exactly how you would partition the data would depend on what fields you have and how you are using them, but the general idea is the same.

Answer 5

You really have to be more specific with respect to what you want to do. What is your mix of operations? What is your table structure? The generic advice is to use indices as appropriate but you aren't going to get much help with such a generic question.

Also, 80,000 records is nothing. It is a moderate-sized table and should not make any decent database break a sweat.

Answer 6

First of all, indexes are really a necessity if you want a well-performing database.

Besides that, though, the techniques depend on what you need to optimize for: Size, speed, memory, etc?

Answer 7

Keep the database in RAM.

Answer 8

Such a wide topic, which does depend on what you want to optimise for. But the basics:

• indexes. A good indexing strategy is important, indexing the right columns that are frequently queried on/ordered by is important. However, the more indexes you add, the slower your INSERTs and UPDATEs will be so there is a trade-off.
• maintenance. Keep indexes defragged and statistics up to date
• optimised queries. Identify queries that are slow (using profiler/built-in information available from SQL 2005 onwards) and see if they could be written more efficiently (e.g. avoid CURSORs, used set-based operations where possible
• parameterisation/SPs. Use parameterised SQL to query the db instead of adhoc SQL with hardcoded search values. This will allow better execution plan caching and reuse.
• start with a normalised database schema, and then de-normalise if appropriate to improve performance

80,000 records is not much so I'll stop there (large dbs, with millions of data rows, I'd have suggested partitioning the data)

Answer 9

One thing that is worth knowing is that using a function in the where statement on the indexed field will cause the index not to be used.

Example (Oracle):

SELECT indexed_text FROM your_table WHERE upper(indexed_text) = 'UPPERCASE TEXT';