| Author |
 Topic  |
|
wii
Free ASP Hosts Moderator
Denmark
2632 Posts |
 Posted - 07 October 2005 : 05:30:20
|
| HuwR, do you know of any performance problems using SQL with very large topics ? Is it really my host, or is the code? |
 |
|
|
HuwR
Forum Admin
United Kingdom
20595 Posts |
Posted - 07 October 2005 : 06:36:28
|
quote:
Originally posted by wii
HuwR, do you know of any performance problems using SQL with very large topics ? Is it really my host, or is the code?
I have never seen any very large topics, and certainly wouldn't encourage them here, I would not normally expect to see a topic longer than 20 pages before locking it, but that opinion was gained in the old days of poor server performance. So, no, it is not something that has really been tested, but my assumption would also point to the cursored operation. The best bet here would be to rewrite the snitzcode so as to eliminate the cursor, however being asp that may not actually offer any improvement as it would depend on many external factors too.
The fastest solution may be to create a temp table to pull all related replies in to, and then use the temp table in the main queries rather than the reply table. |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 07 October 2005 : 06:48:49
|
quote:
Originally posted by wii
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 didn't say something was wrong, but it is performing worse than my personal PC, that's for sure  .
quote:
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?
What it may mean is that for the current code, Access does perform better than SQL Server. When I used a client side cursor, it still took about 1 sec for SQL to retrieve all the replies, which is more than Access takes to process all the pages queries. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
HuwR
Forum Admin
United Kingdom
20595 Posts |
Posted - 07 October 2005 : 06:51:30
|
One comment, just because a server has 2Gb of ram doesn't mean it will be a good SQL server  SQL performance depends on drive hardware as well as ram.
This SQL server is a Dual Hyperthreaded 3GHz P4 machine with 4Gb of dual channel memory (SQL uses between 1.5 and 2Gb) . The windows system drive is an 80Gb IDE HD, the sql log files sit on a mirrored 80Gb SATA drive, the SQL data files sit on a 200Gb striped SATA array and the tempdb sits on its own 80Gb IDE drive.
If you want to stick a test db on it drop me an email. |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 07 October 2005 : 06:54:18
|
quote:
Originally posted by pdrg
Rui, your results are really interesting - so it's the paging that is causing wii's problems? (...)
SQL Server performed worse than Access in all strategies I tested: paging using ADO, with a server side or client side cursor, and paging without ADO, "ŕ la MySQL".
Paging certainly plays a role in the current performance. As I wrote, all DB ops took less than 20 milliseconds, except cursor creation (used for paging) which took between 500 and 600 milliseconds.
quote:
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)
I tried different approaches and they did not contribute to improve SQL Server performance when compared to Access. Even different paging approaches
quote:
this has been a fascinating thread :)
Indeed . |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
wii
Free ASP Hosts Moderator
Denmark
2632 Posts |
Posted - 07 October 2005 : 06:56:12
|
I´m aware that locking the topics, and creating new ones is a solution, the reason I didn´t is that all went fine using Access, there were no major differences in generating pages for 1 or 1000, in SQl there is a difference, more pages, longer load times, 1 page loads in 0.6 seconds (average), while 1000 pages topics load in 4 to 5 seconds.
You may say, why didn´t you just stay with Access ? Answer: because I´ve always thought that SQL would perform better in every aspect ! And actually, it does, just not long topics. |
|
|
|
wii
Free ASP Hosts Moderator
Denmark
2632 Posts |
Posted - 07 October 2005 : 06:58:39
|
| HuwR, I would love to try exactly the same files and DB on your server. Can you mail me the details, so I can upload the files? |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 07 October 2005 : 07:01:13
|
quote:
Originally posted by wii
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)
Good question. Archiving won't help regarding forum performance when retrieving pages from those topics.
One could try stored procs (Huw, I do think it would be possible to improve performance somewhat, since the queries would already be pre-compiled and "ready" for execution. How much I don't know). Other than this I don't think there is much that can be done ;).
Of course, I completely agree with Huw's remark on the server hardware architecture issue. The amount of RAM, but mainly the use of different disks for each tempdb, DB file and log file does certainly make a difference in performance.
I have a single 250 GB Seagate SATA 7200 rpm drive. My Windows paging file is kept in another drive, a 160 GB Samsung SATA drive. All SQL Server stuff is kept in my 250 GB drive. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
HuwR
Forum Admin
United Kingdom
20595 Posts |
Posted - 07 October 2005 : 07:07:05
|
quote:
One could try stored procs (Huw, I do think it would be possible to improve performance somewhat, since the queries would already be pre-compiled and "ready" for execution. How much I don't know). Other than this I don't think there is much that can be done ;).
Won't make a difference, that is the misnomer, there is NO precompilation of stored procs in MSSQL, it caches execution plans for each query, also ad-hoc queries. Even better, it will parameterise queries which don't even have parameters to keep the execution plan in the cache
from the SQL manual itself
"SQL Server 2000 and SQL Server version 7.0 incorporate a number of changes to statement processing that extend many of the performance benefits of stored procedures to all SQL statements. SQL Server 2000 and SQL Server 7.0 do not save a partially compiled plan for stored procedures when they are created. A stored procedure is compiled at execution time, like any other Transact-SQL statement. SQL Server 2000 and SQL Server 7.0 retain execution plans for all SQL statements in the procedure cache, not just stored procedure execution plans." |
Edited by - HuwR on 07 October 2005 07:08:12 |
|
|
|
pdrg
Support Moderator
United Kingdom
2897 Posts |
Posted - 07 October 2005 : 07:22:02
|
Very clever...ahh well it used to be the case. You live and learn.
Makes it all the more surprising that it means wii's forum is so much slower with SQL Server though imho |
|
|
|
wii
Free ASP Hosts Moderator
Denmark
2632 Posts |
Posted - 07 October 2005 : 07:29:16
|
So, being a non-programmer, does that mean that there´s nothing I can do about this?
(other than locking long topics, of course). |
|
|
|
HuwR
Forum Admin
United Kingdom
20595 Posts |
Posted - 07 October 2005 : 09:07:50
|
quote:
Originally posted by wii
So, being a non-programmer, does that mean that there´s nothing I can do about this?
(other than locking long topics, of course).
No, there are several things that could be done to maybe improve things, but you do need to completely rule out the server before you go re-writing the code, since it could still be a server issue rather than the actual queries themselves.
How big is your current db ? is there any chance I could take a copy and take a look on my server |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 07 October 2005 : 09:22:03
|
quote:
Originally posted by HuwR
[br Won't make a difference, that is the misnomer, there is NO precompilation of stored procs in MSSQL, it caches execution plans for each query, also ad-hoc queries. Even better, it will parameterise queries which don't even have parameters to keep the execution plan in the cache
from the SQL manual itself
"SQL Server 2000 and SQL Server version 7.0 incorporate a number of changes to statement processing that extend many of the performance benefits of stored procedures to all SQL statements. SQL Server 2000 and SQL Server 7.0 do not save a partially compiled plan for stored procedures when they are created. A stored procedure is compiled at execution time, like any other Transact-SQL statement. SQL Server 2000 and SQL Server 7.0 retain execution plans for all SQL statements in the procedure cache, not just stored procedure execution plans."
I must admit I thought otherwise, and some anecdotal evidence in some previous projects contributed to that. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 07 October 2005 : 09:25:08
|
quote:
Originally posted by HuwR
quote:
Originally posted by wii
So, being a non-programmer, does that mean that there´s nothing I can do about this?
(other than locking long topics, of course).
No, there are several things that could be done to maybe improve things, but you do need to completely rule out the server before you go re-writing the code, since it could still be a server issue rather than the actual queries themselves.
Frankly, I think it may be hard to achieve that. Just the query that retrieves the replies, with all the indexes recommended by the tunning wizard took more than a sec (no cursors in use).
quote:
How big is your current db ? is there any chance I could take a copy and take a look on my server
In my system the database is about 110 MB. Probably the real one is a bit bigger. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
wii
Free ASP Hosts Moderator
Denmark
2632 Posts |
Posted - 07 October 2005 : 09:27:48
|
110 MB ? The Access DB was about 40 MB, ruirib, could you mail the DB to HuwR ? Please delete the databases after testing.
Thanks |
|
|
|
Topic |
|
Topic Locked
