| Author |
 Topic  |
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
 Posted - 22 April 2002 : 23:11:51
|
Yes, it probably should. Here, I've changed it:
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 AND R_DATE < 20020219235900)) 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 AND R_DATE < 20020219235900
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 < 20020219235900
ORDER BY R_DATE DESC;
I also found this one is more correct (small bug on the previous one, the condition in the subquery is needed to get the correct results...
Let me know about the results for this one...
Btw, nice work on the testing. Always good to know real execution data. One thing I'd like to know, though - how many posts are really selected (before selecting the last 10, I mean...)
-------------------------------------------------
Installation Guide | Do's and Dont's | MODs |
 |
|
|
Aaron S.
Average Member
USA
985 Posts |
Posted - 22 April 2002 : 23:20:42
|
It is returning 1160 records before the 10 are pulled out.
The new query you posted doesn't seem to work.
In ASP, it is returning the wrong data, and in Access it doesn't show any data.
--Aaron
MySubs Email MOD
MOD to Smile Mgr
HotKeys MOD |
|
|
|
Aaron S.
Average Member
USA
985 Posts |
Posted - 22 April 2002 : 23:23:08
|
Ok... I got it to work.
In the query you posted it had "<" before the date, and I have been using ">".
The new query seems to load between .14 - .17.
BTW: I have been testing this locally using Access, when I uploaded the pages to my actual site (which uses SQL Server), I get this error:
Microsoft OLE DB Provider for SQL Server error '80040e14'
The identifier that starts with 'SELECT DISTINCT FORUM_TOPICS.TOPIC_ID,FORUM_TOPICS.T_SUBJECT,FORUM_TOPICS.T_STATUS,FORUM_TOPICS.T_LAST_POST,FORUM_TOPICS.T_REPLI'
is too long. Maximum length is 128.
--Aaron
MySubs Email MOD
MOD to Smile Mgr
HotKeys MOD
Edited by - aaron s. on 22 April 2002 23:24:26
Edited by - aaron s. on 22 April 2002 23:30:37 |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 22 April 2002 : 23:46:09
|
Well, this one seems to be slower...
And I don't know what to do with that size limit. Looks like an ADO limitation. Too bad.
It also looks like your mixed SQL / Vb solution is the way to go...
-------------------------------------------------
Installation Guide | Do's and Dont's | MODs |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 23 April 2002 : 18:04:50
|
Aaron,
I forget to wrote yesterday that there is another alternative: transform the SQL instruction into a stored procedure for SQL Server or a query for Access. That will have the aditional benefit of producing even faster results, as the SQL will already have been compiled and execution will be faster than the submission of a normal SQL command.
It will, however, have the obvious disadvantage of difficulting its use in a mod, but that can be overcome for the more experienced users.
-------------------------------------------------
Installation Guide | Do's and Dont's | MODs |
|
|
|
Aaron S.
Average Member
USA
985 Posts |
Posted - 23 April 2002 : 18:27:41
|
Since I just starting using SQL Server, I am not that familar with stored procedures.
BUT, it is definitely something that I would like to know more about, since I know it can dramatically improve performance.
This MOD seems like a good place for me to start.
--Aaron
MySubs Email MOD
MOD to Smile Mgr
HotKeys MOD |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 23 April 2002 : 18:46:00
|
It's really pretty simple to create and execute one. You can create it using Enterprise Manager or Query Analizer.
Let's just say you use EM:
1. Open your DB in EM expand it and right click Stored Procedures.
2. Choose New Stored Procedure
3. Paste the code :
CREATE PROCEDURE pPost
@startDate nvarchar(50),
@mID int
AS
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=@mId AND R_DATE IN (SELECT R_DATE FROM FORUM_REPLY WHERE R_AUTHOR=@mId AND R_DATE > @startDate)) 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=@mId AND R_DATE > @startDate
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=@mId AND FORUM_REPLY.TOPIC_ID Is Null ) OR (FORUM_TOPICS.T_AUTHOR=@mId AND FORUM_REPLY.TOPIC_ID Not In (SELECT TOPIC_ID FROM FORUM_REPLY WHERE R_AUTHOR=5)) )
AS MY_VIEW
WHERE R_DATE > @startDate
ORDER BY R_DATE DESC;
GO
This creates a stored procedure named pPost, with two parameters: startDate and mId, to allow parameterization of the StartDate for the Posts and of the member Id. Following the keyword As you have the SQL statement, and it finishes with GO.
Pressing OK, will store the stored procedure in the Db.
You can then start Query Analizer, connect to server, select your DB and type:
exec pPost 20010101000000, 5 and you'll get the results you need...
This can also be called from an ASP script, but I guess you know that...
-------------------------------------------------
Installation Guide | Do's and Dont's | MODs |
|
|
|
Aaron S.
Average Member
USA
985 Posts |
Posted - 23 April 2002 : 18:59:59
|
Thanks, I'll try that.
I also am going to convert some of the default.asp stmts to stored procedures, as I have 54 categories and over 400 forums... so I need all the performance gains I can get.
--Aaron
MySubs Email MOD
MOD to Smile Mgr
HotKeys MOD |
|
|
|
Topic |
|
Topic Locked
