Speeding up a Query

It is all too easy to overlink tables in MS Query (I do it all the time!) and it can REALLY increase the processing time required.

Also: Select appropriate date filtering functions and criteria carefully as well as the over all order in which you are stacking multi table/column criteria.

There are also cases where a UNION between otherwise JOINable tables produces faster query results.

If you have a query that just seems to grind to a halt, definitely invest the time to manually attempt optimize the drag and drop auto-generated MS-Query code.

Rob Brown

bbelzer42 <bbelzer@...> wrote:
I use Microsoft Query quite a bit to create reports and pivot tables
in excel.

I have found that some queries take about 5 seconds and some others
can take up to 5 minutes for about 1000 rows of data.

I am currently on v 6.10.535 with Progress 9.1D.

Any help in speeding up my queries would be MUCH appreciated!

Thanks in advance,
Ben






---------------------------------
Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now.

[Non-text portions of this message have been removed]
I use Microsoft Query quite a bit to create reports and pivot tables
in excel.

I have found that some queries take about 5 seconds and some others
can take up to 5 minutes for about 1000 rows of data.

I am currently on v 6.10.535 with Progress 9.1D.

Any help in speeding up my queries would be MUCH appreciated!

Thanks in advance,
Ben
There's a whole bunch of things that can affect the speed....I'm not sure about MS Query specifically but few off the top of my head:

1. Always filter/select first on Company code
2. When linking tables always make the first field pair the company code
3. Use all the fields in an index when possible (which will take care of #2 as well)
4. Filter/select, when possible, on index fields first

There are more but doing all the above usually helps a lot in any report writer or query setting.

-Todd C.


________________________________
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of bbelzer42
Sent: Wednesday, January 02, 2008 11:30 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Speeding up a Query


I use Microsoft Query quite a bit to create reports and pivot tables
in excel.

I have found that some queries take about 5 seconds and some others
can take up to 5 minutes for about 1000 rows of data.

I am currently on v 6.10.535 with Progress 9.1D.

Any help in speeding up my queries would be MUCH appreciated!

Thanks in advance,
Ben





[Non-text portions of this message have been removed]
If you're in a single company do #1 & 2 really make any difference?

________________________________

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Todd Caughey
Sent: Wednesday, January 02, 2008 10:39 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Speeding up a Query



There's a whole bunch of things that can affect the speed....I'm not
sure about MS Query specifically but few off the top of my head:

1. Always filter/select first on Company code
2. When linking tables always make the first field pair the company code
3. Use all the fields in an index when possible (which will take care of
#2 as well)
4. Filter/select, when possible, on index fields first

There are more but doing all the above usually helps a lot in any report
writer or query setting.

-Todd C.

________________________________
From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
Behalf Of bbelzer42
Sent: Wednesday, January 02, 2008 11:30 AM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
Subject: [Vantage] Speeding up a Query

I use Microsoft Query quite a bit to create reports and pivot tables
in excel.

I have found that some queries take about 5 seconds and some others
can take up to 5 minutes for about 1000 rows of data.

I am currently on v 6.10.535 with Progress 9.1D.

Any help in speeding up my queries would be MUCH appreciated!

Thanks in advance,
Ben

[Non-text portions of this message have been removed]






[Non-text portions of this message have been removed]
YES! Forces the use of the index route for retrieval rather than record by record evaluation.
-Todd C.

________________________________
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Ken Williams
Sent: Wednesday, January 02, 2008 11:49 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Speeding up a Query


If you're in a single company do #1 & 2 really make any difference?

________________________________

From: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>] On Behalf
Of Todd Caughey
Sent: Wednesday, January 02, 2008 10:39 AM
To: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>
Subject: RE: [Vantage] Speeding up a Query

There's a whole bunch of things that can affect the speed....I'm not
sure about MS Query specifically but few off the top of my head:

1. Always filter/select first on Company code
2. When linking tables always make the first field pair the company code
3. Use all the fields in an index when possible (which will take care of
#2 as well)
4. Filter/select, when possible, on index fields first

There are more but doing all the above usually helps a lot in any report
writer or query setting.

-Todd C.

________________________________
From: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com> ] On
Behalf Of bbelzer42
Sent: Wednesday, January 02, 2008 11:30 AM
To: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
Subject: [Vantage] Speeding up a Query

I use Microsoft Query quite a bit to create reports and pivot tables
in excel.

I have found that some queries take about 5 seconds and some others
can take up to 5 minutes for about 1000 rows of data.

I am currently on v 6.10.535 with Progress 9.1D.

Any help in speeding up my queries would be MUCH appreciated!

Thanks in advance,
Ben

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]





[Non-text portions of this message have been removed]
Believe it or not Yes!! The Company is always part of the index on the
tables, so even if you only have 1 company, you still need to include
it.

Brandon Murch
WindStone Medical Packaging, Inc.
Supervisor of Information Technology
(406)259-6387
bmurch@...
www.windstonemedical.com


-----Original Message-----
From: Ken Williams [mailto:ken@...]
Sent: Wednesday, January 02, 2008 10:49 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Speeding up a Query

If you're in a single company do #1 & 2 really make any difference?

________________________________

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Todd Caughey
Sent: Wednesday, January 02, 2008 10:39 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Speeding up a Query



