~~~~~~~Forum Diskusi Software dan Internet untuk Kristen-Katolik~~~~~~~
mungkin bermanfaat :-)
hdk
The Truth About Access
By 15Seconds Discussion List
http://www.15seconds.com/Issue/010514.htm
Eric Innocently Asks:
Can anybody give me the MS link where is stated that Access can handle 255 connections
please?
I can't find it anymore
Luis Provides:
http://msdn.microsoft.com/library/backgrnd/html/acmsdeop.htm
Ian Sheds Some Light:
I can tell you from experience that Access cannot handle 255 concurrent connections. I
don't care what MS says. We had a database with multiple tables. The main table where
most of the data was retrieved from had about 5000 rows. After 7-8 concurrent
connections we began to see serious problems with performance if not outright ASP
crashes. 5000 rows and 7-8 concurrent connections is not a lot. We basically had about
1000-2000 user sessions a day, also not a whole lot. We had to move the site up to
SQLServer 7.0 and it works like a charm. If you are expecting 255 concurrent
connections to your database DO NOT USE Access. If you do you will immediately need to
redo your site to work with SQLServer or some other equally powerful data server. You
have been warned.
Dave Adds:
Access was never designed to be a database server. It's a great program for desktop
database work, but on a practical basis, it is entirely unsuitable for ANY task where
there will be more than a half a dozen (if that) concurrent connections on a regular
basis. There are a whole host of issues that come into play with locking that make it
unsuitable as a database server. MS isn't entirely forthcoming with the limitations of
Access, but they do exist.
In addition, the stress of using Jet (the access database engine) on the same box as
IIS will slow down the entire web site.
And to the person (Sam?) who was asked to back this fact up with documentation...It
sounds like a management type person is questioning your technical knowledge. Probably
because they don't want to (can't) spring for a SQL server installation.
Just tell them that you have to pay if you want to play. Alternatively, you could look
into MySQL which isn't a bad solution, but if you are developing db apps on the web
using ASP, SQL server is the way to go. If they insist on having you use Access, tell
them that you will do your best, but the results will not be acceptable.
Not only is it robust enough to handle the traffic, but by moving to SQL Server, you
can start dealing with access control (users & groups), logging, and moving your apps
into a two-tier (or even n-tier) structure by using stored procedures.
Just my 2 cents.
Michael Gets Technical:
Access is Apartment-Single Threaded... (single use), multiple access is qued.
SQL Server is Apartment + Free Threaded, multiple uses at one time.
Access is fine for 1-10 users depending on the load, and size of the database. For
more than that, a true database is better. (there are lesser expense, or free
databases out there, that will perform better than an access tablespace)
Ken Adds:
You can download the Kevin Collins Whitepaper on Microsoft Jet Locking from here:
http://support.microsoft.com/support/kb/articles/Q176/6/70.ASP
256 connections are possible - but I wouldn't expect to see that possible in any kind
of real world scenario - each connection would have to be doing something on a
different table, so as not to generate any exclusive non-shared locks...
A Frightened Danny Responds:
You guys are scaring me!
We are using Access on a W2K shared server
We have not had any problems as yet but usage at this stage is pretty light.
It is certainly likely that we will have around 100 users concurrently accessing the
database.
We are a non profit organisation and cannot possibly afford the thousands of �'s that
SQL server must cost.
Is it possible to work with Access 2000 on our development machines and use SQL server
on the remote server?
Marcos Offers This Advice:
Danny,
In your case, a MySQL with Linux will really help you move away from the access
problem, I have too experience the problem wih Access 97 and 2000 , they are good up
to a certain degree. But I will be against any "production level" environment using
Access either for a web app or regular fat client.
When I started the job I have now, we had about 10+ Access DB all over the company,
now we have a SQL2000 server with all of this fragmented db converted into one. I can
control security all the way to the table level and limit their usage on it.
I know that for non-profit org, this is not a feasible alternative but then again how
much value you can put on your data.
Usually I tell my non-profit clients that their best alternative if $$ is a concern is
to invest on a Linux with MySQL enviroment with PHP, that way their ASP guys do not
get too many headaches recoding some of the queries.
Danny Thanks Marcos But Has Concerns:
All our pages have been written using VBscipt ASP and Access type SQL
Would a switch to Linux/MySQL mean a total re-write of the code??!!
Rob Offers This Simple Solution:
just get the mySql database, the myODBC connector, and hook into it with ADO in your
ASP pages.
I've done it before, no problemo.
Michael Clarifies the MySQL Solution:
MySQL is available for Win32 platforms (95/98/me/nt/2k etc.) It is free as it is now
GNU'd, and open source.
The ODBC connector allows you to connect to the database via ODBC, alternatively there
are other methods you could use that would be faster (than odbc), if you are writing
your own com object in vb, or c++.
Stephen Explains The Difference Between Concurrent Users and Connections:
Danny,
Keep in mind that a concurrent connection is different from a concurrent user. If your
code is written well (i.e., database connections are opened only when they're needed
and closed as soon as they're not needed any more), just a few concurrent connections
could support hundreds of users. A concurrent connection means that an active database
connection is open. Again, in a well-written app, this is only when a SQL statement is
being executed (plus some time to set up and take down the connection). If you have
one hundred users who hit a page that uses an Access database, odds are they all won't
make a database request at precisely the same moment.
While I agree that Access really should be avoided, there are too many people who have
put a rule in their mind that *anyone* who even considers using MS Access with a Web
site is daft (without really understanding the gospel they preach). I have seen some
pretty robust applications that use Access as the back-end that perform well under
some pretty decent loads (i.e., 25,000 accesses a day).
So before you panic, look at your current app. Is is working? Is the performance
acceptable? Has anyone complained? Don't fix what ain't broken. If it's a little slow
for your tastes (but no one less seems to mind), look at improving the underlying
code. You can wring some pretty big gains out of writing better code without changing
the database. Then next time the application needs a major overhaul, consider changing
the database, but don't start mucking around simply because a few people made some on
the spot determinations about an application they've never seen or used. Any
performance enhancements you've made to get Access to work quickly, will make the new
database scream.
Michael Adds:
In Access,
1) use an ole-db connection with the jet engine, not an odbc connection
2) look into getstring, and getrows whenever possible.
3) open and close the connection any time you need to run a query.
4) combine queries, and use multiple recordsets whenever possible.
5) try to only run 1 recordset query on a page. (can still return multiple recordsets.)
6) use disconnected recordsets.
Example..
setup multi-recordset query (multiple selects)
open connection
open recordset object
for each recordset returned, check for .eof, and assign to a different array, with
.getrows
close recordset object
close connection
(if you can't run all your queries for a page with one multiple recordset returned,
open the connection as late as possible, and close it as soon as possible, still use
disconnected recordsets, and update via conn.exec, not via recordset row objects)
NOTE: even mysql, would perform better, although it doesn't do table relationship
constraints, which means you have to make sure of your constraints in code.
Roger Adds Caching to the Mix:
If caching is used carefully, Access can do quite well.
The library that powers RSFAST @ http://www.learnasp.com/learn/rsfast.asp
automatically caches every Access query that comes in and only dirties the cache if
the filedate/time changes. (older versions demand the programmer specify a cache
duration, now with Access it auto-caches with no additional code)
You could hit it 10,000 times for say 150 different queries, i.e. "select .. where
state='NY' ", "select .. where state='PA' ", etc. and it would only query the data 150
times, the other 9,850 times would come out of the caches.
My informal benchmarks of this are it always is 2.5 - 6 x faster for the cached data
(and it doesn't HIT THE DATABASE thus concurrent use drops big time except for a quick
check of the file date/time) if Access is involved.
SQLserver even if not cached does quite a bit of caching and 1.5-2.5 x faster is a
typical gain when this library accesses SQLserver but once again it would reduce the
people touching the database and read locks!
Stephen Concludes:
Your own observations are just as valid as the word of an alleged expert. Trust your
instincts. It's very easy to follow along lists like this and try to change everything
that you know already works and works in an acceptable manner for your situation. If
an "expert" says X is true, but you know Y to be true as well, there's no reason to
jump to X.
Access is not the greatest database for widespread multi-user environments. In your
particular case (and many others), Access is adequate enough to get the job done.
You'll just need to make sure that you occasionally compress the database (if the
users are doing updates and deletes) and make sure you have good backups. The most
common problem with Access in a Web environment (besides the possibility of poor
performance) is that the database file gets corrupted. The repair feature within the
ODBC manager comes in handy in these cases (and actually works most of the time).
This conversation string was taken from the 15Seconds ASP Listserv on 5/11/01. If you
have an ASP-related question or would like to share some of your knowledge with
others, you may join the list by clicking here.
Other Articles
Apr 23, 2001 - Integrating User Search with ASP and SQL Server Full-Text Search
This article presents sample source code for a user query parser that allows users to
submit full-text search queries from ASP to SQL Server 2000.
[Read This Article] [Top]
Apr 11, 2001 - Create a Fully Functional, Multifeatured ASP Shopping Basket
Many hosted e-commerce Web sites today still rely on their ISPs for shopping cart
functionality - at additional monthly fees. This article by Manny Agrinya shows how
you can painlessly design and program a multifunctional shopping cart application
using standard hosting plan features like SQL Server, ASP, and ODBC.
[Read This Article] [Top]
Mar 29, 2001 - Overcome Data Shaping Limitations
At some point in their career, most developers have had to create and store a
hierarchy of information. Craig Huber will show you a brief overview of ADO Data
Shaping and its associated limitations. Then he will explore a solution that overcomes
the limitations and supports infinite levels in a hierarchy.
[Read This Article] [Top]
Feb 28, 2001 - The Truth About VarChar
Read what members of the 15Seconds Discussion list had to say on the properties of
SQL VARCHAR.
[Read This Article] [Top]
Feb 15, 2001 - The Nature of A Recordset
Members of the 15Seconds discussion list clear up some confusion about the properties
of ADO recordsets.
[Read This Article] [Top]
Feb 9, 2001 - Importing Files into A Database
Read what advice members of the 15Seconds Discussion list had to offer on importing
files into a database where field 1 contains the filename and field 2 contains the
contents.
[Read This Article] [Top]
Jan 16, 2001 - Using the TDC to Access and Manipulate Legacy Data Client Side
IE provides the perfect tool for accessing legacy data stored in various formats over
the Web. Ian Vink shows us how to harness the power of the Tabular Data Control
feature.
[Read This Article] [Top]
Nov 2, 2000 - Retrieving Dynamic XML from SQL Server 7.0 and 2000
SQL Server is packed with features for retrieving XML documents. Steven Woods offers
a demonstration of extending a SQL Query via the RAW, AUTO, and EXPLICIT modes to
illustrate the functionality available. The article then discusses the concept of
templates, which allow the creation of dynamic parameter-based XML documents, and how
templates can be executed via Visual Basic.
[Read This Article] [Top]
Oct 30, 2000 - ASP AND SQL-DMO:
Create a Server Component Encompassing the SQL-DMO Functionality
Creating an ASP component to manage SQL Server remotely is fairly easy. It only
requires a basic knowledge of ASP, VB, and SQL Server. S.S. Ahmed's article
demonstrates how to create a component that harnesses the power of SQL-DMO so you can
manage SQL tasks remotely.
[Read This Article] [Top]
Aug 31, 2000 - Creating Dependent Select Objects With ASP
Select boxes must often communicate with one another (i.e., a Child select object's
option values depend on the value selected in the Parent object). Jason Butler details
how to create a hierarchy of select objects using ASP, SQL, ADO, and JavaScript.
[Read This Article] [Top]
Aug 17, 2000 - Introduction to Transact SQL User-Defined Functions
User-defined functions are a new feature in SQL Server 2000. Karen Gayda shows how
UDFs can be used to enhance queries and provide functionality that was previously
unavailable.
[Read This Article] [Top]
Aug 10, 2000 - Calling Stored Procedures From Active Server Pages
With a low-level database, offering only basic data storing functionality, SQL code
often needs to be embedded in Active Server Pages. With a more sophisticated database,
stored procedures can be far more effective. Here's a case study, including code,
showing how a database was converted to stored procedures.
[Read This Article] [Top]
Jul 14, 2000 - Content Management Made Easy with ASP
Automated content management is a must for many web sites. It's difficult to provide
fresh new content without a standard automated system. This article discusses how to
generate a general submission system, as well as how to manage the work flow, all with
examples!
[Read This Article] [Top]
Jun 21, 2000 - Keeping Track of Who's In Charge Today?
Many offices, particularly in military and government organizations, are required to
have someone in charge during office hours. If the official manager is absent, that
person delegates responsibility to someone else as acting, but who?
A Key Personnel Today table shows who is acting in every official position and how to
reach them.
[Read This Article] [Top]
Jun 8, 2000 - Get Your Data Faster with a Data Cache
Storing frequently used lookup data in a database is a great idea (e.g. order status
codes, state names, etc.) that saves tremendous amounts of time in design and
maintenance. However, retrieving that data from the database every time it is needed
is very inefficient. This article describes how to use Application variables to cache
frequently used lookup data in memory to achieve lightning fast access times. In my
tests, I've seen as much as a 5000% increase in performance.
[Read This Article] [Top]
Mar 23, 2000 - Using an ADO Standalone/Custom Recordset in VBScript
Developer Stephan Onisick shows us how to create a standalone/custom recordset and
use its organizational ability to perform logical tasks with data without connecting
to a database. This article uses a small application written using VBScript, ADO 2.1,
and an Excel spreadsheet to record and print computer expenses for tax preparations.
The standalone recordset is saved in XML format, and the file can be updated with new
data simply by reopening as a recordset and using normal recordset methods.
[Read This Article] [Top]
Mar 9, 2000 - SQL Solutions
Cindy Cruciger claims there is a better way to write a functional Active Server Page
that allows interaction between a database file and the Web, without getting caught in
an SQL nightmare. She offers a snippet of SQL code and adds some logical layers, error
checking and formatting.
[Read This Article] [Top]
Jan 31, 2000 - ASP-Oracle Connectivity Using OO4O
Selva Kumar�s article shows how to create practical Oracle database connectivity from
ASP using Oracle Objects for OLE (OO4O). OO40, the Oracle middleware, allows native
access to Oracle from client applications using the Microsoft Object Linking and
Embedding (OLE) standard. Sample code is provided.
[Read This Article] [Top]
Nov 11, 1999 - Database-Driven WWW Help System
The help system presented in Vujosevic and Laberge's article is self contained and
can be updated and altered without impacting the original Web application. Much like
an online book, the help icon in the Web application dives into an application system
for the help option. Each Web page has its own separate help page with a database that
contains one row in a table for every calling Web page. Sample code is provided.
[Read This Article] [Top]
Oct 21, 1999 - Updating Excel From the Web
Bill Jeffries's article on Excel's Web Query tool demonstrates how to update selected
spreadsheet cells instantly over an HTTP connection.
[Read This Article] [Top]
May 31, 1997 - Connection Pooling with ASP
Connection pooling might be the easiest way to speed up your dynamic web pages
reading from SQL Server. Unfortunately, connection pooling within is turned off by
default in Active Server pages. Probably because connection pooling is rarely
understood in its entirety. This issue discusses connection pooling with ASP, ISAPI,
IDC, and Visual Basic applications. Included is a discussion about ODBC 3.0 and the
newest bug fix for ODBC.
[Read This Article] [Top]
Dec 10, 1996 - ODBC 3.0 Connection Pooling
This issue of 15 Seconds contain an example of how to create an ISAPI server
extension in MSVC 4.2 with ODBC 3.0 connection pooling. There is also an evaluation of
ODBC 3.0, OLEDB, ADO and DAO.
[Read This Article] [Top]
salam
handita
------ Hemat Bandwith : Hapus pesan yang tidak perlu sebelum reply ------
SUBSCRIBE---> To: [EMAIL PROTECTED], Isi/Body: kosong
UNSUBSCRIBE---> To: [EMAIL PROTECTED], Isi/Body: kosong
Moderator: Ronny <[EMAIL PROTECTED]>, Alex <[EMAIL PROTECTED]>
Web : http://hub.xc.org/cgi-bin/lyris.pl?enter=i-kan-software
