Access backend database speed issues with DRW

S

skc

Hi,

I have made a website using FP2K and Access2K as a backend
database to the site using the FP2K DRW.

At the moment, I am trying to resolve a speed issue. Go
to www.maslenmotors.com, and click the Showroom tab and
click View All Cars using Broadband connection.

You will find that the page takes absolutely ages to
download to a web browser. I am interrogating the
database by using three (or four) parameters such as:
Make, Age, Price and I have indexed these fields in my
Access2K table, by toggling the Indexed (Duplicates OK)
fields.

Please can someone offer me advice on how to speed up the
access. Is it my indexing, or are the pictures and DRW
not a good idea? In which case, how do other people do it?

Please help.

Skc
 
K

Kevin Spencer

Are you pulling the pictures from the database? That is very expensive an
unnecessary. It is much better to put the images into the file system, and
simply store the image file name in the database.

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Big things are made up
of lots of little things.
 
K

Kevin Spencer

Another possibility is your use of Access as your database back-end. Access
is a desktop product, and not designed for Internet use. While it can be
used on the Internet with relatively small numbers of concurrently-connected
clients, it is not optimal. A database server such as SQL Server will
improve performance tremendously.

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Big things are made up
of lots of little things.
 
J

Jon

Hi,
The first thing I'd do is break the results down into groups of 10, you can
do this with the FP wizard, this should speed things up considerably. Also a
minor point you have Mercedes and Mercedes Benz as seperate entries in the
makes box, when I did view all cars a silver CLK came up fairly near the
top, when I search for mercedes benz this car didn't show but of course it
came up under mercedes

Probably the best answer though is to lose the FP wizard and handcode your
app either in asp or asp.net. To get an idea of how much faster this
approach is try going to 1 of my sites www.bookhead.co.uk type in a common
word in the search box and see how quick the results come back, from a
database of several hundred thousand records.

Jon
Microsoft MVP - FP
 
R

Ronx

Tried in IE6 and Netscape7
Netscape 7 showed the top of the page very quickly, (6 seconds) but
continued loading for a while after that - faster than IE6. IE6 took
approx. 25 seconds to display anything.

I have estimated 200K in images (60 cars at 3.5K each), but the killer,
IMHO, is the HTML - 998K. This is huge.
Looking at the HTML, about half is whitespace at the beginning of each line
of code. Get rid of that and the download/render time will be reduced
considerably.
 
S

skc

i am only storing the filename of the image.

the DRW page for the image contains ../images/<%
=FP_FieldVal...%> so that the image uploads from the
images directory based on the image name in my access
table.

is this correct?
 
S

skc

i have lots of problems with the breakdown of results.
the video buttons tend not to work, as i can go from page
1 to page 2, but it sticks on page 2 and I cannot go to
page three.
 
S

skc

Good point - i will look into the whitespace.
-----Original Message-----
Tried in IE6 and Netscape7
Netscape 7 showed the top of the page very quickly, (6 seconds) but
continued loading for a while after that - faster than IE6. IE6 took
approx. 25 seconds to display anything.

I have estimated 200K in images (60 cars at 3.5K each), but the killer,
IMHO, is the HTML - 998K. This is huge.
Looking at the HTML, about half is whitespace at the beginning of each line
of code. Get rid of that and the download/render time will be reduced
considerably.

--
Ron
Reply only to group - emails will be deleted unread.



.
Click to expand...
 
S

skc

is it worth having SQL server for a table with 60 records?

if I had a table with 60,000 records, then it is a
different matter.

skc
 
T

Thomas A. Rowe

Access is not the issue.

Really consider learning to write your own ASP code and not rely on the DRW.
By learning to write your own code, you will be able to do more with less
code, which will result in faster queries, etc.

--

==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
WEBMASTER Resources(tm)

FrontPage Resources, WebCircle,
MS KB Quick Links, etc.
==============================================
 
K

Kevin Spencer

i am only storing the filename of the image.

You're doing that part right. As such, I would guess that it is the number
and total size of the image files being downloaded that is causing your
speed issue.

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Big things are made up
of lots of little things.
 
K

Kevin Spencer

is it worth having SQL server for a table with 60 records?

It's not a matter of how many records you're fetching that would determine
whether you want to use SQL Server or not. It's a matter of speed, and
especially how many concurrent users you expect to be connected. My guess
would be, Access will be fine for now. Again, after your feedback, I believe
the issue is simply the number and total size of the image files being
downloaded.

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Big things are made up
of lots of little things.
 
M

Mike Mueller

I think I checked out the 'sticky' page thing you had going-
no answers for you on that. I did notice using Firefox (aka
FireBird) that if you click the next button twice it will
take you there

Mike

