busy query

[Ashank4vba]

Hi,


I have a select query that uses 21 other queries and a table.
All these 21 queries are 'LEFT JOIN'ed to this table and I am 'SELECT'ing about 2 dozen fields from this query. I also have a criteria where one of the fields should be equal to a textbox value in a form.
The query is too big for me to post here.

The problem is: when I double click on the query, the mouse pointer becomes an hourglass and the application seems to hang for a looooooong time and I 'End Task' MS Access (which is not responding..I guess that this doesnt always mean that the application has crashed but I still did it).

Just to see if the query runs, I ran it when the form mentioned in its criteria is deliberatly not open. I expected it to prompt me for the form field but I get the same behavior - hourglass for more than 15 minutes.
In a colleagues machine, it runs in a minute at the maximum.
My machine configuration is not too bad - 2.4 Ghz P4, 1 GB Ram, Windows 2000.

Please help me run this query successfully! Thanks.

 

[Ashank4vba]

Any suggestions?

 

[Crowley16]

are you saying that the query works fine on your friends machine?

sounds like the query just takes a long time to run...

you could have a look at your network and odbc connections to see if there's a bottleneck somewhere or try to import the backend into your local hdd, but otherwise, there's not much that can really be done, apart from getting a faster machine...

--------------------
Procrastinate Now!

 

[Crowley16]

Oh, forgot to ask, you have tried optimising your query right?

--------------------
Procrastinate Now!

 

[Ashank4vba]

Thanks for your response crowley. I think it was slow because I was needlessly using the 'CStr' function for a couple of left joins + the database itself is a bit bloated.
I have not tried optimizing it. Got to see how the modify the constituting data sources too to see if i can make it faster.
Thanks for your suggestion.