| Author |
 Topic  |
|
|
pierretopping
Junior Member
United Kingdom
224 Posts |
 Posted - 05 January 2009 : 16:35:54
|
Hi All,
I've now successfully migrated (on a test system) our Access Database to MS-SQL by following the steps in the excellent "Step by Step Upgrade from Access to MS SQL" at http://forum.snitz.com/forum/topic.asp?TOPIC_ID=63884
Now since if got the access and MS-SQL database's on the same server (so on the same hardware), I decided to do some comparisons with performance of Access Vs Ms-SQL.
My Database has around 20'000 posts, and is around 25mb in Access, and after its imported to MS-SQL the file size went up a bit.
Now the strange thing is that if I run a search with search.asp against the Access database and using the same search Criteria , I'm getting the response in around 1 second. I then change my config.asp file so its using the ms-sql version of the database, and run the same query and it cones back in at 4 seconds ?
4 times slower ?
I've run the Optimization stuff on the MS-SQL version of the data, but no change. I've also run the test several times in case the data was not being buffed in some way, but no change.
The test system only has 1 cpu, and the like system will have 4, so OK, I can see the MS-SQL version can (maybe) use Parallelism to its advantage later on, but MS-SQL right now id 4 times slower then the access version of the data ?
Any ideas ?
Is there a different version of search.ash to use with MS-SQL ?
Thanks for all your help on this 
< |
|
|
HuwR
Forum Admin
United Kingdom
20584 Posts |
Posted - 05 January 2009 : 17:09:23
|
| that is a fairly typical comparison, the benefits of sqlare not that it can return results quicker, you will see the benefits when you have hundreds/thousands of concurrent users on your forum all reading posting and searching < |
 |
|
|
AnonJr
Moderator
United States
5768 Posts |
Posted - 05 January 2009 : 17:16:30
|
For starters, there is a SQL Server optimized version of search.asp floating around here somewhere - that should change things a little bit.  In order to be more DB agnostic, the code isn't particularly optimized for any given platform. That's a good thing, and a different discussion for another day.
Also, your test is a little disingenuous... you searching for a particular term is not the same as 50 people running the search at the same time. Depending on the hardware, Access does ok up to about 15 or so people, but once you start moving beyond that you're going to see a precipitous drop in performance. But, SQL Server was born to handle multiple connections, and that is where you'll see it shine.
There are a number of other advantages too, not directly related to speed.< |
|
|
|
pierretopping
Junior Member
United Kingdom
224 Posts |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 05 January 2009 : 17:53:30
|
| Don't be surprised. I remember a forum with about 200,000 posts that had serious search performance issues, and that lead me to develop my optimized search page. Even after the search page, the same query in Access was noticeably faster!< |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
pierretopping
Junior Member
United Kingdom
224 Posts |
Posted - 06 January 2009 : 11:20:05
|
Thanks for the input :o)
Apart from the slow search using ms-sql, should I be aware of any other issues with performance running with ms-sql over access ?
We had no performance issues using the access DB, and we only migrated over to MS-SQL because we were aware that access could hit some problems with a large number of posts (20,000) and users(15 to 20 on-line normally).
Thanks again, some great input from all.
Pierre< |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
Flashspot
Starting Member
United States
6 Posts |
Posted - 21 October 2009 : 13:14:46
|
Just a quick thanx to ruirib.
With about 4,500 members, 28,000 topics and 300,000 replies, using his search code improved search performance by about 30%.
(SQL Server 2005/Win Srvr 2008/Snitz 3.4.07) |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
| |
Topic |
|
Topic Locked
