SQL searches slower than Access?
Printed From: Web Wiz Forums
Category: Web Wiz Web App Support Forums
Forum Name: Web Wiz Forums
Forum Description: Support forum for Web Wiz Forums application.
URL: https://forums.webwiz.net/forum_posts.asp?TID=6912
Printed Date: 03 April 2026 at 11:14pm
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com
Topic: SQL searches slower than Access?
Posted By: eeebz
Subject: SQL searches slower than Access?
Date 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. |
Replies:
Posted By: ljamal
Date 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. ------------- L. Jamal Walton http://www.ljamal.com/" rel="nofollow - L. Jamal Inc : Web/ Print Design and ASP Programming |
Posted By: josk
Date 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 |
Posted By: skaue
Date 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.. |
Posted By: WebWiz-Bruce
Date 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. ------------- https://www.webwiz.net/web-wiz-forums/forum-hosting.htm" rel="nofollow - Web Wiz Forums Hosting https://www.webwiz.net/web-hosting/windows-web-hosting.htm" rel="nofollow - ASP.NET Web Hosting |
Posted By: ljamal
Date 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. ------------- L. Jamal Walton http://www.ljamal.com/" rel="nofollow - L. Jamal Inc : Web/ Print Design and ASP Programming |
Posted By: eeebz
Date 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 |
Posted By: ljamal
Date 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. ------------- L. Jamal Walton http://www.ljamal.com/" rel="nofollow - L. Jamal Inc : Web/ Print Design and ASP Programming |
Posted By: DBSilver
Date Posted: 04 November 2003 at 4:53am
|
In My experience, when you search for 1 record out of 10 total - Access will normally outperform SQL Server. As your database grows in size you will see a crossover point where SQL Server will total outperform Access. Regards |
Posted By: wistex
Date Posted: 04 November 2003 at 8:11am
|
DBSilver has a point there. Access is designed for relatively smaller databases and is designed for a limited amount of users accessing at the same time. It has less overhead in doing searches because it isn't expecting 1000 people accessing at once. SQL, on the other hand, is designed for larger databases with a lot a users accessing the data at the same time. This requires more overhead. For smaller databases with not many similtaneous users, this would actually slow down your search. Once you get to a certain size and/or a certain amount of users accessing similtaneously, SQL will outperform Access every time. Also, when the Access database gets too big and/or too many users try to access it at the same time, you will start to have problems and slow access with Access. If your website is smaller with a small community and you don't plan on going big time, then Access may be the way to go. If your site is big, or you plan on making it big, then SQL is the way to go. ------------- http://www.wistex.com" rel="nofollow - WisTex Solutions http://www.caribbeanchoice.com/forums" rel="nofollow - CaribbeanChoice Forums |
DBSilver wrote: