"Not responding" Help simplifying a query?! (1 Viewer)

[killyridols]

SELECT distinct area.shipcode, area.cruisenum, area.corenum, lat, long, waterdepth, (data/summe)*100 AS proz
FROM nandat, indproz_pt1, area, sample
WHERE sample.sample_id=indproz_pt1.sample_id
AND sample.sample_id=nandat.sample_id
AND area.area_id=sample.area_id
AND nandat.art=98
OR nandat.art=45;

This is the query I am trying to run. Everytime I do, Access freezes ("not responding"). I'm assuming it is because of all the WHERE statements, and also because the tables NANDAT, and SAMPLE are both pretty big.
I'm trying to figure out a way, to break down this query into smaller bits so that it isn't as hard to run. I tried taking out the "(data/summe)*100 AS proz" column, and that didn't help at all...
Does anyone have any tips??
THank you!!

 

[Access Hero]

Do you have indexes on the tables?

Which table is bigger? You'll need to evaluate whether it would be best to put an index on nandat.art or sample_id/area_id/sample_id, or some combination of all 3. At any rate, all columns in your where clause are candidates for an index. You might consider putting parenthesis around your where clause sections to remove ambiguity about what conditions occur. Like this:

AND 
(nandat.art=98
OR nandat.art=45)
;

For instance, it LOOKS like if you have a row in nandat that has an "art" value of 45, you will get a cartesian join which will join all the rows of the 3 other tables to that row. Depending on how big the tables are, that could take a long time. You can figure out just how bad it will get by multiplying the number of rows in nandat with art = 45 time the total number of rows in ndproz_pt1times the total number of rows in area times the total number of rows in sample = total number of rows returned from the query.

Another way to resolve this is to put your join criteria in the from clause and reserve the where clause for conditions not related to the join.

Realize that just the act of putting an index on a table could take a bit of time and "seem" to freeze Access if the table is very large.

 

[SOS]

Joins are definitely the first step. They should be added in immediately. That alone should help immensely.

 

[killyridols]

hmmm... thanks guys!
To be honest I don't even know what joins are , or how to implement an index, but I will do some research! I might be asking for some assistance later!
thank you!

 

[killyridols]

WOW!
Actually, just adding those brackets:
AND
(nandat.art=98
OR nandat.art=45)
;worked. it ran fine!
thank you!

 

[Access Hero]

NP. I noticed that while I was reformatting your code.

You probably were getting millions of rows from your query.