PHP + MS Access + How to increase the speed scripts are executed at.

[Tony Clarke]

Hi,

Background:

I have written a Call Logging and Reporting System using PHP that integrates
with an existing MS Access Database. The information in the database is all
held in flat tables with no relationships (I don't know why they did this)
and the users are currently using VBA forms to enter info into the database,
this meant that each time someone wanted to do a report of the call's
recorded over the last week that they would have to go throught the database
themselves and compile a report (e.g. re-type the info into excel or
something). I'm using PHP/GD to generate bar/pie charts and reports in HTML,
PDF and ms excel formats (well a CSV file).

Question:
Each call needs to be allocated a unique number, the way I'm doing this at
the moment is using SQL to look through the database and find the last
record and then incrementing it. The problem is that at the moment there are
over 40,000 records in the database and its taking roughtly about 11 seconds
for that call logging PHP page to load. Is there anyway of improving this
(other than increasing memory/processing power on the server side).

Thanks,

Tony.

[Michael Vilain]

Re: PHP + MS Access + How to increase the speed scripts are executed at.

In article <%X4od.42332$Z1 4.17591@news.in digo.ie>,
"Tony Clarke" <clarket@eircom .net> wrote:
[color=blue]
> Hi,
>
> Background:
>
> I have written a Call Logging and Reporting System using PHP that integrates
> with an existing MS Access Database. The information in the database is all
> held in flat tables with no relationships (I don't know why they did this)
> and the users are currently using VBA forms to enter info into the database,
> this meant that each time someone wanted to do a report of the call's
> recorded over the last week that they would have to go throught the database
> themselves and compile a report (e.g. re-type the info into excel or
> something). I'm using PHP/GD to generate bar/pie charts and reports in HTML,
> PDF and ms excel formats (well a CSV file).
>
> Question:
> Each call needs to be allocated a unique number, the way I'm doing this at
> the moment is using SQL to look through the database and find the last
> record and then incrementing it. The problem is that at the moment there are
> over 40,000 records in the database and its taking roughtly about 11 seconds
> for that call logging PHP page to load. Is there anyway of improving this
> (other than increasing memory/processing power on the server side).[/color]

Usually database problems like this are because the query is being
performed on a field that requires a full table scan (i.e. a linear
scan, row by row, of the entire database). If you were running MySQL,
I'd advise adding a indexed key to speed up things.

You might buy a MS Access book that focuses on performance issues to
give you advise on how to optimize your queries and table layout.

Or a bigger box.

--
DeeDee, don't press that button! DeeDee! NO! Dee...

[CJ Llewellyn]

Re: PHP + MS Access + How to increase the speed scripts are executed at.

"Tony Clarke" <clarket@eircom .net> wrote in message
news:%X4od.4233 2$Z14.17591@new s.indigo.ie...[color=blue]
> Hi,
>
> Background:
>
> I have written a Call Logging and Reporting System using PHP that[/color]
integrates[color=blue]
> with an existing MS Access Database. The information in the database is[/color]
all[color=blue]
> held in flat tables with no relationships (I don't know why they did this)
> and the users are currently using VBA forms to enter info into the[/color]
database,[color=blue]
> this meant that each time someone wanted to do a report of the call's
> recorded over the last week that they would have to go throught the[/color]
database[color=blue]
> themselves and compile a report (e.g. re-type the info into excel or
> something).[/color]

The mind boggles, exporting Access data to Excel is fairly trivial.
[color=blue]
> I'm using PHP/GD to generate bar/pie charts and reports in HTML,
> PDF and ms excel formats (well a CSV file).[/color]

Why not use COM?
[color=blue]
> Question:
> Each call needs to be allocated a unique number, the way I'm doing this at
> the moment is using SQL to look through the database and find the last
> record and then incrementing it.[/color]

Why not use an autonumber field, it does what it says on the tin.

Is the unique field designated primary key?

Is your SQL query written correctly?

SELECT TOP 1 Call.id, Call.name
FROM Call
ORDER BY Call.id DESC;
[color=blue]
> The problem is that at the moment there are
> over 40,000 records in the database and its taking roughtly about 11[/color]
seconds[color=blue]
> for that call logging PHP page to load. Is there anyway of improving this
> (other than increasing memory/processing power on the server side).[/color]

Use a proper SQL database.

Store the sequence number in a table with one row.

[Tony Clarke]

Re: PHP + MS Access + How to increase the speed scripts are executed at.

The reason why I stuck with the Access database was beacuse it had al the
info in it already. I was just really trying to patch the existing database
with PHP to generate reports (I'm not sure why they don't use the export
facility in Access...possib ly they don't know it exists!).
One of the reasons that I tried to redesign it was because everyone
access's the database directly and uses the same VBA forms and as such the
database constanly locks. Which means whoever is locking the database must
save their record.

If I was designing it I would have used MySQL. Is there an easy way to
convert from an Access to MySQL database and would doing so yield a
significant performance increase?



"CJ Llewellyn" <invalid@exampl e.con> wrote in message
news:cnqsra$k4s $1@slavica.ukpo st.com...[color=blue]
> "Tony Clarke" <clarket@eircom .net> wrote in message
> news:%X4od.4233 2$Z14.17591@new s.indigo.ie...[color=green]
> > Hi,
> >
> > Background:
> >
> > I have written a Call Logging and Reporting System using PHP that[/color]
> integrates[color=green]
> > with an existing MS Access Database. The information in the database is[/color]
> all[color=green]
> > held in flat tables with no relationships (I don't know why they did[/color][/color]
this)[color=blue][color=green]
> > and the users are currently using VBA forms to enter info into the[/color]
> database,[color=green]
> > this meant that each time someone wanted to do a report of the call's
> > recorded over the last week that they would have to go throught the[/color]
> database[color=green]
> > themselves and compile a report (e.g. re-type the info into excel or
> > something).[/color]
>
> The mind boggles, exporting Access data to Excel is fairly trivial.
>[color=green]
> > I'm using PHP/GD to generate bar/pie charts and reports in HTML,
> > PDF and ms excel formats (well a CSV file).[/color]
>
> Why not use COM?
>[color=green]
> > Question:
> > Each call needs to be allocated a unique number, the way I'm doing this[/color][/color]
at[color=blue][color=green]
> > the moment is using SQL to look through the database and find the last
> > record and then incrementing it.[/color]
>
> Why not use an autonumber field, it does what it says on the tin.
>
> Is the unique field designated primary key?
>
> Is your SQL query written correctly?
>
> SELECT TOP 1 Call.id, Call.name
> FROM Call
> ORDER BY Call.id DESC;
>[color=green]
> > The problem is that at the moment there are
> > over 40,000 records in the database and its taking roughtly about 11[/color]
> seconds[color=green]
> > for that call logging PHP page to load. Is there anyway of improving[/color][/color]
this[color=blue][color=green]
> > (other than increasing memory/processing power on the server side).[/color]
>
> Use a proper SQL database.
>
> Store the sequence number in a table with one row.
>
>[/color]

[CJ Llewellyn]

Re: PHP + MS Access + How to increase the speed scripts are executed at.

"Tony Clarke" <clarket@eircom .net> wrote in message
news:aE7od.4235 2$Z14.17535@new s.indigo.ie...[color=blue]
> The reason why I stuck with the Access database was beacuse it had al the
> info in it already. I was just really trying to patch the existing[/color]
database[color=blue]
> with PHP to generate reports (I'm not sure why they don't use the export
> facility in Access...possib ly they don't know it exists!).
> One of the reasons that I tried to redesign it was because[/color]
everyone[color=blue]
> access's the database directly and uses the same VBA forms and as such the
> database constanly locks. Which means whoever is locking the database must
> save their record.
>
> If I was designing it I would have used MySQL. Is there an easy way to
> convert from an Access to MySQL database and would doing so yield a
> significant performance increase?[/color]

There is no easy way to convert from Access to MySQL. You can use the
Upsizing Tool to convert to M$ $QL $erver.

You'd have to create all the tables in a MySQL database and redevelop all
their forms to use the new tables.

If the application is of significant business value then they really do need
to sanction a redevelopment of the system using more appropriate
technologies. That is the only way to guarantee performance.

Otherwise read my performance hints in the previous post.

[Tony Clarke]

Re: PHP + MS Access + How to increase the speed scripts are executed at.

Thanks CJ.


"CJ Llewellyn" <invalid@exampl e.con> wrote in message
news:cnr1he$onq $1@slavica.ukpo st.com...[color=blue]
> "Tony Clarke" <clarket@eircom .net> wrote in message
> news:aE7od.4235 2$Z14.17535@new s.indigo.ie...[color=green]
> > The reason why I stuck with the Access database was beacuse it had al[/color][/color]
the[color=blue][color=green]
> > info in it already. I was just really trying to patch the existing[/color]
> database[color=green]
> > with PHP to generate reports (I'm not sure why they don't use the export
> > facility in Access...possib ly they don't know it exists!).
> > One of the reasons that I tried to redesign it was because[/color]
> everyone[color=green]
> > access's the database directly and uses the same VBA forms and as such[/color][/color]
the[color=blue][color=green]
> > database constanly locks. Which means whoever is locking the database[/color][/color]
must[color=blue][color=green]
> > save their record.
> >
> > If I was designing it I would have used MySQL. Is there an easy way to
> > convert from an Access to MySQL database and would doing so yield a
> > significant performance increase?[/color]
>
> There is no easy way to convert from Access to MySQL. You can use the
> Upsizing Tool to convert to M$ $QL $erver.
>
> You'd have to create all the tables in a MySQL database and redevelop all
> their forms to use the new tables.
>
> If the application is of significant business value then they really do[/color]
need[color=blue]
> to sanction a redevelopment of the system using more appropriate
> technologies. That is the only way to guarantee performance.
>
> Otherwise read my performance hints in the previous post.
>
>[/color]