skc wrote:
: i have lots of problems with the breakdown of results.
: the video buttons tend not to work, as i can go from page
: 1 to page 2, but it sticks on page 2 and I cannot go to
: page three.
:
:: -----Original Message-----
:: Hi,
:: The first thing I'd do is break the results down into
:: groups of 10, you can do this with the FP wizard, this
:: should speed things up considerably. Also a minor point
:: you have Mercedes and Mercedes Benz as seperate entries
:: in the makes box, when I did view all cars a silver CLK
:: came up fairly near the top, when I search for mercedes
:: benz this car didn't show but of course it came up under
:: mercedes
::
:: Probably the best answer though is to lose the FP wizard
:: and handcode your app either in asp or asp.net. To get
:: an idea of how much faster this approach is try going to
:: 1 of my sites www.bookhead.co.uk type in a common word
:: in the search box and see how quick the results come
:: back, from a database of several hundred thousand
:: records.
::
:: Jon
:: Microsoft MVP - FP
::
:: message ::: Hi,
:::
::: I have made a website using FP2K and Access2K as a
::: backend database to the site using the FP2K DRW.
:::
::: At the moment, I am trying to resolve a speed issue. Go
::: to www.maslenmotors.com, and click the Showroom tab and
::: click View All Cars using Broadband connection.
:::
::: You will find that the page takes absolutely ages to
::: download to a web browser. I am interrogating the
::: database by using three (or four) parameters such as:
::: Make, Age, Price and I have indexed these fields in my
::: Access2K table, by toggling the Indexed (Duplicates OK)
::: fields.
:::
::: Please can someone offer me advice on how to speed up
::: the access. Is it my indexing, or are the pictures and
::: DRW not a good idea? In which case, how do other
::: people do it?
:::
::: Please help.
:::
::: Skc
::
::
:: .
 
S

skc

Can I have an example?
-----Original Message-----
Access is not the issue.

Really consider learning to write your own ASP code and not rely on the DRW.
By learning to write your own code, you will be able to do more with less
code, which will result in faster queries, etc.

--

==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
WEBMASTER Resources(tm)

FrontPage Resources, WebCircle,
MS KB Quick Links, etc.
==============================================
To assist you in getting the best answers for FrontPage support see:
http://www.net-sites.com/sitebuilder/newsgroups.asp




.
Click to expand...
 
S

skc

so how do i deal with it?
-----Original Message-----

You're doing that part right. As such, I would guess that it is the number
and total size of the image files being downloaded that is causing your
speed issue.

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Big things are made up
of lots of little things.




.
Click to expand...
 
S

skc

Thomas,

Can i have an example of sample script that is not as
expensive as the FP DRW?

I can then use this to test the speed.

Skc
 
T

Thomas A. Rowe

SKC:

The following is what I basically use for all my projects, because it has
worked well.

I use a System DSN with a global.asa file generated by FP and I keep the
application open, but open and close all tables as needed.

The following is my basic way of retrieving a query based on the value of
the query string:

<%
Dim DSN_Name
DSN_Name = Application("LAB_ConnectionString")
set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open DSN_Name

Dim Cat
Cat = Request.QueryString("i")

Set objRS = Server.CreateObject("ADODB.Recordset")
SQL = "SELECT * FROM Lamp WHERE Cat = '" & Cat & "' Order By ProductID"
objRS.Open SQL, DSN_Name
%>

Keep in mind that there are others, here that prefer to do things
differently, since some of the method I use are supposedly "depreciated".

I look at this way, as long as something works, and works well. I see no
reason to change my current methods since they are still supported under
Windows 2000/2003 Server and can handle at a minimum 6,000 users per hour.

--

==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
WEBMASTER Resources(tm)

FrontPage Resources, WebCircle,
MS KB Quick Links, etc.
==============================================
 
K

Kevin Spencer

so how do i deal with it?

Typically, this is done by limiting the number of records returned, and
using paging to view more. The FrontPage database tools will allow you to do
this.

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Big things are made up
of lots of little things.
 
J

Jon

Hi,
as an alternative try something like this -
<%
set oRs = server.createobject("adodb.recordset")
oRs.open "select field1,field2 from table", ' your connection string
aData = oRs.getrows
oRs.close: set oRs = nothing
for i = 0 to ubound(aData,2)
response.write aData(0,i) & " " & aData(1,i) & "<BR>"
next
%>
it should be about the fastest way to get data but I think the weak spot in
your app is that you're pulling all the cars at once - my priority would be
to get some paging going. Pulling say 10 cars at a time should speed things
up nicely.

Jon
Microsoft MVP - FP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Speed issue 9
Issues 9
Database connection without DRW 1
Sticking video buttons 5
DRW Question 1
FP2000 and SQL2000 as backend database 1
Buttons don't work!!!! 13
Video buttons in DRW 4

Top