Edit: Using the UNION operator

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)

Edit: Using the UNION operator

Author

Topic

cbrinson
Starting Member

36 Posts

Posted - 2002-04-04 : 17:03:03

Here is the scenario. I have a custom forum system with a root table for new topics and a child table for all replies to new topics. I want to select all the posts corresponding to a given new topic (for a specific root_id) and paginate them. So I end up with a temp table with the following data:

#temp_tbl
temp_id root_id child_id
1 345 0
2 345 346
3 345 348
4 345 349
5 345 355

I want to return a single recordset and join the first entry in #temp_tbl to the "root" table only when page 1 is requested and join the rest of the records to the "child" table.

I know this solution works but will it be overly slow?

IF @page_num = 1
BEGIN

(SELECT wr.nickname
,wr.subject
,wr.message
,wr.thread_start_date
,wr.root_id
,0
,wr.member_id
FROM [dbo].[#webboard_temp] temp_tbl
LEFT JOIN webboardII_root wr ON temp_tbl.root_id = wr.root_id
WHERE temp_tbl.temp_id =1)
UNION ALL
(SELECT wc.nickname
,wc.subject
,wc.message
,wc.post_date
,wc.root_id
,wc.child_id
,wc.member_id
FROM [dbo].[#webboard_temp] temp_tbl
JOIN webboardII_children wc ON temp_tbl.child_id = wc.child_id
WHERE temp_tbl.temp_id BETWEEN (@page_size * (@page_num - 1))+1 AND (@page_size * @page_num) )
END

ELSE

BEGIN
SELECT wc.nickname
,wc.subject
,wc.message
,wc.post_date
,wc.root_id
,wc.child_id
,wc.member_id
FROM [dbo].[#webboard_temp] temp_tbl
JOIN webboardII_children wc ON temp_tbl.child_id = wc.child_id
WHERE temp_tbl.temp_id BETWEEN (@page_size * (@page_num - 1))+1 AND (@page_size * @page_num)
ORDER BY temp_tbl.temp_id ASC
END

Thanks as always for the help!

-Chris

Edited by - cbrinson on 04/04/2002 18:33:23

Edited by - cbrinson on 04/04/2002 18:34:48

cbrinson
Starting Member

36 Posts

Posted - 2002-04-05 : 15:06:18

Anyone? My explanation is not that clear I guess? I just wanted to bump this to the top one time to see if anyone had any feedback. The situation is just that I need to paginate posts to a given thread (such as the one you are now reading). Thanks as always for the help.

Take care,
Chris

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2002-04-05 : 15:13:56

What do you think will be slow about it?

Obviously the If statement will not be slow

I don't not believe the UNION statement will be slow either.

As for your joins, the speed of the query will really depend on exactly how your tables are set up.

Jay99

468 Posts

Posted - 2002-04-05 : 15:52:53

quote:

I know this solution works but will it be overly slow?

It depends on...

Your definition of slow.

Your hardware.

Your indexing.

The amount of data being processed.

Load on the server.

blah blah blah

You tell us. Is it overly slow? What could you do to make if faster . . .

Re-definite slow.

Upgrade you hardware.

Improve you indexing.

Decrease the amount of data being processed.

Decrease the load on the server.

blah blah blah

If you would like someone to take a crack at improving performance, give us your ddl (Create table statements) and some sample data so we can work with it a bit.

Aside from that, I'll toss a couple things in. First, if you are on 2k, you may find Table Variables useful here assuming you have enuff RAM. I would bet there is a way to write this as one select statement rather than having a large IF..ELSE.. thingy. DDL would help for that . . . Finally, maybe there is a way to handle paging in some middle tier rather than on the db....

Jay
<O>

cbrinson
Starting Member

36 Posts

Posted - 2002-04-05 : 16:21:42

It is not slow at all but it is running on a development server with one user executing the proc. The live site is a 8 Million impression a month site. I guess what I was saying is, this is the solution I was able to come up with to address the problem of figuring out how to paginate the posts within a thread. Does it seem like a clumsy solution or does it seem like a reasonable solution? I am not the best SQL programmer so I don't know if using an if statement to determine when I need to use the union operator is a good solution. I was just seeking feedback from more experience SQL programmers.

The DB server has a Gig of RAM and runs on Win2k. I would think paging on the DB would be much faster than in a middle tier since you would not have to send excess data that will not be sent to the client from the DB server to the web/application server. Plus, creating temp tables is supposed to be very quick, right? Also, I was thinking in terms of being consistent. The forum tables have hundreds of thousands of records in them so when displaying the list of threads you certainly would want to paginate on the database so that you are not sending a hundred thousand records to the application server.

Chris

Jay99

468 Posts

Posted - 2002-04-05 : 16:33:38

Can you do this...


SELECT wc.nickname 
,wc.subject 
,wc.message 
,wc.post_date 
,wc.root_id 
,wc.child_id 
,wc.member_id 
FROM [dbo].[#webboard_temp] temp_tbl 
JOIN webboardII_children wc
ON (temp_tbl.child_id = wc.child_id  or
   (temp_tbl.root = wr.rood_id and
    @page_num = 1))
WHERE
    temp_tbl.temp_id BETWEEN (@page_size * (@page_num - 1))+1 AND (@page_size * @page_num) or
   (temp_tbl.temp_id = 1 and @page_num = 1)

Without seeing you DDL, its hard to tell . . .

As to if it is a generally kludgy solution? . . . I would say that based on the information you have given us, it seem reasonable . . .

Jay
<O>

EDIT: I am not altogether sure you can construct an ON like that . . . It was just an idea

Edited by - Jay99 on 04/05/2002 16:34:25

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources