| Author |
 Topic  |
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
 Posted - 02 August 2004 : 04:10:53
|
quote:
Originally posted by Nathan
I actually think if fulltext indexing were to be used MS SQL and MySQL seaching would be much quicker than Access.
I should keep my mouth shut though because I don't know that snitz doesn't use fulltext indexing, I'm only assuming.
It doesn't. Full text indexing can't be used just outta the box, since it involves SQL Server configuration and, as such, it could not be available at all instalations, what would have the effect of making search unavailable for anyone who used servers without fulltext support (at least that's what I think I read somewhere around here). |
Snitz 3.4 Readme | Like the support? Support Snitz too |
 |
|
|
pdrg
Support Moderator
United Kingdom
2897 Posts |
Posted - 02 August 2004 : 06:08:50
|
I think the question will boil down slightly more - if a database was optimised for Access, it will run best in Access unless there are sooo many users (up to 255, technically) it grinds to a halt. If the db was optimised for SQL Server, boy will it run better for SQL Server (sprocs, stored execution plans, optimiser hints, client-server, etc).
Horses for courses... |
|
|
|
-gary
Development Team Member
406 Posts |
Posted - 10 August 2004 : 13:38:16
|
My site didn't see a huge performance problem with Access until past 100,000 posts. Switched to MSSQL on the same box and went from 5-6sec page loads to 1-2secs on a far-from-stock install. I took the MSSQL search mod and changed it to use full-text indexes and the search times went down 90%. Now I'm sitting on > 180,000 posts and 30-60 10 minute users at any given time. The single IDE and memory usage is what is killing me at the moment. In the last 4 hours of uptime, MSSQL has used 1:10 of processor and IIS has used 1:23. Most of that is due to latency in the file system.
I've been playing with MySQL and for a single user, the performance is 50-60% slower, but the memory usage is about 10% of MSSQL, which might end up making it faster in production. If I don't move to MySQL, I'll be looking at stored procs and triggers for some of the DB functions. |
KawiForums.com
 |
|
|
|
-gary
Development Team Member
406 Posts |
Posted - 10 August 2004 : 15:07:09
|
I was just doing some testing on my staging box if anyone cares. I don't have my new DB format in Access or I'd try it.
default.asp
MySQL .57secs
MSSQL .20secs
forum.asp > 2,400 topics
MySQL 2.18secs
MSSQL .42secs
topic.asp 29 posts
MySQL .72secs
MSSQL .30secs
Searching for the word "test" in entire message timed out at 5 minutes with the stock search code and MySQL. MSSQL returned 1,184 results in 10secs with the full-text mod.
MySQL is using 10.7MB of memory, MSSQL 282.9MB.
Athlon XP 2600, 1GB DDR333, 3 WD 10K RPM Raptors in RAID5, Win2K |
KawiForums.com
|
|
|
|
Gremlin
General Help Moderator
New Zealand
7528 Posts |
Posted - 10 August 2004 : 18:49:03
|
quote:
MySQL is using 10.7MB of memory, MSSQL 282.9MB.
Try turning on Query Caching in mySQL with something larger than the default 1MB and you'll probably see some slightly different results ... time for first query will probably stay roughly the same but it certinaly should improve subsequent querys. Caching is what its all about for DB speed hence MS SQL being faster and using 270MB more memory. |
Kiwihosting.Net - The Forum Hosting Specialists
|
|
|
|
Doug G
Support Moderator
USA
6493 Posts |
Posted - 10 August 2004 : 22:41:30
|
MS SQL Server will take over all free memory, and then relinquishes memory back to the OS when it's needed, so a large memory number only indicates SQL server is operating normally.
|
======
Doug G
======
Computer history and help at www.dougscode.com |
|
|
|
Jeepaholic
Average Member
USA
697 Posts |
Posted - 11 August 2004 : 02:53:28
|
If you're interested in sharing your full-text mod, I would be VERY interested in seeing it! 
quote:
Originally posted by -gary
Searching for the word "test" in entire message timed out at 5 minutes with the stock search code and MySQL. MSSQL returned 1,184 results in 10secs with the full-text mod.
MySQL is using 10.7MB of memory, MSSQL 282.9MB.
Athlon XP 2600, 1GB DDR333, 3 WD 10K RPM Raptors in RAID5, Win2K
|
Al Bsharah
Aholics.com
Jeepaholics Anonymous
Broncoholics Anonymous
Network Insight
|
|
|
|
Gremlin
General Help Moderator
New Zealand
7528 Posts |
Posted - 11 August 2004 : 04:03:37
|
quote:
Originally posted by Doug G
MS SQL Server will take over all free memory, and then relinquishes memory back to the OS when it's needed, so a large memory number only indicates SQL server is operating normally.
Most of my SQL2K Boxes seem to take around 50% of the avail memory and hang around about there (havent done much in the way of performance tuning on these boxes yet), of course having memory pre-allocated and ready to cache queries is a performance gain though :). |
Kiwihosting.Net - The Forum Hosting Specialists
|
|
|
|
Nathan
Help Moderator
USA
7664 Posts |
Posted - 11 August 2004 : 05:00:18
|
quote:
Originally posted by ruirib
It doesn't. Full text indexing can't be used just outta the box, since it involves SQL Server configuration and, as such, it could not be available at all instalations, what would have the effect of making search unavailable for anyone who used servers without fulltext support (at least that's what I think I read somewhere around here).
I suppose so, but things like that can be easily enabled/disabled with a control in the AdminCP, so I wasn't sure. I've seen it on some forums (invision?) |
 Nathan Bales
CoreBoard | Active Users Download |
|
|
|
-gary
Development Team Member
406 Posts |
Posted - 11 August 2004 : 10:44:46
|
I turned on query caching and set it to 100MB for fun. The memory usage went to 13MB, and the performance jumped a bit when you rerun a cached query, but still not to MSSQL's level and not near it on an uncached query. Still, MSSQL is using 283MB for the same tasks.
default.asp
MySQL caching on: .32secs
forum.asp > 2,400 topics
MySQL caching on: 1.7secs
topic.asp 29 posts
MySQL caching on: .42secs
Cold turkey into a forum with 3,908 posts.
MySQL first run: 2.7secs
MySQL additional: 2.25secs
MSSQL first run: .52secs
MSSQL additional: .47secs
I forced MSSQL to 16MB of memory and it still took 38MB. The same forum.asp run took 5 seconds.
I've been told that of course MySQL is going to suck on Windows since it wasn't written for Windows, but I can't back that up yet. I'm going to do some more testing with an FC2 box, but I have no other external MSSQL box to test on, so I don't know how valid of a comparison it will be. The last time I tried MSSQL separately, it killed the performance. These are such small queries and returns, I don't know that a separate box will benefit anything except offloading processing to make more room for IIS.
Some in other discussions about this have taken it that I'm ragging on MySQL. I think it's just the opposite. Sure it's not performing at the MSSQL level, but I didn't expect it to either. MS has been developing SQL for quite some time now and their target has always been Oracle. That's some big competition to be shooting at, and surpassing in some instances, and I don't think that MySQL is anywhere near that level. It's doing quite well against MS considering that MS has the home field advantage, and you can't beat the memory usage and price. |
KawiForums.com
|
|
|
|
-gary
Development Team Member
406 Posts |
|
|
Topic |
|
Topic Locked
