| Author |
 Topic Search  Topic Options
|
eeebz 
Newbie
Joined: 01 November 2003
Status: Offline
Points: 3
|
 Post Options
 Thanks(0)
 Quote  Reply
 Topic: SQL searches slower than Access?
Posted: 01 November 2003 at 4:00pm |
Summary: I am did some testing, and it seems that forum searches in the access version are ~400% faster than SQL. Why would that be?
Details: I am looking at upgrading my forums from 7.01 to 7.5. I am also looking at upgrading from Access to SQL. I created a copy of my DB and did some testing. I was able to upgrade to 7.5 access no problem. I also was able to get my DB ported to SQL. I was able to see some improvements in speed, or more that the pages were loading faster according to the number at the bottom. I can't tell the difference really. Anyway, I decided to test a search. I was surprised that searches in the SQL version were taking 4-5 times longer than the access searches. A SQL search takes about 6 seconds. The Access ones take about 1.3. The databases are identical, and both are seperate from the one my users are using. IE it's just me poking around.
My access DB is ~10MB. When ported to SQL it jumped to ~50MB (28mb free). I have about 50 active users total, and about 20 new posts per day. The most I have ever seen on at one time is 6. The server is a dual P3-500 with 512MB RAM running server 2003. The SQL I have is SQL 2000.
I did a search here and didn't find any hits. Anyone have any thoughts? Is this just how it is?
Thanks in advance,
Eeebz.
Edited by eeebz
|
 |
ljamal
Mod Builder Group
Joined: 16 April 2003
Status: Offline
Points: 888
|
Post Options
Thanks(0)
Quote Reply
Posted: 01 November 2003 at 4:09pm |
|
My guess is that the SQL query is not optimized for SQL Server. It's almost impossible to write code that is simultaneous optimized for SQL Server and Access and the forum is mostly optimized for Access.
|
|
|
|
josk
Mod Builder Group
Joined: 10 June 2003
Location: Finland
Status: Offline
Points: 42
|
Post Options
Thanks(0)
Quote Reply
Posted: 02 November 2003 at 3:10am |
Strange! When I upgrade my forum to sql server i get feeling that its much faster. Never really did any closer lookup but pages opened much faster than in access version of forum. I used also same forum data.
I have about 150 registered active users total, and about 15 new posts per day. The most I have ever seen on at one time is 80 active users (including guests). The server is a Duron 1300Mhz with 1GB RAM running server 2003. The SQL I have is SQL 2000.
josk
|
|
skaue
Mod Builder Group
Joined: 26 October 2003
Location: Norway
Status: Offline
Points: 16
|
Post Options
Thanks(0)
Quote Reply
Posted: 02 November 2003 at 5:00am |
|
A little note:
The Jet Engine, used by Access, as layer between code and db is alot faster than odbc or some other dsn.
But I think ljamal is onto something.... its probably the SQL statement that needs to be tweaked. Why not have the queryt printed out and post it here... so we can have a look at it..
|
|
WebWiz-Bruce
Admin Group
Web Wiz Developer
Joined: 03 September 2001
Location: Bournemouth
Status: Offline
Points: 9844
|
Post Options
Thanks(0)
Quote Reply
Posted: 02 November 2003 at 5:18am |
|
I would check the SQL server, 50Mb seems like allot from 10Mb Access DB.
The SQL server database for this site with over 32,000 posts and 6,500 members is only just above 30Mb in size.
|
|
|
|
ljamal
Mod Builder Group
Joined: 16 April 2003
Status: Offline
Points: 888
|
Post Options
Thanks(0)
Quote Reply
Posted: 02 November 2003 at 7:22am |
|
The SQL DB is only 22MB (50 MB with 28 Free) and I suspect that most of that is in the transaction log due to the move.
First, are the SQL Server and Web Server on the same machine? Are they on the same network? If they are on the same machine, you will see a performance hit because they are both processor intensive. With the new faster chips this is less of a problem, but is could still be a problem. If they are not on the same network, you will see a performance hit due to the number of SQL calls to the database. Each will take increasingly more time resulting in long page refreshes.
However, note that Snitz forum has this same problem and the best solution is to use SQL Server's full text query for searches.
|
|
|
|
eeebz
Newbie
Joined: 01 November 2003
Status: Offline
Points: 3
|
Post Options
Thanks(0)
Quote Reply
Posted: 02 November 2003 at 10:27am |
Yes, they are both SQL and IIS are on the same server. Maybe it is a processor bottleneck when searching the SQL DB. Seems weird though that the same thing wouldn't happen with the access searches.
Skaue - I'm a SQL newbie. Let me figure out what a queryt is and I'll post it.
E
|
|
ljamal
Mod Builder Group
Joined: 16 April 2003
Status: Offline
Points: 888
|
Post Options
Thanks(0)
Quote Reply
Posted: 02 November 2003 at 10:40am |
|
Open the windows task manager on the web server and look at the performance tab when you do a search. If the CPU Usagae pane jumps above 65-75% for the entire duration of the query than you should split the SQL and Web server or optimize the SQL Server and ISS for the machine.
You will appearance a problem with Access because Access is not an enterprise database server. It's essentially a suped up flat file database. Saying that it doesn't happen with Access search would be kin to saying that you don't have the problem with a text file.
|
|
|
|
