Need to increase performance - 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 2005 Forums

Transact-SQL (2005)

Need to increase performance

Author

Topic

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2011-07-28 : 00:03:23

Hi All,

Below is my script

DECLARE @USERID VARCHAR(MAX)
SET @USERID = ''
SELECT @USERID = @USERID + CAST(USERID AS VARCHAR(10))+CAST(ISNULL(GROUP1NAME,'') AS VARCHAR(10))+',' FROM USERGROUPS1
SELECT @USERID

The USERGROUPS1 table consists 65000 rows, I need to concate all the userid and group1name column into single string.
The above query take more that 15 minutes to complete.
please suggest me to execute it in faster way.

Solutions are easy. Understanding the problem, now, that's the hard part

Sachin.Nand

2937 Posts

Posted - 2011-07-29 : 03:21:19

Try this



DECLARE @USERID VARCHAR(MAX)

Select @USERID=REPLACE(USERID ,'  ',',') from
(
Select (Select  convert(varchar(10),USERID) + '' + ISNULL(GROUP1NAME,'') FROM USERGROUPS1 for xml path(''))USERID 
)T

Select @USERID

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources