Very simple update query running super slow!

I have two tables: Rugman and PDrive
The table look like this:
Rugman -- ID (Auto/key) / Number (text!) / location (text) / test (text)
PDrive -- ID (Auto/key) / Field1 (text!)

The reason I had to use text for Number and Field1, is because there are a 
few lines what have a number like: 1234-2

I just want to check the Number in table Rugman and see if it does exist in 
the table PDrive... If it does: field test must be changed to "done" for that 
record where the numer exists in the other table...
Don't get why it's running so slow.
I have created also other update queries for the same tables and those 
complete good within seconds!
Look for example at this one, that completes perfect within 15 seconds:
---
UPDATE PDrive, RugMan SET RugMan.test = "GOOD"
WHERE ((([RugMan]![Number])=[PDrive]![Field1]) AND 
(([RugMan]![ImageFile])=[PDrive]![Field1]));
---

It's not rocket science what I'm trying to do, is it??
Thanks all!
0
Utf
4/10/2007 3:18:02 PM
access.queries 6343 articles. 1 followers. Follow

4 Replies
617 Views

Similar Articles

[PageSpeed] 48

1. Instead of a WHERE clause, JOIN the 2 tables.

2. In table design view, make sure both fields are indexed.

Number is a reserved word in JET, so not a good name for a field. I doubt 
that's the cause of the performance problem but you may like to avoid these 
field names:
    http://allenbrowne.com/AppIssueBadWord.html

There is also a problem with fields containing a dash, so you may want to 
avoid that if possible:
    http://support.microsoft.com/kb/271661/en-us

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Flipandboef" <Flipandboef@discussions.microsoft.com> wrote in message
news:BD442254-6BD9-4C49-82F5-BE47A25B5471@microsoft.com...
>I have two tables: Rugman and PDrive
> The table look like this:
> Rugman -- ID (Auto/key) / Number (text!) / location (text) / test (text)
> PDrive -- ID (Auto/key) / Field1 (text!)
>
> The reason I had to use text for Number and Field1, is because there are a
> few lines what have a number like: 1234-2
>
> I just want to check the Number in table Rugman and see if it does exist 
> in
> the table PDrive... If it does: field test must be changed to "done" for 
> that
> record where the numer exists in the other table...
> Don't get why it's running so slow.
> I have created also other update queries for the same tables and those
> complete good within seconds!
> Look for example at this one, that completes perfect within 15 seconds:
> ---
> UPDATE PDrive, RugMan SET RugMan.test = "GOOD"
> WHERE ((([RugMan]![Number])=[PDrive]![Field1]) AND
> (([RugMan]![ImageFile])=[PDrive]![Field1]));
> ---
>
> It's not rocket science what I'm trying to do, is it??
> Thanks all! 

0
Allen
4/10/2007 3:32:14 PM
Hi Allen,
I was indeed aware of the Number name... Even tried changing it...
Both fields are indexed (no duplicates) and the dash in the field, i'm 
afraid I cannot avoid that in this case...


"Allen Browne" wrote:

> 1. Instead of a WHERE clause, JOIN the 2 tables.
> 
> 2. In table design view, make sure both fields are indexed.
> 
> Number is a reserved word in JET, so not a good name for a field. I doubt 
> that's the cause of the performance problem but you may like to avoid these 
> field names:
>     http://allenbrowne.com/AppIssueBadWord.html
> 
> There is also a problem with fields containing a dash, so you may want to 
> avoid that if possible:
>     http://support.microsoft.com/kb/271661/en-us
> 
> -- 
> Allen Browne - Microsoft MVP.  Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
> 
> "Flipandboef" <Flipandboef@discussions.microsoft.com> wrote in message
> news:BD442254-6BD9-4C49-82F5-BE47A25B5471@microsoft.com...
> >I have two tables: Rugman and PDrive
> > The table look like this:
> > Rugman -- ID (Auto/key) / Number (text!) / location (text) / test (text)
> > PDrive -- ID (Auto/key) / Field1 (text!)
> >
> > The reason I had to use text for Number and Field1, is because there are a
> > few lines what have a number like: 1234-2
> >
> > I just want to check the Number in table Rugman and see if it does exist 
> > in
> > the table PDrive... If it does: field test must be changed to "done" for 
> > that
> > record where the numer exists in the other table...
> > Don't get why it's running so slow.
> > I have created also other update queries for the same tables and those
> > complete good within seconds!
> > Look for example at this one, that completes perfect within 15 seconds:
> > ---
> > UPDATE PDrive, RugMan SET RugMan.test = "GOOD"
> > WHERE ((([RugMan]![Number])=[PDrive]![Field1]) AND
> > (([RugMan]![ImageFile])=[PDrive]![Field1]));
> > ---
> >
> > It's not rocket science what I'm trying to do, is it??
> > Thanks all! 
> 
> 
0
Utf
4/10/2007 3:42:01 PM
Issue is resolved...



"Flipandboef" wrote:

> Hi Allen,
> I was indeed aware of the Number name... Even tried changing it...
> Both fields are indexed (no duplicates) and the dash in the field, i'm 
> afraid I cannot avoid that in this case...
> 
> 
> "Allen Browne" wrote:
> 
> > 1. Instead of a WHERE clause, JOIN the 2 tables.
> > 
> > 2. In table design view, make sure both fields are indexed.
> > 
> > Number is a reserved word in JET, so not a good name for a field. I doubt 
> > that's the cause of the performance problem but you may like to avoid these 
> > field names:
> >     http://allenbrowne.com/AppIssueBadWord.html
> > 
> > There is also a problem with fields containing a dash, so you may want to 
> > avoid that if possible:
> >     http://support.microsoft.com/kb/271661/en-us
> > 
> > -- 
> > Allen Browne - Microsoft MVP.  Perth, Western Australia
> > Tips for Access users - http://allenbrowne.com/tips.html
> > Reply to group, rather than allenbrowne at mvps dot org.
> > 
> > "Flipandboef" <Flipandboef@discussions.microsoft.com> wrote in message
> > news:BD442254-6BD9-4C49-82F5-BE47A25B5471@microsoft.com...
> > >I have two tables: Rugman and PDrive
> > > The table look like this:
> > > Rugman -- ID (Auto/key) / Number (text!) / location (text) / test (text)
> > > PDrive -- ID (Auto/key) / Field1 (text!)
> > >
> > > The reason I had to use text for Number and Field1, is because there are a
> > > few lines what have a number like: 1234-2
> > >
> > > I just want to check the Number in table Rugman and see if it does exist 
> > > in
> > > the table PDrive... If it does: field test must be changed to "done" for 
> > > that
> > > record where the numer exists in the other table...
> > > Don't get why it's running so slow.
> > > I have created also other update queries for the same tables and those
> > > complete good within seconds!
> > > Look for example at this one, that completes perfect within 15 seconds:
> > > ---
> > > UPDATE PDrive, RugMan SET RugMan.test = "GOOD"
> > > WHERE ((([RugMan]![Number])=[PDrive]![Field1]) AND
> > > (([RugMan]![ImageFile])=[PDrive]![Field1]));
> > > ---
> > >
> > > It's not rocket science what I'm trying to do, is it??
> > > Thanks all! 
> > 
> > 
0
Utf
4/10/2007 3:48:03 PM
On Tue, 10 Apr 2007 08:18:02 -0700, Flipandboef
<Flipandboef@discussions.microsoft.com> wrote:

>I have two tables: Rugman and PDrive
>The table look like this:
>Rugman -- ID (Auto/key) / Number (text!) / location (text) / test (text)
>PDrive -- ID (Auto/key) / Field1 (text!)
>
>The reason I had to use text for Number and Field1, is because there are a 
>few lines what have a number like: 1234-2
>
>I just want to check the Number in table Rugman and see if it does exist in 
>the table PDrive... If it does: field test must be changed to "done" for that 
>record where the numer exists in the other table...
>Don't get why it's running so slow.
>I have created also other update queries for the same tables and those 
>complete good within seconds!
>Look for example at this one, that completes perfect within 15 seconds:
>---
>UPDATE PDrive, RugMan SET RugMan.test = "GOOD"
>WHERE ((([RugMan]![Number])=[PDrive]![Field1]) AND 
>(([RugMan]![ImageFile])=[PDrive]![Field1]));
>--

Replace the ! with . for one thing - ! is for Object references (such as a
form control); . is the appropriate delimiter for table.field.

More importantly - make sure that there is an Index on [Number] and on
[Field1]. If [Number] is unique in the table [RugMan] make it a unique index;
you may not be able to do the update if it's not. Then use a JOIN rather than
a WHERE clause:

UPDATE PDrive INNER JOIN Rugman
ON Pdrive.Field1 = Rugman.[Number]
SET Rugman.Text = "GOOD";

I have NO idea what ImageFile has to do with anything. Your WHERE clause
implies that it's equal to the value of Field1, which would mean that it's
redundant - do ImageFile and Number contain the same information!?

             John W. Vinson [MVP]
0
John
4/10/2007 4:57:45 PM
Reply:

Similar Artilces:

How do I increase query field length (>20 characters) ?
I want to exclude 3 or 4 variables from a particular query. Using Not "xx"or"yy" is fine, but when excluding more than this, query fails. I think the problem is that the total text characters is quite large (approx 60 characters in total, from four exclusions). Ideas, please ? 60 characters is not the limit. 1024 characters is the limit. POST the SQL of the query that is not working (View: SQL on the menu) or at a minimum post what you are attempting as the criteria. Also "query fails" does not tell us if you get an error (what is it) or the wrong resul...

Outlook Express Visual C++ Runtime Library Run-Time Error
I have a friend who's using Outlook Express 6 and is getting an "abnormal program termination" error whenever he attempts to attach a graphic file within Outlook Express. He reports that it works fine when using the Explorer shell to call OE. My friend has WinXP Home. Any advice? Jay ...

Was 2005 just updated?
When I downloaded this morning the display that says to click on to hide the display was not there and my downloads occurred much faster than previously. It is interesting that this happened on the day after I started trying Quicken. (More on that later). ...

Can Update Office 11.0
I am trying to give Office a whirl on my powerbook however I need to upgrade to 11.1 for it to work with my ofices Lotus Notes e-Mail. When I try upgrading I get the 11002:2,-14 error I've heard talked about in this newsgroup. I've tried all the suggestions but still no luck. I tried repairing permisions, re-installing, turning of Entourage notifications. Nothing works! Please help. Haven't a clue. Previous threads didn't offer anything? Try re-downloading the update. Maybe previous updates haven't been run yet. Use Help | Check for Updates and let it tell you what...

run macros automatically
i have got around 10 macros in a excel spreadsheet. i want all of them to run when i open the file. or there should be a button which will runs all the macros whenever the user wants. You can do it either way really, First create a new macro, that runs all the ten macros that are in your workbook. If you want this macro to run whenever the sheet is opened, save it with the name 'Auto_Exec' and it will run automatically when you open the book. If you want to execute the macro on a button press, you can call it any name you wish. Next you will need to create a new button, so go ...

Query wildcard symbols
Just a heads-up in case this bites you, too. I migrated an application from Access 2002 to Access 2007. I have a search form for the users to enter some criteria ... the form dynamically builds a SQL WHERE clause before opening a "results" form based on an underlying query that includes all the fields available to search on. Once in Access 2007 format, the search form opens empty every time?! When I add selection criteria to the underlying query directly, the result set is empty every time?! Oh wait, I'm using the "*" (anything/everything) wildcard character. W...

Slow mail delivery from new Exch 2003 box
I have Exch 2003 installed on my network on a Win2000 server. I've just installed Exch 2003 on a 2003 member server joined to that domain. Now I have two Exch 2003 servers in my Org. I created a test mailbox account on the new Exch 2003 server. Whenever I send email from the test account, it is very slow to arrive (28mins). If I move that mailbox to the old Exch 2003 on the Win2000 server, it arrives to the recipient instantly. If I run the DCDiag & Netdiag tools; Is there anything in particular in need to look for? Please Help anyone! Post some more info on your AD infrastuct...

Text to Columns from drop down list update
Hi I need to perform a Text to Column conversion from a drop down list, but I also need the extracted value to be updated if the value in the list is changed. eg: Drop down list has 2 values: 1. 4x16K 2. 8x8K If the user selects 1 I can easily extract out to another cell the value 1 using Text to columns, however if the user then changes the choice to 2 the text to columns extraction is not updated to 2. Is there a way to update changes in the original cell using text to columns? Or is there another way I can assign a value to a drop down menu choice in a different cell while havi...

Keeping the format but update the data
I have a report I run everyday. I want to update the data, but keep the column sizes and formatting. Any suggestions? Anybody suggest a good ebook to become more formuliar with excel? JoeM JoeM, this could be as simple as recording a macro during the formatting of the sheet after importing the data once. Then each day, after importing the new data, you simply run that macro. As for online eBook's for Excel, I'm not actually aware of any although I suspect a Google search would turn up several. It's hard to beat the "For Dummies..." series of hardcopy...

simple? pasting simple formula, error on copy
I have some store buying spreadsheets I made up. Very very basic, one of the columns has a total at the bottom or end as you may call it for total $ amount bought. that final number is in c33. c1, c2, c3 etc is a amount from each vendor. I want to have a column telling me the percentage being spent on each, simple enough so I make a column with the following formula =c33/c5. I get a number, change it to percentage and BAM I have exactly what I want. I pasted that formula and pasted it into all the other rows of that certain column, but I keep getting the #div/0 error, cause the new formulas...

Can't connect after updates.
I downloaded the two software updates and now I can't connect with Live ID. Anyone one else have this problem? I am actually experiencing the same issue. I just installed, it said that there were updates available which I installed and now I can not connect from within money... I am using vista, not sure if that is part of the problem or not... "Jakryk" wrote: > I downloaded the two software updates and now I can't connect with Live ID. > Anyone one else have this problem? I'm using Vista also. I've reinstalled Money and I cancel the updates. It happen...

Run two copies of Outlook with different profiles
Hello! I'm trying to run two copies of Outlook with different (outlook)profiles at the same time. Normally i can choose the profile with the /profile switch, but if there is already a copy of outlook running, this switch seems to be ignored. Any ideas how to handle this are welcome. Regards, Reinhard "Reinhard" <reinhard.spieker@bkvibro.de> wrote in message news:60a7e218.0311190408.6523100b@posting.google.com... > Hello! > > I'm trying to run two copies of Outlook with different > (outlook)profiles at the same time. > > Normally i can choose the p...

Importing a table from Access query
What is the quickest and easiest way to do the above? Thanks in advance. Hi The way I usually do this is run the select query, click on the top-right box (which selects all records) and use Ctrl+C to copy and Ctrl+V to paste into my workbook. An alternative is to right-click on the query in the Database Window and left-click on Export. In the Save As dialog box, select ..xls type. -- Andy. "Trish" <Trish@discussions.microsoft.com> wrote in message news:6BF3DE22-6590-4CAD-9EE1-FC978A3BB63B@microsoft.com... > What is the quickest and easiest way to do the above? >...

does publisher 2000 run on windows xp?
thanks for helping if you know the answer Yes, all versions of Publisher run on XP, Publisher 1.0 maybe the exception. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Baudouin" <Baudouin@discussions.microsoft.com> wrote in message news:6AA35200-15D7-47F2-8471-4E6737171B6A@microsoft.com... > thanks for helping if you know the answer Mary Sauer <gsauer@mycolumbus.rr.com> was very recently heard to utter: > Yes, all versions of Publisher run on XP, Publisher 1.0 maybe the > exception. It runs. Not on...

Frustrated Beyond Measure
I loved Money 2005, recommended it to friends and got 2 great years of use out of it. Then, it forced me to upgrade to continue online services. I didn't mind since it was only $40 and I got two good years out of it. However, Money Plus Deluxe has been a nuisance. I have never gotten portfolio manager to work. I spent 2 weeks sending emails with support where the guy eventually said "if this doesn't work, I can't help you." Great... shoudl I go buy Quicken? This weekend, I decided to try to completely uninstall Money and try a reinstall to get Portfolio Manag...

Trying to get a type of video file format to open and run on my we
I created a video using Windows Movie maker and saved as .wmp file. I saved it to my computer and then inserted on my web page under Front Page 2003. Nothing! I then converted the file to avi format - still nothing! I then tried mpeg4 still nothing. The manual is next to useless on this subject. Anyone tell me what I need to do so that when a user opens up the page the video plays automatically. -- Simon Holloway Save as a .wmv and insert a suitable player into the page. For IE only, Insert->Web Component->Advanced Controls->ActiveX Control Click Next Choose Window...

Try that critical update from the Microsoft
--cvopnkiwnuksx Content-Type: multipart/related; boundary="euamswwo"; type="multipart/alternative" --euamswwo Content-Type: multipart/alternative; boundary="qcidyxbg" --qcidyxbg Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Microsoft Customer this is the latest version of security update, the "October 2003, Cumulative Patch" update which fixes all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three new vulnerabilities. Install now to help protect your computer from ...

