Watch Those Indexes

Kennie Nybo Pontoppidan explains why that one university registration system was always throwing errors:

Remember that myITU was implemented on an Oracle database. Unlike on SQL Server, all tables in an Oracle database are physically represented as heaps, unless explicitly specified otherwise. That means no indexes. And I didn’t know anything about database performance back then, so I didn’t add any. Effectively any query against the course table would give a full table scans in the EnumerationType and EnumerationValue tables as a side effect. Fast forward to course enrolments…

At ITU, we had implemented a selection algorithm, which considered both the study program you were enrolled into as well as how early you registered for a course. Early birds got the course, so students would be ready when course enrolment was opened at 12pm. And at 12.05 myITU would start failing with 500 Internal Server Errors.

This is a pretty common occurrence, followed up by the “let’s add all the indexes” phase.

Related Posts

T-SQL Tuesday 117 Roundup

Steve Jones has the roundup for this month’s T-SQL Tuesday: The summary from my fourth T-SQL Tuesday hosting for #117. This time I was scrambling a bit, but since I’ve worked with a few customers in the last year that use MOT tables, I thought this might be a good topic. Either everyone is on vacation […]

Read More

When Adding Indexes Hurts Performance

Jeffry Schwartz takes us through an odd case: Recently, a customer requested that we tune a query that took 13 seconds to return 11 rows.  SQL Server 2017 suggested an index to improve performance, so we added it in a development environment.  The improvement made the query run 647 seconds, almost 50 TIMES longer than the original!  This naturally caused […]

Read More

Categories

July 2017
MTWTFSS
« Jun Aug »
 12
3456789
10111213141516
17181920212223
24252627282930
31