2024-08-02 - How Much Effect Can a Database Index Have - Chewing the Fat 📈

[bradystroud]

Any success stories with database indexes?
Any stories of database indexes making things worse?

https://ssw.com.au/rules/sql-real-world-indexes

[Freego1783]

Not really a DB guy so I don't have any good stories 😬

[PothieuG]

Great video Bryden !

I don't have much experience with large databases, but in the instances where I did, everything went smoothly

[matt-goldman-ssw]

You think Sentinel performance is bad now? You should have seen the first version where I made the primary key a concatenation of various other fields rather than just using a composite key 🤦

[TobyChurches]

Great Video @brydeno !!

I was working on fixing a query in TimePro relating to iterations. It would also look at the Timesheets related to get the latest timesheet (i.e. when it was last used). This was fixed with the help of @jernejk and @brydeno, and had a massive performance impact. It reduced the logical reads by a factor of 100,000!! (yes - one hundred thousand)

[leahy268]

Great Video @brydeno

I guess I kind of understood how indexes worked already, but that was such a simple analogy of how they work. Love it..

Any success stories with database indexes?

No, not really created any large DB's before myself. Have used some badly performing databases but didn't really have any control over them.

Any stories of database indexes making things worse?

No, I wouldn't have had that kind of control.

[MarcusIrmscherSSW]

I don't have any experience with database indexes, but I imagine it would help a lot with scalability.

[GabbyWard]

An interesting read and video @brydeno 📔

I haven't worked on any DBs yet, but I definitely know some prior clients from my Wood days that should have used something like this to shorten their wait times for data exports.

[jakebayliss]

Unique indexes have stopped me accidentally inserting duplicated data, so thats another great thing about them :)

[tkapa]

There's always a story about how XYZ Index shaved 0.2 seconds off of a query, making a process that previously took 2 hours, take 10 minutes.

Indexing is obviously a powerful tool, but it seems to have either fallen out of style, or isn't taught much anymore these days.

[ricksu978]

I have been using .NET's Guid.NewGuid() as a value for primary key in the past.
This has caused tremendous index fragmentation which crippled the database performance.

Alternate solution is - let EF Core generate the Guid value

[ncn-ssw]

SugarLearning was having issues with the leaderboard database query timing out at the end of last year. Since @jernejk and @Dhruv-0987 improved the indexes in the database, we have seen no further issues.

[Ben0189]

Great video, I havent really worked much on database index

[steven0x51]

Great video, good analogy of cook book, that explained database index that eveyone would understand.
I haven't worked much on databse project yet.

[joshbermanssw]

Have never worked too much database side, nor much thought onto applying indexes to one.

[edkranz]

My understanding of indexes likens it to graphics pre-processing — utilising the fact that we typically have lots of storage, and are limited by time/network speeds. Indexes let you shave down time complexity at the cost of space complexity, a typically worthwhile tradeoff.

[jerryluossw]

Any success stories with database indexes?

Have reduced the query time of a complex query from 64s to 500ms through indexes.

Any stories of database indexes making things worse?

After adding the indexes, the query time in the Staging reduced to 100ms. However, in the Prod, the query time increased instead. The reason is that the query optimizer in the Prod made a different decision compared to the Staging, which is very strange.

[AnkyChe]

I'm not a dev, but I find the video easy to understand!

[tino-liu]

I don't have any success stories with database. But I am curious on how the drawbacks are for using indexing.. One thing I read is that it could potentially increase the disk space required.

[babakamyljanovssw]

As @ncn-ssw and @Dhruv-0987 mentioned, SugarLearning had issue with leaderboard database query.

[KristenHu]

I don't have much experience with DB so it's hard to provide any stories.

[bradystroud]

They seem simpler than expected.
Interested in learning the code first approach to indexing.

I wonder if the concept is applicable to storing content in markdown.

[danielmackay]

Any success stories with database indexes?
Not really.

Any stories of database indexes making things worse?
Not really.

[PennyWalker]

Reading the above dev responses I've learned a lot from other people's experience. Was great to see Toby's fix on TimePro :)

[uly1]

Well done Byrdyen... super clear explanaton!

[vladislav-kir]

Great video by Bryden!

I have not previously created database indexes myself, but saw in action how they majorly improved the performance of the database queries.

[Hona]

Thanks Bryden - I've been watching Brent Ozar's Think like the Engine series slowly... :)

[stephanfakossw]

