Query data pull takes FOREVER

[CS45]

Hi all,

I run an Access query every week that pulls data from a table that is linked to an external warehouse. The query selects a ton of data, but it takes about 40 minutes to execute. Is there any way to speed that up, or is it a bandwidth issue?

 

[theDBguy]

Are you aggregating the data as you pull them? If so, try pulling the raw data only.

 

[CS45]

By aggregating the data, do you mean the “Group By” clause? If so then yes I do use that...I’m not quite sure hat it does but I did not write the query, just fixing it.
What do the values in the “total” row do?

 

[theDBguy]

By aggregating the data, do you mean the “Group By” clause? If so then yes I do use that...I’m not quite sure hat it does but I did not write the query, just fixing it.
What do the values in the “total” row do?

I thought so. Just as a test, try clicking on the Sigma button on the ribbon to remove the Totals row and compare how long the query runs without it.

 

[CS45]

I removed the totals ribbon and am currently running it again. I also have a few criteria that are necessary for the data I need; would those slow the pull down as well?

 

[theDBguy]

I removed the totals ribbon and am currently running it again. I also have a few criteria that are necessary for the data I need; would those slow the pull down as well?

They should actually speed them up.

 

[CS45]

Is the HAVING clause the same as the criteria? I know one is SQL and one is in design view but are they related?
I ask because the internet is saying that the HAVING clause filters the data after it is grouped, but if I’m not grouping them that may be an issue...

 

[CS45]

Could I move the things in criteria (such as Like “COMPLETE”) into the Sort row?

 

[theDBguy]

Is the HAVING clause the same as the criteria? I know one is SQL and one is in design view but are they related?
I ask because the internet is saying that the HAVING clause filters the data after it is grouped, but if I’m not grouping them that may be an issue...

Hi. HAVING and WHERE clauses are not the same.
Sent from phone...

 

[theDBguy]

Could I move the things in criteria (such as Like “COMPLETE”) into the Sort row?

No, I don't think so. In any case, I was just trying to see if the time it takes to execute is any different when you pull the raw data compared to when you aggregate first. What did you find out?

 

[CS45]

the difference was marginal. With total row it was 27 minutes (usually between 20 and 50). Without the row, it was 24 minutes.
Any other tips that could drastically reduce that time? I run other queries with more data that take 1-2 minutes, so I don’t know why this one is so long...

 

[theDBguy]

the difference was marginal. With total row it was 27 minutes (usually between 20 and 50). Without the row, it was 24 minutes.
Any other tips that could drastically reduce that time? I run other queries with more data that take 1-2 minutes, so I don’t know why this one is so long...

Hi. Without the Totals, how many records were you getting in 24 minutes? How many records were you getting with those 1-2 minutes queries?

 

[Isaac]

Based on our other thread, I suspect you may already be doing this, but as you probably know...usually, creating a pass through query, and sending the remote system's preferred SQL over to the server to process will (often) be faster than using an Access linked table.

Secondly, if you can post the SQL -- or a manipulated/sanitized version of it -- we may be able to make a suggestion for optimizing it or at least point to what is causing it to be so slow.

 

[Minty]

In later versions of Access, it will (depending on what it's connecting to) automatically move a Having clause to a Where clause.

In general SQL query terms, it will evaluate all the records before then applying the HAVING clause, so it's definitely slower than using WHERE.
I agree about the pass-through query being a plausible speed up as the leg work would all be done on the server, so you'd get back just the results you wanted.

 

[CS45]

Hi. Without the Totals, how many records were you getting in 24 minutes? How many records were you getting with those 1-2 minutes queries?

The 24 minute query returned 3554 records, the 1 minute returned 2091.

 

[CS45]

Based on our other thread, I suspect you may already be doing this, but as you probably know...usually, creating a pass through query, and sending the remote system's preferred SQL over to the server to process will (often) be faster than using an Access linked table.

Secondly, if you can post the SQL -- or a manipulated/sanitized version of it -- we may be able to make a suggestion for optimizing it or at least point to what is causing it to be so slow.

I can’t post any of my code for security reasons, sorry. On the note of the other thread, I tried creating a pass through query but I can’t get to the server to look at what the table’s fields are titled.

 

[theDBguy]

The 24 minute query returned 3554 records, the 1 minute returned 2091.

I wonder if you're joining multiple tables in the slow query.

 

[CS45]

What do you mean? And how do I check? I think the query pulls from the same table but it may not...

 

[theDBguy]

What do you mean? And how do I check? I think the query pulls from the same table but it may not...

Check the SQL statements. Do they both have the same number of table sources in the FROM clause?

 

[Isaac]

I can’t post any of my code for security reasons, sorry

So if you sanitize the table name(s), database name(s), and any criteria, and you end up posting code like:
select col1, col2, col3 from table1 where col1="criteria"

.... that will be too sensitive to post?