Recently our production database began experiencing very poor I/O
performance. How do I begin to narrow down the queries or tables that are
affecting this?It would be useful to know which platform is in use (SQL 2000 or SQL 2005),
because there are some changes in the way you can investigate.
1) Verify the server situation: is there any fragmentation problem
(especially on the pagefile)?
2) verify the cause of the wait, using DBCC SQLPERF(WAITSTATS) in SQL 2000
or querying the sys.dm_os_wait_stats DMV in SQL 2005;
you should be able to identify the correct area of investigation:
there is a simple example of using the SQL 2005 DMV:
SELECT
wait_type,
waiting_tasks_count,
wait_time_ms,
max_wait_time_ms,
signal_wait_time_ms
FROM sys.dm_os_wait_stats
ORDER BY wait_type
3) Get some useful information about the involved resources using
master.dbo.sysperfinfo in SQL 2000 or sys.dm_os_performance_counters in SQL
2005;
4) Use ::fn_virtualfilestats in SQL 2000 or sys.dm_io_virtual_file_stats to
identify the more stressed database files:
here is an example of using the DMO:
WITH DBIO AS
SELECT
DB_NAME(IVFS.database_id) AS db,
CASE WHEN MF.type = 1 THEN 'log' ELSE 'data' END AS file_type,
SUM(IVFS.num_of_bytes_read + IVFS.num_of_bytes_written) AS io,
SUM(IVFS.io_stall) AS io_stall
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS IVFS
JOIN sys.master_files AS MF
ON IVFS.database_id = MF.database_id
AND IVFS.file_id = MF.file_id
GROUP BY DB_NAME(IVFS.database_id), MF.type
5)Pay attention to the transaction log of the critical databases (location,
number of virtual logs, etc.) and dedicate special attention to the tempDB
(it is usually the more stressed file of a SQL Instance); look at cursor
usage, temporary tables and table variables.
6)Finally, when you have sufficient elements of investigation, you can
profile your instance or your databases with a SQL Profiler trace using the
appropriate event classes.
In this moment i think is premature suggest you which kind of profiling
could be the best: you should decide it when you will have gathered all of
the infos from the previously described steps.
Gilberto
"Dan" wrote:
> Recently our production database began experiencing very poor I/O
> performance. How do I begin to narrow down the queries or tables that are
> affecting this?
Showing posts with label queries. Show all posts
Showing posts with label queries. Show all posts
Friday, March 30, 2012
How to diagnose IO Problems
Recently our production database began experiencing very poor I/O
performance. How do I begin to narrow down the queries or tables that are
affecting this?It would be useful to know which platform is in use (SQL 2000 or SQL 2005),
because there are some changes in the way you can investigate.
1) Verify the server situation: is there any fragmentation problem
(especially on the pagefile)?
2) verify the cause of the wait, using DBCC SQLPERF(WAITSTATS) in SQL 2000
or querying the sys.dm_os_wait_stats DMV in SQL 2005;
you should be able to identify the correct area of investigation:
there is a simple example of using the SQL 2005 DMV:
SELECT
wait_type,
waiting_tasks_count,
wait_time_ms,
max_wait_time_ms,
signal_wait_time_ms
FROM sys.dm_os_wait_stats
ORDER BY wait_type
3) Get some useful information about the involved resources using
master.dbo.sysperfinfo in SQL 2000 or sys.dm_os_performance_counters in SQL
2005;
4) Use ::fn_virtualfilestats in SQL 2000 or sys.dm_io_virtual_file_stats to
identify the more stressed database files:
here is an example of using the DMO:
WITH DBIO AS
SELECT
DB_NAME(IVFS.database_id) AS db,
CASE WHEN MF.type = 1 THEN 'log' ELSE 'data' END AS file_type,
SUM(IVFS.num_of_bytes_read + IVFS.num_of_bytes_written) AS io,
SUM(IVFS.io_stall) AS io_stall
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS IVFS
JOIN sys.master_files AS MF
ON IVFS.database_id = MF.database_id
AND IVFS.file_id = MF.file_id
GROUP BY DB_NAME(IVFS.database_id), MF.type
5)Pay attention to the transaction log of the critical databases (location,
number of virtual logs, etc.) and dedicate special attention to the tempDB
(it is usually the more stressed file of a SQL Instance); look at cursor
usage, temporary tables and table variables.
6)Finally, when you have sufficient elements of investigation, you can
profile your instance or your databases with a SQL Profiler trace using the
appropriate event classes.
In this moment i think is premature suggest you which kind of profiling
could be the best: you should decide it when you will have gathered all of
the infos from the previously described steps.
Gilberto
"Dan" wrote:
> Recently our production database began experiencing very poor I/O
> performance. How do I begin to narrow down the queries or tables that are
> affecting this?
performance. How do I begin to narrow down the queries or tables that are
affecting this?It would be useful to know which platform is in use (SQL 2000 or SQL 2005),
because there are some changes in the way you can investigate.
1) Verify the server situation: is there any fragmentation problem
(especially on the pagefile)?
2) verify the cause of the wait, using DBCC SQLPERF(WAITSTATS) in SQL 2000
or querying the sys.dm_os_wait_stats DMV in SQL 2005;
you should be able to identify the correct area of investigation:
there is a simple example of using the SQL 2005 DMV:
SELECT
wait_type,
waiting_tasks_count,
wait_time_ms,
max_wait_time_ms,
signal_wait_time_ms
FROM sys.dm_os_wait_stats
ORDER BY wait_type
3) Get some useful information about the involved resources using
master.dbo.sysperfinfo in SQL 2000 or sys.dm_os_performance_counters in SQL
2005;
4) Use ::fn_virtualfilestats in SQL 2000 or sys.dm_io_virtual_file_stats to
identify the more stressed database files:
here is an example of using the DMO:
WITH DBIO AS
SELECT
DB_NAME(IVFS.database_id) AS db,
CASE WHEN MF.type = 1 THEN 'log' ELSE 'data' END AS file_type,
SUM(IVFS.num_of_bytes_read + IVFS.num_of_bytes_written) AS io,
SUM(IVFS.io_stall) AS io_stall
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS IVFS
JOIN sys.master_files AS MF
ON IVFS.database_id = MF.database_id
AND IVFS.file_id = MF.file_id
GROUP BY DB_NAME(IVFS.database_id), MF.type
5)Pay attention to the transaction log of the critical databases (location,
number of virtual logs, etc.) and dedicate special attention to the tempDB
(it is usually the more stressed file of a SQL Instance); look at cursor
usage, temporary tables and table variables.
6)Finally, when you have sufficient elements of investigation, you can
profile your instance or your databases with a SQL Profiler trace using the
appropriate event classes.
In this moment i think is premature suggest you which kind of profiling
could be the best: you should decide it when you will have gathered all of
the infos from the previously described steps.
Gilberto
"Dan" wrote:
> Recently our production database began experiencing very poor I/O
> performance. How do I begin to narrow down the queries or tables that are
> affecting this?
How to diagnose IO Problems
Recently our production database began experiencing very poor I/O
performance. How do I begin to narrow down the queries or tables that are
affecting this?
It would be useful to know which platform is in use (SQL 2000 or SQL 2005),
because there are some changes in the way you can investigate.
1) Verify the server situation: is there any fragmentation problem
(especially on the pagefile)?
2) verify the cause of the wait, using DBCC SQLPERF(WAITSTATS) in SQL 2000
or querying the sys.dm_os_wait_stats DMV in SQL 2005;
you should be able to identify the correct area of investigation:
there is a simple example of using the SQL 2005 DMV:
SELECT
wait_type,
waiting_tasks_count,
wait_time_ms,
max_wait_time_ms,
signal_wait_time_ms
FROM sys.dm_os_wait_stats
ORDER BY wait_type
3) Get some useful information about the involved resources using
master.dbo.sysperfinfo in SQL 2000 or sys.dm_os_performance_counters in SQL
2005;
4) Use ::fn_virtualfilestats in SQL 2000 or sys.dm_io_virtual_file_stats to
identify the more stressed database files:
here is an example of using the DMO:
WITH DBIO AS
SELECT
DB_NAME(IVFS.database_id) AS db,
CASE WHEN MF.type = 1 THEN 'log' ELSE 'data' END AS file_type,
SUM(IVFS.num_of_bytes_read + IVFS.num_of_bytes_written) AS io,
SUM(IVFS.io_stall) AS io_stall
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS IVFS
JOIN sys.master_files AS MF
ON IVFS.database_id = MF.database_id
AND IVFS.file_id = MF.file_id
GROUP BY DB_NAME(IVFS.database_id), MF.type
5)Pay attention to the transaction log of the critical databases (location,
number of virtual logs, etc.) and dedicate special attention to the tempDB
(it is usually the more stressed file of a SQL Instance); look at cursor
usage, temporary tables and table variables.
6)Finally, when you have sufficient elements of investigation, you can
profile your instance or your databases with a SQL Profiler trace using the
appropriate event classes.
In this moment i think is premature suggest you which kind of profiling
could be the best: you should decide it when you will have gathered all of
the infos from the previously described steps.
Gilberto
"Dan" wrote:
> Recently our production database began experiencing very poor I/O
> performance. How do I begin to narrow down the queries or tables that are
> affecting this?
performance. How do I begin to narrow down the queries or tables that are
affecting this?
It would be useful to know which platform is in use (SQL 2000 or SQL 2005),
because there are some changes in the way you can investigate.
1) Verify the server situation: is there any fragmentation problem
(especially on the pagefile)?
2) verify the cause of the wait, using DBCC SQLPERF(WAITSTATS) in SQL 2000
or querying the sys.dm_os_wait_stats DMV in SQL 2005;
you should be able to identify the correct area of investigation:
there is a simple example of using the SQL 2005 DMV:
SELECT
wait_type,
waiting_tasks_count,
wait_time_ms,
max_wait_time_ms,
signal_wait_time_ms
FROM sys.dm_os_wait_stats
ORDER BY wait_type
3) Get some useful information about the involved resources using
master.dbo.sysperfinfo in SQL 2000 or sys.dm_os_performance_counters in SQL
2005;
4) Use ::fn_virtualfilestats in SQL 2000 or sys.dm_io_virtual_file_stats to
identify the more stressed database files:
here is an example of using the DMO:
WITH DBIO AS
SELECT
DB_NAME(IVFS.database_id) AS db,
CASE WHEN MF.type = 1 THEN 'log' ELSE 'data' END AS file_type,
SUM(IVFS.num_of_bytes_read + IVFS.num_of_bytes_written) AS io,
SUM(IVFS.io_stall) AS io_stall
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS IVFS
JOIN sys.master_files AS MF
ON IVFS.database_id = MF.database_id
AND IVFS.file_id = MF.file_id
GROUP BY DB_NAME(IVFS.database_id), MF.type
5)Pay attention to the transaction log of the critical databases (location,
number of virtual logs, etc.) and dedicate special attention to the tempDB
(it is usually the more stressed file of a SQL Instance); look at cursor
usage, temporary tables and table variables.
6)Finally, when you have sufficient elements of investigation, you can
profile your instance or your databases with a SQL Profiler trace using the
appropriate event classes.
In this moment i think is premature suggest you which kind of profiling
could be the best: you should decide it when you will have gathered all of
the infos from the previously described steps.
Gilberto
"Dan" wrote:
> Recently our production database began experiencing very poor I/O
> performance. How do I begin to narrow down the queries or tables that are
> affecting this?
Wednesday, March 21, 2012
how to design queries graphically?
Are there anytool, which would allow me to design queries graphically, if not design then atlease analyse them graphically.
Tool should atleast show join of 4tables graphically.
Thank You.
SQL Server Management Studio has graphical capabilities - launched in one of two ways:
1.Create a 'New Query' then right-click on the query pane and select 'Design Query in Editor...'.
2.Connect to a server in Object Explorer then navigate to the following node:
Server -> Databases -> <Your database> -> Views
Right-click on 'Views' and select 'New View...' to launch the query editor.
Chris
|||Try this......
And it's a single query, runs at a single go. I would like to know any s/w / utility (can be third party) which could decode following query.
SELECT '' AS TranNo, 'SC' AS JournalCode, cSupplier AS SupplierCode, cOrderNo AS OrderNo, cWareHouse AS Warehouse, nGrnNo AS GRNNo,
dReceiptDate AS BookEntryDate, vDefaultGLAC AS AccString, CONVERT(NUMERIC(18, 2),
SUM(CASE WHEN [cReceiptsMultiDivFlag] = 'D' THEN nTrnsQty * nUnitPrice / [nReceiptsCurrConRate] ELSE nTrnsQty * nUnitPrice * [nReceiptsCurrConRate] END)) AS FOB, CONVERT(NUMERIC(18, 2),
SUM(CASE WHEN [cReceiptsMultiDivFlag] = 'D' THEN LandedCost / [nReceiptsCurrConRate] ELSE LandedCost * [nReceiptsCurrConRate] END))
AS LandedCost, CONVERT(NUMERIC(18, 2),
SUM(CASE WHEN [cLineDiscTag] = '2' THEN (CASE WHEN [cReceiptsMultiDivFlag] = 'D' THEN (nTrnsQty * nLineDisc1)
/ [nReceiptsCurrConRate] ELSE (nTrnsQty * nLineDisc1) * [nReceiptsCurrConRate] END)
ELSE (CASE WHEN [cReceiptsMultiDivFlag] = 'D' THEN (nTrnsQty * nUnitPrice * nLineDisc1 / 100)
/ [nReceiptsCurrConRate] ELSE (nTrnsQty * nUnitPrice * nLineDisc1 / 100) * [nReceiptsCurrConRate] END) END)) AS Discount1, CONVERT(NUMERIC(18,
2), SUM(CASE WHEN [cLineDiscTag] = '2' THEN (CASE WHEN [cReceiptsMultiDivFlag] = 'D' THEN (nTrnsQty * nLineDisc2)
/ [nReceiptsCurrConRate] ELSE (nTrnsQty * nLineDisc2) * [nReceiptsCurrConRate] END)
ELSE (CASE WHEN [cReceiptsMultiDivFlag] = 'D' THEN (((nTrnsQty * nUnitPrice) - (nTrnsQty * nUnitPrice * nLineDisc1 / 100)) * nLineDisc2 / 100)
/ [nReceiptsCurrConRate] ELSE (((nTrnsQty * nUnitPrice) - (nTrnsQty * nUnitPrice * nLineDisc1 / 100)) * nLineDisc2 / 100) * [nReceiptsCurrConRate] END)
END)) AS Discount2, CONVERT(NUMERIC(18, 2),
SUM(CASE WHEN [cLineDiscTag] = '2' THEN (CASE WHEN [cReceiptsMultiDivFlag] = 'D' THEN (nTrnsQty * nLineDisc3)
/ [nReceiptsCurrConRate] ELSE (nTrnsQty * nLineDisc3) * [nReceiptsCurrConRate] END)
ELSE (CASE WHEN [cReceiptsMultiDivFlag] = 'D' THEN (((nTrnsQty * nUnitPrice) - (nTrnsQty * nUnitPrice * nLineDisc1 / 100) - (((nTrnsQty * nUnitPrice)
- (nTrnsQty * nUnitPrice * nLineDisc1 / 100)) * nLineDisc2 / 100)) * nLineDisc3 / 100) / [nReceiptsCurrConRate] ELSE (((nTrnsQty * nUnitPrice)
- (nTrnsQty * nUnitPrice * nLineDisc1 / 100) - (((nTrnsQty * nUnitPrice) - (nTrnsQty * nUnitPrice * nLineDisc1 / 100)) * nLineDisc2 / 100))
* nLineDisc3 / 100) * [nReceiptsCurrConRate] END) END)) AS Discount3
FROM (SELECT PMPT.cSupplier, PMPT.cOrderNo, PMPT.nLineNo,
PMPT.cItemCode, PMPT.cWareHouse, PMPT.nGrnNo, CONVERT(Varchar,
PMPT.dReceiptDate, 112) AS dReceiptDate, ItemMaster.vDefaultGLAC, PMPT.nTrnsQty,
PMPT.nUnitPrice, PMPT.nReceiptsCurrConRate, PMPT.cReceiptsMultiDivFlag,
cLineDiscTag, nLineDisc1, nLineDisc2, nLineDisc3, SUM(CASE WHEN PMGRNLLC.nBElementalCost IS NULL
THEN 0 ELSE PMGRNLLC.nBElementalCost END) AS LandedCost
FROM PMPT LEFT OUTER JOIN
PMGRNLLC ON PMPT.cCompanyNo = PMGRNLLC.cCompanyCode AND
PMPT.cSupplier = PMGRNLLC.cSupplierCode AND
PMPT.cOrderNo = PMGRNLLC.cOrderNo AND
PMPT.nLineNo = PMGRNLLC.nLineNo AND
PMPT.cItemCode = PMGRNLLC.cItemCode AND
PMPT.cWareHouse = PMGRNLLC.cWareHouse AND
PMPT.nGrnNo = PMGRNLLC.nGrnNo LEFT OUTER JOIN
ItemMaster ON PMPT.cItemCode = ItemMaster.cItemCode LEFT OUTER JOIN
OPENROWSET('SQLOLEDB', '.'; 'sa'; 'sa',
'SELECT DISTINCT CompanyCode, SupplierCode, OrderNo, WareHouse, GRNNo FROM rsiDB.dbo.GRNs') AS B ON
PMPT.cCompanyNo = B.CompanyCode AND RTRIM(PMPT.cSupplier) = B.SupplierCode AND
PMPT.cOrderNo = B.OrderNo AND PMPT.cWareHouse = B.WareHouse AND
PMPT.nGrnNo = B.GRNNo
WHERE (PMPT.cCompanyNo = 'GT') AND (PMPT.nGrnNo > 0) AND (PMPT.nTrnsQty > 0) AND
(B.SupplierCode IS NULL)
GROUP BY PMPT.cSupplier, PMPT.cOrderNo, PMPT.nLineNo,
PMPT.cItemCode, PMPT.cWareHouse, PMPT.nGrnNo, CONVERT(Varchar,
PMPT.dReceiptDate, 112), ItemMaster.vDefaultGLAC, PMPT.nTrnsQty,
PMPT.nUnitPrice, PMPT.nReceiptsCurrConRate, PMPT.cReceiptsMultiDivFlag,
cLineDiscTag, nLineDisc1, nLineDisc2, nLineDisc3) AS A
WHERE (dReceiptDate >= '20060401') AND (dReceiptDate <= '20070331')
GROUP BY nGrnNo, dReceiptDate, cSupplier, cOrderNo, cWareHouse, nGrnNo, vDefaultGLAC
ORDER BY GRNNo
|||Although the query looks scary it actually isn't as complex as it looks at first sight, however I've put the query through a 3rd-party refactoring tool for clarity, see below.
It depends what you mean by 'decode'? What are you trying to ascertain by studying a diagram that studying the query wouldn't reveal?
Chris
Code Snippet
SELECT '' AS TranNo,'SC' AS JournalCode,
cSupplier AS SupplierCode,
cOrderNo AS OrderNo,
cWareHouse AS Warehouse,
nGrnNo AS GRNNo,
dReceiptDate AS BookEntryDate,
vDefaultGLAC AS AccString,
CONVERT(NUMERIC(18, 2), SUM(CASE WHEN [cReceiptsMultiDivFlag] = 'D'
THEN nTrnsQty * nUnitPrice
/ [nReceiptsCurrConRate]
ELSE nTrnsQty * nUnitPrice
* [nReceiptsCurrConRate]
END)) AS FOB,
CONVERT(NUMERIC(18, 2), SUM(CASE WHEN [cReceiptsMultiDivFlag] = 'D'
THEN LandedCost
/ [nReceiptsCurrConRate]
ELSE LandedCost
* [nReceiptsCurrConRate]
END)) AS LandedCost,
CONVERT(NUMERIC(18, 2), SUM(CASE WHEN [cLineDiscTag] = '2'
THEN (CASE WHEN [cReceiptsMultiDivFlag] = 'D'
THEN (nTrnsQty
* nLineDisc1)
/ [nReceiptsCurrConRate]
ELSE (nTrnsQty
* nLineDisc1)
* [nReceiptsCurrConRate]
END)
ELSE (CASE WHEN [cReceiptsMultiDivFlag] = 'D'
THEN (nTrnsQty
* nUnitPrice
* nLineDisc1 / 100)
/ [nReceiptsCurrConRate]
ELSE (nTrnsQty
* nUnitPrice
* nLineDisc1 / 100)
* [nReceiptsCurrConRate]
END)
END)) AS Discount1,
CONVERT(NUMERIC(18, 2), SUM(CASE WHEN [cLineDiscTag] = '2'
THEN (CASE WHEN [cReceiptsMultiDivFlag] = 'D'
THEN (nTrnsQty
* nLineDisc2)
/ [nReceiptsCurrConRate]
ELSE (nTrnsQty
* nLineDisc2)
* [nReceiptsCurrConRate]
END)
ELSE (CASE WHEN [cReceiptsMultiDivFlag] = 'D'
THEN (((nTrnsQty
* nUnitPrice)
- (nTrnsQty * nUnitPrice * nLineDisc1 / 100))
* nLineDisc2 / 100)
/ [nReceiptsCurrConRate]
ELSE (((nTrnsQty
* nUnitPrice)
- (nTrnsQty * nUnitPrice * nLineDisc1 / 100))
* nLineDisc2 / 100)
* [nReceiptsCurrConRate]
END)
END)) AS Discount2,
CONVERT(NUMERIC(18, 2), SUM(CASE WHEN [cLineDiscTag] = '2'
THEN (CASE WHEN [cReceiptsMultiDivFlag] = 'D'
THEN (nTrnsQty
* nLineDisc3)
/ [nReceiptsCurrConRate]
ELSE (nTrnsQty
* nLineDisc3)
* [nReceiptsCurrConRate]
END)
ELSE (CASE WHEN [cReceiptsMultiDivFlag] = 'D'
THEN (((nTrnsQty
* nUnitPrice)
- (nTrnsQty * nUnitPrice * nLineDisc1 / 100)
- (((nTrnsQty * nUnitPrice) - (nTrnsQty * nUnitPrice * nLineDisc1 / 100)) * nLineDisc2 / 100))
* nLineDisc3 / 100)
/ [nReceiptsCurrConRate]
ELSE (((nTrnsQty
* nUnitPrice)
- (nTrnsQty * nUnitPrice * nLineDisc1 / 100)
- (((nTrnsQty * nUnitPrice) - (nTrnsQty * nUnitPrice * nLineDisc1 / 100)) * nLineDisc2 / 100))
* nLineDisc3 / 100)
* [nReceiptsCurrConRate]
END)
END)) AS Discount3
FROM (
SELECT PMPT.cSupplier,
PMPT.cOrderNo,
PMPT.nLineNo,
PMPT.cItemCode,
PMPT.cWareHouse,
PMPT.nGrnNo,
CONVERT(Varchar, PMPT.dReceiptDate, 112) AS dReceiptDate,
ItemMaster.vDefaultGLAC,
PMPT.nTrnsQty,
PMPT.nUnitPrice,
PMPT.nReceiptsCurrConRate,
PMPT.cReceiptsMultiDivFlag,
cLineDiscTag,
nLineDisc1,
nLineDisc2,
nLineDisc3,
SUM(CASE WHEN PMGRNLLC.nBElementalCost IS NULL THEN 0
ELSE PMGRNLLC.nBElementalCost
END) AS LandedCost
FROM PMPT
LEFT OUTER JOIN PMGRNLLC ON PMPT.cCompanyNo = PMGRNLLC.cCompanyCode
AND PMPT.cSupplier = PMGRNLLC.cSupplierCode
AND PMPT.cOrderNo = PMGRNLLC.cOrderNo
AND PMPT.nLineNo = PMGRNLLC.nLineNo
AND PMPT.cItemCode = PMGRNLLC.cItemCode
AND PMPT.cWareHouse = PMGRNLLC.cWareHouse
AND PMPT.nGrnNo = PMGRNLLC.nGrnNo
LEFT OUTER JOIN ItemMaster ON PMPT.cItemCode = ItemMaster.cItemCode
LEFT OUTER JOIN OPENROWSET('SQLOLEDB', '.'; 'sa'; 'sa',
'SELECT DISTINCT CompanyCode, SupplierCode, OrderNo, WareHouse, GRNNo FROM rsiDB.dbo.GRNs')
AS B ON PMPT.cCompanyNo = B.CompanyCode
AND RTRIM(PMPT.cSupplier) = B.SupplierCode
AND PMPT.cOrderNo = B.OrderNo
AND PMPT.cWareHouse = B.WareHouse
AND PMPT.nGrnNo = B.GRNNo
WHERE (PMPT.cCompanyNo = 'GT')
AND (PMPT.nGrnNo > 0)
AND (PMPT.nTrnsQty > 0)
AND (B.SupplierCode IS NULL)
GROUP BY PMPT.cSupplier,
PMPT.cOrderNo,
PMPT.nLineNo,
PMPT.cItemCode,
PMPT.cWareHouse,
PMPT.nGrnNo,
CONVERT(Varchar, PMPT.dReceiptDate, 112),
ItemMaster.vDefaultGLAC,
PMPT.nTrnsQty,
PMPT.nUnitPrice,
PMPT.nReceiptsCurrConRate,
PMPT.cReceiptsMultiDivFlag,
cLineDiscTag,
nLineDisc1,
nLineDisc2,
nLineDisc3
) AS A
WHERE (dReceiptDate >= '20060401')
AND (dReceiptDate <= '20070331')
GROUP BY nGrnNo,
dReceiptDate,
cSupplier,
cOrderNo,
cWareHouse,
nGrnNo,
vDefaultGLAC
ORDER BY GRNNo|||
3rd-party refactoring tool is what i'm looking for, it would be much more better if it does graphical representation.
Can you give me any names?
|||This is a great product for laying out (refactoring) SQL:
http://www.red-gate.com/products/SQL_Refactor/index.htm
However it can't create graphical representations of queries.
To be honest I'm not sure you'd gain a lot from displaying that particular query graphically - in fact I reckon that doing so, and making subsequent modifications, could lead to errors due to the query's complexity. IMO in this case it would be far better to 'bite the bullet' and try to interpret and understand the query text.
Chris
Monday, March 19, 2012
how to design queries graphically?
Are there anytool, which would allow me to design queries graphically, if not design then atlease analyse them graphically.
Tool should atleast show join of 4tables graphically.
Thank You.
SQL Server Management Studio has graphical capabilities - launched in one of two ways:
1.Create a 'New Query' then right-click on the query pane and select 'Design Query in Editor...'.
2.Connect to a server in Object Explorer then navigate to the following node:
Server -> Databases -> <Your database> -> Views
Right-click on 'Views' and select 'New View...' to launch the query editor.
Chris
|||Try this......
And it's a single query, runs at a single go. I would like to know any s/w / utility (can be third party) which could decode following query.
SELECT '' AS TranNo, 'SC' AS JournalCode, cSupplier AS SupplierCode, cOrderNo AS OrderNo, cWareHouse AS Warehouse, nGrnNo AS GRNNo,
dReceiptDate AS BookEntryDate, vDefaultGLAC AS AccString, CONVERT(NUMERIC(18, 2),
SUM(CASE WHEN [cReceiptsMultiDivFlag] = 'D' THEN nTrnsQty * nUnitPrice / [nReceiptsCurrConRate] ELSE nTrnsQty * nUnitPrice * [nReceiptsCurrConRate] END)) AS FOB, CONVERT(NUMERIC(18, 2),
SUM(CASE WHEN [cReceiptsMultiDivFlag] = 'D' THEN LandedCost / [nReceiptsCurrConRate] ELSE LandedCost * [nReceiptsCurrConRate] END))
AS LandedCost, CONVERT(NUMERIC(18, 2),
SUM(CASE WHEN [cLineDiscTag] = '2' THEN (CASE WHEN [cReceiptsMultiDivFlag] = 'D' THEN (nTrnsQty * nLineDisc1)
/ [nReceiptsCurrConRate] ELSE (nTrnsQty * nLineDisc1) * [nReceiptsCurrConRate] END)
ELSE (CASE WHEN [cReceiptsMultiDivFlag] = 'D' THEN (nTrnsQty * nUnitPrice * nLineDisc1 / 100)
/ [nReceiptsCurrConRate] ELSE (nTrnsQty * nUnitPrice * nLineDisc1 / 100) * [nReceiptsCurrConRate] END) END)) AS Discount1, CONVERT(NUMERIC(18,
2), SUM(CASE WHEN [cLineDiscTag] = '2' THEN (CASE WHEN [cReceiptsMultiDivFlag] = 'D' THEN (nTrnsQty * nLineDisc2)
/ [nReceiptsCurrConRate] ELSE (nTrnsQty * nLineDisc2) * [nReceiptsCurrConRate] END)
ELSE (CASE WHEN [cReceiptsMultiDivFlag] = 'D' THEN (((nTrnsQty * nUnitPrice) - (nTrnsQty * nUnitPrice * nLineDisc1 / 100)) * nLineDisc2 / 100)
/ [nReceiptsCurrConRate] ELSE (((nTrnsQty * nUnitPrice) - (nTrnsQty * nUnitPrice * nLineDisc1 / 100)) * nLineDisc2 / 100) * [nReceiptsCurrConRate] END)
END)) AS Discount2, CONVERT(NUMERIC(18, 2),
SUM(CASE WHEN [cLineDiscTag] = '2' THEN (CASE WHEN [cReceiptsMultiDivFlag] = 'D' THEN (nTrnsQty * nLineDisc3)
/ [nReceiptsCurrConRate] ELSE (nTrnsQty * nLineDisc3) * [nReceiptsCurrConRate] END)
ELSE (CASE WHEN [cReceiptsMultiDivFlag] = 'D' THEN (((nTrnsQty * nUnitPrice) - (nTrnsQty * nUnitPrice * nLineDisc1 / 100) - (((nTrnsQty * nUnitPrice)
- (nTrnsQty * nUnitPrice * nLineDisc1 / 100)) * nLineDisc2 / 100)) * nLineDisc3 / 100) / [nReceiptsCurrConRate] ELSE (((nTrnsQty * nUnitPrice)
- (nTrnsQty * nUnitPrice * nLineDisc1 / 100) - (((nTrnsQty * nUnitPrice) - (nTrnsQty * nUnitPrice * nLineDisc1 / 100)) * nLineDisc2 / 100))
* nLineDisc3 / 100) * [nReceiptsCurrConRate] END) END)) AS Discount3
FROM (SELECT PMPT.cSupplier, PMPT.cOrderNo, PMPT.nLineNo,
PMPT.cItemCode, PMPT.cWareHouse, PMPT.nGrnNo, CONVERT(Varchar,
PMPT.dReceiptDate, 112) AS dReceiptDate, ItemMaster.vDefaultGLAC, PMPT.nTrnsQty,
PMPT.nUnitPrice, PMPT.nReceiptsCurrConRate, PMPT.cReceiptsMultiDivFlag,
cLineDiscTag, nLineDisc1, nLineDisc2, nLineDisc3, SUM(CASE WHEN PMGRNLLC.nBElementalCost IS NULL
THEN 0 ELSE PMGRNLLC.nBElementalCost END) AS LandedCost
FROM PMPT LEFT OUTER JOIN
PMGRNLLC ON PMPT.cCompanyNo = PMGRNLLC.cCompanyCode AND
PMPT.cSupplier = PMGRNLLC.cSupplierCode AND
PMPT.cOrderNo = PMGRNLLC.cOrderNo AND
PMPT.nLineNo = PMGRNLLC.nLineNo AND
PMPT.cItemCode = PMGRNLLC.cItemCode AND
PMPT.cWareHouse = PMGRNLLC.cWareHouse AND
PMPT.nGrnNo = PMGRNLLC.nGrnNo LEFT OUTER JOIN
ItemMaster ON PMPT.cItemCode = ItemMaster.cItemCode LEFT OUTER JOIN
OPENROWSET('SQLOLEDB', '.'; 'sa'; 'sa',
'SELECT DISTINCT CompanyCode, SupplierCode, OrderNo, WareHouse, GRNNo FROM rsiDB.dbo.GRNs') AS B ON
PMPT.cCompanyNo = B.CompanyCode AND RTRIM(PMPT.cSupplier) = B.SupplierCode AND
PMPT.cOrderNo = B.OrderNo AND PMPT.cWareHouse = B.WareHouse AND
PMPT.nGrnNo = B.GRNNo
WHERE (PMPT.cCompanyNo = 'GT') AND (PMPT.nGrnNo > 0) AND (PMPT.nTrnsQty > 0) AND
(B.SupplierCode IS NULL)
GROUP BY PMPT.cSupplier, PMPT.cOrderNo, PMPT.nLineNo,
PMPT.cItemCode, PMPT.cWareHouse, PMPT.nGrnNo, CONVERT(Varchar,
PMPT.dReceiptDate, 112), ItemMaster.vDefaultGLAC, PMPT.nTrnsQty,
PMPT.nUnitPrice, PMPT.nReceiptsCurrConRate, PMPT.cReceiptsMultiDivFlag,
cLineDiscTag, nLineDisc1, nLineDisc2, nLineDisc3) AS A
WHERE (dReceiptDate >= '20060401') AND (dReceiptDate <= '20070331')
GROUP BY nGrnNo, dReceiptDate, cSupplier, cOrderNo, cWareHouse, nGrnNo, vDefaultGLAC
ORDER BY GRNNo
|||Although the query looks scary it actually isn't as complex as it looks at first sight, however I've put the query through a 3rd-party refactoring tool for clarity, see below.
It depends what you mean by 'decode'? What are you trying to ascertain by studying a diagram that studying the query wouldn't reveal?
Chris
Code Snippet
SELECT '' AS TranNo,'SC' AS JournalCode,
cSupplier AS SupplierCode,
cOrderNo AS OrderNo,
cWareHouse AS Warehouse,
nGrnNo AS GRNNo,
dReceiptDate AS BookEntryDate,
vDefaultGLAC AS AccString,
CONVERT(NUMERIC(18, 2), SUM(CASE WHEN [cReceiptsMultiDivFlag] = 'D'
THEN nTrnsQty * nUnitPrice
/ [nReceiptsCurrConRate]
ELSE nTrnsQty * nUnitPrice
* [nReceiptsCurrConRate]
END)) AS FOB,
CONVERT(NUMERIC(18, 2), SUM(CASE WHEN [cReceiptsMultiDivFlag] = 'D'
THEN LandedCost
/ [nReceiptsCurrConRate]
ELSE LandedCost
* [nReceiptsCurrConRate]
END)) AS LandedCost,
CONVERT(NUMERIC(18, 2), SUM(CASE WHEN [cLineDiscTag] = '2'
THEN (CASE WHEN [cReceiptsMultiDivFlag] = 'D'
THEN (nTrnsQty
* nLineDisc1)
/ [nReceiptsCurrConRate]
ELSE (nTrnsQty
* nLineDisc1)
* [nReceiptsCurrConRate]
END)
ELSE (CASE WHEN [cReceiptsMultiDivFlag] = 'D'
THEN (nTrnsQty
* nUnitPrice
* nLineDisc1 / 100)
/ [nReceiptsCurrConRate]
ELSE (nTrnsQty
* nUnitPrice
* nLineDisc1 / 100)
* [nReceiptsCurrConRate]
END)
END)) AS Discount1,
CONVERT(NUMERIC(18, 2), SUM(CASE WHEN [cLineDiscTag] = '2'
THEN (CASE WHEN [cReceiptsMultiDivFlag] = 'D'
THEN (nTrnsQty
* nLineDisc2)
/ [nReceiptsCurrConRate]
ELSE (nTrnsQty
* nLineDisc2)
* [nReceiptsCurrConRate]
END)
ELSE (CASE WHEN [cReceiptsMultiDivFlag] = 'D'
THEN (((nTrnsQty
* nUnitPrice)
- (nTrnsQty * nUnitPrice * nLineDisc1 / 100))
* nLineDisc2 / 100)
/ [nReceiptsCurrConRate]
ELSE (((nTrnsQty
* nUnitPrice)
- (nTrnsQty * nUnitPrice * nLineDisc1 / 100))
* nLineDisc2 / 100)
* [nReceiptsCurrConRate]
END)
END)) AS Discount2,
CONVERT(NUMERIC(18, 2), SUM(CASE WHEN [cLineDiscTag] = '2'
THEN (CASE WHEN [cReceiptsMultiDivFlag] = 'D'
THEN (nTrnsQty
* nLineDisc3)
/ [nReceiptsCurrConRate]
ELSE (nTrnsQty
* nLineDisc3)
* [nReceiptsCurrConRate]
END)
ELSE (CASE WHEN [cReceiptsMultiDivFlag] = 'D'
THEN (((nTrnsQty
* nUnitPrice)
- (nTrnsQty * nUnitPrice * nLineDisc1 / 100)
- (((nTrnsQty * nUnitPrice) - (nTrnsQty * nUnitPrice * nLineDisc1 / 100)) * nLineDisc2 / 100))
* nLineDisc3 / 100)
/ [nReceiptsCurrConRate]
ELSE (((nTrnsQty
* nUnitPrice)
- (nTrnsQty * nUnitPrice * nLineDisc1 / 100)
- (((nTrnsQty * nUnitPrice) - (nTrnsQty * nUnitPrice * nLineDisc1 / 100)) * nLineDisc2 / 100))
* nLineDisc3 / 100)
* [nReceiptsCurrConRate]
END)
END)) AS Discount3
FROM (
SELECT PMPT.cSupplier,
PMPT.cOrderNo,
PMPT.nLineNo,
PMPT.cItemCode,
PMPT.cWareHouse,
PMPT.nGrnNo,
CONVERT(Varchar, PMPT.dReceiptDate, 112) AS dReceiptDate,
ItemMaster.vDefaultGLAC,
PMPT.nTrnsQty,
PMPT.nUnitPrice,
PMPT.nReceiptsCurrConRate,
PMPT.cReceiptsMultiDivFlag,
cLineDiscTag,
nLineDisc1,
nLineDisc2,
nLineDisc3,
SUM(CASE WHEN PMGRNLLC.nBElementalCost IS NULL THEN 0
ELSE PMGRNLLC.nBElementalCost
END) AS LandedCost
FROM PMPT
LEFT OUTER JOIN PMGRNLLC ON PMPT.cCompanyNo = PMGRNLLC.cCompanyCode
AND PMPT.cSupplier = PMGRNLLC.cSupplierCode
AND PMPT.cOrderNo = PMGRNLLC.cOrderNo
AND PMPT.nLineNo = PMGRNLLC.nLineNo
AND PMPT.cItemCode = PMGRNLLC.cItemCode
AND PMPT.cWareHouse = PMGRNLLC.cWareHouse
AND PMPT.nGrnNo = PMGRNLLC.nGrnNo
LEFT OUTER JOIN ItemMaster ON PMPT.cItemCode = ItemMaster.cItemCode
LEFT OUTER JOIN OPENROWSET('SQLOLEDB', '.'; 'sa'; 'sa',
'SELECT DISTINCT CompanyCode, SupplierCode, OrderNo, WareHouse, GRNNo FROM rsiDB.dbo.GRNs')
AS B ON PMPT.cCompanyNo = B.CompanyCode
AND RTRIM(PMPT.cSupplier) = B.SupplierCode
AND PMPT.cOrderNo = B.OrderNo
AND PMPT.cWareHouse = B.WareHouse
AND PMPT.nGrnNo = B.GRNNo
WHERE (PMPT.cCompanyNo = 'GT')
AND (PMPT.nGrnNo > 0)
AND (PMPT.nTrnsQty > 0)
AND (B.SupplierCode IS NULL)
GROUP BY PMPT.cSupplier,
PMPT.cOrderNo,
PMPT.nLineNo,
PMPT.cItemCode,
PMPT.cWareHouse,
PMPT.nGrnNo,
CONVERT(Varchar, PMPT.dReceiptDate, 112),
ItemMaster.vDefaultGLAC,
PMPT.nTrnsQty,
PMPT.nUnitPrice,
PMPT.nReceiptsCurrConRate,
PMPT.cReceiptsMultiDivFlag,
cLineDiscTag,
nLineDisc1,
nLineDisc2,
nLineDisc3
) AS A
WHERE (dReceiptDate >= '20060401')
AND (dReceiptDate <= '20070331')
GROUP BY nGrnNo,
dReceiptDate,
cSupplier,
cOrderNo,
cWareHouse,
nGrnNo,
vDefaultGLAC
ORDER BY GRNNo|||
3rd-party refactoring tool is what i'm looking for, it would be much more better if it does graphical representation.
Can you give me any names?
|||This is a great product for laying out (refactoring) SQL:
http://www.red-gate.com/products/SQL_Refactor/index.htm
However it can't create graphical representations of queries.
To be honest I'm not sure you'd gain a lot from displaying that particular query graphically - in fact I reckon that doing so, and making subsequent modifications, could lead to errors due to the query's complexity. IMO in this case it would be far better to 'bite the bullet' and try to interpret and understand the query text.
Chris
Monday, March 12, 2012
How to derive a calculated from a measure and a dimension attributes?
Hi,Does anyone know how to write an MDX queries that can enable calculation of a measure value and a dimension attributes? Eg: Measure Revenue * Conversion Rate (from dimension attribute).Reason that this conversion rate is not in the fact layer is that the conversion rate is dependant to the Currency the user want to convert to during run-time.Thanks.JLim
Hello
Here you have the mdx query performed on the Food Mart 2000 Sales cube.
with member [Measures].[Modified Unit Sales] as 'iif([Promotions].CurrentMember.Name="Dollar Days",[Measures].[Unit Sales]*0.9,[Measures].[Unit Sales])'
select {[Measures].[Modified Unit Sales],[Measures].[Unit Sales]} on axis(0) , {[Promotions].DefaultMember.Children} on axis(1)
from [sales]
Due to possible performance problems please consider the option to create one measure per each currency.
Regards
Maciej Kiewra
Subscribe to:
Posts (Atom)