There's a whole bunch of things that can affect the speed....I'm not
sure about MS Query specifically but few off the top of my head:

1. Always filter/select first on Company code
2. When linking tables always make the first field pair the company code
3. Use all the fields in an index when possible (which will take care of
#2 as well)
4. Filter/select, when possible, on index fields first

There are more but doing all the above usually helps a lot in any report
writer or query setting.

-Todd C.

________________________________
From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
Behalf Of bbelzer42
Sent: Wednesday, January 02, 2008 11:30 AM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
Subject: [Vantage] Speeding up a Query

I use Microsoft Query quite a bit to create reports and pivot tables
in excel.

I have found that some queries take about 5 seconds and some others
can take up to 5 minutes for about 1000 rows of data.

I am currently on v 6.10.535 with Progress 9.1D.

Any help in speeding up my queries would be MUCH appreciated!

Thanks in advance,
Ben

[Non-text portions of this message have been removed]






[Non-text portions of this message have been removed]



Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must
have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder
and Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links
Yahoo! Groups Links



***********************************************************************
Privileged and/or confidential information may be contained in
this message. If you are not the addressee indicated in this
message (or are not responsible for delivery of this message to that person), you may not copy or deliver this message to
anyone. In such case, you should destroy this message and
notify the sender by reply e-mail. If you or your employer do
not consent to Internet e-mail for messages of this kind,
please advise the sender. Windstone Medical Packaging,
Inc. does not provide or endorse any opinions, conclusions
or other information in this message that do not relate to the
official business of the company.
***********************************************************************
Good to know. I know of at least one situation where I had to create a
relationship and I didn't include the company, I'll have to go check all
my queries now.

Thanks for the tip!

________________________________

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Todd Caughey
Sent: Wednesday, January 02, 2008 10:49 AM
To: vantage@yahoogroups.com
Subject: RE: RE: [Vantage] Speeding up a Query



YES! Forces the use of the index route for retrieval rather than record
by record evaluation.
-Todd C.

________________________________
From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
Behalf Of Ken Williams
Sent: Wednesday, January 02, 2008 11:49 AM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
Subject: RE: [Vantage] Speeding up a Query

If you're in a single company do #1 & 2 really make any difference?

________________________________

From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>]
On Behalf
Of Todd Caughey
Sent: Wednesday, January 02, 2008 10:39 AM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
Subject: RE: [Vantage] Speeding up a Query

There's a whole bunch of things that can affect the speed....I'm not
sure about MS Query specifically but few off the top of my head:

1. Always filter/select first on Company code
2. When linking tables always make the first field pair the company code
3. Use all the fields in an index when possible (which will take care of
#2 as well)
4. Filter/select, when possible, on index fields first

There are more but doing all the above usually helps a lot in any report
writer or query setting.

-Todd C.

________________________________
From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com> ]
On
Behalf Of bbelzer42
Sent: Wednesday, January 02, 2008 11:30 AM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
Subject: [Vantage] Speeding up a Query

I use Microsoft Query quite a bit to create reports and pivot tables
in excel.

I have found that some queries take about 5 seconds and some others
can take up to 5 minutes for about 1000 rows of data.

I am currently on v 6.10.535 with Progress 9.1D.

Any help in speeding up my queries would be MUCH appreciated!

Thanks in advance,
Ben

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]






[Non-text portions of this message have been removed]
Yes, because Progress tries to pick an index based on your selection string. It looks for a match going one field at a time and almost all indexes begin with the Company. The UserFile is a table that doesn't even have the Company.

If one of the fields is a flag, like Job Closed, use "... and Job.JobClosed eq Yes" or "and Job.JobClosed eq No" as opposed to simply "and Job.JobClosed" and "and not Job.JobClosed". The latter breaks the index search, the former does not.

----- Original Message -----
From: "Ken Williams" <ken@...>
To: <vantage@yahoogroups.com>
Sent: Wednesday, January 02, 2008 9:48 AM
Subject: RE: [Vantage] Speeding up a Query


If you're in a single company do #1 & 2 really make any difference?

________________________________

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Todd Caughey
Sent: Wednesday, January 02, 2008 10:39 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Speeding up a Query



There's a whole bunch of things that can affect the speed....I'm not
sure about MS Query specifically but few off the top of my head:

1. Always filter/select first on Company code
2. When linking tables always make the first field pair the company code
3. Use all the fields in an index when possible (which will take care of
#2 as well)
4. Filter/select, when possible, on index fields first

There are more but doing all the above usually helps a lot in any report
writer or query setting.

-Todd C.

________________________________
From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
Behalf Of bbelzer42
Sent: Wednesday, January 02, 2008 11:30 AM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
Subject: [Vantage] Speeding up a Query

I use Microsoft Query quite a bit to create reports and pivot tables
in excel.

I have found that some queries take about 5 seconds and some others
can take up to 5 minutes for about 1000 rows of data.

I am currently on v 6.10.535 with Progress 9.1D.

Any help in speeding up my queries would be MUCH appreciated!

Thanks in advance,
Ben

[Non-text portions of this message have been removed]






[Non-text portions of this message have been removed]



Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and Crystal Reports and other 'goodies', please goto: http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto: http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto: http://groups.yahoo.com/group/vantage/links
Yahoo! Groups Links




[Non-text portions of this message have been removed]