If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Select Min Query
Hello,
I first off want to say thank you to all those who post on this site. I am not very experienced in Access and the advice and direction offered here is top notch! I have been handed a database that has well data in it. There is a table called Wells which has the following fields: Well_ID Well_Name Well_Location I also have another table called Well_Data. This table contains the well data (specific descriptions of items/materials encountered down the well) for each well. Each well can have numerous well data entries. The two tables are joined on Well_ID. Here are the fields for the Well_Data Table: Well_Data_ID Well_ID Item_Depth Item_Description I am writing a query to search on a specific Item_Description (let's say gold ). I need to be able to return the well name, well location, item depth, item description, as well as the next item depth below the current item depth. So for example, I would get a return of: Well_Name: 40000 Well_Location: Some Place Item Depth: 150 Item Description: Gold Next Item Depth 250 I tried to build this by creating one query. I have no problems using ... Like"*Gold*"... to get me the wells that have gold in them and the query is relatively quick (being that there are over 2 million Well_Data_ID records). The problem arises when I try to get the "Next Item Depth" value. The whole query hangs and Access shuts down. I don't know if I just haven't let it run long enough but I let it go for an hour and it still hung up. Here's how I tried to do that: NextItemDepthSelect Min(Item_Depth) from Well_Data Where Depth Well_Data_Alias.[Depth] and Well_ID = Well_Data_Alias.[Well_ID]) I know this works. I created a copy of the database and deleted over 2/3 of the records and this query completed in about a minute. I was just wondering if I can somehow re-structure this query to be more efficient. As for indexes, I ran the analyzer and indexed the fields it told me to. It didn't help at all. Any Help would be greatly appreciated. Thanks. Joel |
#2
|
|||
|
|||
Select Min Query
On Sat, 01 May 2010 21:06:24 GMT, "Joel_123" u59738@uwe wrote:
Hello, I first off want to say thank you to all those who post on this site. I am not very experienced in Access and the advice and direction offered here is top notch! On behalf of the many volunteers who answer questions here... thank you. I have been handed a database that has well data in it. There is a table called Wells which has the following fields: Well_ID Well_Name Well_Location I also have another table called Well_Data. This table contains the well data (specific descriptions of items/materials encountered down the well) for each well. Each well can have numerous well data entries. The two tables are joined on Well_ID. Here are the fields for the Well_Data Table: Well_Data_ID Well_ID Item_Depth Item_Description Good so far... I am writing a query to search on a specific Item_Description (let's say gold ). I need to be able to return the well name, well location, item depth, item description, as well as the next item depth below the current item depth. gnnn... that's the gotcha of course. So for example, I would get a return of: Well_Name: 40000 Well_Location: Some Place Item Depth: 150 Item Description: Gold Next Item Depth 250 I tried to build this by creating one query. I have no problems using ... Like"*Gold*"... to get me the wells that have gold in them and the query is relatively quick (being that there are over 2 million Well_Data_ID records). eeep....!!! And of course an index won't help on the wildcard. Good that *that* part of it is working anyway... The problem arises when I try to get the "Next Item Depth" value. The whole query hangs and Access shuts down. I don't know if I just haven't let it run long enough but I let it go for an hour and it still hung up. Here's how I tried to do that: NextItemDepthSelect Min(Item_Depth) from Well_Data Where Depth Well_Data_Alias.[Depth] and Well_ID = Well_Data_Alias.[Well_ID]) I know this works. I created a copy of the database and deleted over 2/3 of the records and this query completed in about a minute. I was just wondering if I can somehow re-structure this query to be more efficient. As for indexes, I ran the analyzer and indexed the fields it told me to. It didn't help at all. Yep... subqueries will be really really slow because it must run the query for every record. Try a JOIN instead: SELECT First(W.Well_Name) AS TheWellName, First(W.Well_Location) AS Location, A.[Item Depth], First(A.[Item Description]) AS Description, Max(B.[Item Depth]) AS Next_Item_Depth FROM (Wells AS W INNER JOIN Well_Data AS A ON W.Well_Name = A.Well_Name) INNER JOIN Well_Data AS B ON B.Well_ID = A.Well_ID AND B.Item_Depth A.Item_Depth WHERE A.[Item Description] LIKE "*" & [Enter target substance:] & "*" GROUP BY W.Well_ID, A.[Item Depth]; Well_ID should already be indexed, be sure that Item_Depth is indexed too (nonuniquely). Don't know if this will solve the problem (or even improve it) but it's worth a try. I'd suggest avoiding blanks in fieldnames - won't affect the speed but may make queries easier to edit. -- John W. Vinson [MVP] |
#3
|
|||
|
|||
Select Min Query
On Sat, 01 May 2010 18:13:08 -0600, John W. Vinson
wrote: John's advice is good. If this does not help enough, the *Gold* wildcard is probably the culprit. For a performance test try Gold* which can use the index. My guess is it will run much faster. If true but the restriction is unacceptable, you could change the design and rather than free text in Item_Description you would ask the user to select any number of keywords from a list. They would be stored in a new table 1:M related to the current one. That lookup should be very fast. -Tom. Microsoft Access MVP On Sat, 01 May 2010 21:06:24 GMT, "Joel_123" u59738@uwe wrote: Hello, I first off want to say thank you to all those who post on this site. I am not very experienced in Access and the advice and direction offered here is top notch! On behalf of the many volunteers who answer questions here... thank you. I have been handed a database that has well data in it. There is a table called Wells which has the following fields: Well_ID Well_Name Well_Location I also have another table called Well_Data. This table contains the well data (specific descriptions of items/materials encountered down the well) for each well. Each well can have numerous well data entries. The two tables are joined on Well_ID. Here are the fields for the Well_Data Table: Well_Data_ID Well_ID Item_Depth Item_Description Good so far... I am writing a query to search on a specific Item_Description (let's say gold ). I need to be able to return the well name, well location, item depth, item description, as well as the next item depth below the current item depth. gnnn... that's the gotcha of course. So for example, I would get a return of: Well_Name: 40000 Well_Location: Some Place Item Depth: 150 Item Description: Gold Next Item Depth 250 I tried to build this by creating one query. I have no problems using ... Like"*Gold*"... to get me the wells that have gold in them and the query is relatively quick (being that there are over 2 million Well_Data_ID records). eeep....!!! And of course an index won't help on the wildcard. Good that *that* part of it is working anyway... The problem arises when I try to get the "Next Item Depth" value. The whole query hangs and Access shuts down. I don't know if I just haven't let it run long enough but I let it go for an hour and it still hung up. Here's how I tried to do that: NextItemDepthSelect Min(Item_Depth) from Well_Data Where Depth Well_Data_Alias.[Depth] and Well_ID = Well_Data_Alias.[Well_ID]) I know this works. I created a copy of the database and deleted over 2/3 of the records and this query completed in about a minute. I was just wondering if I can somehow re-structure this query to be more efficient. As for indexes, I ran the analyzer and indexed the fields it told me to. It didn't help at all. Yep... subqueries will be really really slow because it must run the query for every record. Try a JOIN instead: SELECT First(W.Well_Name) AS TheWellName, First(W.Well_Location) AS Location, A.[Item Depth], First(A.[Item Description]) AS Description, Max(B.[Item Depth]) AS Next_Item_Depth FROM (Wells AS W INNER JOIN Well_Data AS A ON W.Well_Name = A.Well_Name) INNER JOIN Well_Data AS B ON B.Well_ID = A.Well_ID AND B.Item_Depth A.Item_Depth WHERE A.[Item Description] LIKE "*" & [Enter target substance:] & "*" GROUP BY W.Well_ID, A.[Item Depth]; Well_ID should already be indexed, be sure that Item_Depth is indexed too (nonuniquely). Don't know if this will solve the problem (or even improve it) but it's worth a try. I'd suggest avoiding blanks in fieldnames - won't affect the speed but may make queries easier to edit. |
#4
|
|||
|
|||
Select Min Query
Thank you both for the help. I made one change - I changed "max" to "min" to
search for the next highest value and it worked awesome. The whole query takes about 20 seconds. I do have one other questions though. When I ran my previous query searching simply for ... Like "*Gold*", I get a return of about 95,000 records. When I use this query, i get a return of about 85,000 records. I have figured out that this is a result of some wells having a "gold" entry as the last Well_Data entry and there are no further values to enter as the "next depth". How would I account for this. I imagine that I would need something to account for NULL but I don't think Nz() would work in SQL. Any ideas? Thanks Again. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201005/1 |
#5
|
|||
|
|||
Select Min Query
On Sun, 02 May 2010 18:45:13 GMT, "Joel_123 via AccessMonster.com"
u59738@uwe wrote: Thank you both for the help. I made one change - I changed "max" to "min" to search for the next highest value and it worked awesome. The whole query takes about 20 seconds. I do have one other questions though. When I ran my previous query searching simply for ... Like "*Gold*", I get a return of about 95,000 records. When I use this query, i get a return of about 85,000 records. I have figured out that this is a result of some wells having a "gold" entry as the last Well_Data entry and there are no further values to enter as the "next depth". How would I account for this. I imagine that I would need something to account for NULL but I don't think Nz() would work in SQL. Any ideas? Thanks Again. It will if you change the INNER JOIN to B to LEFT JOIN... I hope. I'm delighted that it improved the performance so much! -- John W. Vinson [MVP] |
#6
|
|||
|
|||
Select Min Query
Try this --
SELECT Well_Name, Well_Location, Item_Depth, Item_Description, (SELECT TOP 1 [XX].Item_Depth FROM Well_Data AS [XX] WHERE [XX].Well_ID = Wells.Well_ID AND [XX].Item_Depth Well_Data.Item_Depth ORDER BY [XX].Item_Depth) AS [Next Item Depth] FROM Wells LEFT JOIN Well_Data ON Wells.Well_ID = Well_Data.Well_ID WHERE Well_Data.Item_Description Like "*GOLD*"; -- Build a little, test a little. "Joel_123" wrote: Hello, I first off want to say thank you to all those who post on this site. I am not very experienced in Access and the advice and direction offered here is top notch! I have been handed a database that has well data in it. There is a table called Wells which has the following fields: Well_ID Well_Name Well_Location I also have another table called Well_Data. This table contains the well data (specific descriptions of items/materials encountered down the well) for each well. Each well can have numerous well data entries. The two tables are joined on Well_ID. Here are the fields for the Well_Data Table: Well_Data_ID Well_ID Item_Depth Item_Description I am writing a query to search on a specific Item_Description (let's say gold ). I need to be able to return the well name, well location, item depth, item description, as well as the next item depth below the current item depth. So for example, I would get a return of: Well_Name: 40000 Well_Location: Some Place Item Depth: 150 Item Description: Gold Next Item Depth 250 I tried to build this by creating one query. I have no problems using ... Like"*Gold*"... to get me the wells that have gold in them and the query is relatively quick (being that there are over 2 million Well_Data_ID records). The problem arises when I try to get the "Next Item Depth" value. The whole query hangs and Access shuts down. I don't know if I just haven't let it run long enough but I let it go for an hour and it still hung up. Here's how I tried to do that: NextItemDepthSelect Min(Item_Depth) from Well_Data Where Depth Well_Data_Alias.[Depth] and Well_ID = Well_Data_Alias.[Well_ID]) I know this works. I created a copy of the database and deleted over 2/3 of the records and this query completed in about a minute. I was just wondering if I can somehow re-structure this query to be more efficient. As for indexes, I ran the analyzer and indexed the fields it told me to. It didn't help at all. Any Help would be greatly appreciated. Thanks. Joel . |
Thread Tools | |
Display Modes | |
|
|