|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
SQL PerformanceI have the question about the SQL Server Performance and design considerations. I am developing code that is going to deal with a lot of records (specific detail in the attachment). I am writing code right now that is working fine with a few records. My concern is to know how the performance is going to respond when the database get the full load (that I don't have now). Just for complete my scenario, I came from FoxPro database and have about 3 years with C# and visual studio and this is my first full SQL Server application. The attachment (jpg file) is the query that most concern to me for the performance problem, if some one can see it and contact me for further explanations. it was to big for attachment, the image is here and use the full size botton for get the detail information http://privadas.fotopic.net/p14387002.html Specific questions about the query: The result for the parameter (@id_articulo) is going to be a few thousand when the database has millions of records, what time normally take a query like this with all the best approach that you can recommend? The query has to be saved as a Store procedure? All I read said for the best performance it's recommended the store procedure, is that right? The recommendation for index, and clustered index? We may need the new 64 b Windows Server 2003? Any recommendations and best approach for the query Thanks everybody and don't forget to have a nice day! MajorTom PD: Sorry for my bad English Sorry i would help you, if i could read the picture, its too small for me to
read. Perhaps you post the query here to give you some recommondations for it. Jens Suessmeyer. Show quote "MajorTom" <m.pulga***@NOverizon.net.do> schrieb im Newsbeitrag news:%23rETfYYTFHA.4056@TK2MSFTNGP15.phx.gbl... > Hello Everybody > > I have the question about the SQL Server Performance and design > considerations. > > I am developing code that is going to deal with a lot of records (specific > detail in the attachment). I am writing code right now that is working > fine > with a few records. My concern is to know how the performance is going to > respond when the database get the full load (that I don't have now). > > Just for complete my scenario, I came from FoxPro database and have about > 3 > years with C# and visual studio and this is my first full SQL Server > application. > > The attachment (jpg file) is the query that most concern to me for the > performance problem, if some one can see it and contact me for further > explanations. > > it was to big for attachment, the image is here and use the full size > botton > for get the detail information > http://privadas.fotopic.net/p14387002.html > > Specific questions about the query: > > The result for the parameter (@id_articulo) is going to be a few thousand > when the database has millions of records, what time normally take a query > like this with all the best approach that you can recommend? > > The query has to be saved as a Store procedure? All I read said for the > best > performance it's recommended the store procedure, is that right? > > The recommendation for index, and clustered index? > > We may need the new 64 b Windows Server 2003? > > Any recommendations and best approach for the query > > Thanks everybody and don't forget to have a nice day! > > MajorTom > > PD: Sorry for my bad English > > Sorry
It's a classical inventory tracking application, with a lot of input and output that go to a central table that track all the stock move. This particular table can grow about 5,000 record by day and some time is going to have millions, the query track all the records for a single master record, with a relation from few thousand from the millions. The tracking table have relation to the sales and input tables that are very big to. This is the query (from the SP): CREATE PROCEDURE dbo.spPOSConsultaHistorialArticulo ( @id_articulo int ) AS SET NOCOUNT ON; SELECT inv_master.key1 AS id_articulo, inv_existencias.id_causa, inv_existencias.fechah, inv_causas.nombre AS causa, inv_almacenes.nombre AS almacen, inv_existencias.cantidad, inv_compras0.no_factura AS noCompra, inv_comprasd.costo_rec, pos_ventas0.no_doc AS noVenta, pos_ventas1.precio_f, inv_comprasd.fecha_venc FROM pos_ventas1 INNER JOIN pos_ventas0 ON pos_ventas1.id_venta = pos_ventas0.key1 RIGHT OUTER JOIN inv_existencias INNER JOIN inv_almacenes ON inv_existencias.id_almacen = inv_almacenes.codigo INNER JOIN inv_master ON inv_existencias.id_articulo = inv_master.key1 INNER JOIN inv_causas ON inv_existencias.id_causa = inv_causas.codigo ON pos_ventas1.id_articulo = inv_existencias.id_articulo AND pos_ventas0.key1 = inv_existencias.id_venta LEFT OUTER JOIN inv_compras0 INNER JOIN inv_comprasd ON inv_compras0.key1 = inv_comprasd.id_compras0 ON inv_existencias.id_articulo = inv_comprasd.id_articulo AND inv_existencias.id_compra = inv_compras0.key1 WHERE (inv_master.key1 = @id_articulo) ORDER BY inv_existencias.fechah But the image has a lot of information about the record by each table This link gets you to the full size, about 1920 pixel wide, and I hope you can read it http://images3.fotopic.net/?iid=y8jd20&outx=600&noresize=1&nostamp=1 Thanks for your interest MajorTom Show quote "Jens Süßmeyer" <Jens@Remove_this_For_Contacting.sqlserver2005.de> wrote in message news:uHB6%23cYTFHA.2768@tk2msftngp13.phx.gbl... > Sorry i would help you, if i could read the picture, its too small for me > to read. > > Perhaps you post the query here to give you some recommondations for it. > > Jens Suessmeyer. > > > > "MajorTom" <m.pulga***@NOverizon.net.do> schrieb im Newsbeitrag > news:%23rETfYYTFHA.4056@TK2MSFTNGP15.phx.gbl... >> Hello Everybody >> >> I have the question about the SQL Server Performance and design >> considerations. >> >> I am developing code that is going to deal with a lot of records >> (specific >> detail in the attachment). I am writing code right now that is working >> fine >> with a few records. My concern is to know how the performance is going to >> respond when the database get the full load (that I don't have now). >> >> Just for complete my scenario, I came from FoxPro database and have about >> 3 >> years with C# and visual studio and this is my first full SQL Server >> application. >> >> The attachment (jpg file) is the query that most concern to me for the >> performance problem, if some one can see it and contact me for further >> explanations. >> >> it was to big for attachment, the image is here and use the full size >> botton >> for get the detail information >> http://privadas.fotopic.net/p14387002.html >> >> Specific questions about the query: >> >> The result for the parameter (@id_articulo) is going to be a few thousand >> when the database has millions of records, what time normally take a >> query >> like this with all the best approach that you can recommend? >> >> The query has to be saved as a Store procedure? All I read said for the >> best >> performance it's recommended the store procedure, is that right? >> >> The recommendation for index, and clustered index? >> >> We may need the new 64 b Windows Server 2003? >> >> Any recommendations and best approach for the query >> >> Thanks everybody and don't forget to have a nice day! >> >> MajorTom >> >> PD: Sorry for my bad English >> >> > > You can use various optimization techniques, but there comes a point where
only physical partitioning of the data will make a significant difference. Periodically, you may want to run a maintenance process on the stock move table that insert records (older than perhaps one year) into a stock move history table with an identical structure. Once done, delete the same records from the stock move table and re-index. For reporting purposes, you can implement a view that unionizes the current and historical stock move records. Show quote "MajorTom" <m.pulga***@NOverizon.net.do> wrote in message news:%23htGqKZTFHA.2304@tk2msftngp13.phx.gbl... > Sorry > > > > It's a classical inventory tracking application, with a lot of input and > output that go to a central table that track all the stock move. This > particular table can grow about 5,000 record by day and some time is going > to have millions, the query track all the records for a single master > record, with a relation from few thousand from the millions. The tracking > table have relation to the sales and input tables that are very big to. > > > > This is the query (from the SP): > > > > CREATE PROCEDURE dbo.spPOSConsultaHistorialArticulo > > ( > > @id_articulo int > > ) > > AS > > SET NOCOUNT ON; > > SELECT inv_master.key1 AS id_articulo, inv_existencias.id_causa, > inv_existencias.fechah, inv_causas.nombre AS causa, inv_almacenes.nombre AS > almacen, inv_existencias.cantidad, inv_compras0.no_factura AS noCompra, > inv_comprasd.costo_rec, pos_ventas0.no_doc AS noVenta, pos_ventas1.precio_f, > inv_comprasd.fecha_venc > > FROM pos_ventas1 INNER JOIN pos_ventas0 ON pos_ventas1.id_venta = > pos_ventas0.key1 RIGHT OUTER JOIN inv_existencias INNER JOIN inv_almacenes > ON inv_existencias.id_almacen = inv_almacenes.codigo INNER JOIN inv_master > ON inv_existencias.id_articulo = inv_master.key1 INNER JOIN inv_causas ON > inv_existencias.id_causa = inv_causas.codigo ON pos_ventas1.id_articulo = > inv_existencias.id_articulo AND pos_ventas0.key1 = inv_existencias.id_venta > LEFT OUTER JOIN inv_compras0 INNER JOIN inv_comprasd ON inv_compras0.key1 = > inv_comprasd.id_compras0 ON inv_existencias.id_articulo = > inv_comprasd.id_articulo AND inv_existencias.id_compra = inv_compras0.key1 > > WHERE (inv_master.key1 = @id_articulo) ORDER BY inv_existencias.fechah > > > > But the image has a lot of information about the record by each table > > This link gets you to the full size, about 1920 pixel wide, and I hope you > can read it > > http://images3.fotopic.net/?iid=y8jd20&outx=600&noresize=1&nostamp=1 > > > > Thanks for your interest > > > > MajorTom > > > > > "Jens Süßmeyer" <Jens@Remove_this_For_Contacting.sqlserver2005.de> wrote in > message news:uHB6%23cYTFHA.2768@tk2msftngp13.phx.gbl... > > Sorry i would help you, if i could read the picture, its too small for me > > to read. > > > > Perhaps you post the query here to give you some recommondations for it. > > > > Jens Suessmeyer. > > > > > > > > "MajorTom" <m.pulga***@NOverizon.net.do> schrieb im Newsbeitrag > > news:%23rETfYYTFHA.4056@TK2MSFTNGP15.phx.gbl... > >> Hello Everybody > >> > >> I have the question about the SQL Server Performance and design > >> considerations. > >> > >> I am developing code that is going to deal with a lot of records > >> (specific > >> detail in the attachment). I am writing code right now that is working > >> fine > >> with a few records. My concern is to know how the performance is going to > >> respond when the database get the full load (that I don't have now). > >> > >> Just for complete my scenario, I came from FoxPro database and have about > >> 3 > >> years with C# and visual studio and this is my first full SQL Server > >> application. > >> > >> The attachment (jpg file) is the query that most concern to me for the > >> performance problem, if some one can see it and contact me for further > >> explanations. > >> > >> it was to big for attachment, the image is here and use the full size > >> botton > >> for get the detail information > >> http://privadas.fotopic.net/p14387002.html > >> > >> Specific questions about the query: > >> > >> The result for the parameter (@id_articulo) is going to be a few thousand > >> when the database has millions of records, what time normally take a > >> query > >> like this with all the best approach that you can recommend? > >> > >> The query has to be saved as a Store procedure? All I read said for the > >> best > >> performance it's recommended the store procedure, is that right? > >> > >> The recommendation for index, and clustered index? > >> > >> We may need the new 64 b Windows Server 2003? > >> > >> Any recommendations and best approach for the query > >> > >> Thanks everybody and don't forget to have a nice day! > >> > >> MajorTom > >> > >> PD: Sorry for my bad English > >> > >> > > > > > > You can use various optimization techniques, but there comes a point where
only physical partitioning of the data will make a significant difference. Periodically, you may want to run a maintenance process on the stock move table that insert records (older than perhaps one year) into a stock move history table with an identical structure. Once done, delete the same records from the stock move table and re-index. For reporting purposes, you can implement a view that unionizes the current and historical stock move records. Show quote "MajorTom" <m.pulga***@NOverizon.net.do> wrote in message news:%23htGqKZTFHA.2304@tk2msftngp13.phx.gbl... > Sorry > > > > It's a classical inventory tracking application, with a lot of input and > output that go to a central table that track all the stock move. This > particular table can grow about 5,000 record by day and some time is going > to have millions, the query track all the records for a single master > record, with a relation from few thousand from the millions. The tracking > table have relation to the sales and input tables that are very big to. > > > > This is the query (from the SP): > > > > CREATE PROCEDURE dbo.spPOSConsultaHistorialArticulo > > ( > > @id_articulo int > > ) > > AS > > SET NOCOUNT ON; > > SELECT inv_master.key1 AS id_articulo, inv_existencias.id_causa, > inv_existencias.fechah, inv_causas.nombre AS causa, inv_almacenes.nombre AS > almacen, inv_existencias.cantidad, inv_compras0.no_factura AS noCompra, > inv_comprasd.costo_rec, pos_ventas0.no_doc AS noVenta, pos_ventas1.precio_f, > inv_comprasd.fecha_venc > > FROM pos_ventas1 INNER JOIN pos_ventas0 ON pos_ventas1.id_venta = > pos_ventas0.key1 RIGHT OUTER JOIN inv_existencias INNER JOIN inv_almacenes > ON inv_existencias.id_almacen = inv_almacenes.codigo INNER JOIN inv_master > ON inv_existencias.id_articulo = inv_master.key1 INNER JOIN inv_causas ON > inv_existencias.id_causa = inv_causas.codigo ON pos_ventas1.id_articulo = > inv_existencias.id_articulo AND pos_ventas0.key1 = inv_existencias.id_venta > LEFT OUTER JOIN inv_compras0 INNER JOIN inv_comprasd ON inv_compras0.key1 = > inv_comprasd.id_compras0 ON inv_existencias.id_articulo = > inv_comprasd.id_articulo AND inv_existencias.id_compra = inv_compras0.key1 > > WHERE (inv_master.key1 = @id_articulo) ORDER BY inv_existencias.fechah > > > > But the image has a lot of information about the record by each table > > This link gets you to the full size, about 1920 pixel wide, and I hope you > can read it > > http://images3.fotopic.net/?iid=y8jd20&outx=600&noresize=1&nostamp=1 > > > > Thanks for your interest > > > > MajorTom > > > > > "Jens Süßmeyer" <Jens@Remove_this_For_Contacting.sqlserver2005.de> wrote in > message news:uHB6%23cYTFHA.2768@tk2msftngp13.phx.gbl... > > Sorry i would help you, if i could read the picture, its too small for me > > to read. > > > > Perhaps you post the query here to give you some recommondations for it. > > > > Jens Suessmeyer. > > > > > > > > "MajorTom" <m.pulga***@NOverizon.net.do> schrieb im Newsbeitrag > > news:%23rETfYYTFHA.4056@TK2MSFTNGP15.phx.gbl... > >> Hello Everybody > >> > >> I have the question about the SQL Server Performance and design > >> considerations. > >> > >> I am developing code that is going to deal with a lot of records > >> (specific > >> detail in the attachment). I am writing code right now that is working > >> fine > >> with a few records. My concern is to know how the performance is going to > >> respond when the database get the full load (that I don't have now). > >> > >> Just for complete my scenario, I came from FoxPro database and have about > >> 3 > >> years with C# and visual studio and this is my first full SQL Server > >> application. > >> > >> The attachment (jpg file) is the query that most concern to me for the > >> performance problem, if some one can see it and contact me for further > >> explanations. > >> > >> it was to big for attachment, the image is here and use the full size > >> botton > >> for get the detail information > >> http://privadas.fotopic.net/p14387002.html > >> > >> Specific questions about the query: > >> > >> The result for the parameter (@id_articulo) is going to be a few thousand > >> when the database has millions of records, what time normally take a > >> query > >> like this with all the best approach that you can recommend? > >> > >> The query has to be saved as a Store procedure? All I read said for the > >> best > >> performance it's recommended the store procedure, is that right? > >> > >> The recommendation for index, and clustered index? > >> > >> We may need the new 64 b Windows Server 2003? > >> > >> Any recommendations and best approach for the query > >> > >> Thanks everybody and don't forget to have a nice day! > >> > >> MajorTom > >> > >> PD: Sorry for my bad English > >> > >> > > > > > > Thanks, Is very helpfully all yours comments
MajorTom Show quote "JT" <some***@microsoft.com> wrote in message news:eBjEyAaUFHA.3636@TK2MSFTNGP14.phx.gbl... > You can use various optimization techniques, but there comes a point where > only physical partitioning of the data will make a significant difference. > Periodically, you may want to run a maintenance process on the stock move > table that insert records (older than perhaps one year) into a stock move > history table with an identical structure. Once done, delete the same > records from the stock move table and re-index. For reporting purposes, > you > can implement a view that unionizes the current and historical stock move > records. > > "MajorTom" <m.pulga***@NOverizon.net.do> wrote in message > news:%23htGqKZTFHA.2304@tk2msftngp13.phx.gbl... >> Sorry >> >> >> >> It's a classical inventory tracking application, with a lot of input and >> output that go to a central table that track all the stock move. This >> particular table can grow about 5,000 record by day and some time is >> going >> to have millions, the query track all the records for a single master >> record, with a relation from few thousand from the millions. The tracking >> table have relation to the sales and input tables that are very big to. >> >> >> >> This is the query (from the SP): >> >> >> >> CREATE PROCEDURE dbo.spPOSConsultaHistorialArticulo >> >> ( >> >> @id_articulo int >> >> ) >> >> AS >> >> SET NOCOUNT ON; >> >> SELECT inv_master.key1 AS id_articulo, inv_existencias.id_causa, >> inv_existencias.fechah, inv_causas.nombre AS causa, inv_almacenes.nombre > AS >> almacen, inv_existencias.cantidad, inv_compras0.no_factura AS noCompra, >> inv_comprasd.costo_rec, pos_ventas0.no_doc AS noVenta, > pos_ventas1.precio_f, >> inv_comprasd.fecha_venc >> >> FROM pos_ventas1 INNER JOIN pos_ventas0 ON pos_ventas1.id_venta = >> pos_ventas0.key1 RIGHT OUTER JOIN inv_existencias INNER JOIN >> inv_almacenes >> ON inv_existencias.id_almacen = inv_almacenes.codigo INNER JOIN >> inv_master >> ON inv_existencias.id_articulo = inv_master.key1 INNER JOIN inv_causas ON >> inv_existencias.id_causa = inv_causas.codigo ON pos_ventas1.id_articulo = >> inv_existencias.id_articulo AND pos_ventas0.key1 = > inv_existencias.id_venta >> LEFT OUTER JOIN inv_compras0 INNER JOIN inv_comprasd ON inv_compras0.key1 > = >> inv_comprasd.id_compras0 ON inv_existencias.id_articulo = >> inv_comprasd.id_articulo AND inv_existencias.id_compra = >> inv_compras0.key1 >> >> WHERE (inv_master.key1 = @id_articulo) ORDER BY inv_existencias.fechah >> >> >> >> But the image has a lot of information about the record by each table >> >> This link gets you to the full size, about 1920 pixel wide, and I hope >> you >> can read it >> >> http://images3.fotopic.net/?iid=y8jd20&outx=600&noresize=1&nostamp=1 >> >> >> >> Thanks for your interest >> >> >> >> MajorTom >> >> >> >> >> "Jens Süßmeyer" <Jens@Remove_this_For_Contacting.sqlserver2005.de> wrote > in >> message news:uHB6%23cYTFHA.2768@tk2msftngp13.phx.gbl... >> > Sorry i would help you, if i could read the picture, its too small for > me >> > to read. >> > >> > Perhaps you post the query here to give you some recommondations for >> > it. >> > >> > Jens Suessmeyer. >> > >> > >> > >> > "MajorTom" <m.pulga***@NOverizon.net.do> schrieb im Newsbeitrag >> > news:%23rETfYYTFHA.4056@TK2MSFTNGP15.phx.gbl... >> >> Hello Everybody >> >> >> >> I have the question about the SQL Server Performance and design >> >> considerations. >> >> >> >> I am developing code that is going to deal with a lot of records >> >> (specific >> >> detail in the attachment). I am writing code right now that is working >> >> fine >> >> with a few records. My concern is to know how the performance is going > to >> >> respond when the database get the full load (that I don't have now). >> >> >> >> Just for complete my scenario, I came from FoxPro database and have > about >> >> 3 >> >> years with C# and visual studio and this is my first full SQL Server >> >> application. >> >> >> >> The attachment (jpg file) is the query that most concern to me for the >> >> performance problem, if some one can see it and contact me for further >> >> explanations. >> >> >> >> it was to big for attachment, the image is here and use the full size >> >> botton >> >> for get the detail information >> >> http://privadas.fotopic.net/p14387002.html >> >> >> >> Specific questions about the query: >> >> >> >> The result for the parameter (@id_articulo) is going to be a few > thousand >> >> when the database has millions of records, what time normally take a >> >> query >> >> like this with all the best approach that you can recommend? >> >> >> >> The query has to be saved as a Store procedure? All I read said for >> >> the >> >> best >> >> performance it's recommended the store procedure, is that right? >> >> >> >> The recommendation for index, and clustered index? >> >> >> >> We may need the new 64 b Windows Server 2003? >> >> >> >> Any recommendations and best approach for the query >> >> >> >> Thanks everybody and don't forget to have a nice day! >> >> >> >> MajorTom >> >> >> >> PD: Sorry for my bad English >> >> >> >> >> > >> > >> >> > > Thanks, Is very helpfully all yours comments
MajorTom Show quote "JT" <some***@microsoft.com> wrote in message news:eBjEyAaUFHA.3636@TK2MSFTNGP14.phx.gbl... > You can use various optimization techniques, but there comes a point where > only physical partitioning of the data will make a significant difference. > Periodically, you may want to run a maintenance process on the stock move > table that insert records (older than perhaps one year) into a stock move > history table with an identical structure. Once done, delete the same > records from the stock move table and re-index. For reporting purposes, > you > can implement a view that unionizes the current and historical stock move > records. > > "MajorTom" <m.pulga***@NOverizon.net.do> wrote in message > news:%23htGqKZTFHA.2304@tk2msftngp13.phx.gbl... >> Sorry >> >> >> >> It's a classical inventory tracking application, with a lot of input and >> output that go to a central table that track all the stock move. This >> particular table can grow about 5,000 record by day and some time is >> going >> to have millions, the query track all the records for a single master >> record, with a relation from few thousand from the millions. The tracking >> table have relation to the sales and input tables that are very big to. >> >> >> >> This is the query (from the SP): >> >> >> >> CREATE PROCEDURE dbo.spPOSConsultaHistorialArticulo >> >> ( >> >> @id_articulo int >> >> ) >> >> AS >> >> SET NOCOUNT ON; >> >> SELECT inv_master.key1 AS id_articulo, inv_existencias.id_causa, >> inv_existencias.fechah, inv_causas.nombre AS causa, inv_almacenes.nombre > AS >> almacen, inv_existencias.cantidad, inv_compras0.no_factura AS noCompra, >> inv_comprasd.costo_rec, pos_ventas0.no_doc AS noVenta, > pos_ventas1.precio_f, >> inv_comprasd.fecha_venc >> >> FROM pos_ventas1 INNER JOIN pos_ventas0 ON pos_ventas1.id_venta = >> pos_ventas0.key1 RIGHT OUTER JOIN inv_existencias INNER JOIN >> inv_almacenes >> ON inv_existencias.id_almacen = inv_almacenes.codigo INNER JOIN >> inv_master >> ON inv_existencias.id_articulo = inv_master.key1 INNER JOIN inv_causas ON >> inv_existencias.id_causa = inv_causas.codigo ON pos_ventas1.id_articulo = >> inv_existencias.id_articulo AND pos_ventas0.key1 = > inv_existencias.id_venta >> LEFT OUTER JOIN inv_compras0 INNER JOIN inv_comprasd ON inv_compras0.key1 > = >> inv_comprasd.id_compras0 ON inv_existencias.id_articulo = >> inv_comprasd.id_articulo AND inv_existencias.id_compra = >> inv_compras0.key1 >> >> WHERE (inv_master.key1 = @id_articulo) ORDER BY inv_existencias.fechah >> >> >> >> But the image has a lot of information about the record by each table >> >> This link gets you to the full size, about 1920 pixel wide, and I hope >> you >> can read it >> >> http://images3.fotopic.net/?iid=y8jd20&outx=600&noresize=1&nostamp=1 >> >> >> >> Thanks for your interest >> >> >> >> MajorTom >> >> >> >> >> "Jens Süßmeyer" <Jens@Remove_this_For_Contacting.sqlserver2005.de> wrote > in >> message news:uHB6%23cYTFHA.2768@tk2msftngp13.phx.gbl... >> > Sorry i would help you, if i could read the picture, its too small for > me >> > to read. >> > >> > Perhaps you post the query here to give you some recommondations for >> > it. >> > >> > Jens Suessmeyer. >> > >> > >> > >> > "MajorTom" <m.pulga***@NOverizon.net.do> schrieb im Newsbeitrag >> > news:%23rETfYYTFHA.4056@TK2MSFTNGP15.phx.gbl... >> >> Hello Everybody >> >> >> >> I have the question about the SQL Server Performance and design >> >> considerations. >> >> >> >> I am developing code that is going to deal with a lot of records >> >> (specific >> >> detail in the attachment). I am writing code right now that is working >> >> fine >> >> with a few records. My concern is to know how the performance is going > to >> >> respond when the database get the full load (that I don't have now). >> >> >> >> Just for complete my scenario, I came from FoxPro database and have > about >> >> 3 >> >> years with C# and visual studio and this is my first full SQL Server >> >> application. >> >> >> >> The attachment (jpg file) is the query that most concern to me for the >> >> performance problem, if some one can see it and contact me for further >> >> explanations. >> >> >> >> it was to big for attachment, the image is here and use the full size >> >> botton >> >> for get the detail information >> >> http://privadas.fotopic.net/p14387002.html >> >> >> >> Specific questions about the query: >> >> >> >> The result for the parameter (@id_articulo) is going to be a few > thousand >> >> when the database has millions of records, what time normally take a >> >> query >> >> like this with all the best approach that you can recommend? >> >> >> >> The query has to be saved as a Store procedure? All I read said for >> >> the >> >> best >> >> performance it's recommended the store procedure, is that right? >> >> >> >> The recommendation for index, and clustered index? >> >> >> >> We may need the new 64 b Windows Server 2003? >> >> >> >> Any recommendations and best approach for the query >> >> >> >> Thanks everybody and don't forget to have a nice day! >> >> >> >> MajorTom >> >> >> >> PD: Sorry for my bad English >> >> >> >> >> > >> > >> >> > > Sorry
It's a classical inventory tracking application, with a lot of input and output that go to a central table that track all the stock move. This particular table can grow about 5,000 record by day and some time is going to have millions, the query track all the records for a single master record, with a relation from few thousand from the millions. The tracking table have relation to the sales and input tables that are very big to. This is the query (from the SP): CREATE PROCEDURE dbo.spPOSConsultaHistorialArticulo ( @id_articulo int ) AS SET NOCOUNT ON; SELECT inv_master.key1 AS id_articulo, inv_existencias.id_causa, inv_existencias.fechah, inv_causas.nombre AS causa, inv_almacenes.nombre AS almacen, inv_existencias.cantidad, inv_compras0.no_factura AS noCompra, inv_comprasd.costo_rec, pos_ventas0.no_doc AS noVenta, pos_ventas1.precio_f, inv_comprasd.fecha_venc FROM pos_ventas1 INNER JOIN pos_ventas0 ON pos_ventas1.id_venta = pos_ventas0.key1 RIGHT OUTER JOIN inv_existencias INNER JOIN inv_almacenes ON inv_existencias.id_almacen = inv_almacenes.codigo INNER JOIN inv_master ON inv_existencias.id_articulo = inv_master.key1 INNER JOIN inv_causas ON inv_existencias.id_causa = inv_causas.codigo ON pos_ventas1.id_articulo = inv_existencias.id_articulo AND pos_ventas0.key1 = inv_existencias.id_venta LEFT OUTER JOIN inv_compras0 INNER JOIN inv_comprasd ON inv_compras0.key1 = inv_comprasd.id_compras0 ON inv_existencias.id_articulo = inv_comprasd.id_articulo AND inv_existencias.id_compra = inv_compras0.key1 WHERE (inv_master.key1 = @id_articulo) ORDER BY inv_existencias.fechah But the image has a lot of information about the record by each table This link gets you to the full size, about 1920 pixel wide, and I hope you can read it http://images3.fotopic.net/?iid=y8jd20&outx=600&noresize=1&nostamp=1 Thanks for your interest MajorTom Show quote "Jens Süßmeyer" <Jens@Remove_this_For_Contacting.sqlserver2005.de> wrote in message news:uHB6%23cYTFHA.2768@tk2msftngp13.phx.gbl... > Sorry i would help you, if i could read the picture, its too small for me > to read. > > Perhaps you post the query here to give you some recommondations for it. > > Jens Suessmeyer. > > > > "MajorTom" <m.pulga***@NOverizon.net.do> schrieb im Newsbeitrag > news:%23rETfYYTFHA.4056@TK2MSFTNGP15.phx.gbl... >> Hello Everybody >> >> I have the question about the SQL Server Performance and design >> considerations. >> >> I am developing code that is going to deal with a lot of records >> (specific >> detail in the attachment). I am writing code right now that is working >> fine >> with a few records. My concern is to know how the performance is going to >> respond when the database get the full load (that I don't have now). >> >> Just for complete my scenario, I came from FoxPro database and have about >> 3 >> years with C# and visual studio and this is my first full SQL Server >> application. >> >> The attachment (jpg file) is the query that most concern to me for the >> performance problem, if some one can see it and contact me for further >> explanations. >> >> it was to big for attachment, the image is here and use the full size >> botton >> for get the detail information >> http://privadas.fotopic.net/p14387002.html >> >> Specific questions about the query: >> >> The result for the parameter (@id_articulo) is going to be a few thousand >> when the database has millions of records, what time normally take a >> query >> like this with all the best approach that you can recommend? >> >> The query has to be saved as a Store procedure? All I read said for the >> best >> performance it's recommended the store procedure, is that right? >> >> The recommendation for index, and clustered index? >> >> We may need the new 64 b Windows Server 2003? >> >> Any recommendations and best approach for the query >> >> Thanks everybody and don't forget to have a nice day! >> >> MajorTom >> >> PD: Sorry for my bad English >> >> > >
Show quote
"MajorTom" <m.pulga***@NOverizon.net.do> wrote in message First, make sure that each table has a primary key and each foreign key is news:%23htGqKZTFHA.2304@tk2msftngp13.phx.gbl... > Sorry > > > > It's a classical inventory tracking application, with a lot of input and > output that go to a central table that track all the stock move. This > particular table can grow about 5,000 record by day and some time is going > to have millions, the query track all the records for a single master > record, with a relation from few thousand from the millions. The tracking > table have relation to the sales and input tables that are very big to. > > > > This is the query (from the SP): > > > > CREATE PROCEDURE dbo.spPOSConsultaHistorialArticulo > > ( > > @id_articulo int > > ) > > AS > > SET NOCOUNT ON; > > SELECT inv_master.key1 AS id_articulo, inv_existencias.id_causa, > inv_existencias.fechah, inv_causas.nombre AS causa, inv_almacenes.nombre > AS almacen, inv_existencias.cantidad, inv_compras0.no_factura AS noCompra, > inv_comprasd.costo_rec, pos_ventas0.no_doc AS noVenta, > pos_ventas1.precio_f, inv_comprasd.fecha_venc > > FROM pos_ventas1 INNER JOIN pos_ventas0 ON pos_ventas1.id_venta = > pos_ventas0.key1 RIGHT OUTER JOIN inv_existencias INNER JOIN inv_almacenes > ON inv_existencias.id_almacen = inv_almacenes.codigo INNER JOIN inv_master > ON inv_existencias.id_articulo = inv_master.key1 INNER JOIN inv_causas ON > inv_existencias.id_causa = inv_causas.codigo ON pos_ventas1.id_articulo = > inv_existencias.id_articulo AND pos_ventas0.key1 = > inv_existencias.id_venta LEFT OUTER JOIN inv_compras0 INNER JOIN > inv_comprasd ON inv_compras0.key1 = inv_comprasd.id_compras0 ON > inv_existencias.id_articulo = inv_comprasd.id_articulo AND > inv_existencias.id_compra = inv_compras0.key1 > > WHERE (inv_master.key1 = @id_articulo) ORDER BY inv_existencias.fechah > > supported by an index. So for instance if there is a relationship between pos_ventas1.id_articulo and inv_existencias.id_articulo, then there should be an index on pos_ventas1.id_articulo in addition to the unique index on inv_extencias.id_articulo. Second is you need to stop using the query designer and write your queries by hand. Mixing outer and inner joins can be tricky and the order of operations is not always obvious. Below I have rewritten your query, and I don't know if that's what you intended to design. As a rule of thumb, perform all of your inner joins first, and then use a one or more LEFT JOINS to add additional tables. This makes the query very readable and also makes the logical order of operations obvious without parentheses. The key to knowing how a query will perform is being able to trace the execution from one step to the next and estimate the number of rows at each step. This query will run fine, but it's hard to see that through the nested outer joins. Here's your original query: SELECT inv_master.key1 AS id_articulo, inv_existencias.id_causa, inv_existencias.fechah, inv_causas.nombre AS causa, inv_almacenes.nombre AS almacen, inv_existencias.cantidad, inv_compras0.no_factura AS noCompra, inv_comprasd.costo_rec, pos_ventas0.no_doc AS noVenta, pos_ventas1.precio_f, inv_comprasd.fecha_venc FROM pos_ventas1 INNER JOIN pos_ventas0 ON pos_ventas1.id_venta = pos_ventas0.key1 RIGHT OUTER JOIN (inv_existencias INNER JOIN inv_almacenes ON inv_existencias.id_almacen = inv_almacenes.codigo INNER JOIN inv_master ON inv_existencias.id_articulo = inv_master.key1 INNER JOIN inv_causas ON inv_existencias.id_causa = inv_causas.codigo ) ON pos_ventas1.id_articulo = inv_existencias.id_articulo AND pos_ventas0.key1 = inv_existencias.id_venta LEFT OUTER JOIN inv_compras0 ( INNER JOIN inv_comprasd ON inv_compras0.key1 = inv_comprasd.id_compras0 ) ON inv_existencias.id_articulo = inv_comprasd.id_articulo AND inv_existencias.id_compra = inv_compras0.key1 WHERE (inv_master.key1 = @id_articulo) ORDER BY inv_existencias.fechah Here it is in a more normalized form in which you can see that this query will be driven by inv_master. A single row from inv_master will be joined over to inv_extencias, inv_almances, and inv_causas. At this point you have gathered all the rows for your result. The subsequent left joins may add duplicates, but can't remove rows. The core cost of this query is performing those first four joins. So make sure that each of the joins is supported by an index, and this query should perform well with very large datasets. SELECT inv_master.key1 AS id_articulo, inv_existencias.id_causa, inv_existencias.fechah, inv_causas.nombre AS causa, inv_almacenes.nombre AS almacen, inv_existencias.cantidad, inv_compras0.no_factura AS noCompra, inv_comprasd.costo_rec, pos_ventas0.no_doc AS noVenta, pos_ventas1.precio_f, inv_comprasd.fecha_venc FROM inv_existencias INNER JOIN inv_almacenes ON inv_existencias.id_almacen = inv_almacenes.codigo INNER JOIN inv_master ON inv_existencias.id_articulo = inv_master.key1 INNER JOIN inv_causas ON inv_existencias.id_causa = inv_causas.codigo LEFT OUTER JOIN ( pos_ventas1 INNER JOIN pos_ventas0 ON pos_ventas1.id_venta = pos_ventas0.key1 ) ON pos_ventas1.id_articulo = inv_existencias.id_articulo AND pos_ventas0.key1 = inv_existencias.id_venta LEFT OUTER JOIN ( inv_compras0 INNER JOIN inv_comprasd ON inv_compras0.key1 = inv_comprasd.id_compras0 ) ON inv_existencias.id_articulo = inv_comprasd.id_articulo AND inv_existencias.id_compra = inv_compras0.key1 WHERE (inv_master.key1 = @id_articulo) ORDER BY inv_existencias.fechah David
Show quote
"MajorTom" <m.pulga***@NOverizon.net.do> wrote in message First, make sure that each table has a primary key and each foreign key is news:%23htGqKZTFHA.2304@tk2msftngp13.phx.gbl... > Sorry > > > > It's a classical inventory tracking application, with a lot of input and > output that go to a central table that track all the stock move. This > particular table can grow about 5,000 record by day and some time is going > to have millions, the query track all the records for a single master > record, with a relation from few thousand from the millions. The tracking > table have relation to the sales and input tables that are very big to. > > > > This is the query (from the SP): > > > > CREATE PROCEDURE dbo.spPOSConsultaHistorialArticulo > > ( > > @id_articulo int > > ) > > AS > > SET NOCOUNT ON; > > SELECT inv_master.key1 AS id_articulo, inv_existencias.id_causa, > inv_existencias.fechah, inv_causas.nombre AS causa, inv_almacenes.nombre > AS almacen, inv_existencias.cantidad, inv_compras0.no_factura AS noCompra, > inv_comprasd.costo_rec, pos_ventas0.no_doc AS noVenta, > pos_ventas1.precio_f, inv_comprasd.fecha_venc > > FROM pos_ventas1 INNER JOIN pos_ventas0 ON pos_ventas1.id_venta = > pos_ventas0.key1 RIGHT OUTER JOIN inv_existencias INNER JOIN inv_almacenes > ON inv_existencias.id_almacen = inv_almacenes.codigo INNER JOIN inv_master > ON inv_existencias.id_articulo = inv_master.key1 INNER JOIN inv_causas ON > inv_existencias.id_causa = inv_causas.codigo ON pos_ventas1.id_articulo = > inv_existencias.id_articulo AND pos_ventas0.key1 = > inv_existencias.id_venta LEFT OUTER JOIN inv_compras0 INNER JOIN > inv_comprasd ON inv_compras0.key1 = inv_comprasd.id_compras0 ON > inv_existencias.id_articulo = inv_comprasd.id_articulo AND > inv_existencias.id_compra = inv_compras0.key1 > > WHERE (inv_master.key1 = @id_articulo) ORDER BY inv_existencias.fechah > > supported by an index. So for instance if there is a relationship between pos_ventas1.id_articulo and inv_existencias.id_articulo, then there should be an index on pos_ventas1.id_articulo in addition to the unique index on inv_extencias.id_articulo. Second is you need to stop using the query designer and write your queries by hand. Mixing outer and inner joins can be tricky and the order of operations is not always obvious. Below I have rewritten your query, and I don't know if that's what you intended to design. As a rule of thumb, perform all of your inner joins first, and then use a one or more LEFT JOINS to add additional tables. This makes the query very readable and also makes the logical order of operations obvious without parentheses. The key to knowing how a query will perform is being able to trace the execution from one step to the next and estimate the number of rows at each step. This query will run fine, but it's hard to see that through the nested outer joins. Here's your original query: SELECT inv_master.key1 AS id_articulo, inv_existencias.id_causa, inv_existencias.fechah, inv_causas.nombre AS causa, inv_almacenes.nombre AS almacen, inv_existencias.cantidad, inv_compras0.no_factura AS noCompra, inv_comprasd.costo_rec, pos_ventas0.no_doc AS noVenta, pos_ventas1.precio_f, inv_comprasd.fecha_venc FROM pos_ventas1 INNER JOIN pos_ventas0 ON pos_ventas1.id_venta = pos_ventas0.key1 RIGHT OUTER JOIN (inv_existencias INNER JOIN inv_almacenes ON inv_existencias.id_almacen = inv_almacenes.codigo INNER JOIN inv_master ON inv_existencias.id_articulo = inv_master.key1 INNER JOIN inv_causas ON inv_existencias.id_causa = inv_causas.codigo ) ON pos_ventas1.id_articulo = inv_existencias.id_articulo AND pos_ventas0.key1 = inv_existencias.id_venta LEFT OUTER JOIN inv_compras0 ( INNER JOIN inv_comprasd ON inv_compras0.key1 = inv_comprasd.id_compras0 ) ON inv_existencias.id_articulo = inv_comprasd.id_articulo AND inv_existencias.id_compra = inv_compras0.key1 WHERE (inv_master.key1 = @id_articulo) ORDER BY inv_existencias.fechah Here it is in a more normalized form in which you can see that this query will be driven by inv_master. A single row from inv_master will be joined over to inv_extencias, inv_almances, and inv_causas. At this point you have gathered all the rows for your result. The subsequent left joins may add duplicates, but can't remove rows. The core cost of this query is performing those first four joins. So make sure that each of the joins is supported by an index, and this query should perform well with very large datasets. SELECT inv_master.key1 AS id_articulo, inv_existencias.id_causa, inv_existencias.fechah, inv_causas.nombre AS causa, inv_almacenes.nombre AS almacen, inv_existencias.cantidad, inv_compras0.no_factura AS noCompra, inv_comprasd.costo_rec, pos_ventas0.no_doc AS noVenta, pos_ventas1.precio_f, inv_comprasd.fecha_venc FROM inv_existencias INNER JOIN inv_almacenes ON inv_existencias.id_almacen = inv_almacenes.codigo INNER JOIN inv_master ON inv_existencias.id_articulo = inv_master.key1 INNER JOIN inv_causas ON inv_existencias.id_causa = inv_causas.codigo LEFT OUTER JOIN ( pos_ventas1 INNER JOIN pos_ventas0 ON pos_ventas1.id_venta = pos_ventas0.key1 ) ON pos_ventas1.id_articulo = inv_existencias.id_articulo AND pos_ventas0.key1 = inv_existencias.id_venta LEFT OUTER JOIN ( inv_compras0 INNER JOIN inv_comprasd ON inv_compras0.key1 = inv_comprasd.id_compras0 ) ON inv_existencias.id_articulo = inv_comprasd.id_articulo AND inv_existencias.id_compra = inv_compras0.key1 WHERE (inv_master.key1 = @id_articulo) ORDER BY inv_existencias.fechah David  |
|||||||||||||||||||||||
Other interesting topics