|
Home > Archive > MS SQL Data Warehousing > May 2005 > SQL performance
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| MajorTom 2005-04-30, 9:23 am |
| 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
| |
| MajorTom 2005-04-30, 9:23 am |
| 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. spPOSConsultaHistori
alArticulo
(
@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=y8j...ize=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.pulgarNO@NOverizon.net.do> schrieb im Newsbeitrag
> news:%23rETfYYTFHA.4056@TK2MSFTNGP15.phx.gbl...
>
>
| |
| JohnWoll 2005-04-30, 11:23 am |
| A properly designed SQL database with the correct indexes should handle a
query like you describe in a matter of a few seconds.
"Properly" designed means the right balance between normalized and
not-normalized, for the querying purposes. Highly normalized databases
require many joins, which slows the query. Highly de-normalized means fewer
joins, which is better for querying, but also means data is duplicated across
the tables, and must be maintained via triggers, etc.
The correct indexes means, are there "covering" indexes for the query? (A
covering index is one in which the index itself contains the data which the
query will need, so that it is unnecessary for SQL to leave the index and go
to the data leaf to get the data required by the query.)
Finally, using the Query Analyzer, you should be able to evaluate how SQL
will process the query, and determine where your query is slowest, and why.
It was tough to tell anything from the diagram - even expanded it was too
small to see the details.
(I myself have a long VFP background. IMHO SQL is to the Foxpro data engine
what a Porsch is to VW bug.)
"MajorTom" wrote:
> 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
>
>
>
>
| |
| Michael C# 2005-04-30, 11:23 am |
| First thing, inv_causas table doesn't have a Primary Key. It's probably
doing a table scan every time.
Here's some general tips to help you design queries to run as fast as
possible:
1) Make sure every table has a primary key,
2) Make sure you have updated statistics (set statistics to autocreate and
autoupdate),
3) Take a look at the execution plan for this query. This is very useful
for figuring out which parts of the query are taking the longest and can be
optimized further.
"MajorTom" <m.pulgarNO@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. spPOSConsultaHistori
alArticulo
>
> (
>
> @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=y8j...ize=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...
>
>
| |
| Bruce L-C [MVP] 2005-04-30, 8:23 pm |
| A few million records is nothing for SQL Server. A database I am currently
working with has several tables with 1-5 million rows and one table with 40
million rows. I am running it on a 2 processor machine with 2 Gigs of RAM.
Nothing special and it does fine. Of course it depends on composition of the
tables as well as the number of rows but I guarantee you, there is no reason
to even be thinking about 64 bit machine.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"MajorTom" <m.pulgarNO@NOverizon.net.do> wrote in message
news:%23tS6CXYTFHA.544@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.
"MajorTom" <m.pulgarNO@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. spPOSConsultaHistori
alArticulo
>
> (
>
> @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=y8j...ize=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...
me[color=darkred]
to[color=darkred]
about[color=darkred]
thousand[color=darkr
ed]
>
>
| |
| MajorTom 2005-05-06, 11:23 am |
| Thanks, Is very helpfully all yours comments
MajorTom
"JT" <someone@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.pulgarNO@NOverizon.net.do> wrote in message
> news:%23htGqKZTFHA.2304@tk2msftngp13.phx.gbl...
> AS
> pos_ventas1.precio_f,
> inv_existencias.id_venta
> =
> in
> me
> to
> about
> thousand
>
>
| |
| David Browne 2005-05-07, 1:23 pm |
|
"MajorTom" <m.pulgarNO@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. spPOSConsultaHistori
alArticulo
>
> (
>
> @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
>
>
First, make sure that each table has a primary key and each foreign key is
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
|
|
|
|
|