Performance Issue- Solution Required

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)

Performance Issue- Solution Required

Author

Topic

bridge
Yak Posting Veteran

93 Posts

Posted - 2005-07-22 : 05:20:57

I have a table PageTracking. In this table information about page visits is stored against each visit. I want to make the path of pages visited against each visit. e.g.,
if there is Visit 1 and Pages visited agaisnt this Visit are

Note: Data is dummy

Visit_ID Page
1 www.Yahoo.com
1 www.mail.yahoo.com
1 www.yahoo.signout.com

Now I want output like this

Visit_ID Path
1 www.Yahoo.com->www.mail.yahoo.com->www.yahoo.signout.com

Note:
The result should be in one row. Currently I am doing this opening a cursor on Distinct Visit_ID. But this slows down the process as there could be 1 million rows. Please tell me some other fastest solution.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-22 : 05:28:50

Refer this
http://www.sqlteam.com/item.asp?ItemID=2368
Replace comma by ->

Madhivanan

Failing to plan is Planning to fail

bridge
Yak Posting Veteran

93 Posts

Posted - 2005-07-22 : 08:29:27

Perhaps you did not understand my problem. I want to eliminate cursor and the reference you have given they are also using cursor, which I am already using. Comma and -> doesn't matter. Performance is the main issue.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-22 : 08:43:16

Did you read it fully?
See "The New and Improved Approach" as specified at the last

Madhivanan

Failing to plan is Planning to fail

bridge
Yak Posting Veteran

93 Posts

Posted - 2005-07-22 : 09:07:01

Yes I am doing the same way. Opening cursor on Visit_ID and against each visit makes the Path as

select @path_dsc = coalesce(@path_dsc+'->', '') + page from tabble1

but we can not get this thing like a column, this is a string in a variable. Can you tell me if I could get the same thing as a column.
Then I will make a query with two columns Visit_ID and Path. Path would be the column made by using above statement. But I am unable to get this thing as a column. Please tell me how can I

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-22 : 09:13:12

Use a function which returns that
Refer this
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true

Madhivanan

Failing to plan is Planning to fail

bridge
Yak Posting Veteran

93 Posts

Posted - 2005-07-22 : 10:00:26

Thanks dear

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources