Indexing in Database: Must Do It

I work in CUSIT (City University of Science and IT) as software engineer. I and my colleague do software development, maintenance, and database development and administration. Two days before, accountant called me and complained about an error in a report generation. When I looked, it was timeout error. I checked the underlying query and apparently everything was OK. I ran the query in sqlserver explorer and it took one minute and fifteen seconds. I ran it again and it was speeded up by one second. Query optimization!

The query returned only 18 records. It had several tables inner joined. I rearranged the query and started to execute it incrementally, by joining one table at a time. And without a single table, the query executed in less than a second. The problem was in the last remaining table.

I looked in the table definition.

What? It has no primary key! It has more than 2000 records but no indexing! All but one fields are used for comparison in the join but no indexing!

I added an auto-increment ID field as primary key and indexed all the fields except the one which only had true/false value and was not used in direct comparison.

I ran the query again and now its running time was less than a second. Wow, at least 75 times performance!

I searched for some other tables and found a few others which needed primary keys and indexing. My colleague said that there were no indexing and primary keys in some other tables as well. It was a legacy from our older boss, which was considered a must. He made several mistakes which we (I and my colleague Farrukh Abbas)  corrected after his leave.

Advertisements

DataSet as a DataBase

Using a DataSet as a database and store that directly on desk. Before storage, some encryption and compression can also be made. The whole process can be optimized to reduce desk accesses and RAM usage. A DataSet can be managed to be stored in more than one file, each once containing a distinct set of tables. The tables can be grouped in files to reduce desk accesses and Ram consumption.