I have a tables (simplified) like this:
我有这样一个简化的表格:
Lender
Id (PK)
AgencyCode1
AgencyCode2
AgencyCode3...
AgencyCode20
Agency
AgencyCode
AgencyName
The Lender table is denormalized data coming from a view for reporting. I need one row in my output for each Lender and I need to join the agency codes to an agency table to get the display name for each agency.
贷方表是来自报表视图的非规范化数据。我需要为每个贷款人在输出中加入一行，并将代理代码加入到代理表中，以获取每个代理的显示名称。
The resulting output I am looking for in each row is:
我在每一行中查找的结果输出是:
LenderId, AgencyCode1, AgencyName1, ... AgencyCode20, AgencyName20
What is the pattern that strikes the best balance between performance and (developer) productivity to query something like this?
在性能和(开发人员)生产力之间取得最佳平衡的模式是什么?
--Edit, Agency Code wasn't a primary key, as I had originally indicated.
——编辑代理代码并不是我最初指出的主要关键。
1 个解决方案
#1
4
Echoing @Martin Smith's comment, unpivot, join, and pivot would probably be the most efficient, performance wise -- and, through working out the intricacies of how to unpivot and pivot, your developers would be more expereienced and thus more productive over time.
响应@Martin Smith的评论，unpivot、join和pivot可能是最高效、性能最好的——通过解决如何unpivot和pivot的复杂问题，您的开发人员将会更加熟练，从而随着时间的推移更加高效。
Alternatively, if there are 20 and always 20 denormalized columns, you could just write out 20 left outer joins (under the assumption that ever Lender is not related with 20 Agencies). This is ugly code, and would require SQL to process 20 joins... but if the Agency table is small (where I'd call 8 pages/1 extent small), then the overall processing time might be relatively short. Testing would be called for, to determine which performs best.
或者，如果有20个且总是20个非规范化列，您只需写出20个左外连接(假设任何出借方都与20个代理无关)。这是一个丑陋的代码，需要SQL来处理20个连接…但是如果代理表很小(我称之为8页/1个范围小)，那么总体处理时间可能会相对较短。需要进行测试，以确定哪个性能最好。
As regards to developer productivity overall, keep track of how long everything takes (including posting to SO). When it's done, tally everything up, and use it to show how much time was wasted by your developers having to work with poorly designed tables.
关于开发人员的整体生产力，请记录每件事花费的时间(包括发布到SO)。当它完成时，将所有东西都记录下来，并使用它来显示开发人员不得不使用设计糟糕的表所浪费的时间。
#1
4
Echoing @Martin Smith's comment, unpivot, join, and pivot would probably be the most efficient, performance wise -- and, through working out the intricacies of how to unpivot and pivot, your developers would be more expereienced and thus more productive over time.
响应@Martin Smith的评论，unpivot、join和pivot可能是最高效、性能最好的——通过解决如何unpivot和pivot的复杂问题，您的开发人员将会更加熟练，从而随着时间的推移更加高效。
Alternatively, if there are 20 and always 20 denormalized columns, you could just write out 20 left outer joins (under the assumption that ever Lender is not related with 20 Agencies). This is ugly code, and would require SQL to process 20 joins... but if the Agency table is small (where I'd call 8 pages/1 extent small), then the overall processing time might be relatively short. Testing would be called for, to determine which performs best.
或者，如果有20个且总是20个非规范化列，您只需写出20个左外连接(假设任何出借方都与20个代理无关)。这是一个丑陋的代码，需要SQL来处理20个连接…但是如果代理表很小(我称之为8页/1个范围小)，那么总体处理时间可能会相对较短。需要进行测试，以确定哪个性能最好。
As regards to developer productivity overall, keep track of how long everything takes (including posting to SO). When it's done, tally everything up, and use it to show how much time was wasted by your developers having to work with poorly designed tables.
关于开发人员的整体生产力，请记录每件事花费的时间(包括发布到SO)。当它完成时，将所有东西都记录下来，并使用它来显示开发人员不得不使用设计糟糕的表所浪费的时间。