Query is very slow to save

iCarbon · Dec 22, 2009

Ran into something with Access that is new to me. I have a DB with about 6 linked ODBC tables. I am writing a query that uses all of those tables and has about 10 result columns. Other than the table joins, the query is a fairly simple select query.

My problem is that the query can take a very long time to save. Not always, but often. The query will actually execute faster than it takes to save after an edit.

I am using WinXP, Access 2007, standard DB format. The DB only has the table links, one module, and the one query. I made a cut down version after running into this on a more complex database.

MSAccessRookie · Dec 22, 2009

iCarbon said:
Ran into something with Access that is new to me. I have a DB with about 6 linked ODBC tables. I am writing a query that uses all of those tables and has about 10 result columns. Other than the table joins, the query is a fairly simple select query.

My problem is that the query can take a very long time to save. Not always, but often. The query will actually execute faster than it takes to save after an edit.

I am using WinXP, Access 2007, standard DB format. The DB only has the table links, one module, and the one query. I made a cut down version after running into this on a more complex database.

It is difficult to comment on what is happening to you without seeing any code, as there could be a number of issues that could contribute to the problem. If you can post the complete SQL Query in a reply, then perhaps someone can see if there is a way to make the query more efficient.

namliam · Dec 22, 2009

If you are running queries on ODBC sources it is better to do this "Pass through" this ensure the data beeing sourced on the source database, instead of on your local machine.

Google it and see how far you get.

MSAccessRookie · Dec 22, 2009

Code:

[FONT=Times New Roman][SIZE=3]One thing that I notice is that you have quotes ( " )  where you do not need them.  Consider the [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]following statement (when [COLOR=black][Main Table]![Project No]="ABCD1234"[/COLOR]:[/SIZE][/FONT]
 
[COLOR=black][SIZE=3][FONT=Times New Roman]Right("[Main Table]![Project No]", (Len("[Main Table]![Project No]") - 4)) [/FONT][/SIZE][/COLOR][COLOR=black][SIZE=3][FONT=Times New Roman]is evaluated as follows:[/FONT][/SIZE][/COLOR]
 
[COLOR=black][SIZE=3][FONT=Times New Roman]"[Main Table]![Project No]" = [Main Table]![Project No][/FONT][/SIZE][/COLOR]
[COLOR=black][SIZE=3][FONT=Times New Roman](Len("[Main Table]![Project No]")) = 25[/FONT][/SIZE][/COLOR]
[COLOR=black][SIZE=3][FONT=Times New Roman](Len("[Main Table]![Project No]") - 4)) = 21[/FONT][/SIZE][/COLOR]
 
[COLOR=black][SIZE=3][FONT=Times New Roman]If you change the code to: [/FONT][/SIZE][/COLOR][COLOR=black][SIZE=3][FONT=Times New Roman]Right([Main Table]![Project No], (Len([Main Table]![Project No]) - 4)) [/FONT][/SIZE][/COLOR]
[COLOR=black][SIZE=3][FONT=Times New Roman]then it will be evaluated as follows:[/FONT][/SIZE][/COLOR]
 
[COLOR=black][SIZE=3][FONT=Times New Roman][Main Table]![Project No] = "ABCD1234"[/FONT][/SIZE][/COLOR]
[COLOR=black][SIZE=3][FONT=Times New Roman](Len([Main Table]![Project No])) = 8[/FONT][/SIZE][/COLOR]
[COLOR=black][SIZE=3][FONT=Times New Roman](Len([Main Table]![Project No]) - 4)) = 4[/FONT][/SIZE][/COLOR]

iCarbon · Dec 22, 2009

Thanks for the responses. Here is the SQL view of the query. And yes, I forgot to say it was a cross-tab query. The function converts between the Unix Epoch time in the DB and MS Time format. Shouldn't be executing during a save operation though.

@namliam: I'm not familiar with a "pass through" parameter, but I have these as linked tables (rather than "import") and that has worked fine for me in the past.

Code:

TRANSFORM First(DESC_DAT.F_NAME) AS DesValue
SELECT TEST_DAT.F_NAME AS TestName, PART_DAT.F_NAME AS Part, PRCS_DAT.F_NAME AS Process, 
  or2mstime([SGRP_INF.F_CRTM]) AS DTWeigh, SGRP_TST.F_VAL AS Weight
FROM ((((((SGRP_INF INNER JOIN SGRP_TST ON 
SGRP_INF.F_SGRP = SGRP_TST.F_SGRP) 
INNER JOIN TEST_DAT ON SGRP_TST.F_TEST = TEST_DAT.F_TEST) 
INNER JOIN PART_DAT ON SGRP_INF.F_PART = PART_DAT.F_PART) 
INNER JOIN PRCS_DAT ON SGRP_INF.F_PRCS = PRCS_DAT.F_PRCS) 
INNER JOIN SGRP_DSC ON SGRP_TST.F_SGRP = SGRP_DSC.F_SGRP) 
INNER JOIN DESC_DAT ON SGRP_DSC.F_DESC = DESC_DAT.F_DESC) 
INNER JOIN DESC_GRP ON SGRP_DSC.F_DSGP = DESC_GRP.F_DSGP
WHERE (((SGRP_INF.F_CRTM) Between ms2ortime(#10/1/2009#) And 
ms2ortime(#10/2/2009#)) AND ((TEST_DAT.F_TSGP)=919682313 Or (TEST_DAT.F_TSGP)=919682209))
GROUP BY TEST_DAT.F_NAME, PART_DAT.F_NAME, PRCS_DAT.F_NAME, 
or2mstime([SGRP_INF.F_CRTM]), SGRP_TST.F_VAL
PIVOT DESC_GRP.F_NAME;

namliam · Dec 22, 2009

Pass Through is a query option (Query> Query specific > Pass trhough)
This forces MSA to send the query to the database, right now it is probably fetching the full tables and doing the cross on your local machine.

Where is the ms2ortime function located? In your DB? Or on the source DB?
Is there an index on the fields F_CRTM and F_TSGP, if so what types of fields are they?

Query is very slow to save

iCarbon

New member

MSAccessRookie

AWF VIP

namliam

The Mailman - AWF VIP