big access databse

[reubenb]

Hi,
i have an access database on my server tahat a client uses for like a forum or something. However, it is extremely slow so maybe if I reduce the size of the mdb is might be faster loading.
Can I like make the database have NO formatting for the tables so it just stores data?
(no i cant transfer to mysql)

thanksss

[raf]

I don't understand your question.

A table wll always have structure, and as soon as there is one record, you'll have both structure and data.
You can create views on tables and work with these (meaning you only create extra structure (the datadefinition part of the table is used) without duplicating the data, but you can't do the reverse (only have data without structure because the RDBM need the datadefinitin in order to be able to select the data and construct a resultset with it)

The reason why you app is slowing down is probably because one or more of these:
- there are to many simultanious users (--> with MsAccess, to many is >=20);
- you leave your connections open to long --> dump the recorset in a 2D array and instantly close the connection and set it to nothing, and then work on this array (as a disconnected recordset)
- you use a DNS to connect
- you don't use stored procedures
- your recordsets are to big --> retrieving fields you don't need, or records you don't use --> do all your dataprocessing and filtering with sql
- your querys aren't optimised (for instance using subqueries where you could use joins)
- your db-design needs denormalising so that you have some precomputed/agregated data which will indeed be redundant but will drastically reduce the required runtime resources
- you didn't properly index the column you select/filter on
- the variables you select/filter on aren't numerical (--> which can be solved with some normalisation so that you only have foreign keys inside your central factstables, that you do your main searching and selecting on)

[reubenb]

I understand what you're saying about structures, it's more clearer now. Thanks.

I don't understand some of these things:


1. - you leave your connections open to long --> dump the recorset in a 2D array and instantly close the connection and set it to nothing, and then work on this array (as a disconnected recordset)
2. - you don't use stored procedures
3. - your db-design needs denormalising so that you have some precomputed/agregated data which will indeed be redundant but will drastically reduce the required runtime resources
4. - the variables you select/filter on aren't numerical (--> which can be solved with some normalisation so that you only have foreign keys inside your central factstables, that you do your main searching and selecting on)


1. how do you do this? mine is

Sub CreateRS()
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open SQL, objCon, adOpenForwardOnly, adLockReadOnly, adCmdText
End Sub

'more code etc.

CloseRS()
CloseConn()%>

what and how do you mean by close it?

2. stored procedures?
3. how would i go about denormalising it?
4. again, how do i normalise it?

thanks alot for this, cheers

[raf]

The codesnippet you post has nothing to do with the connectionstring. It's the code to create a new instance otf the recordsetobject. Your connectionstrings for a DNS-less connection will look like
(assuming they are stored inside a seperate file that you include inside your actual scriptpage)

<%
dim conadmin
set conadmin=server.CreateObject("adodb.connection")
conadmin.Open("provider=microsoft.jet.oledb.4.0;data source="&server.MapPath("../db/yourdb.mdb"))
%>

(the path to the accesfile start with going to the parent directory and then to directory 'db'.

Once the recordset is opened, you need to use getrows() to dump it into an array (run a search in the ASP forum or at google for eaxamples) and then you immedeately close the recordset and connection, like

recorsetname.Close
connectionname.Close

set recordsetname=nothing
set connectionname = nothing


stored procedures --> run a search in the ASP forum or on google. Should turn up hundreds of links

db denormalisation --> i've writen a bit about it in some posts in the ASP forum
http://www.codingforums.com/showthread.php?s=&threadid=17608&highlight=compute
http://www.codingforums.com/showthread.php?t=25719&highlight=aggregate
There's a lott more to write about that, but this should get you the general idea.

db-normalisation --> run a search at googe or the mySQL forum. I've touched it in a few threads like
http://www.codingforums.com/showthread.php?t=26609&highlight=factstable
http://www.codingforums.com/showthread.php?t=36125&highlight=factstable
http://www.codingforums.com/showthread.php?t=23310&highlight=factstable
http://www.codingforums.com/showthread.php?t=22342&highlight=factstable
http://www.codingforums.com/showthread.php?t=38057&highlight=factstable

When reading through these threads, it's important to keep in mind that db-design is closely connected to the screenflow of your application. It's not easy t explain in pure text, but the links to the db-denormalisation should give you some idea what i mean.