need help with slow query - 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

SQL Server Development (2000)

need help with slow query

Author

Topic

ssunny
Posting Yak Master

133 Posts

Posted - 2008-05-19 : 15:26:18

Hi Guys,

I'm trying to find a particular string from a column called articletag(which is a text datatype) from table dbo.getarticle and then inserts results into another table.
For that I wrote a UDF as below.

create function dbo.dba_get_articletag (@input varchar(8000)) returns varchar(8000) as
begin

declare @output varchar(8000),@temp_input varchar(8000),@i int,@loop int,@loopcounter int,
@first_pos int,@second_pos int,@value varchar (1000)

set @output = ''
set @i =0
set @loop = 1
set @temp_input = ''
set @loopcounter = len(ltrim(@input))
while @loop < @loopcounter

begin

if @i > 0
set @input = @temp_input

set @loopcounter = len(ltrim(@input))
select @first_pos = charindex ('<ARTICLE,@input,1)
select @second_pos = charindex ('>',ltrim(@input),@first_pos + 1)

if (@first_pos > 0 and @second_pos > 0)

begin
select @value = substring (ltrim(@input),@first_pos + 1,@second_pos - @first_pos - 1)
set @i = @i+1

end

if @output <> ''
set @output = @output + '|' + @value
else
set @output = @value

set @loop = @second_pos + 1
set @temp_input = substring(ltrim(@input),@loop,len(@input))

if charindex ('<ARTICLE,@temp_input) = 0
goto byebye

end

byebye:

return @output

end

I need to find string statrs with '<ARTICLE' and ends with >.And if there are more than 1 occurance like that then I should get an output like : ARTICLE.... | ARTICLE...... | ARTICLE....
whick works fine.
Now I've created new table in which I want to insert result strings from the UDF :

create table dbo.articlecleanup
(
rowid int identity,
linedata varchar(8000)
)

And my query is:

insert into dbo.articlecleanup
select dbo.dba_get_articletag (convert (varchar(8000),articletag))from dbo.getarticle where ................................

Query is taking longggggggggggggggggg time to execute.So I want to know is there any other way I can achive what I'm trying to achive???

Thanks for the help.

Sunny.

ssunny
Posting Yak Master

133 Posts

Posted - 2008-05-19 : 15:34:09

Forgot to mention that I've around 17,000 records in my DB which satisfies following condition.

select dbo.dba_get_articletag (convert (varchar(8000),articletag))from dbo.getarticle where ................................

Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-19 : 15:50:45

There are plenty of other SPLIT functions.
Have a search.

One of them is named fnParseString.

E 12°55'05.25"
N 56°04'39.16"

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2008-05-19 : 15:56:05

Is this a one time thing or a recurring process?

If it's a once off, then you can play around with your data a bit and make several passes. Try this in your query analyzer.

declare @temp as varchar(600)
set @temp = ';kljhfas;<ARTICLE Yaktacular> akl;fsjdfl;kas<ARTICLE ;lkajsfdsdf>'

select substring(@temp,charindex('<ARTICLE',@temp),charindex('>',@temp)-charindex('<ARTICLE',@temp)+1)

An infinite universe is the ultimate cartesian product.

ssunny
Posting Yak Master

133 Posts

Posted - 2008-05-19 : 16:18:41

quote:
Originally posted by cat_jesus

Is this a one time thing or a recurring process?

If it's a once off, then you can play around with your data a bit and make several passes. Try this in your query analyzer.

declare @temp as varchar(600)
set @temp = ';kljhfas;<ARTICLE Yaktacular> akl;fsjdfl;kas<ARTICLE ;lkajsfdsdf>'

select substring(@temp,charindex('<ARTICLE',@temp),charindex('>',@temp)-charindex('<ARTICLE',@temp)+1)

An infinite universe is the ultimate cartesian product.

Thanks for the quick reply.
It's a one time process.Your code works fine but if the input string has more then 1 occurance of <ARTICLE......> , I need all of them seperates by |.
Let's say from your example I need output like:

ARTICLE Yaktacular | ARTICLE ;lkajsfdsdf

Thx.

ssunny
Posting Yak Master

133 Posts

Posted - 2008-05-21 : 12:26:59

Can someone help me out with this ??

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-21 : 16:01:19

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

E 12°55'05.25"
N 56°04'39.16"

ssunny
Posting Yak Master

133 Posts

Posted - 2008-05-22 : 12:30:37

ok. Here's my table dba_test

test_id (int) linedata (text)

1 <dhjcbd>hjbshb <p><img src="aaaaaaaa.jpg" align="center">hcvdbwhbi

2 gjcjjcd<>sdcv <img src="bbb.jpg" align="center"> dgg<p>testing <img src="ccccc.jpg" align="center">

3 <img src="dddd.jpg" align="center">vjbhvhadsbvkj<img src="crfdtg.gif" align="center">()*&dff<img src="tttt.jpg" align="center">vfvjh''""
.... ..........

17000 <img src="kkkdd.jpg" align="center">

From this table I want to find out image part (from linedata shown below) and inserts into dba_test_out table and result should look like this:

dba_test_out

test_id (int) linedata (text)

1 aaaaaaaa.jpg

2 bbb.jpg | ccccc.jpg

3 dddd.jpg | crfdtg.gif | tttt.jpg

..... .........

17000 kkkdd.jpg

As you can see if there are more then 1 images in the linedata for any test_id I want them to insert them seperated by '|'.

Now I wrote a cursor to do this operation:

create table #texttemp (textdata text)
declare test1 cursor for select test_id from dba_test where charindex('<img src="',linedata,1) > 0
truncate table dba_test_out
open test1
declare @test_id int
fetch next from test1 into @test_id
set nocount on
while @@fetch_status = 0
begin

delete #texttemp
insert into #texttemp select linedata from dba_test where test_id = @test_id
exec dbo.dba_cleartag_test
insert into dba_test_out (test_id,linedata) select @test,textdata from #texttemp
fetch next from test1 into @test_id
end
close test1
deallocate test1
go

And here's the store proc [dbo.dba_cleartag_test] which is being called by cursor.

create proc dbo.dba_cleartag_test as

declare @output varchar(8000),@temp_input varchar(8000),@i int,@loop int,@loopcounter int,
@first_pos int,@second_pos int,@value varchar (1000)
declare @input varchar(8000)

declare @mytextptr varbinary(16)
select @mytextptr = textptr(textdata) from #texttemp
set @input = (select convert (varchar(8000),textdata) from #texttemp)
set @output = ''
set @i =0
set @loop = 1
set @temp_input = ''
set @loopcounter = len(ltrim(@input))
while @loop < @loopcounter

begin

if @i > 0
set @input = @temp_input

set @loopcounter = len(ltrim(@input))
select @first_pos = charindex ('img src="',@input,1)
select @second_pos = charindex ('align',ltrim(@input),@first_pos + 1)

if (@first_pos > 0 and @second_pos > 0)

begin
select @value = substring (ltrim(@input),@first_pos + 9,@second_pos - @first_pos - 11)
set @i = @i+1

end

if @output <> ''
set @output = @output + ' | ' + @value
else
set @output = @value

set @loop = @second_pos + 1
set @temp_input = substring(ltrim(@input),@loop,len(@input))

if charindex ('img src="',@temp_input) = 0
break

writetext #texttemp.textdata @mytextptr @output

end

GO

Now my problem is this cursor operation is taking long time.Yesterday query ran for 10 hours and it did't complete and eventually I had to cancle query.

So any idea how can I improve query???

Please advise. This is something urgent for me.

Thanks,

Sunny.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources