Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 7 of 7 total |
Query 'freezes' Delphi |
Tue, Jul 28 2009 8:22 AM | Permanent Link |
John Evans | 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 |
Tue, Jul 28 2009 8:29 AM | Permanent Link |
"Rita" | "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 |
Tue, Jul 28 2009 8:45 AM | Permanent Link |
John Evans | "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 |
Tue, Jul 28 2009 8:52 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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] |
Tue, Jul 28 2009 10:02 AM | Permanent Link |
Roy Lambert NLH Associates 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] |
Tue, Jul 28 2009 5:20 PM | Permanent Link |
"Rita" | "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 |
Wed, Aug 5 2009 8:07 AM | Permanent Link |
John Evans | "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 |
This web page was last updated on Saturday, May 4, 2024 at 12:54 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |