DBSilver wrote: 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.

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.

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
DBSilver

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

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

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.