Instead of Using Cursor? Other Ways?

Question

Post reply

Instead of Using Cursor? Other Ways?

pockeyfan

Old Hand

Points: 355
More actions
August 13, 2008 at 3:11 am

#70982

Hi, all
My SP runs slowly. I checked the source codes and found it is because of using the following cursor to update "email" column:
declare @email varchar(100)
declare email_cursor cursor for
select customer_id, address_description1
from #tmp_410c
open email_cursor
fetch email_cursor into @customer_id, @email
while @@sqlstatus = 0
begin
update #tmp_410a
set email = email + " / "
where customer_id = @customer_id
and rtrim(ltrim(email)) <> ""
update #tmp_410a
set email = email + @email
where customer_id = @customer_id
fetch email_cursor into @customer_id, @email
end
close email_cursor
deallocate cursor email_cursor
It costs about 3min30sec to run the above codes. During busy time, it even costs 11min. Since one cusotmer may have many different E-mail address, using cursor can get all the addresses. However, time-consuming is the problem.
I tried to update the #tmp_410a as below, it only costs several seconds, however, only two different E-mail addresses could be retrieved. For certain customer, there may be 4 or 5 E-mail addresses:
update #tmp_410a
set tmpa.email = tmpc.address_description1
from #tmp_410a tmpa, #tmp_410c tmpc
where tmpa.customer_id = tmpc.customer_id
update #tmp_410a
set tmpa.email = tmpa.email + " / " + tmpc.address_description1
from #tmp_410a tmpa, #tmp_410c tmpc
where tmpa.customer_id = tmpc.customer_id and
rtrim(ltrim(tmpa.email)) <> rtrim(ltrim(tmpc.address_description1))
Is there any other way to get all the E-mail addresses instead of using cursor? Thanks a lot!

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply

karthik M One Orange Chip Points: 29139 More actions · Answer 1

karthik M

One Orange Chip

Points: 29139

August 13, 2008 at 3:49 am

#857255

Can you Post the same table and data ?

karthik

pockeyfan Old Hand Points: 355 More actions · Answer 2

Hi, for table #tmp_410a, I retrieve some information from several tables and store the data into this temprary table:

select

ad.address_id,

ca.customer_account_id,

ca.customer_account_title,

......

convert(varchar(100), '') email,

......

into #tmp_410a

from customer_account ca,

address ad,

......

where

......

As shown above, "email" column contains nothing at beginning, then, I try to update "email" to store all the avaiable E-mail addresses for certain customers :

update #tmp_410a

set email = tmp.email + ad.address_description1

from #tmp_410a tmp

inner join customer_account_address caa

on tmp.customer_account_id = caa.customer_account_id

inner join address ad

on caa.address_id = ad.address_id

inner join customer cu

on ad.customer_id = cu.customer_id

and ad.internet_tag = 'Y'

However, for a certain customer (customer_id), there may be 4 to 5 different E-Mail addresses, I want to get all of them and store one by one with a " / " in the "email" column, that's why I use "Cursor" to implement it, however, time-consuming problem arises with "Cursor".

update #tmp_410a

set email = tmp.email + " / " + ad.address_description1

from #tmp_410a tmp

inner join customer_account_address caa

on tmp.customer_account_id = caa.customer_account_id

inner join address ad

on caa.address_id = ad.address_id

inner join customer cu

on ad.customer_id = cu.customer_id

and ad.internet_tag = 'Y'

where rtrim(ltrim(tmp.email)) <> ''

I want to know if there's other way to get all the E-Mail addresses without using "Cursor", by direct "update", I can only get two different E-mail addresses.

Am I clear?

pockeyfan Old Hand Points: 355 More actions · Answer 3

I want to get the following result sets:

In table address, there are several rows to store different E-Mail addresses, for example:

customer_id address_description1
HK08012698 edxxxxx@gmail.com
HK08012698 kimxxxx@gmail.com
HK08012698 yunxxxx@hotmail.com

When update "#tmp_410a", I hope to store all the above E-mail addresses into "email", the result sets are as follows:

customer_id email
HK08012698 edxxxxx@gmail.com / kimxxxx@gmail.com / yunxxxx@hotmail.com
HK08012698 edxxxxx@gmail.com / kimxxxx@gmail.com / yunxxxx@hotmail.com
HK08012698 edxxxxx@gmail.com / kimxxxx@gmail.com / yunxxxx@hotmail.com

However, without "Cursor", I could only get two different E-mail addresses as shown:

