February 17, 2018 at 3:55 pm
Dear All
I have a requirement as below. May I seek your views choosing between Lookup vs Join options?
1) I have 2 business tables to JOIN every day, each of which has volumes in the range of 1+ million rows
2) End result is to populate a table with 10 columns, most of which are inter-dependent. For instance, 8th column depends on 7th columns value and so on. Since columns are not persistent in constructing a record, the code needs to be repeated.
3) There are 5 lookup tables (reference purpose) to be used to construct the columns. Each of these reference tables has not more than couple of hundred records.
Question:
OPTION-1
Is it a better option to use these reference tables on-the-fly for lookup purpose while constructing columns or use them in LEFT OUTER JOIN along with 2 heavy-weight business tables.
Benefit: With this approach we are NOT creating temp tables.
Disadvantage: Bit repetitive code in SELECT criteria.
Option-2
LEFT OUTER JOIN of each of these (though small) reference tables.
Benefit: SELECT criteria becomes tidier and slimmer.
Disadvantage: Each LoJ results in additional 1+ million volume temp table. Not only from storage standpoint, searching these million plus temp tables is time-consuming as well.
Please let me know your thoughts
thank you
February 19, 2018 at 4:37 am
It's really hard to offer sound advice on a query I can't see, against a structure I don't know, with unknown indexes, constraints, statistics and no knowledge of the execution plan. From the descriptions, I'm not completely sure I understand what my two options are here and whether or not there isn't a 3rd (4th, 5th...) option to consider. However, within the strictures you've outlined, I'd say Option 1 because it sounds like Option 2 is sacrificing I/O to simplify the query. I would only ever do that if by simplifying the query I saw a performance enhancement (better plan, faster, less I/O, etc.). However, with the information at hand, that's just a vague guess.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 19, 2018 at 4:56 am
Are you talking about constructing 'lookup' tables in a query?
LikeSELECT <stuff> FROM Transactions
Inner Join (SELECT 1 As StatusID, 'Active' as Status UNION ALL SELECT 2, 'Inactive') As Statuses on Transactions.StatusID = Statuses.StatusID
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply