0

I have a simple structure of Customer, Setting and CustomerSetting.

There are around 60 settings, all using a UNIQUEIDENTIFIER as the key, since these can be shared across multiple systems and can be created in any order, so Id is not guaranteed to be the same)

Each customer could have these settings, but not necessarily any or all of them.

I'm trying to find the fastest (performance wise) way of listing all customers and all settings.

I've tried using functions, table value functions, joins, cross apply...and it's simply very slow. Just for the sample below it takes 35 seconds on my local SQL server to list the 2,000 users and their settings. In real life I have over 2.5Mn.

FN and TVF's are very quick for a single user - but once this gets to multiple rows it just grinds to a halt.

Is there a trick to writing a quick version of this?

NOTE: I am happy to have the settings GUID's hard-coded in the view as they change very infrequently.

NOTE: The data can change very frequently so an Indexed view is (I think) out.

Sample Database Tables, Indexes, View, Data and Select * I am trying to acheive

IMPORTANT - This is a sample of data to show the structure, not the complete database.

BlueChippy
  • 5,935
  • 16
  • 81
  • 131
  • Re performance: You need to add an index to `CustomerSetting` table. I suggest a composite (Id_Customer, Setting) but you should experiment. See https://www.sqlskills.com/blogs/kimberly/when-did-sql-server-stop-putting-indexes-on-foreign-key-columns/ – Alex Aug 15 '18 at 03:55
  • Have a look at this: https://stackoverflow.com/questions/1336449/eav-over-sql-server – Alex Aug 15 '18 at 04:36

1 Answers1

2

Do real structures have indexes? And instead of 20 joints, you can just PIVOT the SettingValue rows into columns

Ruslan Tolkachev
  • 634
  • 4
  • 9