| Author |
 Topic  |
|
wii
Free ASP Hosts Moderator
Denmark
2632 Posts |
 Posted - 02 October 2005 : 14:34:06
|
| Run the Profiler ? I have never heard about this before, what and where is it? |
 |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
wii
Free ASP Hosts Moderator
Denmark
2632 Posts |
Posted - 03 October 2005 : 01:58:12
|
| Oh, you are talking about the Enterprise Manager !? Ok, Iīll take a look at the Profiler. |
|
|
|
wii
Free ASP Hosts Moderator
Denmark
2632 Posts |
Posted - 03 October 2005 : 06:07:03
|
| Is it the index tuning wizard ? Seems like I canīt access that, only my host can. |
|
|
|
pdrg
Support Moderator
United Kingdom
2897 Posts |
Posted - 03 October 2005 : 06:10:49
|
Try using Query Analysewr (isql.exe) and looking at the estimated execution plan - if you see 'table scan' coming up, you can almost certainly get a boost from indexing - let me know what it says :)
hth |
|
|
|
wii
Free ASP Hosts Moderator
Denmark
2632 Posts |
Posted - 03 October 2005 : 06:14:33
|
| Hmm, the Query Analyzer is empty - where do I look? |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
wii
Free ASP Hosts Moderator
Denmark
2632 Posts |
Posted - 03 October 2005 : 15:53:30
|
But I need a query to execute before I see anything, right?
God, Iīm really lost in this Enterprise Manager. |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 03 October 2005 : 16:15:46
|
| Yeah, that's why the profiler could be helpful, no query needed. You can try to output the SQL for query that retrieves the replies, in topic.asp, paste it into query analizer and then check it for improvements, as pdrg suggested. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
wii
Free ASP Hosts Moderator
Denmark
2632 Posts |
Posted - 03 October 2005 : 16:25:15
|
| I asked my host to look at this topic, weīll see what happens. |
|
|
|
pdrg
Support Moderator
United Kingdom
2897 Posts |
Posted - 04 October 2005 : 04:47:27
|
wii - if you've got 'Query Analyser' (which you do have!) then just pick out the SQL related to that particular table and paste it in query analyser - then you do NOT have to execute it, just 'show estimated execution plan' - it asks the dbengine how it would attempt to run that SQL and brings back a pretty picture of the execution plan. Once you've got the pretty picture (with lots of arrows on it) we can look at that to see if you need extra indexing, all without running the profiler :)
look in 'Books Online' (sqlbol.chm) for 'Graphically Displaying the Execution Plan Using SQL Query Analyzer' - we can then help you look at the elements to see what needs tweaking :)
hth |
|
|
|
wii
Free ASP Hosts Moderator
Denmark
2632 Posts |
Posted - 04 October 2005 : 06:25:53
|
Ok, I got an answer from my host - which I will try to translate here:
-------------
I have tried to look into the calls that takes the longest for the SQL server to perform, it seems like itīs a script called stored procedures.
I have attached one of these calls:
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
When I try to run the call, it takes about 3 seconds in the Query Analyzer, if I try to update the site, it takes about 3-5 seconds.
By pressing CTRL+K in the Query Analyser I could retrieve an Execution Plan (which I also attached:)
I have tried to create 2 indexes for the table FORUM_REPLY by rightclicking the table and choose Design Table, then rightclick and choose Indexes/Keys.
The 2 indexed I created are called IX_FORUM_REPLY_test og IX_FORUM_REPLY_test2
Unfortunately, this didnīt help much on the time, so I tried to create a relation between FORUM_MEMBERS and FORUM_REPLY, this can be seen under the folder Relationships
This relation is called FK_FORUM_REPLY_FORUM_MEMBERS.
I couldnīt get any significant speed improvement on the forum, but I hope to give you a little more to work with. |
Edited by - wii on 04 October 2005 06:28:35 |
|
|
|
pdrg
Support Moderator
United Kingdom
2897 Posts |
Posted - 04 October 2005 : 07:20:31
|
Hmmm adding an index would speed up the select but slow down the insert, typically, but as it's making no difference I'm not sure where to turn. You're trying all the obvious places - what fields did you build the indexes on?
The table scans may not be making that big a difference if they're clustered on ID, I suppose, so that may be a red herring - maybe the overall server load is high.
I'm not familiar with sp_cursoropen, but cursors are notoriously clumsy and clunky. Maybe someone more familar with the Snitz sprocs can comment further...I'm in meetings for the rest of the day now, I'll look back in tomorrow and see if I can think of anything in the meantime! |
|
|
|
wii
Free ASP Hosts Moderator
Denmark
2632 Posts |
Posted - 04 October 2005 : 07:27:31
|
The above is from my host, as I have no idea what to do.
I donīt think the server load is high, it just got MSSQL installed locally on my request, so I maybe the only one using it currently, I can ask my host about that, but as everything else on the forum is really fast, I tend to think itīs something in the Snitz code.
Does anyone else here have very long topics (over 300-400 pages) running on MSSQL, so I can compare speeds? |
|
|
|
wii
Free ASP Hosts Moderator
Denmark
2632 Posts |
|
|
Topic |
|
Topic Locked
