Snitz Forums 2000
Snitz Forums 2000
Home | Profile | Register | Active Topics | Members | Search | FAQ
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 Help Groups for Snitz Forums 2000 Users
 Help: General / Classic ASP versions(v3.4.XX)
 Strange problem		  New Topic  Topic Locked
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 3

Podge
Support Moderator

Ireland
3776 Posts
Posted - 07 October 2009 :  17:32:54  Show Profile  Send Podge an ICQ Message  Send Podge a Yahoo! Message
Thanks HuwR. I think you missed the point of my question, which was - you can only use the columns in the order that they were selected.

That doesn't explain what is happening sporadically on forum.asp though. I suspect its something to do with paging & lots of rows being returned.

I meant an SQL forum as opposed to MS-SQL.
Podge.

The Hunger Site - Click to donate free food | My Blog | Snitz 3.4.05 AutoInstall (Beta!)

My Mods: CAPTCHA Mod | GateKeeper Mod
Tutorial: Enable subscriptions on your board

Warning: The post above or below may contain nuts.
Go to Top of Page

HuwR
Forum Admin

United Kingdom
20600 Posts
Posted - 07 October 2009 :  17:41:36  Show Profile  Visit HuwR's Homepage
quote:
Originally posted by Podge

Thanks HuwR. I think you missed the point of my question, which was - you can only use the columns in the order that they were selected.

No, I didn't, you can use them in any order, the issue is that with some versions of MDAC ALL MEMO FIELDS must come last in the select statement.

the MS-SQL forum is for microsoft flavoured SQL, the My-SQL forum is for my-sql flavoured SQL since they are not the same, that is why there is a forum for each db type, they all use slightly different SQL

have never come across any issues with forum.asp regardless of how many pages of topics it returns, how many rows/pages are you talking about
Go to Top of Page

Podge
Support Moderator

Ireland
3776 Posts
Posted - 07 October 2009 :  18:11:30  Show Profile  Send Podge an ICQ Message  Send Podge a Yahoo! Message
That makes it clearer. Thanks.

quote:
have never come across any issues with forum.asp regardless of how many pages of topics it returns, how many rows/pages are you talking about

In this particular forum its retuning 4297 rows when you really only need 20 rows and the number of pages (or the total rowcount) to calculate paging. The first few times I load forum.asp nothing shows up. When the query finally returns all the records its cached by sql server so that subsequent requests for it are returned much faster. The problem I have is that I have hundreds of very large forums, resulting in many hundreds of thousands of rows being returned (with 500 concurrent users, lots of them are viewing forum.asp at the same time). MySql has the LIMIT keyword but MSSQL 2000 doesn't. Am I mis-understanding how paging works on forum.asp ?
Podge.

The Hunger Site - Click to donate free food | My Blog | Snitz 3.4.05 AutoInstall (Beta!)

My Mods: CAPTCHA Mod | GateKeeper Mod
Tutorial: Enable subscriptions on your board

Warning: The post above or below may contain nuts.
Edited by - Podge on 07 October 2009 18:12:37
Go to Top of Page

HuwR
Forum Admin

United Kingdom
20600 Posts
Posted - 07 October 2009 :  19:07:19  Show Profile  Visit HuwR's Homepage
you would have to implement your owwn paging using a stored procedure (the .Net version does that), see here for an example
Go to Top of Page

Podge
Support Moderator

Ireland
3776 Posts
Posted - 08 October 2009 :  07:05:03  Show Profile  Send Podge an ICQ Message  Send Podge a Yahoo! Message
This is the problem query (contains mod columns that you may need to remove to test on your forum)

SELECT T.T_STATUS, T.CAT_ID, T.FORUM_ID, T.TOPIC_ID, T.T_VIEW_COUNT, T.T_SUBJECT, T.T_ISPOLL,T.T_AUTHOR, T.T_STICKY, T.T_REPLIES, T.T_UREPLIES, T.T_LAST_POST, T.T_LAST_POST_AUTHOR, T.T_LAST_POST_REPLY_ID, M.M_NAME, MEMBERS_1.M_NAME AS LAST_POST_AUTHOR_NAME, T.T_MSGICON 

FROM FORUM_MEMBERS M, FORUM_TOPICS T, FORUM_MEMBERS AS MEMBERS_1 

WHERE M.MEMBER_ID = T.T_AUTHOR AND T.T_LAST_POST_AUTHOR = MEMBERS_1.MEMBER_ID AND T.FORUM_ID = 2 ORDER BY T.T_STICKY DESC, T.T_LAST_POST DESC


This is what I've come up with (no need for a stored procedure).

