Reduce execution time - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Reduce execution time

Author

Topic

vbuser26
Starting Member

21 Posts

Posted - 2007-05-21 : 22:13:28

Is there a way how to reduce the execution time for a 100,000 records and 52 columns using select query?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-21 : 22:18:27

yes.
- Optimized your query
- Ensure proper index

Post your table DDL, and the query here

vbuser26
Starting Member

21 Posts

Posted - 2007-05-21 : 22:25:38

Can anybody help me in getting the same output without putting a distinct in the query because i figured it out that the distinct is also the one who take the execution so long.

below is the query tnx.

SELECT DISTINCT
PO_HDR.PO_ID AS [PO_ID],
PO_HDR.MESSAGE_ID AS [MESSAGE_ID],
--RIGHT('00' + LTRIM(CONVERT(VARCHAR(2), PO_HDR.PO_STATUS)),2) AS [POSTATUS],
(CASE isnull(PO_DET.LINE_NO,'') WHEN '' THEN '99' ELSE RIGHT('00' + LTRIM(CONVERT(VARCHAR(2), PO_HDR.PO_STATUS)),2) END) AS [POSTATUS], -- rc 11/04/2002

(CASE PO_HDR.PO_TYPE WHEN 1 THEN 'BulkPO' ELSE CASE PO_HDR.PO_TYPE WHEN 2 THEN 'QRPO' ELSE CASE PO_HDR.PO_TYPE WHEN 3 THEN 'ProdPO' ELSE 'BulkPO' END END END) AS [POTYPE],
PO_HDR.PO_NUMBER AS [PO_NUMBER],
PO_HDR.PO_DATE AS [PO_DATE],
PO_HDR.PO_REL_NO AS [REVISION_NUMBER],
'' AS [PO_REVISION_DATE],
--A.CODE AS [INTERNAL_BUYER_CODE], -- rc 11/04/2002 --commented by HAJ 05/16/2003
(CASE PO_DET.UOM WHEN 'CA' THEN PO_DET.PACK_GROUP ELSE '' END) AS [INTERNAL_BUYER_CODE],
PO_HDR.SUPPLIER_CODE AS [VENDOR_CODE], -- rc 11/04/2002 -- rc 11/06/2002
PO_HDR.DEPT_DESC AS [DEPT_DESC], -- rc 11/04/2002
'' AS [ORDER_TYPE], -- rc 11/04/2002
'' AS [SEASON], -- rc 11/04/2002
PO_HDR.DELIVERY_TERMS AS [DELIVERY_TERMS], -- rc 11/04/2002
'' AS [DISTRIBUTION_CENTER], -- rc 11/04/2002
SHIP_TO_CODE AS [DC_CODE], -- rc 11/04/2002
PO_HDR.SHIP_TO_NAME AS [SHIP_TO_NAME], -- rc 11/04/2002
PO_HDR.SHIP_TO_COUNTRY AS [SHIP_TO_COUNTRY_CODE], -- rc 11/04/2002
PO_HDR.PAYMENT_TYPE AS [PAYMENT_METHOD], -- rc 11/04/2002
PO_DET.LINE_NO AS [LINE_NUMBER], -- rc 11/04/2002
PO_DET.QUANTITY AS [LINE_ITEM_QTY], -- rc 11/04/2002
PO_DET.UOM AS [UOM], -- rc 11/04/2002
PO_DET.UNIT_PRICE AS [ITEM_UNIT_PRICE], -- rc 11/04/2002
PO_HDR.CURRENCY_CODE AS [CURRENCY_CODE], -- rc 11/04/2002
PO_DET.STYLE_DESC AS [STYLE_DESC], -- rc 11/04/2002 -- CUSTOMER STYLE CODE
PO_DET.STYLE_NO AS [STYLE_CODE], -- rc 11/04/2002 -- CUSTOMER STYLE DESC
(CASE isnull(PO_DET.COLOR_CODE,'') WHEN '' THEN PO_DET.COLOR_DESC ELSE PO_DET.COLOR_CODE END) AS [COLOR_CODE], -- rc 11/04/2002
(CASE isnull(PO_DET.COLOR_DESC,'') WHEN '' THEN PO_DET.COLOR_CODE ELSE PO_DET.COLOR_DESC END) AS [COLOR_DESC], -- rc 11/04/2002
PO_DET.SIZE_CODE AS [SIZE_CODE], -- rc 11/04/2002
PO_DET.SIZE_CODE AS [SIZE_DESC], -- rc 11/04/2002
PO_DET.UPC_CODE AS [UPC_EAN_CODE], -- rc 11/04/2002
PO_HDR.EXPORT_DATE AS [START_SHIP_DATE], -- rc 11/04/2002
PO_HDR.DUE_DATE AS [START_CANCEL_DATE], -- rc 11/04/2002
PO_HDR.DUE_DATE AS [DELIVERY_DATE], -- rc 11/04/2002 -- NEW -- rc 11/06/2002
PO_DET.DELIVERY_DATE AS [FREIGHT_DATE], -- rc 11/04/2002
(CASE WHEN convert(nvarchar(10), isnull(PO_DET.SHIP_DATE, ''), 101) = '01/01/1900' THEN PO_HDR.EX_FACTORY_DATE ELSE PO_DET.SHIP_DATE END) AS [EX_FTY_DATE], -- rc 11/04/2002
-- ZJO 042005(CASE WHEN convert(nvarchar(10), isnull(PO_DET.DELIVERY_DATE, ''), 101) = '01/01/1900' THEN PO_HDR.DUE_DATE ELSE PO_DET.DELIVERY_DATE END) AS [IN_DC_DATE], -- rc 11/04/2002
CASE PO_HDR.PO_TYPE WHEN 3 THEN PO_HDR.EXPORT_DATE
ELSE(CASE WHEN convert(nvarchar(10), isnull(PO_DET.DELIVERY_DATE, ''), 101) = '01/01/1900' THEN PO_HDR.DUE_DATE ELSE PO_DET.DELIVERY_DATE END)
END AS [IN_DC_DATE],
(
LTRIM(RTRIM((DBO.F_STORE_NO_QTY(PO_HDR.PO_ID,PO_DET.LINE_NO))))
+ LTRIM(RTRIM(dbo.GetQualifierValue(PO_HDR.PO_ID, PO_DET.PO_DET_ID, 'OIC'))) -- ZJO 090303
+ LTRIM(RTRIM(dbo.GetQualifierValue(PO_HDR.PO_ID, PO_DET.PO_DET_ID, 'E9'))) -- ZJO 090303
)AS [STORE_NO_QTY],
A.TRADING_ID AS [CODES_TRADING_ID],
A.ENTITY_ID AS [CODES_ENTITY_ID],
PO_DET.RESALE_PRICE AS [RESALE_PRICE] -- ZJO 090303
, dbo.GetQualifierValue(PO_HDR.PO_ID, PO_DET.PO_DET_ID, 'MR') AS [MIC] -- ZJO 090303
, dbo.GetQualifierValue(PO_HDR.PO_ID, PO_DET.PO_DET_ID, 'BT') AS [GROUP_CODE]-- ZJO 090303
, PO_HDR.PAYMENT_TYPE AS [TERM_TYPE_CODE]
, PO_HDR.TOTAL_DAYS_DUE AS [TERM_NET_DAYS]
, '' AS [TERM_DISCOUNT]
, '' AS [TERM_DISC_DAYS]

