Query - Performance Problem - 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)

Query - Performance Problem

Author

Topic

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-12-02 : 06:50:05

Dear All,
I have the Following Query it's Working good but it Gives poor performace anybody can able to suggest me to improve the performance

SELECT @counter_table = ( SELECT COUNT(*) FROM #tcd_temp )

SELECT @tcd_desc = ''
SELECT @Tcd_Amount = ''

WHILE ( @counter <> @counter_table )
BEGIN
SELECT @counter = @counter + 1
SELECT @tcd_desc = @tcd_desc + char(10)+ tcd_desc ,
@Tcd_Amount = @Tcd_Amount + char(10) + CONVERT(VARCHAR(20),Convert(Numeric(28,2),tcd_amt))
FROM #tcd_temp
WHERE SNo = @counter
END

insert into #tcd_temp_inv(tcd_desc,tcd_amt,inv_no)
values (ltrim(rtrim(@tcd_desc)),ltrim(rtrim(@Tcd_Amount)),ltrim(rtrim(@invoice_no)))

Thanks and regards
Krishnakumar.C

Kristen
Test

22859 Posts

Posted - 2006-12-02 : 07:26:00

What about getting rid of the loop and just doing:

SELECT @tcd_desc = @tcd_desc + char(10)+ tcd_desc ,
@Tcd_Amount = @Tcd_Amount + char(10) + CONVERT(VARCHAR(20),Convert(Numeric(28,2),tcd_amt))
FROM #tcd_temp

??

(Although I think this no longer works, in this manner, under SQL 2005)

Kristen

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-12-02 : 07:37:04

here they want to concat all of the taxes (It have 3 to 7 taxes ) in a single row
then they have inserting into another temp table.

in the counter table i have 100,000 records, so it's realy slow

the actual query is
while (select count(*) from #inv)>0
begin
select @invoice_no=''
select @invoice_no=min(invoice_no) from #inv

--Make the temp table for tcd
CREATE TABLE #tcd_temp (
SNo INT IDENTITY (1,1) ,
tcd_no cmn_tax_cd,
tcd_amt cmn_pamt,
tcd_desc varchar(255)
)

INSERT INTO #tcd_temp ( tcd_no , tcd_amt , tcd_desc )
SELECT
tcd.tcd_no ,
CONVERT(VARCHAR(20),Convert(Numeric(28,2),tcd_amt)),
dsc.tcd_desc

FROM shp..Dms_t_dms_doc_tcd tcd (nolock),
shp..M_TCD dsc (nolock)

WHERE tcd.invoice_no = @Invoice_no
and tcd.company_no = dsc.company_no
and tcd.invoice_locn= dsc.locn_no
and tcd.tcd_no = dsc.tcd_no

ORDER BY invoice_no ,tcd_desc desc

DECLARE @counter int
DECLARE @counter_table int

SELECT @counter = 0
SELECT @counter_table = 0

SELECT @counter_table = ( SELECT COUNT(*) FROM #tcd_temp )

SELECT @tcd_desc = ''
SELECT @Tcd_Amount = ''

WHILE ( @counter <> @counter_table )
BEGIN
SELECT @counter = @counter + 1
SELECT @tcd_desc = @tcd_desc + char(10)+ tcd_desc ,
@Tcd_Amount = @Tcd_Amount + char(10) + CONVERT(VARCHAR(20),Convert(Numeric(28,2),tcd_amt))
FROM #tcd_temp
WHERE SNo = @counter
END
-- SELECT @tcd_desc
-- SELECT @Tcd_Amount
--SELECT * FROM #tcd_temp
insert into #tcd_temp_inv(tcd_desc,tcd_amt,inv_no)
values (ltrim(rtrim(@tcd_desc)),ltrim(rtrim(@Tcd_Amount)),ltrim(rtrim(@invoice_no)))

DROP TABLE #tcd_temp
delete From #inv where invoice_no=@invoice_no
end

Kristen
Test

22859 Posts

Posted - 2006-12-03 : 02:50:43

"here they want to concat all of the taxes (It have 3 to 7 taxes ) in a single row
then they have inserting into another temp table."

OK, I think I get that. But why the loop? Doesn't a SELECT, on its own, concatenate all the values in one go?

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources