Hi,

I have D5 and DBISAM 3.27.

I have very little SQL experience. I am trying to write a query that will update a certain field (fUr) in Table1 (tblScripts)  based on data in
another table (tblPatients).

The common field is fPatNo.

tblScripts contains 180,000+ records

Here is what I have so far:

update tblScripts
set tblScripts.fUR=tblPatients.fUR
from tblScripts, tblPatients
where tblScripts.fPatNo=tblPatients.fPatNo

When I try to run the query it Delphi appears to freeze. In reality I think it is working because if I terminate delphi and re-open the project
some of the data from the query is there. It just seems to take so long and the hour glass keeps spinning.

Is there a way to speed this up?

Thanks,

John

"John Evans" <john@legacyoflove.net> wrote in message
news:DB57C6B0-8DB2-471F-B8AA-BBF53B0BD05C@news.elevatesoft.com...
>
> When I try to run the query it Delphi appears to freeze.

What happens if you run the query in Dbsys ?
I take it you mean by Delphi a compiled Delphi
program rather than the IDE ?
Rita

"Rita" wrote:


"John Evans" <john@legacyoflove.net> wrote in message
news:DB57C6B0-8DB2-471F-B8AA-BBF53B0BD05C@news.elevatesoft.com...
>
> When I try to run the query it Delphi appears to freeze.

What happens if you run the query in Dbsys ?
I take it you mean by Delphi a compiled Delphi
program rather than the IDE ?
Rita

Hi Rita,

In Dbsys it takes a little over 10 mins to run the query.

Is there a way to speed this up (e.g. can I tell it to only run the query of records with today's date?. If so what is the correct syntax for this?)

(The original questions relates to both IDE and compiled program)

Thanks

John

John


First rule to get speed is to make sure the appropriate indices are in place. In this case it means the two fields in the WHERE clause should have indices. If they are string fields (probably not with that name) make sure the two indices are the same ie both case sensitive or both case insensitive.

The other thing is that if you can try it and leave it running (overnight if necessary) in DBSys check the generate plan box and the plan produced will give you information you can use or post here to help us help you.

Final suggestion is, if supported by your version, to look at using JOIN rather than WHERE.

Almost every time though its lack of the appropriate index

[Team Elevate]

John

>Is there a way to speed this up (e.g. can I tell it to only run the query of records with today's date?. If so what is the correct syntax for this?)

That's easy

You add to your WHERE clause

AND datefield = CURRENT_DATE

Roy Lambert [Team Elevate]

"John Evans" <john@legacyoflove.net> wrote in message
news:C0E61957-CC0C-4BC0-B6C0-E4CB37D1FFC3@news.elevatesoft.com...

Roy beat me to both,  180,000 records fly in DBIsam SQL if the fields
are all indexed as Roy says its case sensitive so take a peep 1st.
Both tblScripts.fPatNo AND tblPatients.fPatNo should contain an index
on fPatNo I'am guessing thats Patient Number ? could that be something
like JE01234 if so make sure the JE part is the same case in the individual
tables if not destroy the old index convert the field's in both to upper
reindex
and thats it.
HTH
Rita

"Rita" wrote:


Thanks Rita and Roy for your prompt responses. I thought I had correct indexes for the Where fields but it still ran very slowly. Then I deleted and
recreated the indexes and now we are looking at 0.1sec.

John