FROM PO_HDR
LEFT OUTER JOIN PO_DET ON PO_HDR.PO_ID = PO_DET.PO_ID, XOTRACK.DBO.A_CODES A
WHERE PO_HDR.PO_NUMBER = '0085790301'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-23 : 21:52:45

quote:
FROM PO_HDR
LEFT OUTER JOIN PO_DET ON PO_HDR.PO_ID = PO_DET.PO_ID, XOTRACK.DBO.A_CODES A

Cross join to A_CODES ?

vbuser26
Starting Member

21 Posts

Posted - 2007-05-23 : 22:03:59

I dont get it sorry.
Can you provide me the proper codes tnx.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-23 : 22:14:37

what is the relationship between PO_HDR, PO_DET and A_CODES ?


FROM PO_HDR
LEFT OUTER JOIN PO_DET ON PO_HDR.PO_ID = PO_DET.PO_ID
WHERE PO_HDR.PO_NUMBER = '0085790301'

The above query will give you records from PO_HDR and PO_DET for PO_NUMBER 0085790301

FROM PO_HDR
LEFT OUTER JOIN PO_DET ON PO_HDR.PO_ID = PO_DET.PO_ID, XOTRACK.DBO.A_CODES A
WHERE PO_HDR.PO_NUMBER = '0085790301'

Adding the A_CODES to the join without specifying join type, in you case just a comma ',' in fact you are creating a cross join of result of ( PO_HDR left join PO_DET ) to A_CODES. Which means if the result of PO_HDR and PO_DET gives you 10 rows (for PO_NUMBER 0085790301) and A_CODES has 1000 rows of records, the query (without distinct) will gives you 10 x 1000 rows.

What you need to change is use a JOIN for the A_CODES table.

vbuser26
Starting Member

21 Posts

Posted - 2007-05-23 : 23:00:41

Hi again i read the tables and i see that PO_HDR and PO_DET have a relation but the A_CODES is only an additional no ralation from the 2 any idea pls.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-23 : 23:07:55

There must be a relationship between PO_HDR and PO_DET or else you are getting all permutation of (PO_HDR + PO_DET) to A_CODES.

A_CODES is only reference over here


A.TRADING_ID AS [CODES_TRADING_ID],
A.ENTITY_ID AS [CODES_ENTITY_ID],

What are these 2 columns for ? Try to mask off these 2 columns and the A_CODES from your query and run your query.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources