{"id":1810,"date":"2020-05-25T18:07:40","date_gmt":"2020-05-25T18:07:40","guid":{"rendered":"http:\/\/manualsqlserver.com\/?p=1810"},"modified":"2023-12-05T23:44:55","modified_gmt":"2023-12-05T23:44:55","slug":"comparando-joins-subconsultas-y-fdu","status":"publish","type":"post","link":"https:\/\/manualsqlserver.com\/?p=1810","title":{"rendered":"Comparando Joins, Subconsultas y FDU"},"content":{"rendered":"\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"200\" height=\"242\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_Comparando_Joins_Subconsultas_FDU___D1.png\" alt=\"\" class=\"wp-image-1811\"\/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Comparando agrupamientos, Subconsultas y FDU<\/h2>\n\n\n\n<p>Este art\u00edculo explica como se debe analizar el resultado en la extracci\u00f3n de datos desde varias tablas, compararemos los valores del \u00abPlan de ejecuci\u00f3n estimado\u00bb de las siguientes tres maneras:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Usando Joins y agrupamientos<\/li>\n\n\n\n<li>Usando Subconsultas<\/li>\n\n\n\n<li>Usando Funciones definidas por el usuario<\/li>\n<\/ol>\n\n\n\n<!--more-->\n\n\n\n<p style=\"font-size:18px\"><strong>Para mas informaci\u00f3n ver:<\/strong><\/p>\n\n\n\n<div class=\"wp-block-group\"><div class=\"wp-block-group__inner-container is-layout-flow wp-block-group-is-layout-flow\">\n<ul class=\"wp-block-list\">\n<li><a rel=\"noreferrer noopener\" href=\"https:\/\/manualsqlserver.com\/?p=215\" target=\"_blank\">Consultas de varias tablas usando Joins<\/a><\/li>\n\n\n\n<li><a rel=\"noreferrer noopener\" href=\"https:\/\/manualsqlserver.com\/?p=219\" target=\"_blank\">Subconsultas<\/a><\/li>\n\n\n\n<li><a rel=\"noreferrer noopener\" href=\"https:\/\/manualsqlserver.com\/?p=335\" target=\"_blank\">Funciones definidas por el usuario<\/a><\/li>\n<\/ul>\n<\/div><\/div>\n\n\n\n<script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script>\n<ins class=\"adsbygoogle\"\n     style=\"display:block; text-align:center;\"\n     data-ad-layout=\"in-article\"\n     data-ad-format=\"fluid\"\n     data-ad-client=\"ca-pub-2636008503986218\"\n     data-ad-slot=\"1902193434\"><\/ins>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script>\n\n\n\n<p><strong>Ejercicios<br><\/strong>Usando la base de datos Northwind<br>Use Northwind<br>go<\/p>\n\n\n\n<p style=\"font-size:22px\"><strong>Ejercicio 1<\/strong><\/p>\n\n\n\n<p><strong>Listado de Categorias, la cantidad de productos y la cantidad de items en Stock.  El resultado para las tres opciones es el que se muestra en la figura<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"795\" height=\"313\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_Comparando_Joins_Subconsultas_FDU__01.png\" alt=\"\" class=\"wp-image-1812\" style=\"width:708px;height:278px\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_Comparando_Joins_Subconsultas_FDU__01.png 795w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_Comparando_Joins_Subconsultas_FDU__01-300x118.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_Comparando_Joins_Subconsultas_FDU__01-768x302.png 768w\" sizes=\"auto, (max-width: 795px) 100vw, 795px\" \/><\/figure>\n\n\n\n<p style=\"font-size:18px\"><strong>Soluci\u00f3n usando Joins a agrupamientos<\/strong><\/p>\n\n\n\n<p>select C.CategoryID As &#8216;C\u00f3d. Categor\u00eda&#8217;, C.CategoryName As &#8216;Categor\u00eda&#8217;,<br>COUNT(P.ProductID) As &#8216;Cantidad Productos&#8217;,<br>Sum(P.UnitsInStock) As &#8216;Items en Stock&#8217;<br>from dbo.Categories As C<br>join dbo.Products As P on C.CategoryID = P.CategoryID<br>Group by C.CategoryID , C.CategoryName<br>go<\/p>\n\n\n\n<p><strong><strong>El plan de ejecuci\u00f3n estimado es como se muestra en la figura<\/strong>, <strong>observe el costo de sub\u00e1rbol estimado.<\/strong><\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"466\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_Comparando_Joins_Subconsultas_FDU__02-1024x466.png\" alt=\"\" class=\"wp-image-1814\" style=\"width:743px;height:337px\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_Comparando_Joins_Subconsultas_FDU__02-1024x466.png 1024w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_Comparando_Joins_Subconsultas_FDU__02-300x137.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_Comparando_Joins_Subconsultas_FDU__02-768x350.png 768w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_Comparando_Joins_Subconsultas_FDU__02.png 1076w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p style=\"font-size:18px\"><strong>Soluci\u00f3n usando subconsultas<\/strong><\/p>\n\n\n\n<p>select C.CategoryID As &#8216;C\u00f3d. Categor\u00eda&#8217;, C.CategoryName As &#8216;Categor\u00eda&#8217;,<br>(select COUNT(P.ProductID) from dbo.Products As P<br>where P.CategoryID = C.CategoryID ) As &#8216;Cantidad Productos&#8217;,<br>(select Sum(P.UnitsInStock) from Products As P<br>where P.CategoryID = C.CategoryID ) As &#8216;Items en Stock&#8217;<br>from dbo.Categories As C<br>go<\/p>\n\n\n\n<p><strong>El plan de ejecuci\u00f3n estimado es como se muestra en la figura<\/strong>, <strong>observe el costo de sub\u00e1rbol estimado.<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"486\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_Comparando_Joins_Subconsultas_FDU__03-1024x486.png\" alt=\"\" class=\"wp-image-1813\" style=\"width:713px;height:338px\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_Comparando_Joins_Subconsultas_FDU__03-1024x486.png 1024w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_Comparando_Joins_Subconsultas_FDU__03-300x142.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_Comparando_Joins_Subconsultas_FDU__03-768x364.png 768w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_Comparando_Joins_Subconsultas_FDU__03.png 1062w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p style=\"font-size:18px\"><strong>Soluci\u00f3n usando una funci\u00f3n definida por el usuario<\/strong><\/p>\n\n\n\n<p><strong>Las FDU que retornan la cantidad de productos y la cantidad de productos en stock para cada categor\u00eda son como se muestran a continuaci\u00f3n<br>FDU para la cantidad de productos de una categor\u00eda<br><\/strong>Create function dbo.fduRetornaCantidadProductosPorCategoria(@CodigoCategoria int)<br>returns int<br>As<br>Begin<br>Declare @CantidadProductos int<br>Set @CantidadProductos = (select COUNT(P.ProductID) from dbo.Products As P<br>where P.CategoryID = @CodigoCategoria)<br>\/* Tambi\u00e9n se puede usar el select de la siguiente manera<br>select @CantidadProductos = COUNT(P.ProductID) from dbo.Products As P<br>where P.CategoryID = @CodigoCategoria *\/<br>Return @CantidadProductos<br>End<br>go<\/p>\n\n\n\n<p><strong>FDU para la cantidad de items de cada productos de una categor\u00eda<\/strong><\/p>\n\n\n\n<p>Create function dbo.fduRetornaCantidadProductosEnStockPorCategoria(@CodigoCategoria int)<br>returns Numeric(9,2)<br>As<br>Begin<br>Declare @CantidadProductosEnStock int<br>Set @CantidadProductosEnStock =<br>(select Sum(P.UnitsInStock) from Products As P<br>where P.CategoryID = @CodigoCategoria)<br>Return @CantidadProductosEnStock<br>End<br>go<\/p>\n\n\n\n<p><strong>El resultado requerido usando las FDU<\/strong><br>select C.CategoryID As &#8216;C\u00f3d. Categor\u00eda&#8217;, C.CategoryName As &#8216;Categor\u00eda&#8217;,<br>dbo.fduRetornaCantidadProductosPorCategoria(C.CategoryID) As &#8216;Cantidad Productos&#8217;,<br>dbo.fduRetornaCantidadProductosEnStockPorCategoria(C.CategoryID) As &#8216;Items en Stock&#8217;<br>from dbo.Categories As C<br>go<\/p>\n\n\n\n<p><strong>El plan de ejecuci\u00f3n estimado es como se muestra en la figura<\/strong>, <strong>observe el costo de sub\u00e1rbol estimado.<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"982\" height=\"554\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_Comparando_Joins_Subconsultas_FDU__04.png\" alt=\"\" class=\"wp-image-1815\" style=\"width:752px;height:424px\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_Comparando_Joins_Subconsultas_FDU__04.png 982w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_Comparando_Joins_Subconsultas_FDU__04-300x169.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_Comparando_Joins_Subconsultas_FDU__04-768x433.png 768w\" sizes=\"auto, (max-width: 982px) 100vw, 982px\" \/><\/figure>\n\n\n\n<p class=\"has-very-light-gray-background-color has-background\" style=\"font-size:18px\"><strong>Comparando los costos<br>Usando Joins y Agrupamientos: 0.0199542<br>Usando Subconsultas : 0.0127294<br>Usando FDU : 0.0032916<br>Definitivamente para esta consulta la mejor opci\u00f3n es el uso de FDU.<\/strong><\/p>\n\n\n\n<p style=\"font-size:22px\"><strong>Ejercicio 2<\/strong><\/p>\n\n\n\n<p><strong>Listado de los empleados y la cantidad de \u00f3rdenes generadas<br>El resultado para las tres opciones es el que se muestra en la figura<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"668\" height=\"350\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_Comparando_Joins_Subconsultas_FDU__05.png\" alt=\"\" class=\"wp-image-1816\" style=\"width:710px;height:371px\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_Comparando_Joins_Subconsultas_FDU__05.png 668w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_Comparando_Joins_Subconsultas_FDU__05-300x157.png 300w\" sizes=\"auto, (max-width: 668px) 100vw, 668px\" \/><\/figure>\n\n\n\n<p><strong>Soluci\u00f3n usando Joins a agrupamientos<\/strong><\/p>\n\n\n\n<p>select E.EmployeeID As &#8216;C\u00f3d. Empleado&#8217;, E.LastName + SPACE(1)+ E.FirstName As &#8216;Empleado&#8217;,<br>COUNT(O.OrderID) As &#8216;Cantidad Ordenes&#8217;<br>from dbo.Orders As O<br>join dbo.Employees As E on O.EmployeeID = E.EmployeeID<br>Group by E.EmployeeID, E.LastName + SPACE(1)+ E.FirstName<br>go<br><strong><strong>Listado de los empleados y la cantidad de \u00f3rdenes generadas<br>El resultado para las tres opciones es el que se muestra en la figura<\/strong><\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"1020\" height=\"544\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_Comparando_Joins_Subconsultas_FDU__06.png\" alt=\"\" class=\"wp-image-1817\" style=\"width:710px;height:378px\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_Comparando_Joins_Subconsultas_FDU__06.png 1020w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_Comparando_Joins_Subconsultas_FDU__06-300x160.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_Comparando_Joins_Subconsultas_FDU__06-768x410.png 768w\" sizes=\"auto, (max-width: 1020px) 100vw, 1020px\" \/><\/figure>\n\n\n\n<p style=\"font-size:18px\"><strong>Soluci\u00f3n usando subconsultas<\/strong><\/p>\n\n\n\n<p>select E.EmployeeID As &#8216;C\u00f3d. Empleado&#8217;, E.LastName + SPACE(1)+ E.FirstName As &#8216;Empleado&#8217;,<br>(select COUNT(O.OrderID) from dbo.Orders As O where O.EmployeeID = E.EmployeeID ) As &#8216;Cantidad Ordenes&#8217;<br>from dbo.Employees As E<br>go<br><strong><strong>Listado de los empleados y la cantidad de \u00f3rdenes generadas<br>El resultado para las tres opciones es el que se muestra en la figura<\/strong><\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"990\" height=\"552\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_Comparando_Joins_Subconsultas_FDU__07.png\" alt=\"\" class=\"wp-image-1818\" style=\"width:703px;height:391px\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_Comparando_Joins_Subconsultas_FDU__07.png 990w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_Comparando_Joins_Subconsultas_FDU__07-300x167.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_Comparando_Joins_Subconsultas_FDU__07-768x428.png 768w\" sizes=\"auto, (max-width: 990px) 100vw, 990px\" \/><\/figure>\n\n\n\n<p style=\"font-size:18px\"><strong>Soluci\u00f3n usando una FDU<\/strong><\/p>\n\n\n\n<p><strong>Las FDU que retornan la cantidad de \u00f3rdenes<\/strong><br>Create function fduRetornaCantidadOrdenesPorEmpleado(@CodigoEmpleado int)<br>returns int<br>As<br>Begin<br>Declare @CantidadOrdenes int<br>Set @CantidadOrdenes = (select COUNT(O.OrderID) from dbo.Orders As O<br>where O.EmployeeID =@CodigoEmpleado)<br>Return @CantidadOrdenes<br>End<br>go<br><strong>El resultado requerido usando las FDU<\/strong><br>select E.EmployeeID As &#8216;C\u00f3d. Empleado&#8217;, E.LastName + SPACE(1)+ E.FirstName As &#8216;Empleado&#8217;,<br>dbo.fduRetornaCantidadOrdenesPorEmpleado(E.EmployeeID) As &#8216;Cantidad Productos&#8217;<br>from dbo.Employees As E<br>go<br><strong>Listado de los empleados y la cantidad de \u00f3rdenes generadas<br>El resultado para las tres opciones es el que se muestra en la figura<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"932\" height=\"548\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_Comparando_Joins_Subconsultas_FDU__08.png\" alt=\"\" class=\"wp-image-1819\" style=\"width:732px;height:430px\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_Comparando_Joins_Subconsultas_FDU__08.png 932w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_Comparando_Joins_Subconsultas_FDU__08-300x176.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_Comparando_Joins_Subconsultas_FDU__08-768x452.png 768w\" sizes=\"auto, (max-width: 932px) 100vw, 932px\" \/><\/figure>\n\n\n\n<p class=\"has-very-light-gray-background-color has-background has-medium-font-size\"><strong>Comparando los costos<\/strong><\/p>\n\n\n\n<p class=\"has-very-light-gray-background-color has-background has-normal-font-size\"><strong>Usando Joins y Agrupamientos: 0.0100247<br>Usando Subconsultas : 0.0123854<br>Usando FDU : 0.0032928<br>Definitivamente para esta consulta la mejor opci\u00f3n es el uso de FDU.<\/strong><\/p>\n\n\n\n<p class=\"has-very-light-gray-background-color has-background has-medium-font-size\"><strong>Recomendaci\u00f3n:<br>Analizar siempre el resultado usando el Plan de ejecuci\u00f3n estimado y seleccionar la que tenga el menor valor en el costo estimado de sub \u00e1rbol.<\/strong><\/p>\n\n\n\n<script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script>\n<ins class=\"adsbygoogle\"\n     style=\"display:block; text-align:center;\"\n     data-ad-layout=\"in-article\"\n     data-ad-format=\"fluid\"\n     data-ad-client=\"ca-pub-2636008503986218\"\n     data-ad-slot=\"1902193434\"><\/ins>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script>\n","protected":false},"excerpt":{"rendered":"<p>Comparando agrupamientos, Subconsultas y FDU Este art\u00edculo explica como se debe analizar el resultado en la extracci\u00f3n de datos desde varias tablas, compararemos los valores del \u00abPlan de ejecuci\u00f3n estimado\u00bb de las siguientes tres maneras:<\/p><p><a class=\"more-link btn\" href=\"https:\/\/manualsqlserver.com\/?p=1810\">Seguir leyendo<\/a><\/p>\n","protected":false},"author":1,"featured_media":1811,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4,231],"tags":[29,79,40,51],"class_list":["post-1810","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-consultasdedatos","category-subconsultas","tag-fdu","tag-group-by","tag-join","tag-select","nodate","item-wrap"],"_links":{"self":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/1810","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1810"}],"version-history":[{"count":2,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/1810\/revisions"}],"predecessor-version":[{"id":2860,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/1810\/revisions\/2860"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/media\/1811"}],"wp:attachment":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1810"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1810"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1810"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}