| Author |
 Topic  |
|
wii
Free ASP Hosts Moderator
Denmark
2632 Posts |
 Posted - 05 October 2005 : 16:57:35
|
Hmmm, this is what I got:
Server: Msg 15009, Level 16, State 1, Procedure sp_helptext, Line 52
The object 'sp_cursoropen' does not exist in database 'snitzdb'.
quote:
Originally posted by pdrg
nah, no need - I suspect sp_cursoropen is to blame - cursors are the scourge of databasing, and only used for procedural programming against a dataset - it would also explain why it slows down almost linearly with the #recs returned.
If you have query analyser handy, could you enter
sp_helptext 'sp_cursoropen' (and run the query)
We'll see what's in the sproc
The deep root of the problem is that Access (which I believe Snitz was originally written for) does not support stored procedures or proper client-server architecture. When somebody made the adaptation to SQL Server, it still had to work with the Snitz codebase, so had to keep a very similar architecture (and use cursors, etc) - if designed without cursors (to be set-wise, in fact), boy you'd see SQL Server fly compared with Access :)
Let me know what the above test turns up, I'd be interested to see if there are any tunings/easy wins we can put in (without having to rearchitect!)
|
 |
|
|
pdrg
Support Moderator
United Kingdom
2897 Posts |
Posted - 06 October 2005 : 05:18:17
|
quote:
Originally posted by wii
declare @P1 int
set @P1=180150000
declare @P2 int
set @P2=8
declare @P3 int
set @P3=1
declare @P4 int
set @P4=15262
exec sp_cursoropen @P1 output, N'SELECT M.M_NAME, M.M_RECEIVE_EMAIL, M.M_PMRECEIVE, M.M_AIM, M.M_ICQ, M.M_MSN, M.M_YAHOO, M.M_TITLE, M.MEMBER_ID, M.M_HOMEPAGE, M.M_LEVEL, M.M_POSTS, M.M_COUNTRY, M.M_DATE, R.REPLY_ID, R.FORUM_ID, R.R_AUTHOR, R.TOPIC_ID, R.R_MESSAGE, R.R_LAST_EDIT, R.R_LAST_EDITBY, R.R_SIG, R.R_STATUS, R.R_DATE, R.R_MSGICON, M.M_SIG, M.M_Avatar, M.M_AVATAR_WIDTH, M.M_AVATAR_HEIGHT FROM FORUM_MEMBERS M, FORUM_REPLY R WHERE M.MEMBER_ID = R.R_AUTHOR AND R.TOPIC_ID = 1675 AND (R.R_STATUS < 3 OR R.R_AUTHOR = 153) ORDER BY R.R_DATE ASC', @P2 output, @P3 output, @P4 output
select @P1, @P2, @P3, @P4
Ummmm - so sp_cursoropen doesn't exist, yet this seems to be using it...blimey! What you have is a right old mess in that case :(
I think, in that case, sp_cursoropen is an internal sproc in Master db - blimey, it's not supported for user use.
Are we sure it is the bit causing the problem, it could be a red herring...?
>>Thinking cap back on<< |
Edited by - pdrg on 06 October 2005 05:21:21 |
|
|
|
pdrg
Support Moderator
United Kingdom
2897 Posts |
Posted - 06 October 2005 : 05:25:09
|
How about starting a nice clean fresh Snitz install on the same db server (different db, though) (without mods, etc) so we can investigate just using that?
I think the issue might be clouded at the moment, so let's attack it one step at a time.
If a clean install still causes these problems, detach the database and send me a copy of thye .mdf file, I'll try a local restore and look deeper into tuning for you |
|
|
|
wii
Free ASP Hosts Moderator
Denmark
2632 Posts |
Posted - 06 October 2005 : 06:06:59
|
exec sp_cursoropen
Hmmm, could that be for one of the MODS ? In any case, it doesn´t explain why this behaviour is still there with a clean snitz code, of course the DB is the same, so!?
I could install a fresh snitz forum running on SQL, but how do we check the bad load time on large topics without any posts in it?
pdrg,
ruirib is on it already - he has the DB, so he will try to re-generate the problem, and maybe find a solution to this.
I wonder why no one has ever mentioned this? Is that because I´m the only one having several very large topics (over 500 pages) ? |
|
|
|
pdrg
Support Moderator
United Kingdom
2897 Posts |
Posted - 06 October 2005 : 07:11:59
|
sp_cursoropen looks like a system sproc for SQL server, and as such is not typically supposed to be used directly (it is not supported and liable to undocumented changes), however we don't know the context of where it is being called from the code pasted n by your host - maybe it was from a profiler trace ro something. Anyway, the calling context would be all-important here in giving us a clue, maybe the host could let us know where that code was called from?
the fresh install I mentioned would be to install a fresh/clean snitz on SQL but then import all the non-mod stuff, so it'd be a clone of your forum without all the frills - that way we could slowly chip away at the problem.
re the db copy - if Rui has it already, I'm certain he'll do a great job looking into it, he's a good lad  . I don't know why this is taking so long to execute though, be interesting to see what he discovers.
Anyway if I can help, here I am :-D |
|
|
|
wii
Free ASP Hosts Moderator
Denmark
2632 Posts |
Posted - 06 October 2005 : 07:16:57
|
I will ask my host if they can setup another DB on the SQl server, so I can do a clean install.
We´ll figure this out eventually. |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 06 October 2005 : 07:38:33
|
I'm expecting to be able to do it today. Preparing the DB will take a bit of time, cause you do have a lot of mods  . Then I'll just import info from the Access DB. Executing the Profiler will be the next task and hopefully will be able to nail it down.
I enjoy doing this stuff. It's always fun to tackle these situations. I'd say topic.asp was much improved in terms of database access in 3.4.x, so I'm thinking the problem may lie with indexing, but don't want to antecipate anything before the actual data shows us what is happening.
wii, I wouldn't think, at a first glance, that this is related to mods. Can't rule it out, but... |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
wii
Free ASP Hosts Moderator
Denmark
2632 Posts |
Posted - 06 October 2005 : 07:56:24
|
Well, actually I hope it "just" related to MODs, because if not it´s related to the base code, and that would be an entirely more serious problem. Keep me updated.
Thanks |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 06 October 2005 : 20:50:50
|
I followed the standard procedure to recreate wii's SQL Server database, from his Access database. He had a lot of mods, so after creating a standard install, added all the mod's dbs changes and proceeded with the data import using DTS.
Started a profiler trace and visited the topics page several times, visiting some of his longest topic pages (in my own computer, even with the longest pages, page generation time was never higher than 2 secs, and usually were below that. I run a 3.2 GHz P4 with 2 GB RAM).
Stopped the profiler and looked at the trace data. Confirming my own predictions, DB access from topic.asp improved a lot from Snitz 3.x. Duration of queries in topic.asp was quite low, with the exception of exec sp_cursoropen ops, which is a server side cursor created by ADO. Duration for most queries is below 20, while the cursor creation operations is between 500 and 600. I looked at the Snitz code and the server side cursor in use is almost always a forward only read only cursor, the fastest of them all. In the case of opening a topic in a given page, however, of the cursors used, the one that takes most of the time is a static cursor, used to retrieve the replies info. This one needs to be static, so that we can navigate to the desired page using ADO.
I tried several options and the best that worked for me, after creating two indexes that I will describe below, was to make the cursor a client side one, instead of the (default server side one). This took about .5 secs less than using a server side cursor.
Index Tuning wizard suggested two clustered indexes, one for the replies table and the other for the topics table:
CREATE CLUSTERED INDEX [FORUM_REPLY1] ON [dbo].[FORUM_REPLY] ([TOPIC_ID] ASC, [R_AUTHOR] ASC )
CREATE CLUSTERED INDEX [FORUM_TOPICS2] ON [dbo].[FORUM_TOPICS] ([T_LAST_POST] ASC )
These took about .1 secs off the time required to navigate to a given page in a long topic.
I did a final test, using the MySQL page navigation strategy, instead of using ADO to navigate to the desired page. This resulted in the fastest times of all, shaving off another .1 secs from the best previous times. This still required about 1.2-1.3 secs (page generation time) to navigate to a given page in a 1000+ page topic.
wii, it is my conviction that your server is the main obstacle to a better performance. Applying some of the strategies I used here you may diminish some time, but you likely you won't be able to diminish that much on that server...
I also confirm that Access handles itself better than SQL Server. Times using Access are usually below .7 secs (about .5-.6 secs better than SQL server). Not bad for a desktop DB, is it?
I have to try this on SQL Server 2005. I'm curious about the way it will perform on this... |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
wii
Free ASP Hosts Moderator
Denmark
2632 Posts |
Posted - 07 October 2005 : 02:56:33
|
Thanks a lot for your work.
I did contact my host, and they say there´s nothing wrong with the server, has 2GB RAM, and resource use is very low on the server.
I find it interesting that Access performs better at long topics than SQL, doesn´t that mean that the code is optimized for Access and not SQL? |
|
|
|
pdrg
Support Moderator
United Kingdom
2897 Posts |
Posted - 07 October 2005 : 03:51:35
|
quote:
doesn´t that mean that the code is optimized for Access and not SQL?
Certainly means that these particular procs are. In fact, the whole of Snitz was designed around Access (I think!), then SQL migrations came later, so the code doesn't take advantage of all the powerful features of SQL Server (stored procedures, for instance)
Rui, your results are really interesting - so it's the paging that is causing wii's problems? That's totally an architectural issue - we could possibly create some exception code to handle the paging from the db side, but it would leave a very non-standard Snitz install and break wii's upgrade path (although it's arguable that the mods do this anyway)
this has been a fascinating thread :) |
|
|
|
wii
Free ASP Hosts Moderator
Denmark
2632 Posts |
Posted - 07 October 2005 : 04:14:45
|
The big question is, what can I (we) do about this in my particular forum ?
(I changed the title, to match what we have talked about) |
Edited by - wii on 07 October 2005 04:16:12 |
|
|
|
pdrg
Support Moderator
United Kingdom
2897 Posts |
Posted - 07 October 2005 : 05:01:38
|
| would archiving help at all? Means you'd have a much smaller cursor to traverse... |
|
|
|
wii
Free ASP Hosts Moderator
Denmark
2632 Posts |
Posted - 07 October 2005 : 05:03:55
|
| I will test that. |
|
|
|
HuwR
Forum Admin
United Kingdom
20595 Posts |
Posted - 07 October 2005 : 05:26:25
|
quote:
so the code doesn't take advantage of all the powerful features of SQL Server (stored procedures, for instance)
The advantages of a stored procedure being what exactly ? NONE, it is a total misnomer that stored procedures are better or faster than a standard queery |
|
|
|
Topic |
|
|
|
Topic Locked
