Hi:

My apologies if you've read this before; I tried to post the question
yesterday, but although Google told me it was posted, it has never
appeared. (And if you have seen it, please let me know where!)

My client has opened a new site, and needs to share some data with
their original site. Due to their location, they can't get a very
fast connection, and some parts of the database are exceedingly slow.
I have moved as many tables as possible to the local server, but some
just have to be accessed remotely, because the data is shared.

The biggest problem at the moment is in one particular function. When
they need to pick the next chartnum, it takes a bit more than 70
seconds. Way too slow to be functional in a busy office.

Here's the relevant code:

Set rs = db.OpenRecordset("select top 1 chartval from
[patients for chartnum] order by chartval desc")
nextchartnum = rs!chartval + 1

[patients for chartnum] is a query on the patients table;
chartnum is a text field because they use text to differentiate some
of their chart numbers.
Chartval is set in the query as val(chartnum) with the idea that the
search would be faster on a number field
chartnum is indexed (no duplicates)

I have tried sorting in the query, restricting the values in the
query, not sorting, using [chartnum] directly instead of [chartval],
doing the "top 1" as above, not doing that, nothing works. I've
thought of writing to a temporary table, but I don't see how that
would work because someone else could be taking a number while this
one was in the temp table.

Anyone have any ideas?

Thanks.

Jan

On Mon, 11 Oct 2010 06:41:40 -0700 (PDT), "jan (AT) dontspamme (DOT) com"
<jan (AT) stempelconsulting (DOT) com> wrote:

You wrote: "Chartval is set in the query as val(chartnum) with the
idea that the search would be faster on a number field"
No, that's incorrect. Try it for yourself.

However, if chartnum is currently a text field containing numbers, and
it can only have numbers, you should change the db design and make it
a numeric field.

Not that this would explain the 70 seconds; that seems way too long.

Have you tried this equivalent query:
select max(chartval) from [patients for chartnum]

-Tom.
Microsoft Access MVP

Hi, Tom:

Thanks for the reply.

Chartnum is a text field containing some records w/all numbers, and
others w/a number preceded by a letter. So unfortunately I can't
redefine it (would have done that years ago if I could, but they like
this system).

I'm away from my desk today and can't try the other query right now,
but I'm pretty sure I tried it that way earlier; I certainly did as
many different permutations of the query as I could think of. I will
definitely try it tonight and let you know if it has any results.

As for the 70 seconds, I don't know. They said they were going to get
a faster line, but then said that what they have is the fastest
available; the providers w/faster service don't serve in this
neighborhood. So I just have to figure a way to program around it.

Thanks.

Jan



On Oct 11, 10:16*pm, Tom van Stiphout <tom7744.no.s... (AT) cox (DOT) net> wrote: