i am really sorry but just began to work on SQL I did some work on SQL before but just updates, deletes and inserts now I do need to optimize the following query which takes 7 seconds for 70 items.
One table which has line items, and another table which includes suppliers offers and also a table for warehouse information.
I do need to show line items with best 3 suppliers offer (offer 1- offer 1 name - offer 1 comment ) and also warehouse information
Could you please help me about it and teach me a way to make my queries fast.
Thank you,
declare @usd nvarchar(10)
declare @gbp nvarchar(10)
declare @euro nvarchar(10)
set @usd=(select top 1 KUR from _APPA_WEB_KUR WHERE CURRENCY = 'USD' ORDER BY INCKEYNO DESC)
set @gbp=(select top 1 KUR from _APPA_WEB_KUR WHERE CURRENCY = 'GBP' ORDER BY INCKEYNO DESC)
set @euro=(select top 1 KUR from _APPA_WEB_KUR WHERE CURRENCY = 'EURO' ORDER BY INCKEYNO DESC)
SELECT
item.INCKEYNO, COMMENT, item.SIRA, item.[FILE_NUMBER], [REFERANCE_NO],
[GROUP_ID], [IMPA_CODE], [DESCRIPTION], [UNIT], [QTTY],
item.[CREATED_BY], item.[DATE], removed,
(SELECT TOP 1 (isnull
((SELECT cast(sum(STHAR_GCMIK) AS int)FROM TBLSTHAR
WHERE STHAR_GCKOD = 'G' AND TBLSTHAR.STOK_KODU LIKE ('%' + item.IMPA_CODE + '%') AND sthar_htur != 'L' AND SUBE_KODU = '4'), 0) - isnull
((SELECT cast(sum(STHAR_GCMIK) AS int)
FROM TBLSTHAR
WHERE STHAR_GCKOD = 'C' AND TBLSTHAR.STOK_KODU LIKE ('%' + item.IMPA_CODE + '%') AND sthar_htur != 'L' AND SUBE_KODU = '4'), 0)) AS bakiye
FROM TBLSTHAR) AS depo4,
(SELECT TOP 1 (isnull
((SELECT cast(sum(STHAR_GCMIK) AS int)
FROM TBLSTHAR WHERE STHAR_GCKOD = 'G' AND TBLSTHAR.STOK_KODU LIKE ('%' + item.IMPA_CODE + '%') AND sthar_htur != 'L' AND SUBE_KODU = '2'), 0)
- isnull ((SELECT cast(sum(STHAR_GCMIK) AS int) FROM TBLSTHAR WHERE STHAR_GCKOD = 'C' AND
TBLSTHAR.STOK_KODU LIKE ('%' + item.IMPA_CODE + '%') AND sthar_htur != 'L' AND SUBE_KODU = '2'), 0)) AS bakiye
FROM TBLSTHAR) AS depo2,
(SELECT TOP 1 ([SECILI_FIYAT])
FROM [dbo].[_APPA_SUPPLIER_OFFER_SECILI_FIYATLI_VIEW]
WHERE FILE_NUMBER = item.file_number AND IMPA_CODE = item.IMPA_CODE AND PRICE IS NOT NULL AND REFERANCE_NO = item.REFERANCE_NO
ORDER BY CAST(isnull(SECILI_FIYAT, 0) AS DECIMAL(18, 2)) ASC) AS OFFER1,
(SELECT TOP 1 SUPPLIER_ID
FROM [dbo].[_APPA_SUPPLIER_OFFER_SECILI_FIYATLI_VIEW]
WHERE FILE_NUMBER = item.file_number AND IMPA_CODE = item.IMPA_CODE AND PRICE IS NOT NULL AND REFERANCE_NO = item.REFERANCE_NO
ORDER BY CAST(isnull(SECILI_FIYAT, 0) AS DECIMAL(18, 2)) ASC) AS order1_supplier,
(SELECT TOP 1 COMMENT
FROM [dbo].[_APPA_SUPPLIER_OFFER_SECILI_FIYATLI_VIEW]
WHERE FILE_NUMBER = item.file_number AND IMPA_CODE = item.IMPA_CODE AND PRICE IS NOT NULL AND REFERANCE_NO = item.REFERANCE_NO
ORDER BY CAST(isnull(SECILI_FIYAT, 0) AS DECIMAL(18, 2)) ASC) AS order1_comment,
(SELECT dbo.trk(cari_isim)
FROM TBLCASABIT
WHERE CARI_KOD = (SELECT SUPPLIER_ID
FROM (SELECT SUPPLIER_ID, PRICE, ROW_NUMBER() OVER (ORDER BY CAST(isnull(SECILI_FIYAT, 0) AS DECIMAL(18, 2)) ASC) AS Salary_Order
FROM [dbo].[_APPA_SUPPLIER_OFFER_SECILI_FIYATLI_VIEW]
WHERE FILE_NUMBER = item.file_number AND IMPA_CODE = item.IMPA_CODE AND PRICE IS NOT NULL AND REFERANCE_NO = item.REFERANCE_NO) DT
WHERE DT.Salary_Order = 1)) AS offer1cari,
(SELECT [SECILI_FIYAT]
FROM (SELECT SECILI_FIYAT, ROW_NUMBER() OVER (ORDER BY CAST(isnull(SECILI_FIYAT, 0) AS DECIMAL(18, 2)) ASC) AS Salary_Order
FROM [dbo].[_APPA_SUPPLIER_OFFER_SECILI_FIYATLI_VIEW]
WHERE FILE_NUMBER = item.file_number AND IMPA_CODE = item.IMPA_CODE AND PRICE IS NOT NULL AND REFERANCE_NO = item.REFERANCE_NO) DT
WHERE DT.Salary_Order = 2) AS OFFER2,
(SELECT SUPPLIER_ID
FROM (SELECT SUPPLIER_ID, SECILI_FIYAT, ROW_NUMBER() OVER (ORDER BY CAST(isnull(SECILI_FIYAT, 0) AS DECIMAL(18, 2)) ASC) AS Salary_Order
FROM [dbo].[_APPA_SUPPLIER_OFFER_SECILI_FIYATLI_VIEW]
WHERE FILE_NUMBER = item.file_number AND IMPA_CODE = item.IMPA_CODE AND PRICE IS NOT NULL AND REFERANCE_NO = item.REFERANCE_NO) DT
WHERE DT.Salary_Order = 2) AS order2_supplier,
(SELECT dbo.trk(cari_isim)
FROM TBLCASABIT
WHERE CARI_KOD =(SELECT SUPPLIER_ID
FROM (SELECT SUPPLIER_ID, PRICE, ROW_NUMBER() OVER (ORDER BY CAST(isnull(SECILI_FIYAT, 0) AS DECIMAL(18, 2)) ASC) AS Salary_Order
FROM [dbo].[_APPA_SUPPLIER_OFFER_SECILI_FIYATLI_VIEW]
WHERE FILE_NUMBER = item.file_number AND IMPA_CODE = item.IMPA_CODE AND PRICE IS NOT NULL AND REFERANCE_NO = item.REFERANCE_NO) DT
WHERE DT.Salary_Order = 2)) AS offer2cari,
(SELECT COMMENT
FROM (SELECT COMMENT, ROW_NUMBER() OVER (ORDER BY CAST(isnull(SECILI_FIYAT, 0) AS DECIMAL(18, 2)) ASC) AS Salary_Order
FROM [dbo].[_APPA_SUPPLIER_OFFER_SECILI_FIYATLI_VIEW]
WHERE FILE_NUMBER = item.file_number AND IMPA_CODE = item.IMPA_CODE AND PRICE IS NOT NULL AND REFERANCE_NO = item.REFERANCE_NO) DT
WHERE DT.Salary_Order = 2) AS order2_comment,
(SELECT [SECILI_FIYAT]
FROM (SELECT [SECILI_FIYAT], vat, ROW_NUMBER() OVER (ORDER BY CAST(isnull(SECILI_FIYAT, 0) AS DECIMAL(18, 2)) ASC) AS Salary_Order
FROM [dbo].[_APPA_SUPPLIER_OFFER_SECILI_FIYATLI_VIEW]
WHERE FILE_NUMBER = item.file_number AND IMPA_CODE = item.IMPA_CODE AND PRICE IS NOT NULL AND REFERANCE_NO = item.REFERANCE_NO) DT
WHERE DT.Salary_Order = 3) AS OFFER3,
(SELECT SUPPLIER_ID
FROM (SELECT SUPPLIER_ID, SECILI_FIYAT, ROW_NUMBER() OVER (ORDER BY CAST(isnull(SECILI_FIYAT, 0) AS DECIMAL(18, 2)) ASC) AS Salary_Order
FROM [dbo].[_APPA_SUPPLIER_OFFER_SECILI_FIYATLI_VIEW]
WHERE FILE_NUMBER = item.file_number AND IMPA_CODE = item.IMPA_CODE AND PRICE IS NOT NULL AND REFERANCE_NO = item.REFERANCE_NO) DT
WHERE DT.Salary_Order = 3) AS order3_supplier,
(SELECT dbo.trk(cari_isim)
FROM TBLCASABIT
WHERE CARI_KOD =(SELECT SUPPLIER_ID
FROM (SELECT SUPPLIER_ID, SECILI_FIYAT, ROW_NUMBER() OVER (ORDER BY CAST(isnull(SECILI_FIYAT, 0) AS DECIMAL(18, 2)) ASC) AS Salary_Order
FROM [dbo].[_APPA_SUPPLIER_OFFER_SECILI_FIYATLI_VIEW]
WHERE FILE_NUMBER = item.file_number AND IMPA_CODE = item.IMPA_CODE AND PRICE IS NOT NULL AND REFERANCE_NO = item.REFERANCE_NO) DT
WHERE DT.Salary_Order = 3)) AS offer3cari,
(SELECT COMMENT
FROM (SELECT COMMENT, ROW_NUMBER() OVER (ORDER BY CAST(isnull(SECILI_FIYAT, 0) AS DECIMAL(18, 2)) ASC) AS Salary_Order
FROM [dbo].[_APPA_SUPPLIER_OFFER_SECILI_FIYATLI_VIEW]
WHERE FILE_NUMBER = item.file_number AND IMPA_CODE = item.IMPA_CODE AND PRICE IS NOT NULL AND REFERANCE_NO = item.REFERANCE_NO) DT
WHERE DT.Salary_Order = 3) AS order3_comment, @euro AS EURO, @gbp AS GBP, @usd AS USD, mas.CURRENCY,
'0' depo4_fiyat,'0' depo2_fiyat, item.price, item.supplier
FROM [dbo].[_APPA_WEB_PURCHASE_OFFER_LINE_ITEMS_2] item LEFT OUTER JOIN
dbo._APPA_WEB_PURCHASE_OFFER_MASTER AS mas ON mas.FILE_NUMBER = item.FILE_NUMBER
WHERE (item.removed != 1 OR
item.removed IS NULL)
Your code is quite difficult to read. I did my best to refactor accurately, but I don't have any way to check. So here it is:
;WITH line_items AS (
-- Read all the lines that we will need
SELECT *
FROM [dbo].[_APPA_WEB_PURCHASE_OFFER_LINE_ITEMS_2] item
WHERE COALESCE(item.removed,0) != 1
),
suppliers AS (
-- Get a ranked list of supplier, offers and comments
SELECT v.COMMENT,
v.SUPPLIER_ID,
v.[SECILI_FIYAT],
ROW_NUMBER() OVER(ORDER BY CAST(isnull(v.SECILI_FIYAT, 0) AS DECIMAL(18, 2)) ASC) AS Salary_Order,
v.FILE_NUMBER,
v.IMPA_CODE,
v.REFERANCE_NO,
dbo.trk(b.cari_isim) AS cari
FROM [dbo].[_APPA_SUPPLIER_OFFER_SECILI_FIYATLI_VIEW] v
INNER JOIN line_items item
ON v.FILE_NUMBER = item.file_number
AND v.IMPA_CODE = item.IMPA_CODE
AND v.REFERANCE_NO = item.REFERANCE_NO
INNER JOIN TBLCASABIT b
ON b.CARI_KOD = v.SUPPLIER_ID
WHERE v.PRICE IS NOT NULL
),
t_STHAR AS (
SELECT
CAST( ISNULL(SUM(CASE WHEN st.SUBE_KODU = '2' AND st.STHAR_GCKOD = 'G' THEN st.STHAR_GCMIK ELSE 0 END),0) -
ISNULL(SUM(CASE WHEN st.SUBE_KODU = '2' AND st.STHAR_GCKOD = 'C' THEN st.STHAR_GCMIK ELSE 0 END),0) AS INT) AS depo2,
CAST( ISNULL(SUM(CASE WHEN st.SUBE_KODU = '4' AND st.STHAR_GCKOD = 'G' THEN st.STHAR_GCMIK ELSE 0 END),0) -
ISNULL(SUM(CASE WHEN st.SUBE_KODU = '4' AND st.STHAR_GCKOD = 'C' THEN st.STHAR_GCMIK ELSE 0 END),0) AS INT) AS depo4
FROM TBLSTHAR
WHERE STHAR_HTUR != 'L'
AND EXISTS (
SELECT 1
FROM line_items
WHERE STOK_KODU LIKE('%'+item.IMPA_CODE+'%')
)
)
SELECT item.INCKEYNO,
COMMENT,
item.SIRA,
item.[FILE_NUMBER],
[REFERANCE_NO],
[GROUP_ID],
[IMPA_CODE],
[DESCRIPTION],
[UNIT],
[QTTY],
item.[CREATED_BY],
item.[DATE],
removed,
st.depo2,
st.depo4,
-- Use (JOIN, MAX, GROUP BY) method to transpose rows to columns
MAX(CASE WHEN s.Salary_Order = 1 THEN s.[SECILI_FIYAT] END) AS OFFER1,
MAX(CASE WHEN s.Salary_Order = 1 THEN s.SUPPLIER_ID END) AS order1_supplier,
MAX(CASE WHEN s.Salary_Order = 1 THEN s.cari END) AS offer1cari,
MAX(CASE WHEN s.Salary_Order = 1 THEN s.COMMENT END) AS order1_comment,
MAX(CASE WHEN s.Salary_Order = 2 THEN s.[SECILI_FIYAT] END) AS OFFER1,
MAX(CASE WHEN s.Salary_Order = 2 THEN s.SUPPLIER_ID END) AS order2_supplier,
MAX(CASE WHEN s.Salary_Order = 2 THEN s.cari END) AS offer2cari,
MAX(CASE WHEN s.Salary_Order = 2 THEN s.COMMENT END) AS order2_comment,
MAX(CASE WHEN s.Salary_Order = 3 THEN s.[SECILI_FIYAT] END) AS OFFER3,
MAX(CASE WHEN s.Salary_Order = 3 THEN s.SUPPLIER_ID END) AS order3_supplier,
MAX(CASE WHEN s.Salary_Order = 3 THEN s.cari END) AS offer3cari, ---
MAX(CASE WHEN s.Salary_Order = 3 THEN s.COMMENT END) AS order3_comment,
@euro AS EURO,
@gbp AS GBP,
@usd AS USD,
mas.CURRENCY,
'0' depo4_fiyat,
'0' depo2_fiyat,
item.price,
item.supplier
FROM line_items item
LEFT OUTER JOIN dbo._APPA_WEB_PURCHASE_OFFER_MASTER mas
ON mas.FILE_NUMBER = item.FILE_NUMBER
LEFT JOIN suppliers s
ON item.FILE_NUMBER = s.FILE_NUMBER
AND item.IMPA_CODE = s.IMPA_CODE
AND item.REFERANCE_NO = s.REFERANCE_NO
CROSS APPLY t_STHAR st
GROUP BY item.INCKEYNO,
COMMENT,
item.SIRA,
item.[FILE_NUMBER],
[REFERANCE_NO],
[GROUP_ID],
[IMPA_CODE],
[DESCRIPTION],
[UNIT],
[QTTY],
item.[CREATED_BY],
item.[DATE],
removed,
st.depo2,
st.depo4,
mas.CURRENCY,
item.price,
item.supplier
;