Reading empty query returns
When I open a form that has a bound query as the record source, no data appears (including the control objects of the form) if the query's select statement does not finds records that match the criteria I specify. This causes a problem in my VBA code as the variable I use to check the number of records returned by the query does not seem to be able to read empty query returns, even if I use isnull. So I guess I am asking how can I get Access to read an empty result set in VBA from a SQL select statement that is run??? Here is part of my code for this: DoCmd.OpenForm stDocName2, , ...

Query Flaw???
I need only one value from an aggregate Query - TheStores, which means the Number of Inspections in a "From - To" Timeframe. I use that as part of a calculation. The "Inspections" table contains only a Store and an InspectionDate. It gives me the result I need...but no Join...the Query has several other Fields from "Randy_F_FindFreq", but has been trimmed as an example here. SELECT Randy_F_FindFreq.AAll, Randy_Insp.TheStores, Round([AAll]/[TheStores],4) AS PerAll FROM Randy_F_FindFreq, Randy_Insp; Is this "sound"? TIA - Bob Without a join yo...

script error running reports from home pages, why??
I'm not a GP user/expert, we recently installed GP2010. Since then one user has said, "When I try to run reports from the home pages, I am getting this script error and cannot run any reports." The error is: Line 47 Char 5 Error The system cannot find the file specified Code 0 URL: file:///C:/Users/username/AppData/Local/Temp/tmp/AE8.tmp I have received differing responses to this issue: 1) enable the Run command on the Start menu of our XenApp server for the regular domain users, which I view as a big security risk 2) "the UserData folder does not exist in the %ap...

Internet Explorer 8 is too slow
Windows XP pro sp 3 Ram memory 1gb I decided to download Internet Explorer 8 since there will be an end to Internet Explorer 6. I liked Internet Explorer 6 because it was fast. This is the way I downloaded Internet Explorer 8 Internet Explorer 8 download: http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=341c2ad5-8c3d-4347-8c03-08cdecd8852b The download went very well and all the updates have been completed. My question is, "Why is Internet Explorer 8 so much slower than 6"? I can almost go get a cup of coffee in the time it takes to open. ...

Updating MS Office 98 for Mac to newer MS Office version & other questions
Hi! I have a Mac iBook with a Power PC G3 Processor, Disk Cache: 4096k, Virtual Memory 129 MB, Built-In Memory 128 MB, Machine Speed 600MHz (so it says in the system profiler...I don't really know what all this means and if someone has an easy explanation, I'd appreciate it). Though it came with OSX, I have been running it on OS 9.2 because I purchased Microsoft Office 98 for Mac (Word, Excel, Powerpoint) back in 1999 for my last Mac (a desktop), so I loaded it onto this iBook when I got it. My question is, can I qualify for an update of MS Office? From browsing the web I noticed ther...

I guess only super computer can solve this
Ekim from MrExcel.com has directed me to a below given code form Myrna Larson (Microsoft MVP). I want some modifications done to that code. What I did is: -------------- 1. Typed "C" in cell A1 of Sheet1 2. Typed "4" in cell A2 of sheet1 3. Typed 1, 4, 5, 19, 21, 24, 30, 31, 37, 40, 44, 46, 48, 50, 53, 57, 58, 62, 72 & 76 vertically in the range A3:A22 4. Ran the ListPermutationsOrCombinations() Macro 5. Got 4845 combinations in sheet4 i.e., all combinations of 4 each of the above provided numbers in step # 3 Similarly I want to make all combinations of 80...

Can Excel 97 read Excel 2003 simple spreadsheet?
I am writing a ReadMe file and want to list minimum requirements for viewing an Excel 2003 spreadsheet. The only formulas are Count functions used to tally columns. Other than that it is a basic flat list spreadsheet. For viewing this .xls file may I be safe to say that a minimum of Excel 97 is needed? Thanks. Using your criteria, I would say Yes. Gord Dibben Excel MVP On Mon, 21 Nov 2005 10:00:12 -0800, MSfStl <MSfStl@discussions.microsoft.com> wrote: >I am writing a ReadMe file and want to list minimum requirements for viewing >an Excel 2003 spreadsheet. The only formu...

stange folder after install updates
hello, I've some computers with XP SP3 which after install some updates appear "strange folders". For example there're some folders in c: called: 6ac86d2ad9be93fc0f0ded or 22553c8e848b459d87bc49 I think that these folder are created when the installation on the update fails but my problem is in order to delete them I have to put myself as propietary of the folder manually, Is there any way to delete them automatically? sgr wrote: :: hello, :: :: I've some computers with XP SP3 which after install some updates :: appear "strange folders". F...