[https://outlook-1.cdn.office.net/assets/reaction/heart.png] Stephan Fako [SSW] reacted to your message: [cid:ssw-circle(1)_4c7259a6-23f6-49e5-b933-230ca304562e.png] Stephan Fako SSW Senior Accountant Stalk: SSW People<https://www.ssw.com.au/people/Stephan-Fako?utm_source=emailsignature> | Walk: SSW Brisbane<https://www.ssw.com.au/ssw/Company/Offices/?utm_source=emailsignature> | Talk: +61 451 901 807<https://www.ssw.com.au/ssw/Company/ContactUs.aspx> [cid:youtube-icon_b8b5cda9-a973-4973-8763-cf3d4922e060.png] <https://www.youtube.com/c/SSWTV> [cid:linkedin-icon_b153ac9a-eb66-47d9-9c51-ad20b42b8a0a.png] <https://www.linkedin.com/company/ssw/> [cid:x-logo_3eb6d0d3-24fb-487d-a4a7-2e30cddc1ebf.png] <https://x.com/SSW_TV> [cid:teams-icon_552803c2-9c44-4267-b563-7fe1a7a0e589.png] ***@***.***> TinaCMS, the #1 CMS on GitHub, is now part of the SSW family<https://adamcogan.com/2024/05/15/ssw-purchases-tinacms?utm_source=emailsignature>

…

________________________________ From: Luke Parker ***@***.***> Sent: Thursday, August 8, 2024 5:00:59 AM To: SSWConsulting/SSW.Rules.Content ***@***.***> Cc: Stephan Fako [SSW] ***@***.***>; Comment ***@***.***> Subject: Re: [SSWConsulting/SSW.Rules.Content] 2024-08-02 - How Much Effect Can a Database Index Have - Chewing the Fat &#128200; (Discussion #8960) Thanks Bryden - I've been watching Brent Ozar's Think like the Engine series slowly... :) — Reply to this email directly, view it on GitHub<#8960 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AW6RWDQG3ZNCMRPP6PXI4YTZQL3QXAVCNFSM6AAAAABLYM3IT6VHI2DSMVQWIX3LMV43URDJONRXK43TNFXW4Q3PNVWWK3TUHMYTAMRXGEYDMNQ>. You are receiving this because you commented.Message ID: ***@***.***>

[ozair-ashfaque]

Thanks, @brydeno for the video. Great explanation

No success or bad stories yet

[jernejk]

Great video @brydeno! While I haven't yet the opportunity to vastly improve performance with indexes yet (it turned out that indexes can't fix exceptionally bad queries), I'm looking forward to a success story using indexes. :)

Last year, I started to add indexes to my project in addition to optimising my queries and I rarely have cases where I need to do any further optimization unless the data scale is truly massive (100k+).

Generic tips for others for DBs in general:

• Never use lazy loading, it will backfire when you least expect it
• Select only the column you need
• Use pagination on the API side (doing it only in the browser doesn't count!)
• Relational DBs: Reduce the amount of join you do in queries
• Cache or fetch separately small data that repeat often (for instance, categories, languages, etc.)
  • For example, instead of fetching 500 books + category names, you can fetch 500 books, create a list of unique category IDs used by 500 books and fetch them separately. Query splitting can sometimes 100x speed up when the amount of data requested increases.
• Changing the table you start in FROM section may result in better performance/easier query
  • For instance, fetching all books from an author:
    • Most would do SELECT b.Id [BookId], a.Name [AuthorName], b.Title [BookTitle] FROM Authors a JOIN Books b ON a.Id = b.AuthorId WHERE a.Id = @AuthorId
    • Potentially better query SELECT b.Id [BookId], a.Name [AuthorName], b.Title [BookTitle] FROM Books b JOIN Author a ON a.Id = b.AuthorId WHERE b.AuthorId = @AuthorId
    • EF Core tip: You can use AsSplitQuery() to fetch author separately and potentially improve performance, even more, when there are a lot of books

EF Core specific ones:

• Use TagWith or if available TagWithContext for your queries, it will add that content as a comment in SQL query for easier debugging
• Use interceptors for entity auditing over manually setting CreatedOn, CreatedBy, etc.
• When you need a lot of joins or duplicate data (like a category for the book), try using AsSplitQuery()
• EF Core is a lot more powerful than most people believe/know

[SamBWagner]

Nup. Not really a database doooood. I did relational DB courses in uni but that was eons ago.