Performance Required - 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)

Performance Required

Author

Topic

bridge
Yak Posting Veteran

93 Posts

Posted - 2005-06-07 : 05:48:39

I have a visit table. I want to know what was the path of the visit i.e; If the user is visiting Yahoo page. Then his visit suppose is 1 and he first open the home page then news, shopping, sign me up etc. etc. This sequence of visiting the pages is his path. Please see the code and I hope you’ll understand. The code works fine. But if there are 200, 000 rows then it deads keeps on running. Please check if we can optimize the code?

set nocount on
declare @visit_id int, @path_id varchar(8000), @path_dsc varchar(8000), @sr_nbr int
declare cur_path cursor
scroll
dynamic
for
select distinct visit_id
from visit
order by visit_id

open cur_path
fetch first from cur_path
into @visit_id

while @@fetch_status = 0
begin
select @path_id = coalesce(@path_id, '') + cast(pageid as varchar)+'-',
@path_dsc = coalesce(@path_dsc+'->', '') + page
from PageTracking
where visit_id <> 0 and visit_id = @visit_id and page is not null and page <> ''
order by visit_id, page_hit_time asc

set @path_id = left(@path_id, len(@path_id)-1)

if @path_id is not null
begin
if not exists(select 1 from path where path_id = @path_id)
begin
select @sr_nbr = isnull(max(sr_nbr), 0) + 1 from path
insert into path values (@sr_nbr, @path_id, @path_dsc)
end

print @visit_id; print @path_id
if not exists(select 1 from visit where visit_id = @visit_id and path_id = @path_id)
begin
update visit
set path_id = @path_id
where visit_id = @visit_id
end
end
set @path_id = null
set @path_dsc = null

fetch next from cur_path
into @visit_id
end
close cur_path
deallocate cur_path

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-06-07 : 06:45:53

Could You please provide DDL and some sample data for your tables visit, PageTracking, path.
I think You can optimize the code by removing some cursors

rockmoose

bridge
Yak Posting Veteran

93 Posts

Posted - 2005-06-07 : 07:11:15

Ok, give me your email so I can give you the schema and sample data

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-06-07 : 07:13:21

Why not post it here, so we all can help you?

bridge
Yak Posting Veteran

93 Posts

Posted - 2005-06-07 : 07:37:17

I am unable to see the option for attaching files here

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-06-07 : 07:40:47

You can keep it simple.
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

We basically just need a few sample rows to play with.
Make the sample cover all the quirky situations that You can think of.
Remember You are having performance problems, so maybe tackling the problem from another angle might do the trick.

rockmoose

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-06-07 : 07:42:29

You cant it should be in text

eg
CREATE TABLE TableName(..........)

INSERT TableName(...................)
VALUES (..........................)

Desired result
Col1 |Col2 | Col3
1 | A | 42

What you have so far
SELECT ...........
FROM
WHERE

Andy

Beauty is in the eyes of the beerholder