Multi-target Query Assistance

Is there a certain structure to the breakdown of data in Content1 and Content2, and what is the reasoning of them being two separate tables (they appear to have the same definition)?  For example, are newer records (by CreatedDateUtc) in Content2 and older records in Content1?  Also, what was the bottleneck with performance in your attempts so far?  What do the queries look like?  Understanding this context should help with solutioning.

Perhaps there is a reasonable partitioning scheme that would sufficiently optimize the lookup?  There are some potential drawbacks, but maybe an indexed view would be worth consideration in this scenario as well.

I should have prefaced this with "This is a vendor database and we do not own the schema."  But that % would have my first question if I saw this question in the wild, itCanOnlyBeJared .

The trouble here is that given a particular inefficient table design there isn't a lot you can do via your query to make it more efficient.  
Is there currently an index on all the relevant tables for the keys and where conditions you need to be able to use?  Without indexes in the right places query performance is pretty doomed.

Is your key on all these tables effectively the combined contentid + contenttypeid?  I've seen people make a lot of progress with silly large composite primary keys by creating a composite index with a hash.  Downside of hashes is they are not good if you need to do order by or want to use range conditions in the filter, but looking at these kinds of GUID style keys I'm not sure you are going to be doing that.  If a hash is not good then I think the indexed view built out of joins mentioned by itCanOnlyBeJared  makes good sense.

If the data set you actually intend to produce is really supposed to be in the millions of rows and no combination of clever indexes and where conditions is going to be able to reduce that you also just have to be prepared for the performance to be what it is.  At that point your main bottleneck is likely to be read speeds and RAM and caching.  How slow are you talking about?  Like a minute or two or walk away and make dinner?

Yeah - speeds are bad.

• For 100 rows < 1 sec
• For 1000 rows < 8 sec
• For 10000 rows < 72 seconds
• For all rows, I'll let you know when it finishes.

Sounds like I just need to deal with this inefficiency at the moment.

My understanding (which is complete conjecture) is that the application layer "above" this database knows about the contextual connections and pivots to the correct table as necessary.

Wanted to test this out when I had fresh eyes.  Today, I ran an experiment on the LEFT vs. INNER JOINs with my data set:

• Returning 1,000,000 rows with the LEFT JOIN: 12 seconds
• Returning 1,000,000 rows with the INNER JOIN: 0 seconds, but no rows returned.

Sadly, my initial hunch was correct, and I need to use the LEFT JOIN to eliminate bad lookups.

But thanks itCanOnlyBeJared for the recommendation.

And oddly, the speed seems way up this morning.  Might be the reindexing, might be the underlying IO, might be divine intervention, but I'll take whatever improvement I can.

mesverrum said:

I've seen people make a lot of progress with silly large composite primary keys by creating a composite index with a hash.

Talk to me about how this would look.  I know composite keys, but I don't think I've ever boiled them down to a hash before.

I've done this before - it doesn't work in every situation, but can be a lifesaver when it does work.  Essentially you concatenate all of the fields in the composite key and dump it into the hashbytes function.  Picking a common datatype for all of the fields is usually the hardest part.  So for a table with a composite key of name, streetnumber, streetname, you'd end up with something like:

select hashbytes('<algorithm>', name+convert(varchar, streetnumber)+streetname) as hashkey from 

If the values for the columns of a given row were 'Big Bird', 123, 'Sesame Street' and using SHA2_256 as the algorithm, the statement would translate to 

hashbytes('SHA2_256', 'Big Bird123SesameStreet') which yields a value of 0x737CFF2ABACBD32B5C2244561DB735AC29EFB623E053983E84F6D8DEA7FE589D

Comparing hex values should nearly always be faster than comparing strings outright; the big question is whether the conversions to the common datatype and computing the hashes offset the gains or not.

This article does a decent job talking about one approach: Multiple-Column Indexes and Hashing: The Ultimate Guide to Boosting Database Performance | by LordNeic | Medium

When you tested the INNER JOINs, did you update the query to use the UNIONs as well (between the different selects for each target table)?  Substituting INNER for LEFT joins in the original query would have a much different effect.

Does anyone know (simply for curiosity) how the comparisons of hash bytes would compare to unique identifiers?

I'd assume hashes vs. GUIDs equality comparisons would be similarly performant because neither are technically strings (varchar/nvarchar/text/ntext).

Or am I totally off base with this assumption about them not being a primitive string type?

Great question!  I honestly don't know the answer here.  Your assumption is intuitive and feels correct - both are technically binary values.  Assuming they are both treated as binary for the purposes of a join, a GUID may actually be slightly faster than hashbytes depending on the output length of the hashbytes function.  I'm hesitant to commit to that stance though because I know that GUIDs are considered strings when doing comparisons against character strings; usage in the where clause for instance always hits performance hard.  For a straight join between GUIDs?  Not sure.