Any kind of tuning is possible....

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)

Any kind of tuning is possible....

Author

Topic

avijit111@gmail.com
Yak Posting Veteran

55 Posts

Posted - 2010-03-31 : 04:51:15

In the below code taking too much time to execute. where two po's are getting update taking much time to execute .please tell me any kind of tuning i can their,which make the code execute more faster.
/*************************************************/
/*** To update the two POs for this indent ***/
UPDATE #pur_pr_list
SET status = 'C'

SET ROWCOUNT 1
WHILE EXISTS(SELECT '*' FROM #pur_pr_list WHERE status = 'C')
BEGIN
UPDATE #pur_pr_list
SET status = 'D'
WHERE Status = 'C'

SELECT @po_no = NULL,
@po_serial = NULL,
@po_date = NULL,
@qty_covered = NULL,
@po1_status = null

SELECT @po_no = a.po_no,
@po_serial = b.po_serial,
@po_date = a.po_date,
@qty_covered = c.covered_qty_puom,
@po1_status = a.po_status
FROM pur_po_header a (nolock),
pur_po_detail b (nolock),
pur_po_indent c (nolock)
WHERE a.company_code = @company_no
and a.locn_no = @location_no
AND a.company_code = b.company_code
AND a.company_code = c.company_code
AND a.locn_no = b.locn_no
AND a.locn_no = c.locn_no
AND a.po_no = b.po_no
AND a.po_no = c.po_no
and b.po_serial = c.po_serial
AND c.indent_no = (SELECT indent_no FROM #pur_pr_list WHERE status = 'D')
AND c.indent_serial = (SELECT serial_no FROM #pur_pr_list WHERE status = 'D')
AND a.po_status <> 'F'

UPDATE #pur_pr_list
SET po_no1 = @po_no,
po_date1 = convert(char(10), @po_date, 103),
po_serial1 = @po_serial,
qty_covered1 = @qty_covered,
po1_status = @po1_status
WHERE status = 'D'

SELECT @po_no = NULL, @po_serial = NULL,
@po_date = NULL, @qty_covered = NULL,
@po2_status = null

SELECT @po_no = a.po_no,
@po_serial = b.po_serial,
@po_date = a.po_date,
@qty_covered = c.covered_qty_puom,
@po2_status = a.po_status
FROM pur_po_header a (nolock),
pur_po_detail b (nolock),
pur_po_indent c (nolock)
WHERE a.company_code = @company_no
and a.locn_no = @location_no
AND a.company_code = b.company_code
AND a.company_code = c.company_code
AND a.locn_no = b.locn_no
AND a.locn_no = c.locn_no
AND a.po_no = b.po_no
AND a.po_no = c.po_no
and b.po_serial = c.po_serial
AND c.indent_no = (SELECT indent_no FROM #pur_pr_list WHERE status = 'D')
AND c.indent_serial = (SELECT serial_no FROM #pur_pr_list WHERE status = 'D')
AND (b.po_no <> (SELECT po_no1 FROM #pur_pr_list WHERE status = 'D')
OR b.po_serial <> (SELECT po_serial1 FROM #pur_pr_list WHERE status = 'D'))
AND a.po_status <> 'F'

UPDATE #pur_pr_list
SET po_no2 = @po_no, po_date2 = convert(char(10), @po_date, 103),
po_serial2 = @po_serial,qty_covered2 = @qty_covered,
po2_status = @po2_status
WHERE status = 'D'

SELECT @po_no = NULL, @po_serial = NULL,
@po_date = NULL, @qty_covered = NULL,
@po2_status= null

SELECT @po_no = a.po_no, @po_serial = b.po_serial,
@po_date = a.po_date, @qty_covered = c.covered_qty_puom,
@po2_status = a.po_status
FROM pur_po_header a (nolock),
pur_po_detail b (nolock),
pur_po_indent c (nolock)
WHERE a.company_code = @company_no
and a.locn_no = @location_no
AND a.company_code = b.company_code
AND a.company_code = c.company_code
AND a.locn_no = b.locn_no
AND a.locn_no = c.locn_no
AND a.po_no = b.po_no
AND a.po_no = c.po_no
and b.po_serial = c.po_serial
AND c.indent_no = (SELECT indent_no FROM #pur_pr_list WHERE status = 'D')
AND c.indent_serial= (SELECT serial_no FROM #pur_pr_list WHERE status = 'D')
AND (b.po_no <> (SELECT po_no1 FROM #pur_pr_list WHERE status = 'D')
OR b.po_serial <> (SELECT po_serial1 FROM #pur_pr_list WHERE status = 'D'))
AND (b.po_no <> (SELECT po_no2 FROM #pur_pr_list WHERE status = 'D')
OR b.po_serial <> (SELECT po_serial2 FROM #pur_pr_list WHERE status = 'D'))

AND a.po_status <> 'F'

UPDATE #pur_pr_list
SET po_no3 = @po_no, po_date3 = convert(char(10), @po_date, 103),
po_serial3 = @po_serial,qty_covered3 = @qty_covered,
po3_status = @po3_status
WHERE status = 'D'

UPDATE #pur_pr_list
SET qty_covered = ISNULL(QTY_COVERED1,0) + ISNULL(QTY_COVERED2,0) + ISNULL(QTY_COVERED3,0)
WHERE status = 'D'

UPDATE #pur_pr_list
SET status = 'E'
WHERE status ='D'

/*************************************************/
and the full code is given below....

alter PROCEDURE pur_pr_list

-- exec pur_pr_list 'aaa','vvv',1,'','2010/01/01','2010/01/30','2010/01/01','2010/01/30','','','','','ALL','N','ser','AUTO_INDENT_LIST'

@company_no glcompany ,
@location_no gllocn ,
@lang_id int ,
@requester varchar(6) ,
@date_from datetime ,
@date_to datetime ,
@daten_from datetime ,
@daten_to datetime ,
@from_stock IMSSTKNO ,
@to_stock IMSSTKNO ,
@From_variant IMSSTKVAR ,
@to_variant IMSSTKVAR ,
@dept varchar(40),
@flag char(1) = 'N', /** Y -> For pending PRs */ /* N -> For all PRS **/
@Indent_catagory char(15) ,
@auto_indent varchar(60)

AS
BEGIN
set nocount on
DECLARE @indent_no cmn_tranno ,
@line_item cmn_slno ,
@descn1 varchar(255),
@descn2 varchar(255),
@company_name varchar(40) ,
@heading varchar(100),
@po_no cmn_tranno ,
@po_serial cmn_slno ,
@po_date datetime ,
@qty_covered cmn_pqty ,
@po1_status char(1) ,
@po2_status char(1) ,
@po3_status char(1)

IF @from_stock is NULL OR @from_stock = ''
SELECT @from_stock = '0'

IF @to_stock is NULL OR @to_stock = ''
SELECT @to_stock = 'zzzzzzzzzzzzzzzz'

IF @from_variant is NULL OR @from_variant = ''
SELECT @from_variant = '#'

IF @to_variant is NULL OR @to_variant = ''
SELECT @to_variant = 'zzzz'

--Added by Milin 21/11/2006
if @requester is null or @requester = ''
select @requester = '%'
--Milin 21/11/2006

--Modified by Milin on 03/11/2007
if @dept= 'ALL'
begin
select @dept = NULL
end
--Modified by Milin on 03/11/2007

CREATE TABLE #pur_pr_list
(
company_no glcompany ,
location_no gllocn ,
indent_no cmn_tranno ,
indent_date char(10) NULL,
cc_no maccost NULL,
serial_no cmn_slno ,
stock_no imsstkno NULL,
variant_no IMSSTKVAR NULL,
stock_desc1 varchar(255)NULL,
stock_desc2 varchar(255)NULL,
dept varchar(40) NULL,
department char(2) NULL,
uom IMSUOMNO NULL,
due_date CHAR(10) NULL,
qty_required cmn_pqty NULL,
qty_covered cmn_pqty NULL,
po_no1 cmn_tranno NULL,
po_serial1 cmn_slno NULL,
po_date1 char(10) NULL,
qty_covered1 cmn_pqty NULL,
po_no2 cmn_tranno NULL,
po_serial2 cmn_slno NULL,
po_date2 char(10) NULL,
qty_covered2 cmn_pqty null,
po_no3 cmn_tranno NULL,
po_serial3 cmn_slno NULL,
po_date3 char(10) NULL,
qty_covered3 cmn_pqty NULL,
status char(1) NULL,
po1_status char(1) null,
po2_status char(1) null,
po3_status char(1) null,
ref_doc_no cmn_tranno ,
indent_type varchar(40) ,
requester empnum null, --Added by Milin 21/11/2006
requester_name varchar(40) null, --Added by Milin 21/11/2006
indent_category varchar(5) , --Added by AVIJIT 16/10/2009
user_id cmn_userid -- Added by AVIJIT 28/03/2010
)

if @Indent_catagory = 'SER'
begin
INSERT INTO #pur_pr_list (company_no,location_no,indent_no, indent_date, cc_no, serial_no,
stock_no,variant_no,uom, due_date, qty_required,qty_covered,
dept, ref_doc_no,indent_type, requester, indent_category, user_id
)
SELECT a.company_code,a.locn_no,a.indent_no, CONVERT(CHAR(10), a.indent_date, 103),
a.cc_no, b.indent_serial,b.stock_no, b.stock_variant, b.purchase_uom,
CONVERT(CHAR(10), a.due_date, 103),b.authorised_qty_puom, b.po_covered_qty_puom,
a.purpose, b.ref_doc_no,a.indent_type, a.req_emp_no, indent_category, a.user_id
FROM pur_indent_header a (nolock), pur_indent_detail b (nolock)
WHERE a.company_code = @company_no
and a.locn_no = @location_no
and a.indent_status <> 'L'
AND a.company_code = b.company_code
and a.locn_no = b.locn_no
AND a.indent_no = b.indent_no
and a.indent_type= 'gen'
and a.indent_category = 'SER'
and a.indent_date between @date_from and @date_to
and a.req_emp_no LIKE @requester
ORDER BY a.indent_no
end
else
begin
INSERT INTO #pur_pr_list (company_no,location_no,indent_no, indent_date, cc_no, serial_no,
stock_no,variant_no,uom, due_date, qty_required,qty_covered, dept, ref_doc_no,indent_type,
requester, indent_category, user_id )

SELECT a.company_code,a.locn_no,a.indent_no, CONVERT(CHAR(10), a.indent_date, 103),
a.cc_no, b.indent_serial,b.stock_no, b.stock_variant, b.purchase_uom,
CONVERT(CHAR(10), a.due_date, 103),b.authorised_qty_puom, b.po_covered_qty_puom,
a.purpose, b.ref_doc_no,a.indent_type, a.req_emp_no,a.indent_category, a.user_id
FROM pur_indent_header a (nolock), pur_indent_detail b (nolock)
WHERE a.company_code = @company_no
and a.locn_no = @location_no
and a.indent_status <> 'L'
AND a.company_code = b.company_code
and a.locn_no = b.locn_no
AND a.indent_no = b.indent_no
and a.indent_type= 'gen'
and a.indent_date between @date_from and @date_to
and b.stock_no BETWEEN @from_stock AND @TO_stock
and b.stock_variant BETWEEN @from_variant AND @TO_VARIANT
and a.req_emp_no LIKE @requester
ORDER BY a.indent_no
end

update #pur_pr_list
set dept = a.param_value
from pomdb..pur_comp_parameter_detail a (nolock), #pur_pr_list b
where a.company_code = b.company_no
and a.param_class = 'USRDFL'
and a.param_cat = 'POM'
and a.param_type = 'WUSD'
and a.param_code = b.dept
-- select * from #pur_pr_list
-- return
IF @flag = 'Y'
DELETE #pur_pr_list
WHERE qty_covered >= qty_required

IF ltrim(RTRIM(@dept)) IS NOT NULL
DELETE #pur_pr_list
WHERE ltrim(RTRIM(dept)) <> ltrim(RTRIM(@dept))

/*** To select the description from UDS table ***/

SET ROWCOUNT 1
WHILE EXISTS(SELECT '*' FROM #pur_pr_list WHERE status IS NULL)
BEGIN
UPDATE #pur_pr_list
SET status = 'A'
WHERE status IS NULL

SELECT @indent_no = indent_no, @line_item = serial_no
from #pur_pr_list
where status = 'A'

/***********************/

if @Indent_catagory = 'SER'
begin
UPDATE #pur_pr_list
SET stock_desc1 = b.M4
from #pur_pr_list a, pppp.UDSNEW1410065135_M b
where a.indent_no = SUBSTRING ( b.key_value , 21 , 15 )
and a.serial_no = b.M1
AND a.status = 'A'

UPDATE #pur_pr_list
SET status = 'B'
WHERE status = 'A'
end
else
begin
EXECUTE scl_descn_fetch_sp_PRN @indent_no,'PR', @line_item,
@descn1 output, @descn2 output

EXECUTE scl_conv_inches @descn1, @descn1 output
EXECUTE scl_conv_inches @descn2, @descn2 output

UPDATE #pur_pr_list
SET stock_desc1 = @descn1,
stock_desc2 = @descn2
WHERE status = 'A'

UPDATE #pur_pr_list
SET status = 'B'
WHERE status = 'A'
end
END
SET ROWCOUNT 0

-- select * from #pur_pr_list
-- return

/*** To update the two POs for this indent ***/
UPDATE #pur_pr_list
SET status = 'C'

SET ROWCOUNT 1
WHILE EXISTS(SELECT '*' FROM #pur_pr_list WHERE status = 'C')
BEGIN
UPDATE #pur_pr_list
SET status = 'D'
WHERE Status = 'C'

SELECT @po_no = NULL,
@po_serial = NULL,
@po_date = NULL,
@qty_covered = NULL,
@po1_status = null

SELECT @po_no = a.po_no,
@po_serial = b.po_serial,
@po_date = a.po_date,
@qty_covered = c.covered_qty_puom,
@po1_status = a.po_status
FROM pur_po_header a (nolock),
pur_po_detail b (nolock),
pur_po_indent c (nolock)
WHERE a.company_code = @company_no
and a.locn_no = @location_no
AND a.company_code = b.company_code
AND a.company_code = c.company_code
AND a.locn_no = b.locn_no
AND a.locn_no = c.locn_no
AND a.po_no = b.po_no
AND a.po_no = c.po_no
and b.po_serial = c.po_serial
AND c.indent_no = (SELECT indent_no FROM #pur_pr_list WHERE status = 'D')
AND c.indent_serial = (SELECT serial_no FROM #pur_pr_list WHERE status = 'D')
AND a.po_status <> 'F'

UPDATE #pur_pr_list
SET po_no1 = @po_no,
po_date1 = convert(char(10), @po_date, 103),
po_serial1 = @po_serial,
qty_covered1 = @qty_covered,
po1_status = @po1_status
WHERE status = 'D'

SELECT @po_no = NULL, @po_serial = NULL,
@po_date = NULL, @qty_covered = NULL,
@po2_status = null

SELECT @po_no = a.po_no,
@po_serial = b.po_serial,
@po_date = a.po_date,
@qty_covered = c.covered_qty_puom,
@po2_status = a.po_status
FROM pur_po_header a (nolock),
pur_po_detail b (nolock),
pur_po_indent c (nolock)
WHERE a.company_code = @company_no
and a.locn_no = @location_no
AND a.company_code = b.company_code
AND a.company_code = c.company_code
AND a.locn_no = b.locn_no
AND a.locn_no = c.locn_no
AND a.po_no = b.po_no
AND a.po_no = c.po_no
and b.po_serial = c.po_serial
AND c.indent_no = (SELECT indent_no FROM #pur_pr_list WHERE status = 'D')
AND c.indent_serial = (SELECT serial_no FROM #pur_pr_list WHERE status = 'D')
AND (b.po_no <> (SELECT po_no1 FROM #pur_pr_list WHERE status = 'D')
OR b.po_serial <> (SELECT po_serial1 FROM #pur_pr_list WHERE status = 'D'))
AND a.po_status <> 'F'

UPDATE #pur_pr_list
SET po_no2 = @po_no, po_date2 = convert(char(10), @po_date, 103),
po_serial2 = @po_serial,qty_covered2 = @qty_covered,
po2_status = @po2_status
WHERE status = 'D'

SELECT @po_no = NULL, @po_serial = NULL,
@po_date = NULL, @qty_covered = NULL,
@po2_status= null

SELECT @po_no = a.po_no, @po_serial = b.po_serial,
@po_date = a.po_date, @qty_covered = c.covered_qty_puom,
@po2_status = a.po_status
FROM pur_po_header a (nolock),
pur_po_detail b (nolock),
pur_po_indent c (nolock)
WHERE a.company_code = @company_no
and a.locn_no = @location_no
AND a.company_code = b.company_code
AND a.company_code = c.company_code
AND a.locn_no = b.locn_no
AND a.locn_no = c.locn_no
AND a.po_no = b.po_no
AND a.po_no = c.po_no
and b.po_serial = c.po_serial
AND c.indent_no = (SELECT indent_no FROM #pur_pr_list WHERE status = 'D')
AND c.indent_serial= (SELECT serial_no FROM #pur_pr_list WHERE status = 'D')
AND (b.po_no <> (SELECT po_no1 FROM #pur_pr_list WHERE status = 'D')
OR b.po_serial <> (SELECT po_serial1 FROM #pur_pr_list WHERE status = 'D'))
AND (b.po_no <> (SELECT po_no2 FROM #pur_pr_list WHERE status = 'D')
OR b.po_serial <> (SELECT po_serial2 FROM #pur_pr_list WHERE status = 'D'))

AND a.po_status <> 'F'

UPDATE #pur_pr_list
SET po_no3 = @po_no, po_date3 = convert(char(10), @po_date, 103),
po_serial3 = @po_serial,qty_covered3 = @qty_covered,
po3_status = @po3_status
WHERE status = 'D'

UPDATE #pur_pr_list
SET qty_covered = ISNULL(QTY_COVERED1,0) + ISNULL(QTY_COVERED2,0) + ISNULL(QTY_COVERED3,0)
WHERE status = 'D'

UPDATE #pur_pr_list
SET status = 'E'
WHERE status ='D'

END
SET ROWCOUNT 0

IF @flag = 'Y'
DELETE #pur_pr_list
WHERE qty_covered >= qty_required

SELECT @company_name = fs_company_name
from common..fs_company_master (nolock)
WHERE fs_company_code = @company_no

SELECT @heading = 'For the period from '+ convert(char(10), @date_from, 103) + ' to '
+ convert(char(10), @date_to, 103)

update #pur_pr_list
set requester_name = rtrim(ISNULL(H04_First_Name,''))+' '+rtrim(ISNULL(H04_Middle_Name,''))+' '+rtrim(ISNULL(H04_Last_Name,''))
from common..h04_namemaster a(nolock), #pur_pr_list b
where a.C03_Organisation_code = @company_no
and a.H01_emp_num = b.requester

/*******************************************************/
if @Indent_catagory ='gen'
begin
if @auto_indent ='AUTO_INDENT_LIST'
begin
SELECT company_name = ltrim(RTRIM(@company_name)), heading = @heading,
flag = @flag, *
FROM #pur_pr_list
where indent_type = 'gen'
and indent_category = 'GEN'
AND user_id = 2
ORDER BY indent_no, serial_no
end
if @auto_indent ='MANUAL_INDENT_LIST'
begin
SELECT company_name = ltrim(RTRIM(@company_name)), heading = @heading,
flag = @flag, *
FROM #pur_pr_list
where indent_type = 'gen'
and indent_category = 'GEN'
AND user_id != 2
ORDER BY indent_no, serial_no
end
if @auto_indent ='ALL'
begin
SELECT company_name = ltrim(RTRIM(@company_name)), heading = @heading,
flag = @flag, *
FROM #pur_pr_list
where indent_type = 'gen'
and indent_category = 'GEN'
ORDER BY indent_no, serial_no
end

end
if @Indent_catagory = 'ser'
begin
if @auto_indent ='AUTO_INDENT_LIST'
BEGIN
SELECT company_name = ltrim(RTRIM(@company_name)), heading = @heading,
flag = @flag, *
FROM #pur_pr_list
where indent_type = 'gen'
and indent_category = 'SER'
AND USER_ID = 2
ORDER BY indent_no, serial_no
END

if @auto_indent ='MANUAL_INDENT_LIST'
BEGIN
SELECT company_name = ltrim(RTRIM(@company_name)), heading = @heading,
flag = @flag, *
FROM #pur_pr_list
where indent_type = 'gen'
and indent_category = 'SER'
AND USER_ID != 2
ORDER BY indent_no, serial_no
END

if @auto_indent ='ALL'
BEGIN
SELECT company_name = ltrim(RTRIM(@company_name)), heading = @heading,
flag = @flag, *
FROM #pur_pr_list
where indent_type = 'gen'
and indent_category = 'SER'
ORDER BY indent_no, serial_no
END

end

if @Indent_catagory = 'All'

begin
if @auto_indent ='AUTO_INDENT_LIST'
BEGIN
SELECT company_name = ltrim(RTRIM(@company_name)), heading = @heading,
flag = @flag, *
FROM #pur_pr_list
WHERE USER_ID = 2
ORDER BY indent_no, serial_no
END

if @auto_indent ='MANUAL_INDENT_LIST'
BEGIN
SELECT company_name = ltrim(RTRIM(@company_name)), heading = @heading,
flag = @flag, *
FROM #pur_pr_list
where indent_type = 'gen'
and indent_category = 'SER'
AND USER_ID != 2
ORDER BY indent_no, serial_no
END

if @auto_indent ='ALL'

BEGIN
SELECT company_name = ltrim(RTRIM(@company_name)), heading = @heading,
flag = @flag, *
FROM #pur_pr_list
ORDER BY indent_no, serial_no
END
end
/**********************************************************/

end

Kristen
Test

22859 Posts

Posted - 2010-03-31 : 05:05:51

1. Use SET BASED processes - this procedural "one-by-one" method you are using if horribly inefficient.

2. Do NOT use NOLOCK. The side effects of this are potentially disastrous. This post is in SQL 2000 forum, but if you have SQL 2005 or later use READ_COMMITTED_SNAPSHOT - if you are still using SQL 2000 this may be the single most important reason to upgrade!

3. Do NOT do this:


AND c.indent_no = (SELECT indent_no FROM #pur_pr_list WHERE status = 'D') 
AND c.indent_serial= (SELECT serial_no FROM #pur_pr_list WHERE status = 'D') 
AND (b.po_no <> (SELECT po_no1 FROM #pur_pr_list WHERE status = 'D') 
OR b.po_serial <> (SELECT po_serial1 FROM #pur_pr_list WHERE status = 'D')) 
AND (b.po_no <> (SELECT po_no2 FROM #pur_pr_list WHERE status = 'D') 
OR b.po_serial <> (SELECT po_serial2 FROM #pur_pr_list WHERE status = 'D'))

use a JOIN instead. One JOIN will save 7 separate lookups in this example, and a lot of time.

If you MUST process the records one-by-one then do NOT use your multiple-update approach. Create #pur_pr_list with an IDENTITY column and use a loop to process it item-number-by-item-number.

4. Do NOT do this:


UPDATE #pur_pr_list 
SET po_no3 = @po_no, po_date3 = convert(char(10), @po_date, 103), 
po_serial3 = @po_serial,qty_covered3 = @qty_covered, 
po3_status = @po3_status 
WHERE status = 'D' 

UPDATE #pur_pr_list 
SET qty_covered = ISNULL(QTY_COVERED1,0) + ISNULL(QTY_COVERED2,0) + ISNULL(QTY_COVERED3,0) 
WHERE status = 'D' 

UPDATE #pur_pr_list 
SET status = 'E' 
WHERE status ='D'

why cannot these be all done in a single UPDATE statement?

Lastly, the "scope" of ROWCOUNT=1 seems very long to me. I think there is a risk that some of the other queries could match multiple rows (in JOINed tables) but because of ROWCOUNT=1 you are only processing one record, at random, when in fact more may be matched.

Anyway, you can probably do them all in one strike using set-based methods, and if you can I expect the process will run 100 times faster, perhaps even better.

avijit111@gmail.com
Yak Posting Veteran

55 Posts

Posted - 2010-04-03 : 00:50:45

thanks kristen for ur support ....

avijit111@gmail.com
Yak Posting Veteran

55 Posts

Posted - 2010-04-03 : 04:25:19

Dear kristen
I tried to use join with below code....but giving error.....please show me the proper way to implement join in below code...

SELECT @po_no = a.po_no,
@po_serial = b.po_serial,
@po_date = a.po_date,
@qty_covered = c.covered_qty_puom,
@po2_status = a.po_status
FROM pur_po_header a (nolock),
pur_po_detail b (nolock),
pur_po_indent c (nolock)
WHERE a.company_code = @company_no
and a.locn_no = @location_no
AND a.company_code = b.company_code
AND a.company_code = c.company_code
AND a.locn_no = b.locn_no
AND a.locn_no = c.locn_no
AND a.po_no = b.po_no
AND a.po_no = c.po_no
and b.po_serial = c.po_serial
AND c.indent_no = (SELECT indent_no FROM #pur_pr_list WHERE status = 'D')
AND c.indent_serial = (SELECT serial_no FROM #pur_pr_list WHERE status = 'D')
AND (b.po_no <> (SELECT po_no1 FROM #pur_pr_list WHERE status = 'D')
OR b.po_serial <> (SELECT po_serial1 FROM #pur_pr_list WHERE status = 'D'))
AND a.po_status <> 'F'

Kristen
Test

22859 Posts

Posted - 2010-04-03 : 05:23:20

[code]
FROM pur_po_header a (nolock)
JOIN #pur_pr_list AS T
ON T.status = 'D'
,
pur_po_detail b (nolock),
pur_po_indent c (nolock)
WHERE a.company_code = @company_no
and a.locn_no = @location_no
AND a.company_code = b.company_code
AND a.company_code = c.company_code
AND a.locn_no = b.locn_no
AND a.locn_no = c.locn_no
AND a.po_no = b.po_no
AND a.po_no = c.po_no
and b.po_serial = c.po_serial
AND c.indent_no = T.indent_no
AND c.indent_serial = T.serial_no
AND (b.po_no <> T.po_no1
OR b.po_serial <> T.po_serial1)
[/code]
but as I said above you should not be processing these one-by-one, in a loop, but rather using Set-based methods.

avijit111@gmail.com
Yak Posting Veteran

55 Posts

Posted - 2010-04-05 : 01:23:34

Hi kristen I tried lot but nothing has been changed. please show me how to do in set-based...
thanks for your reply dear...

In the below code taking too much time to execute. where two po's are getting update taking much time to execute .please tell me any kind of tuning i can their,which make the code execute more faster.
/*************************************************/
/*** To update the two POs for this indent ***/
UPDATE #pur_pr_list
SET status = 'C'

SET ROWCOUNT 1
WHILE EXISTS(SELECT '*' FROM #pur_pr_list WHERE status = 'C')
BEGIN
UPDATE #pur_pr_list
SET status = 'D'
WHERE Status = 'C'

SELECT @po_no = NULL,
@po_serial = NULL,
@po_date = NULL,
@qty_covered = NULL,
@po1_status = null

SELECT @po_no = a.po_no,
@po_serial = b.po_serial,
@po_date = a.po_date,
@qty_covered = c.covered_qty_puom,
@po1_status = a.po_status
FROM pur_po_header a (nolock),
pur_po_detail b (nolock),
pur_po_indent c (nolock)
WHERE a.company_code = @company_no
and a.locn_no = @location_no
AND a.company_code = b.company_code
AND a.company_code = c.company_code
AND a.locn_no = b.locn_no
AND a.locn_no = c.locn_no
AND a.po_no = b.po_no
AND a.po_no = c.po_no
and b.po_serial = c.po_serial
AND c.indent_no = (SELECT indent_no FROM #pur_pr_list WHERE status = 'D')
AND c.indent_serial = (SELECT serial_no FROM #pur_pr_list WHERE status = 'D')
AND a.po_status <> 'F'

UPDATE #pur_pr_list
SET po_no1 = @po_no,
po_date1 = convert(char(10), @po_date, 103),
po_serial1 = @po_serial,
qty_covered1 = @qty_covered,
po1_status = @po1_status
WHERE status = 'D'

SELECT @po_no = NULL, @po_serial = NULL,
@po_date = NULL, @qty_covered = NULL,
@po2_status = null

SELECT @po_no = a.po_no,
@po_serial = b.po_serial,
@po_date = a.po_date,
@qty_covered = c.covered_qty_puom,
@po2_status = a.po_status
FROM pur_po_header a (nolock),
pur_po_detail b (nolock),
pur_po_indent c (nolock)
WHERE a.company_code = @company_no
and a.locn_no = @location_no
AND a.company_code = b.company_code
AND a.company_code = c.company_code
AND a.locn_no = b.locn_no
AND a.locn_no = c.locn_no
AND a.po_no = b.po_no
AND a.po_no = c.po_no
and b.po_serial = c.po_serial
AND c.indent_no = (SELECT indent_no FROM #pur_pr_list WHERE status = 'D')
AND c.indent_serial = (SELECT serial_no FROM #pur_pr_list WHERE status = 'D')
AND (b.po_no <> (SELECT po_no1 FROM #pur_pr_list WHERE status = 'D')
OR b.po_serial <> (SELECT po_serial1 FROM #pur_pr_list WHERE status = 'D'))
AND a.po_status <> 'F'

UPDATE #pur_pr_list
SET po_no2 = @po_no, po_date2 = convert(char(10), @po_date, 103),
po_serial2 = @po_serial,qty_covered2 = @qty_covered,
po2_status = @po2_status
WHERE status = 'D'

SELECT @po_no = NULL, @po_serial = NULL,
@po_date = NULL, @qty_covered = NULL,
@po2_status= null

SELECT @po_no = a.po_no, @po_serial = b.po_serial,
@po_date = a.po_date, @qty_covered = c.covered_qty_puom,
@po2_status = a.po_status
FROM pur_po_header a (nolock),
pur_po_detail b (nolock),
pur_po_indent c (nolock)
WHERE a.company_code = @company_no
and a.locn_no = @location_no
AND a.company_code = b.company_code
AND a.company_code = c.company_code
AND a.locn_no = b.locn_no
AND a.locn_no = c.locn_no
AND a.po_no = b.po_no
AND a.po_no = c.po_no
and b.po_serial = c.po_serial
AND c.indent_no = (SELECT indent_no FROM #pur_pr_list WHERE status = 'D')
AND c.indent_serial= (SELECT serial_no FROM #pur_pr_list WHERE status = 'D')
AND (b.po_no <> (SELECT po_no1 FROM #pur_pr_list WHERE status = 'D')
OR b.po_serial <> (SELECT po_serial1 FROM #pur_pr_list WHERE status = 'D'))
AND (b.po_no <> (SELECT po_no2 FROM #pur_pr_list WHERE status = 'D')
OR b.po_serial <> (SELECT po_serial2 FROM #pur_pr_list WHERE status = 'D'))

AND a.po_status <> 'F'

UPDATE #pur_pr_list
SET po_no3 = @po_no, po_date3 = convert(char(10), @po_date, 103),
po_serial3 = @po_serial,qty_covered3 = @qty_covered,
po3_status = @po3_status
WHERE status = 'D'

UPDATE #pur_pr_list
SET qty_covered = ISNULL(QTY_COVERED1,0) + ISNULL(QTY_COVERED2,0) + ISNULL(QTY_COVERED3,0)
WHERE status = 'D'

UPDATE #pur_pr_list
SET status = 'E'
WHERE status ='D'

/*************************************************/
and the full code is given below....

alter PROCEDURE pur_pr_list

-- exec pur_pr_list 'aaa','vvv',1,'','2010/01/01','2010/01/30','2010/01/01','2010/01/30','','','','','ALL','N','ser','AUTO_INDENT_LIST'

@company_no glcompany ,
@location_no gllocn ,
@lang_id int ,
@requester varchar(6) ,
@date_from datetime ,
@date_to datetime ,
@daten_from datetime ,
@daten_to datetime ,
@from_stock IMSSTKNO ,
@to_stock IMSSTKNO ,
@From_variant IMSSTKVAR ,
@to_variant IMSSTKVAR ,
@dept varchar(40),
@flag char(1) = 'N', /** Y -> For pending PRs */ /* N -> For all PRS **/
@Indent_catagory char(15) ,
@auto_indent varchar(60)

AS
BEGIN
set nocount on
DECLARE @indent_no cmn_tranno ,
@line_item cmn_slno ,
@descn1 varchar(255),
@descn2 varchar(255),
@company_name varchar(40) ,
@heading varchar(100),
@po_no cmn_tranno ,
@po_serial cmn_slno ,
@po_date datetime ,
@qty_covered cmn_pqty ,
@po1_status char(1) ,
@po2_status char(1) ,
@po3_status char(1)

IF @from_stock is NULL OR @from_stock = ''
SELECT @from_stock = '0'

IF @to_stock is NULL OR @to_stock = ''
SELECT @to_stock = 'zzzzzzzzzzzzzzzz'

IF @from_variant is NULL OR @from_variant = ''
SELECT @from_variant = '#'

IF @to_variant is NULL OR @to_variant = ''
SELECT @to_variant = 'zzzz'

--Added by Milin 21/11/2006
if @requester is null or @requester = ''
select @requester = '%'
--Milin 21/11/2006

--Modified by Milin on 03/11/2007
if @dept= 'ALL'
begin
select @dept = NULL
end
--Modified by Milin on 03/11/2007

CREATE TABLE #pur_pr_list
(
company_no glcompany ,
location_no gllocn ,
indent_no cmn_tranno ,
indent_date char(10) NULL,
cc_no maccost NULL,
serial_no cmn_slno ,
stock_no imsstkno NULL,
variant_no IMSSTKVAR NULL,
stock_desc1 varchar(255)NULL,
stock_desc2 varchar(255)NULL,
dept varchar(40) NULL,
department char(2) NULL,
uom IMSUOMNO NULL,
due_date CHAR(10) NULL,
qty_required cmn_pqty NULL,
qty_covered cmn_pqty NULL,
po_no1 cmn_tranno NULL,
po_serial1 cmn_slno NULL,
po_date1 char(10) NULL,
qty_covered1 cmn_pqty NULL,
po_no2 cmn_tranno NULL,
po_serial2 cmn_slno NULL,
po_date2 char(10) NULL,
qty_covered2 cmn_pqty null,
po_no3 cmn_tranno NULL,
po_serial3 cmn_slno NULL,
po_date3 char(10) NULL,
qty_covered3 cmn_pqty NULL,
status char(1) NULL,
po1_status char(1) null,
po2_status char(1) null,
po3_status char(1) null,
ref_doc_no cmn_tranno ,
indent_type varchar(40) ,
requester empnum null, --Added by Milin 21/11/2006
requester_name varchar(40) null, --Added by Milin 21/11/2006
indent_category varchar(5) , --Added by AVIJIT 16/10/2009
user_id cmn_userid -- Added by AVIJIT 28/03/2010
)

if @Indent_catagory = 'SER'
begin
INSERT INTO #pur_pr_list (company_no,location_no,indent_no, indent_date, cc_no, serial_no,
stock_no,variant_no,uom, due_date, qty_required,qty_covered,
dept, ref_doc_no,indent_type, requester, indent_category, user_id
)
SELECT a.company_code,a.locn_no,a.indent_no, CONVERT(CHAR(10), a.indent_date, 103),
a.cc_no, b.indent_serial,b.stock_no, b.stock_variant, b.purchase_uom,
CONVERT(CHAR(10), a.due_date, 103),b.authorised_qty_puom, b.po_covered_qty_puom,
a.purpose, b.ref_doc_no,a.indent_type, a.req_emp_no, indent_category, a.user_id
FROM pur_indent_header a (nolock), pur_indent_detail b (nolock)
WHERE a.company_code = @company_no
and a.locn_no = @location_no
and a.indent_status <> 'L'
AND a.company_code = b.company_code
and a.locn_no = b.locn_no
AND a.indent_no = b.indent_no
and a.indent_type= 'gen'
and a.indent_category = 'SER'
and a.indent_date between @date_from and @date_to
and a.req_emp_no LIKE @requester
ORDER BY a.indent_no
end
else
begin
INSERT INTO #pur_pr_list (company_no,location_no,indent_no, indent_date, cc_no, serial_no,
stock_no,variant_no,uom, due_date, qty_required,qty_covered, dept, ref_doc_no,indent_type,
requester, indent_category, user_id )

SELECT a.company_code,a.locn_no,a.indent_no, CONVERT(CHAR(10), a.indent_date, 103),
a.cc_no, b.indent_serial,b.stock_no, b.stock_variant, b.purchase_uom,
CONVERT(CHAR(10), a.due_date, 103),b.authorised_qty_puom, b.po_covered_qty_puom,
a.purpose, b.ref_doc_no,a.indent_type, a.req_emp_no,a.indent_category, a.user_id
FROM pur_indent_header a (nolock), pur_indent_detail b (nolock)
WHERE a.company_code = @company_no
and a.locn_no = @location_no
and a.indent_status <> 'L'
AND a.company_code = b.company_code
and a.locn_no = b.locn_no
AND a.indent_no = b.indent_no
and a.indent_type= 'gen'
and a.indent_date between @date_from and @date_to
and b.stock_no BETWEEN @from_stock AND @TO_stock
and b.stock_variant BETWEEN @from_variant AND @TO_VARIANT
and a.req_emp_no LIKE @requester
ORDER BY a.indent_no
end

update #pur_pr_list
set dept = a.param_value
from pomdb..pur_comp_parameter_detail a (nolock), #pur_pr_list b
where a.company_code = b.company_no
and a.param_class = 'USRDFL'
and a.param_cat = 'POM'
and a.param_type = 'WUSD'
and a.param_code = b.dept
-- select * from #pur_pr_list
-- return
IF @flag = 'Y'
DELETE #pur_pr_list
WHERE qty_covered >= qty_required

IF ltrim(RTRIM(@dept)) IS NOT NULL
DELETE #pur_pr_list
WHERE ltrim(RTRIM(dept)) <> ltrim(RTRIM(@dept))

/*** To select the description from UDS table ***/

SET ROWCOUNT 1
WHILE EXISTS(SELECT '*' FROM #pur_pr_list WHERE status IS NULL)
BEGIN
UPDATE #pur_pr_list
SET status = 'A'
WHERE status IS NULL

SELECT @indent_no = indent_no, @line_item = serial_no
from #pur_pr_list
where status = 'A'

/***********************/

if @Indent_catagory = 'SER'
begin
UPDATE #pur_pr_list
SET stock_desc1 = b.M4
from #pur_pr_list a, pppp.UDSNEW1410065135_M b
where a.indent_no = SUBSTRING ( b.key_value , 21 , 15 )
and a.serial_no = b.M1
AND a.status = 'A'

UPDATE #pur_pr_list
SET status = 'B'
WHERE status = 'A'
end
else
begin
EXECUTE scl_descn_fetch_sp_PRN @indent_no,'PR', @line_item,
@descn1 output, @descn2 output

EXECUTE scl_conv_inches @descn1, @descn1 output
EXECUTE scl_conv_inches @descn2, @descn2 output

UPDATE #pur_pr_list
SET stock_desc1 = @descn1,
stock_desc2 = @descn2
WHERE status = 'A'

UPDATE #pur_pr_list
SET status = 'B'
WHERE status = 'A'
end
END
SET ROWCOUNT 0

-- select * from #pur_pr_list
-- return

/*** To update the two POs for this indent ***/
UPDATE #pur_pr_list
SET status = 'C'

SET ROWCOUNT 1
WHILE EXISTS(SELECT '*' FROM #pur_pr_list WHERE status = 'C')
BEGIN
UPDATE #pur_pr_list
SET status = 'D'
WHERE Status = 'C'

SELECT @po_no = NULL,
@po_serial = NULL,
@po_date = NULL,
@qty_covered = NULL,
@po1_status = null

SELECT @po_no = a.po_no,
@po_serial = b.po_serial,
@po_date = a.po_date,
@qty_covered = c.covered_qty_puom,
@po1_status = a.po_status
FROM pur_po_header a (nolock),
pur_po_detail b (nolock),
pur_po_indent c (nolock)
WHERE a.company_code = @company_no
and a.locn_no = @location_no
AND a.company_code = b.company_code
AND a.company_code = c.company_code
AND a.locn_no = b.locn_no
AND a.locn_no = c.locn_no
AND a.po_no = b.po_no
AND a.po_no = c.po_no
and b.po_serial = c.po_serial
AND c.indent_no = (SELECT indent_no FROM #pur_pr_list WHERE status = 'D')
AND c.indent_serial = (SELECT serial_no FROM #pur_pr_list WHERE status = 'D')
AND a.po_status <> 'F'

UPDATE #pur_pr_list
SET po_no1 = @po_no,
po_date1 = convert(char(10), @po_date, 103),
po_serial1 = @po_serial,
qty_covered1 = @qty_covered,
po1_status = @po1_status
WHERE status = 'D'

SELECT @po_no = NULL, @po_serial = NULL,
@po_date = NULL, @qty_covered = NULL,
@po2_status = null

SELECT @po_no = a.po_no,
@po_serial = b.po_serial,
@po_date = a.po_date,
@qty_covered = c.covered_qty_puom,
@po2_status = a.po_status
FROM pur_po_header a (nolock),
pur_po_detail b (nolock),
pur_po_indent c (nolock)
WHERE a.company_code = @company_no
and a.locn_no = @location_no
AND a.company_code = b.company_code
AND a.company_code = c.company_code
AND a.locn_no = b.locn_no
AND a.locn_no = c.locn_no
AND a.po_no = b.po_no
AND a.po_no = c.po_no
and b.po_serial = c.po_serial
AND c.indent_no = (SELECT indent_no FROM #pur_pr_list WHERE status = 'D')
AND c.indent_serial = (SELECT serial_no FROM #pur_pr_list WHERE status = 'D')
AND (b.po_no <> (SELECT po_no1 FROM #pur_pr_list WHERE status = 'D')
OR b.po_serial <> (SELECT po_serial1 FROM #pur_pr_list WHERE status = 'D'))
AND a.po_status <> 'F'

UPDATE #pur_pr_list
SET po_no2 = @po_no, po_date2 = convert(char(10), @po_date, 103),
po_serial2 = @po_serial,qty_covered2 = @qty_covered,
po2_status = @po2_status
WHERE status = 'D'

SELECT @po_no = NULL, @po_serial = NULL,
@po_date = NULL, @qty_covered = NULL,
@po2_status= null

SELECT @po_no = a.po_no, @po_serial = b.po_serial,
@po_date = a.po_date, @qty_covered = c.covered_qty_puom,
@po2_status = a.po_status
FROM pur_po_header a (nolock),
pur_po_detail b (nolock),
pur_po_indent c (nolock)
WHERE a.company_code = @company_no
and a.locn_no = @location_no
AND a.company_code = b.company_code
AND a.company_code = c.company_code
AND a.locn_no = b.locn_no
AND a.locn_no = c.locn_no
AND a.po_no = b.po_no
AND a.po_no = c.po_no
and b.po_serial = c.po_serial
AND c.indent_no = (SELECT indent_no FROM #pur_pr_list WHERE status = 'D')
AND c.indent_serial= (SELECT serial_no FROM #pur_pr_list WHERE status = 'D')
AND (b.po_no <> (SELECT po_no1 FROM #pur_pr_list WHERE status = 'D')
OR b.po_serial <> (SELECT po_serial1 FROM #pur_pr_list WHERE status = 'D'))
AND (b.po_no <> (SELECT po_no2 FROM #pur_pr_list WHERE status = 'D')
OR b.po_serial <> (SELECT po_serial2 FROM #pur_pr_list WHERE status = 'D'))

AND a.po_status <> 'F'

UPDATE #pur_pr_list
SET po_no3 = @po_no, po_date3 = convert(char(10), @po_date, 103),
po_serial3 = @po_serial,qty_covered3 = @qty_covered,
po3_status = @po3_status
WHERE status = 'D'

UPDATE #pur_pr_list
SET qty_covered = ISNULL(QTY_COVERED1,0) + ISNULL(QTY_COVERED2,0) + ISNULL(QTY_COVERED3,0)
WHERE status = 'D'

UPDATE #pur_pr_list
SET status = 'E'
WHERE status ='D'

END
SET ROWCOUNT 0

IF @flag = 'Y'
DELETE #pur_pr_list
WHERE qty_covered >= qty_required

SELECT @company_name = fs_company_name
from common..fs_company_master (nolock)
WHERE fs_company_code = @company_no

SELECT @heading = 'For the period from '+ convert(char(10), @date_from, 103) + ' to '
+ convert(char(10), @date_to, 103)

update #pur_pr_list
set requester_name = rtrim(ISNULL(H04_First_Name,''))+' '+rtrim(ISNULL(H04_Middle_Name,''))+' '+rtrim(ISNULL(H04_Last_Name,''))
from common..h04_namemaster a(nolock), #pur_pr_list b
where a.C03_Organisation_code = @company_no
and a.H01_emp_num = b.requester

/*******************************************************/
if @Indent_catagory ='gen'
begin
if @auto_indent ='AUTO_INDENT_LIST'
begin
SELECT company_name = ltrim(RTRIM(@company_name)), heading = @heading,
flag = @flag, *
FROM #pur_pr_list
where indent_type = 'gen'
and indent_category = 'GEN'
AND user_id = 2
ORDER BY indent_no, serial_no
end
if @auto_indent ='MANUAL_INDENT_LIST'
begin
SELECT company_name = ltrim(RTRIM(@company_name)), heading = @heading,
flag = @flag, *
FROM #pur_pr_list
where indent_type = 'gen'
and indent_category = 'GEN'
AND user_id != 2
ORDER BY indent_no, serial_no
end
if @auto_indent ='ALL'
begin
SELECT company_name = ltrim(RTRIM(@company_name)), heading = @heading,
flag = @flag, *
FROM #pur_pr_list
where indent_type = 'gen'
and indent_category = 'GEN'
ORDER BY indent_no, serial_no
end

end
if @Indent_catagory = 'ser'
begin
if @auto_indent ='AUTO_INDENT_LIST'
BEGIN
SELECT company_name = ltrim(RTRIM(@company_name)), heading = @heading,
flag = @flag, *
FROM #pur_pr_list
where indent_type = 'gen'
and indent_category = 'SER'
AND USER_ID = 2
ORDER BY indent_no, serial_no
END

if @auto_indent ='MANUAL_INDENT_LIST'
BEGIN
SELECT company_name = ltrim(RTRIM(@company_name)), heading = @heading,
flag = @flag, *
FROM #pur_pr_list
where indent_type = 'gen'
and indent_category = 'SER'
AND USER_ID != 2
ORDER BY indent_no, serial_no
END

if @auto_indent ='ALL'
BEGIN
SELECT company_name = ltrim(RTRIM(@company_name)), heading = @heading,
flag = @flag, *
FROM #pur_pr_list
where indent_type = 'gen'
and indent_category = 'SER'
ORDER BY indent_no, serial_no
END

end

if @Indent_catagory = 'All'

begin
if @auto_indent ='AUTO_INDENT_LIST'
BEGIN
SELECT company_name = ltrim(RTRIM(@company_name)), heading = @heading,
flag = @flag, *
FROM #pur_pr_list
WHERE USER_ID = 2
ORDER BY indent_no, serial_no
END

if @auto_indent ='MANUAL_INDENT_LIST'
BEGIN
SELECT company_name = ltrim(RTRIM(@company_name)), heading = @heading,
flag = @flag, *
FROM #pur_pr_list
where indent_type = 'gen'
and indent_category = 'SER'
AND USER_ID != 2
ORDER BY indent_no, serial_no
END

if @auto_indent ='ALL'

BEGIN
SELECT company_name = ltrim(RTRIM(@company_name)), heading = @heading,
flag = @flag, *
FROM #pur_pr_list
ORDER BY indent_no, serial_no
END
end
/**********************************************************/

end

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources