Cross: Access speed

[Igor]

Can I use access for big database (cca 10-30 MB, cca 10-30k lines in
database). It will be asp.net 2.0 aplication and very fast server. Mostly
simple transactions (like SELECT * From Books Where Name like 'SomeName%').

Can it be problem if I have 10 000 unique visitors per day? I see that many
big sites have access database. Is it ok, ir it is big risk?

 

[=?ISO-8859-1?Q?Arne_Vajh=F8j?=]

Can I use access for big database (cca 10-30 MB, cca 10-30k lines in
database). It will be asp.net 2.0 aplication and very fast server. Mostly
simple transactions (like SELECT * From Books Where Name like 'SomeName%').

Can it be problem if I have 10 000 unique visitors per day? I see that many
big sites have access database. Is it ok, ir it is big risk?

Assuming that:
- you have index on Name column
- the MDB is on a local disk
- the disk is not heavily used by something else
- the unique visitors only make one query (or only a few)
- the load is reasonable good distributed over time
then it should not be a problem.

30 MB can easily be cached in disk/file cache.

10000/day is only 7/minute over 24 hours or 21/minute over
8 hours and that is not heavy.

Arne

 

[sloan]

You might want to cache items that are very often run (but seldom change)
queries.

There is a Cache object in the System.Web namespace.

You can also find an object called "WeakReference" which is a way to cache
items, but to release them if there is a memory drain.

I wouldn't start with Access. I'd start with MSDE (2000) or Sql Express
(2005).

But you might be able to pull it off.

 

[Cor Ligthert [MVP]]

Igor,

It is the same as the others wrote, however probably easier to manage.

You can set a dataset shared in your program. Because all your users share
the same application is that for all your users. It has to be a dataset that
from the userside will never be changed otherwise you should never create a
shared dataset in an AspNet appliction.

Don't forget to make an extra page to set the dataset to nothing, otherwise
you can never change it.

Cor

 

[Rotsey]

use SQL Express, it is free and durable

 

[Cor Ligthert [MVP]]

use SQL Express, it is free and durable

Hi Igor,

Are you from the Netherlands?

Cor

 

[Igor]

Hi Igor,

Are you from the Netherlands?

No, I am from Croatia!

 

[Igor]

Assuming that:

- you have index on Name column

What index?

- the MDB is on a local disk

It is on web hosting account like www.webhost4life.com. It is on fast server
(i hope that it is true).

- the disk is not heavily used by something else

It is shared hosting, but good hosting.

- the unique visitors only make one query (or only a few)

One or few queries in one page load.

- the load is reasonable good distributed over time

Distribution... hmm. It is for tourist agency. Visitors mostly come at
summer. I don't know how many often at different hours.

> 30 MB can easily be cached in disk/file cache.

Can I cashe this if it is not on my server. It is on shared hosting named
webhost4life.com or something simmilar (profesional hosting).

10000/day is only 7/minute over 24 hours or 21/minute over
8 hours and that is not heavy.

What if 100 or 300 visitors come at the same second and every of them
request 2-3 select queries?

 

[Cor Ligthert [MVP]]

I thought that you was Dutch, because there was asked about speed and you
are answering about the cost.



Cor

 

[brooksr]

1. Go into Table view of the MDB containing the actual data.
2. Open the table in design mode.
3. Go to the bottom of the screen under the 'General' tab.
4. Find the Indexed property and set it to 'Yes" either with or without
duplicates, depending on your data.
(this may vary a little bit depending on your version of Access)

Brooks

 

[Igor]

4. Find the Indexed property and set it to 'Yes" either with or without

duplicates, depending on your data.
(this may vary a little bit depending on your version of Access)

Why indexed filed is important, because of speed or something else?

 

[Mark Rae]

Why indexed filed is important, because of speed or something else?

You *really* need to find a beginner's guide to databases... Indexes are
absolutely fundamental to database performance.

As others have pointed out, Access is not particularly suited to web
deployment but, if you're determined to use it, this might be a good place
to start:
http://www.amazon.com/Access-2003-A...=pd_bbs_1/105-7348212-5254862?ie=UTF8&s=books

 

[brooksr]

Yes, primarly for speed.

Mark is right, get Access for Dummies.

It is scary that you are trying to do this without more knowledge of
databases and Access. You are probably going to have further issues
....

A STRONG Access developer may be able to make this work and will know
when it is time to move to SQL Server. A newbie ...

Brooks

 

[Mark Rae]

A STRONG Access developer may be able to make this work and will know
when it is time to move to SQL Server. A newbie ...

No chance whatsoever...

 

[RobinS]

No chance whatsoever...

That's not true. Sometimes people have dumb luck. ;-)

Robin S.

 

[Michael Lang]

SQL Express is not well suited to shared hosting environments, especially
if your provider is using IIS 6.0 or above and has assigned every site it's
own application pool, which is the most common scenario.

SQL Express is good, however it is a bit of a memory hog, it uses a fair
chunk of memory, at least 80-100Megs. If the server is hosting hundreds of
sites using SQL Express you can see how this might become a problem pretty
quickly.

Alternatively if your application is in a shared pool then SQL Express is
probably OK; however you will have all the security and reliability
problems that come with that sort of environment.

If you really believe your site is going to get that much traffic, make your
life easy, just develop for SQL Server.

If you use Access you're probably going to have to get into some threading
stufff, code a DAL with a mutex to stop multiple threads simultaneously
trying to write to your Access file, reads could be less of a problem but I
wouldn't bet my life on it's reliability.

In any case with Access, response times are going to be an issue under load,
and in many cases it's going to crash and require continuous
recycling/rebooting of your server or application pool (depending on the
setup).

Michael
www.mblmsoftware.com

 

[Igor]

SQL Express is not well suited to shared hosting environments, especially
if your provider is using IIS 6.0 or above and has assigned every site
it's own application pool, which is the most common scenario.

1. I have hosting with SQL Server Express and I have SQL Server 2000
developer edition on my computer. I will get SQL Server 2005 soon. What is
the best solution?

2. I am thinking about MySQL Database with ASP.NET 2.0 (C# 2005) for this
aplication. What do you think about this combination?

 

[Mark Rae]

1. I have hosting with SQL Server Express and I have SQL Server 2000
developer edition on my computer. I will get SQL Server 2005 soon. What is
the best solution?

If your ISP offers SQL Server Express, not SQL Server, then you don't have a
lot of choice...

2. I am thinking about MySQL Database with ASP.NET 2.0 (C# 2005) for this
aplication. What do you think about this combination?

Er, but does your ISP support MySQL...?

 

[Igor]

1. I have hosting with SQL Server Express and I have SQL Server 2000

If your ISP offers SQL Server Express, not SQL Server, then you don't have
a lot of choice...

Er, but does your ISP support MySQL...?

My web hosting provider have SQL server 2000, SQL Server 2005, SQL Server
2005 Express, MySQL and Access!
Unlimited Access Databases (I can put many programs which work with access
database)
2 SQL Server Databases
4 MySQL Databases.

So it is cheaper to use MySQL and the cheapest to use Access. I want to know
what is the best technical solution?

 

[Mark Rae]

My web hosting provider have SQL server 2000, SQL Server 2005, SQL Server
2005 Express, MySQL and Access!
OK.

Unlimited Access Databases (I can put many programs which work with access
database)
2 SQL Server Databases
4 MySQL Databases.

So it is cheaper to use MySQL and the cheapest to use Access. I want to
know what is the best technical solution?

Pretty much *anything* is better than Access in a web environment.

However, why do you need more than one database?