0

I've been asked to look into a performance issue for an app that is becoming progressively slower. Pretty quickly, I was able to narrow down the problem to a single database table. Poorly structured C# code I'm ok at optimizing. But with SQL tables, I'm less confident. So I'm here hoping for some help!

The table in question stores multi-lingual translations for certain keywords used across the app. It's a growth table. And as it grows, the performance on basic SELECTs and JOINs is starting to degrade sharply. Right now there are just over 1 million records in the table, which isn't really all that much.

For example:

SELECT * FROM PE_TranslationPhrase WHERE Phrase = 'ABC-123'

That can take anywhere from 8 to 32 seconds to complete.

The table is hosted on Azure SQL. Here's a look at it in SSMS:

enter image description here

So it's not that complex of a table. The Primary Key structure isn't just your normal auto-incremented integer. TranslationId and CultureName together make up the primary key (which is fine).

When dealing with performance issues, the first place to look, of course, is the indexes. This is what's on the table now:

CLUSTERED:
 - [TranslationId] ASC,
 - [CultureName] ASC

STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF

And

NON-CLUSTERED:
 - [CultureName] ASC,
 - INCLUDE ([Phrase])

STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF

The reason for the NON-CLUSTERED index with Phrase and CultureName is because those columns are used all the time for filters and joins. Example:

LEFT JOIN 
    PE_TranslationPhrase TP 
    ON A.Description COLLATE Latin1_General_CS_AS = TP.Phrase COLLATE Latin1_General_CS_AS 
    AND A.CULTURENAME = TP.CultureName

WHAT I'VE TRIED, AND QUESTIONS:

I tried to rebuild the indexes:

ALTER INDEX ALL ON dbo.PE_TranslationPhrase REBUILD

That didn't seem to have a measurable impact on performance.

My question is: Is it bad to have CultureName as part of both indexes? How could I / should I change these indexes?

Thanks!!

Casey Crookston
  • 13,016
  • 24
  • 107
  • 193

1 Answers1

4

For this query:

SELECT * FROM PE_TranslationPhrase WHERE Phrase = 'ABC-123'

You want an index where Phrase is the first key in the index.

None of your indexes have Phrase as the first column, so the database needs to scan the entire table.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Ok, thanks Gordon! That example is just that... an example. Maybe it wasn't a very good one. I'm more concerned about the slow performance on the JOIN's that use both `CultureName` and `Phrase` – Casey Crookston Jul 10 '20 at 16:08
  • @CaseyCrookston The joins may be on both `CultureName` and `Phrase`, but what limits the left part of that join? – GSerg Jul 10 '20 at 16:09
  • 1
    Phrase is `nvarchar(max)` so you'd have to limit it to index it. If the phrases are very long, perhaps you should index PhraseHash and use that in the lookup. – David Browne - Microsoft Jul 10 '20 at 16:10
  • @GSerg, good question. The other half of the join is JSON data passed in as a paramater. Based on your earlier comment, I am looking to see if 'CultureName' is there, and I think it's not. (It's a huge json file) – Casey Crookston Jul 10 '20 at 16:11
  • @DavidBrowne-Microsoft - great catch! I'll take a look at the data in that column and see if it can be changed away from `(max)` – Casey Crookston Jul 10 '20 at 16:13
  • @GSerg, `CultureName` is in the json file: `"CultureName": "en"` – Casey Crookston Jul 10 '20 at 16:15
  • And the column's collation should be `Latin1_General_CS_AS` if you want to enable the index to be used in that join. – David Browne - Microsoft Jul 10 '20 at 16:17
  • @DavidBrowne-Microsoft, can you see my followup question? https://stackoverflow.com/questions/62838849/how-to-replicate-a-computed-hashed-value-in-a-sql-table – Casey Crookston Jul 10 '20 at 16:46