SELECT TOP [pagesize] T.T_STATUS, T.CAT_ID, T.FORUM_ID, T.TOPIC_ID, T.T_VIEW_COUNT, T.T_SUBJECT, T.T_ISPOLL,T.T_AUTHOR, T.T_STICKY, T.T_REPLIES, T.T_UREPLIES, T.T_LAST_POST, T.T_LAST_POST_AUTHOR, T.T_LAST_POST_REPLY_ID, M.M_NAME, MEMBERS_1.M_NAME AS LAST_POST_AUTHOR_NAME, T.T_MSGICON 
FROM FORUM_MEMBERS M, FORUM_TOPICS T, FORUM_MEMBERS AS MEMBERS_1 
WHERE M.MEMBER_ID = T.T_AUTHOR AND T.T_LAST_POST_AUTHOR = MEMBERS_1.MEMBER_ID AND T.FORUM_ID = 2 
AND TOPIC_ID NOT IN (

SELECT TOPIC_ID FROM (

SELECT TOP [offset] T.T_STATUS, T.CAT_ID, T.FORUM_ID, T.TOPIC_ID, T.T_VIEW_COUNT, T.T_SUBJECT, T.T_ISPOLL,T.T_AUTHOR, T.T_STICKY, T.T_REPLIES, T.T_UREPLIES, T.T_LAST_POST, T.T_LAST_POST_AUTHOR, T.T_LAST_POST_REPLY_ID, M.M_NAME, MEMBERS_1.M_NAME AS LAST_POST_AUTHOR_NAME, T.T_MSGICON 
FROM FORUM_MEMBERS M, FORUM_TOPICS T, FORUM_MEMBERS AS MEMBERS_1 
WHERE M.MEMBER_ID = T.T_AUTHOR AND T.T_LAST_POST_AUTHOR = MEMBERS_1.MEMBER_ID AND T.FORUM_ID = 2 ORDER BY T.T_STICKY DESC, T.T_LAST_POST DESC)
as Table_1)

ORDER BY T.T_STICKY DESC, T.T_LAST_POST DESC


[pagesize] is the amount of rows per page you want i.e. 15 topics per page
[offset] is zero based and can be calculated by multiplying the pagesize by the page number you want and subtracting 1 from this total for example

15 topics per page and you want page 1 then offset = (0 * 15) = 0 (rows 0-14)
15 topics per page and you want page 2 then offset = (1 * 15) = 15 (rows 15-29)
15 topics per page and you want page 3 then offset = (2 * 15) = 30 (rows 30-44)

The greater the page number you want the less efficient the query is however it should still be better than returning all rows.

I didn't have much time to test it out fully. Any one see any problems with this method ?
Podge.

The Hunger Site - Click to donate free food | My Blog | Snitz 3.4.05 AutoInstall (Beta!)

My Mods: CAPTCHA Mod | GateKeeper Mod
Tutorial: Enable subscriptions on your board

Warning: The post above or below may contain nuts.
Go to Top of Page

HuwR
Forum Admin

United Kingdom
20600 Posts
Posted - 08 October 2009 :  10:41:48  Show Profile  Visit HuwR's Homepage
why not do this


SELECT TOP [pagesize] T.T_STATUS, T.CAT_ID, T.FORUM_ID, T.TOPIC_ID, T.T_VIEW_COUNT, T.T_SUBJECT, T.T_ISPOLL,T.T_AUTHOR, T.T_STICKY, T.T_REPLIES, T.T_UREPLIES, T.T_LAST_POST, T.T_LAST_POST_AUTHOR, T.T_LAST_POST_REPLY_ID, M.M_NAME, MEMBERS_1.M_NAME AS LAST_POST_AUTHOR_NAME, T.T_MSGICON FROM FORUM_MEMBERS M, FORUM_TOPICS T, FORUM_MEMBERS AS MEMBERS_1 WHERE M.MEMBER_ID = T.T_AUTHOR AND T.T_LAST_POST_AUTHOR = MEMBERS_1.MEMBER_ID AND T.FORUM_ID = 2 AND TOPIC_ID NOT IN (SELECT TOPIC_ID FROM (SELECT TOP [offset] T.TOPIC_ID FROM FORUM_MEMBERS M, FORUM_TOPICS T, FORUM_MEMBERS AS MEMBERS_1 WHERE M.MEMBER_ID = T.T_AUTHOR AND T.T_LAST_POST_AUTHOR = MEMBERS_1.MEMBER_ID AND T.FORUM_ID = 2 ORDER BY T.T_STICKY DESC, T.T_LAST_POST DESC)as Table_1)ORDER BY T.T_STICKY DESC, T.T_LAST_POST DESC

there is no need to select all those values in the second query
Go to Top of Page

Podge
Support Moderator

