| Author |
 Topic  |
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
 Posted - 21 April 2002 : 19:56:57
|
Just select a specific topic, not started by this user, but one the user replied, although not the last reply posted (someone else posted the last reply). Then you'll see that the date returned for the topic is the date for the last post, but not for the last post for the user you are searching for.
Obviously that can affect the topics order.
-------------------------------------------------
Installation Guide | Do's and Dont's | MODs
Edited by - ruirib on 21 April 2002 19:57:50 |
 |
|
|
Aaron S.
Average Member
USA
985 Posts |
Posted - 21 April 2002 : 20:07:12
|
I don't use the LAST_POST field. I use R_DATE (which is the UNION of R_DATE and T_DATE).
In my tests, R_DATE seems to stay consistent and correct.
I just:
1. Created a topic with ANOTHER user
2. Replied with the GIVEN user
3. Replied again with ANOTHER user
The R_DATE stayed the same for the GIVEN user and hence, sorts correctly.
Since this is such a brain numbing QA assignment... I may not be reading the data right, but it looks correct (and matching the previous 2 ways I had done it before).
--Aaron
MySubs Email MOD
MOD to Smile Mgr
HotKeys MOD |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 21 April 2002 : 20:33:23
|
Well, I know it's not working, because I have several posts where the date is wrong.
I'll try to figure a way to correct it, although I'm running out of ideas... Maybe tomorrow. I've already spent a few hours around this today. Better let it cool down a bit...
-------------------------------------------------
Installation Guide | Do's and Dont's | MODs |
|
|
|
Aaron S.
Average Member
USA
985 Posts |
Posted - 21 April 2002 : 20:44:13
|
I'm tickering with it also...
BTW: Here are some results for the first two ways I built the page.
Goal: To show the first 10 MOST recent posts by a given user (NOT first 10 MOST recent topics that a given user posted to at some point in time).
Test: See how long it takes the page to generate given that 1,000+ records are returned by the query.
METHOD ONE: BUBBLE SORT
It took the page between 5.0 - 5.2 seconds to load (!).
METHOD TWO: UNION with Pipe variable to check for duplicate topics
It took the page between .17 - .19 seconds to load (it is loading 25+ times faster than the first method).
METHOD THREE: ONE GIANT SQL STMT
Undetermined (maybe tomorrow)
The second method returns a large data set, but because of getrows, it doesn't really impact performace. In fact, I think it loads pretty darn fast.
--Aaron
MySubs Email MOD
MOD to Smile Mgr
HotKeys MOD
Edited by - aaron s. on 21 April 2002 20:45:41 |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 21 April 2002 : 20:47:16
|
Well I've figured a way to do it. Gonna test it for speed, because I'm not liking it, but I'm testing it in Access linking to SQL Server through ODBC. Gonna test it in SQL Server directly.
Get back to you in a while...
-------------------------------------------------
Installation Guide | Do's and Dont's | MODs |
|
|
|
Aaron S.
Average Member
USA
985 Posts |
|
|
HuwR
Forum Admin
United Kingdom
20584 Posts |
Posted - 21 April 2002 : 21:14:50
|
Just so that you are aware, these queeries will not work in MySQL
|
|
|
|
Aaron S.
Average Member
USA
985 Posts |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 21 April 2002 : 21:53:04
|
Ok, Aaron here it is:
SELECT DISTINCT FORUM_TOPICS.TOPIC_ID, FORUM_TOPICS.T_SUBJECT, FORUM_REPLY.R_AUTHOR, (SELECT TOP 1 R_DATE FROM FORUM_REPLY WHERE (FORUM_REPLY.TOPIC_ID = FORUM_TOPICS.TOPIC_ID AND FORUM_REPLY.R_AUTHOR=5 AND R_DATE IN (SELECT R_DATE FROM FORUM_REPLY WHERE R_AUTHOR=5)) ORDER BY R_DATE DESC) AS R_DATE
FROM FORUM_REPLY AS FORUM_REPLY INNER JOIN FORUM_TOPICS AS FORUM_TOPICS ON FORUM_REPLY.TOPIC_ID = FORUM_TOPICS.TOPIC_ID
WHERE FORUM_REPLY.R_AUTHOR=5
UNION
SELECT DISTINCT FORUM_TOPICS.TOPIC_ID, FORUM_TOPICS.T_SUBJECT, FORUM_TOPICS.T_AUTHOR, FORUM_TOPICS.T_DATE
FROM FORUM_TOPICS AS FORUM_TOPICS LEFT JOIN FORUM_REPLY AS FORUM_REPLY ON FORUM_TOPICS.TOPIC_ID=FORUM_REPLY.TOPIC_ID
WHERE (FORUM_TOPICS.T_AUTHOR=5 AND FORUM_REPLY.TOPIC_ID Is Null) OR (FORUM_TOPICS.T_AUTHOR=5 AND FORUM_REPLY.TOPIC_ID Not In (SELECT TOPIC_ID FROM FORUM_REPLY WHERE R_AUTHOR=5))
ORDER BY R_DATE DESC;
This is guaranteed to work. The previous problem is eliminated. Now I'm not so sure this will be that fast...
I believe the impact of running any type of three strategies will only show with a big number of posts. For a small number of posts any difference shouldn't be very meaningful.
Regarding the difference in ODBC vs. OLEDB I'm not that surprised. One of these days I saw execution time for active.asp come down to 1.6 secs, from 23 secs... I also find it more robust than ODBC. There are many situations where ODBC drivers have a very bad impact on the server.
Another person I helped here had IIS hanging whenever he tried to get to default.asp (was using SQl Server 2000). Guess how it was corrected?... You're right, just changing from ODBC to OLEDB drivers.
-------------------------------------------------
Installation Guide | Do's and Dont's | MODs
Edited by - ruirib on 21 April 2002 22:37:36
Edited by - ruirib on 21 April 2002 22:50:37 |
|
|
|
Aaron S.
Average Member
USA
985 Posts |
Posted - 21 April 2002 : 22:07:38
|
Where does the date compare go (greater than X date)?
Also, how can only the first 10 records be returned (this completes the pop_profile) page.
--Aaron
MySubs Email MOD
MOD to Smile Mgr
HotKeys MOD |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 21 April 2002 : 22:34:53
|
Here is the whole syntax:
SELECT TOP 10 * FROM
(SELECT DISTINCT FORUM_TOPICS.TOPIC_ID, FORUM_TOPICS.T_SUBJECT, FORUM_REPLY.R_AUTHOR, (SELECT TOP 1 R_DATE FROM FORUM_REPLY WHERE (FORUM_REPLY.TOPIC_ID = FORUM_TOPICS.TOPIC_ID AND FORUM_REPLY.R_AUTHOR=5 AND R_DATE IN (SELECT R_DATE FROM FORUM_REPLY WHERE R_AUTHOR=5)) ORDER BY R_DATE DESC) AS R_DATE
FROM FORUM_REPLY AS FORUM_REPLY INNER JOIN FORUM_TOPICS AS FORUM_TOPICS ON FORUM_REPLY.TOPIC_ID = FORUM_TOPICS.TOPIC_ID
WHERE FORUM_REPLY.R_AUTHOR=5
UNION
SELECT DISTINCT FORUM_TOPICS.TOPIC_ID, FORUM_TOPICS.T_SUBJECT, FORUM_TOPICS.T_AUTHOR, FORUM_TOPICS.T_DATE
FROM FORUM_TOPICS AS FORUM_TOPICS LEFT JOIN FORUM_REPLY AS FORUM_REPLY ON FORUM_TOPICS.TOPIC_ID=FORUM_REPLY.TOPIC_ID
WHERE (FORUM_TOPICS.T_AUTHOR=5 AND FORUM_REPLY.TOPIC_ID Is Null ) OR (FORUM_TOPICS.T_AUTHOR=5 AND FORUM_REPLY.TOPIC_ID Not In (SELECT TOPIC_ID FROM FORUM_REPLY WHERE R_AUTHOR=5)) )
AS MY_VIEW
WHERE R_DATE > 20020321010101
ORDER BY R_DATE DESC;
-------------------------------------------------
Installation Guide | Do's and Dont's | MODs
Edited by - ruirib on 21 April 2002 22:56:56 |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 21 April 2002 : 22:55:46
|
Aaron I had mistakenly erased the parts in red. Don't forget to insert them, or dates won't be sorted correctly. Sorry.
-------------------------------------------------
Installation Guide | Do's and Dont's | MODs |
|
|
|
Aaron S.
Average Member
USA
985 Posts |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 21 April 2002 : 23:19:21
|
quote:
This seems to work great! Good job!
Thanks. You helped a lot  , so it's a good job to you too  .
quote:
FYI: The date will need quotes around it.
Funny, I tested it in SQL Server Query Analizer and it forced me to remove the quotes...
One more thing: we possibly could avoid some records being selected if we included the date criteria inside each of the queries, instead of doing it outside, on the union. I don't know if the optimizer can handle that OK. I'll try it tomorrow to see how it goes, now I gotta go to bed.
-------------------------------------------------
Installation Guide | Do's and Dont's | MODs |
|
|
|
Aaron S.
Average Member
USA
985 Posts |
Posted - 22 April 2002 : 22:41:30
|
Ok... from my tests I get the following:
All returning 1000+ posts.
Method 1: Bubble Sort
- horrible, 5 sec.
Method 2: Basic UNION with Pipe variable to sort out dup topics and to get the first 10 records
- between .10 and .13 seconds
Method 3: Giant UNION Stmt with top 10 returned from SQL
- between .13 and .15 seconds
So it looks like returning the entire record set into a getrows array and then pulling the top 10 is slightly faster.
Maybe adding some optimization to the giant stmt to pull out the out-of-range dates sooner will make a difference.
--Aaron
MySubs Email MOD
MOD to Smile Mgr
HotKeys MOD |
|
|
|
Topic |
|
Topic Locked