customer_id email
HK08012698 edxxxxx@gmail.com / kimxxxx@gmail.com
HK08012698 edxxxxx@gmail.com / kimxxxx@gmail.com
HK08012698 edxxxxx@gmail.com / kimxxxx@gmail.com

Lester Policarpio SSCertifiable Points: 7109 More actions · Answer 4

"I think" it can be done using a tally table. Some experts here in the forum maybe can share something about it.

And also please read the article of The "Great" Jeff Moden about tally table 😀 the link is provided below...

http://www.sqlservercentral.com/articles/TSQL/62867/

"-=Still Learning=-"

Lester Policarpio

pockeyfan Old Hand Points: 355 More actions · Answer 5

Thanks for your suggestion!

I'm new to SQL and SP, will read the article to see whether I could apply it in my case...

sgmunson SSC Guru Points: 110639 More actions · Answer 6

I'm going to suggest along another an entirely different line of thought. What reason do you have for aggregating all the e-mail addresses into a single line? It's almost always far better to store the individual e-mail addresses as separate records, and even for display purposes, it's usually best to have them all on separate lines, or if the display medium is wide enough, perhaps a specific limit of the first two or three going "across the page".

The reason I'm asking is because the resulting code for aggregating the strings is likely to be "poor performing" whereas a more set-based solution would probably not be. If you can expand on the reason why, there may be a significantly better solution.

Steve

(aka smunson)

:):):)

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

Lester Policarpio (8/13/2008)
"I think" it can be done using a tally table. Some experts here in the forum maybe can share something about it.
And also please read the article of The "Great" Jeff Moden about tally table 😀 the link is provided below...
http://www.sqlservercentral.com/articles/TSQL/62867/

Heh... thanks for the plug, Lester. This one wouldn't be a Tally table solution, though. It would be a "concatenation function". See the following link...

http://www.sqlservercentral.com/articles/Test+Data/61572/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 8

pockeyfan (8/14/2008)
Thanks for your suggestion!
I'm new to SQL and SP, will read the article to see whether I could apply it in my case...

Tally table won't do it, in this case. You need a "Concatenation Function". For details on that, please refer to the following article for how to do it and some of the pitfalls...

http://www.sqlservercentral.com/articles/Test+Data/61572/

For your current problem, perhaps a demonstration is in order...

Let's say your data looked like this (THIS is the way you should post test data! See the link in my signature line for more details, please).... this is NOT a part of the solution! We're just building some test data to demonstrate the code with... 😉

CREATE TABLE dbo.JBMTest

(Customer_ID VARCHAR(10), Address_Description1 VARCHAR(118))

INSERT INTO dbo.JBMTest

(customer_id,address_description1)

SELECT 'HK08012698','edxxxxx@gmail.com' UNION ALL

SELECT 'HK08012698','kimxxxx@gmail.com' UNION ALL

SELECT 'HK08012698','yunxxxx@hotmail.com' UNION ALL

SELECT 'HK08012699','sallyxx@gmail.com' UNION ALL

SELECT 'HK08012699','joexxxx@gmail.com' UNION ALL

SELECT 'HK08012697','yangxxx@hotmail.com' UNION ALL

SELECT 'HK08012700','ed1xxxx@gmail.com' UNION ALL

SELECT 'HK08012700','ed2xxxx@gmail.com' UNION ALL

SELECT 'HK08012700','ed3xxxx@gmail.com' UNION ALL

SELECT 'HK08012700','ed4xxxx@gmail.com' UNION ALL

SELECT 'HK08012700','ed5xxxx@gmail.com' UNION ALL

SELECT 'HK08012700','ed6xxxx@gmail.com' UNION ALL

SELECT 'HK08012700','ed7xxxx@gmail.com' UNION ALL

SELECT 'HK08012700','ed8xxxx@gmail.com' UNION ALL

SELECT 'HK08012700','ed9xxxx@gmail.com'

What you need to do is build a "Concatenation Function" so concatenate all email addresses for each customer... like this...

CREATE FUNCTION dbo.ConCatEmailDemo

(@Customer_ID VARCHAR(10))

RETURNS VARCHAR(8000)

AS

BEGIN

--===== Declare the return variable

DECLARE @Return VARCHAR(8000)

--===== Concatenate all email addresses for the given customer

SELECT @Return = COALESCE(@Return+',','') + Address_Description1

FROM dbo.JBMTest

WHERE Customer_ID = @Customer_ID

RETURN @Return

END

Then, avoiding one of the pitfalls I mentioned in the article link I posted above, your code would take the general direction of the following...

SELECT Customer_ID,dbo.ConCatEmailDemo(Customer_ID) AS AllEmailAddresses

FROM dbo.JBMTest

GROUP BY Customer_ID

Once you have that, you can use that SELECT to drive an UPDATE or whatever...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

karthik M One Orange Chip Points: 29139 More actions · Answer 9

An alternate way, It may fit your requirement.

create table Email_ID

(

CustomerID varchar(30),

Address varchar(50)

)

insert into Email_ID

SELECT 'HK08012698','edxxxxx@gmail.com' UNION ALL

SELECT 'HK08012698','kimxxxx@gmail.com' UNION ALL

SELECT 'HK08012698','yunxxxx@hotmail.com' UNION ALL

SELECT 'HK08012699','sallyxx@gmail.com' UNION ALL

SELECT 'HK08012699','joexxxx@gmail.com' UNION ALL

SELECT 'HK08012697','yangxxx@hotmail.com' UNION ALL

SELECT 'HK08012700','ed1xxxx@gmail.com' UNION ALL

SELECT 'HK08012700','ed2xxxx@gmail.com' UNION ALL

SELECT 'HK08012700','ed3xxxx@gmail.com' UNION ALL

SELECT 'HK08012700','ed4xxxx@gmail.com' UNION ALL

SELECT 'HK08012700','ed5xxxx@gmail.com' UNION ALL

SELECT 'HK08012700','ed6xxxx@gmail.com' UNION ALL

SELECT 'HK08012700','ed7xxxx@gmail.com' UNION ALL

SELECT 'HK08012700','ed8xxxx@gmail.com' UNION ALL

SELECT 'HK08012700','ed9xxxx@gmail.com'

Create Procedure Email_ID_display

(

@CustID varchar(30)

)

as

Begin

Declare @add varchar(255)

select @add = ''

select @add = @add +Address + '/'

from Email_ID

where CustomerID = @CustID

select @CustID,substring(@Add,1,len(@Add)-1)

End

exec Email_ID_display 'HK08012700'

karthik

pockeyfan Old Hand Points: 355 More actions · Answer 10

Hi, karthikeyan, thanks

However, I could only get the " ed9xxxx@gmail.com" with your codes...

karthik M One Orange Chip Points: 29139 More actions · Answer 11

Modify tthe code as below and let me know.

Declare @add varchar(255)

select @add = ''

select @add = @add + '/' +Address

from Email_ID

where CustomerID = @CustID

select @CustID,substring(@Add,2,len(@Add))

karthik

pockeyfan Old Hand Points: 355 More actions · Answer 12

I tried and got the result "/ed9xxxx@gmail.com", it seems the @add could only store the last record.

karthik M One Orange Chip Points: 29139 More actions · Answer 13

Can you post the code which you run on your system ?

karthik

pockeyfan Old Hand Points: 355 More actions · Answer 14

Hi, I just use your codes, you can try on your system

create table Email_ID
(
CustomerID varchar(30),
Address varchar(50)
)
insert into Email_ID
SELECT 'HK08012698','edxxxxx@gmail.com' UNION ALL
SELECT 'HK08012698','kimxxxx@gmail.com' UNION ALL
SELECT 'HK08012698','yunxxxx@hotmail.com' UNION ALL
SELECT 'HK08012699','sallyxx@gmail.com' UNION ALL
SELECT 'HK08012699','joexxxx@gmail.com' UNION ALL
SELECT 'HK08012697','yangxxx@hotmail.com' UNION ALL
SELECT 'HK08012700','ed1xxxx@gmail.com' UNION ALL
SELECT 'HK08012700','ed2xxxx@gmail.com' UNION ALL
SELECT 'HK08012700','ed3xxxx@gmail.com' UNION ALL
SELECT 'HK08012700','ed4xxxx@gmail.com' UNION ALL
SELECT 'HK08012700','ed5xxxx@gmail.com' UNION ALL
SELECT 'HK08012700','ed6xxxx@gmail.com' UNION ALL
SELECT 'HK08012700','ed7xxxx@gmail.com' UNION ALL
SELECT 'HK08012700','ed8xxxx@gmail.com' UNION ALL
SELECT 'HK08012700','ed9xxxx@gmail.com'
Create Procedure Email_ID_display
(
@CustID varchar(30)
)
as
Begin
Declare @add varchar(255)
select @add = ''
select @add = @add + '/' +Address
from Email_ID
where CustomerID = @CustID
select @CustID,substring(@Add,2,len(@Add))
End
exec Email_ID_display 'HK08012700'
drop table Email_ID
DROP PROCEDURE Email_ID_display