Ireland
3776 Posts
Posted - 08 October 2009 :  12:57:20  Show Profile  Send Podge an ICQ Message  Send Podge a Yahoo! Message
I've no way to test it until tomorrow but I think it has potential.
Podge.

The Hunger Site - Click to donate free food | My Blog | Snitz 3.4.05 AutoInstall (Beta!)

My Mods: CAPTCHA Mod | GateKeeper Mod
Tutorial: Enable subscriptions on your board

Warning: The post above or below may contain nuts.
Go to Top of Page

HuwR
Forum Admin

United Kingdom
20600 Posts
Posted - 08 October 2009 :  13:03:11  Show Profile  Visit HuwR's Homepage
seemed to work ok with the quick test I ran, but don't have any excessively large forums to test it on

actually it allways fetches the same 15 records


Go to Top of Page

Podge
Support Moderator

Ireland
3776 Posts
Posted - 08 October 2009 :  18:16:26  Show Profile  Send Podge an ICQ Message  Send Podge a Yahoo! Message
My version doesn't. You're changing the offset correct ?
Podge.

The Hunger Site - Click to donate free food | My Blog | Snitz 3.4.05 AutoInstall (Beta!)

My Mods: CAPTCHA Mod | GateKeeper Mod
Tutorial: Enable subscriptions on your board

Warning: The post above or below may contain nuts.
Go to Top of Page

HuwR
Forum Admin

United Kingdom
20600 Posts
Posted - 09 October 2009 :  03:10:28  Show Profile  Visit HuwR's Homepage
yes, I was changing the offset, but in sql enterprise manager it returns the same 15 records every time, and looking at the logic it should never do anything other than that, normally to do what you are attempting one query is ordered ascending and the other descending, yours both order in the same direction so logically should not work.

the method your are trying to employ normally goes like so.

select top 10 * from table1 where id in (select top 20 id from table1 order by X desc) order by X asc
Go to Top of Page

Podge
Support Moderator

Ireland
3776 Posts
Posted - 09 October 2009 :  05:30:26  Show Profile  Send Podge an ICQ Message  Send Podge a Yahoo! Message
hmmmm..maybe I copied the wrong one.
Podge.

The Hunger Site - Click to donate free food | My Blog | Snitz 3.4.05 AutoInstall (Beta!)

My Mods: CAPTCHA Mod | GateKeeper Mod
Tutorial: Enable subscriptions on your board

Warning: The post above or below may contain nuts.
Go to Top of Page

Podge
Support Moderator

Ireland
3776 Posts
Posted - 09 October 2009 :  07:21:41  Show Profile  Send Podge an ICQ Message  Send Podge a Yahoo! Message
Don't think I did. If you're not using forum_id=2 make sure that you change both instances in the query to whatever forum_id you are using. I tried it on another forum and it works for me.

offset = page-1 * pagesize

If you want page 9 with a pagezise of 10 then offset = 80
Podge.

The Hunger Site - Click to donate free food | My Blog | Snitz 3.4.05 AutoInstall (Beta!)

My Mods: CAPTCHA Mod | GateKeeper Mod
Tutorial: Enable subscriptions on your board

Warning: The post above or below may contain nuts.
Edited by - Podge on 09 October 2009 07:31:46
Go to Top of Page

HuwR
Forum Admin

United Kingdom
20600 Posts
Posted - 09 October 2009 :  09:01:39  Show Profile  Visit HuwR's Homepage
I'm sure I did that, but I will check , maybe I didn't
Go to Top of Page

Podge
Support Moderator

Ireland
3776 Posts
Posted - 09 October 2009 :  09:41:50  Show Profile  Send Podge an ICQ Message  Send Podge a Yahoo! Message
You're right about the sorting though. I reckon it could be a problem. I haven't even looked at how the sql could be formed on forum.asp yet. At least its broken up up into about 4 sql strings.
Podge.

The Hunger Site - Click to donate free food | My Blog | Snitz 3.4.05 AutoInstall (Beta!)

My Mods: CAPTCHA Mod | GateKeeper Mod
Tutorial: Enable subscriptions on your board

Warning: The post above or below may contain nuts.
Go to Top of Page

HuwR
Forum Admin

United Kingdom
20600 Posts
Posted - 09 October 2009 :  09:49:52  Show Profile  Visit HuwR's Homepage
that was why I suggested a stored proc, the query required is much simpler
Go to Top of Page
Page: of 3 Previous Topic Topic Next Topic  
Previous Page | Next Page
 New Topic  Topic Locked
 Printer Friendly
Jump To:
Snitz Forums 2000 © 2000-2021 Snitz™ Communications Go To Top Of Page
This page was generated in 0.5 seconds. Powered By: Snitz Forums 2000 Version 3.4.07