Query speed affected by sorting

  • Thread starter Thread starter Randal
  • Start date Start date
R

Randal

I am using the following query to get the top 10 for multiple IDs. This
query works very quickly as a select query with no sorting. If you sort it
in the query or in report or turn it into a make table query, it slows to a
crawl.

Does anyone know how to speed this up? I need to display the amounts sorted
in descending order in a report.

SELECT
[Your Table].[ID],
[Your Table].[AMT1]
WHERE
[Your Table].[AMT1] IN
(SELECT TOP 10
[Self].[AMT1]
FROM
[Your Table] AS [Self]
WHERE
[Self].[ID] = [Your Table].[ID])
 
First of all you haven't specified the top 10 AMT because you haven't sorted in
the TOP 10 query. You are just returning any 10 in an undetermined order.
Assuming you want the largest values you would need to change your TOP query.
Of course, that will slow things down some more.

SELECT
[Your Table].[ID],
[Your Table].[AMT1]
WHERE
[Your Table].[AMT1] IN
(SELECT TOP 10
[Self].[AMT1]
FROM
[Your Table] AS [Self]
WHERE
[Self].[ID] = [Your Table].[ID]
ORDER BY Self.Amt1 Desc)

Do you have both the Amt1 and ID fields indexed? That should make a large
difference in the speed of the query. By the way, don't bother to sort in the
overall query if you are using this as the source of a report. Access more or
less ignores any query sorting when displaying records in a report. For
reliable results, you MUST use the Sorting and Grouping dialog of the report.
 
Randal said:
I am using the following query to get the top 10 for multiple IDs. This
query works very quickly as a select query with no sorting. If you sort it
in the query or in report or turn it into a make table query, it slows to a
crawl.

Does anyone know how to speed this up? I need to display the amounts sorted
in descending order in a report.

SELECT
[Your Table].[ID],
[Your Table].[AMT1]
WHERE
[Your Table].[AMT1] IN
(SELECT TOP 10
[Self].[AMT1]
FROM
[Your Table] AS [Self]
WHERE
[Self].[ID] = [Your Table].[ID])
Click to expand...
Hi Randall,

I am not sure this will help, but...
have you tested Brian Camire's
"count correlation" query technique?

SELECT
t1.ID,
t1.AMT1
FROM [Your Table] t1
WHERE
(SELECT COUNT(*)
FROM [Your Table] t2
WHERE t2.ID = t1.ID
AND
t2.AMT1 >= t1.AMT1)<=10;

I would be interested in knowing
if this helps.

Thanks,

Gary Walter
 
Thanks - this helped.

John Spencer (MVP) said:
First of all you haven't specified the top 10 AMT because you haven't sorted in
the TOP 10 query. You are just returning any 10 in an undetermined order.
Assuming you want the largest values you would need to change your TOP query.
Of course, that will slow things down some more.

SELECT
[Your Table].[ID],
[Your Table].[AMT1]
WHERE
[Your Table].[AMT1] IN
(SELECT TOP 10
[Self].[AMT1]
FROM
[Your Table] AS [Self]
WHERE
[Self].[ID] = [Your Table].[ID]
ORDER BY Self.Amt1 Desc)

Do you have both the Amt1 and ID fields indexed? That should make a large
difference in the speed of the query. By the way, don't bother to sort in the
overall query if you are using this as the source of a report. Access more or
less ignores any query sorting when displaying records in a report. For
reliable results, you MUST use the Sorting and Grouping dialog of the report.
I am using the following query to get the top 10 for multiple IDs. This
query works very quickly as a select query with no sorting. If you sort it
in the query or in report or turn it into a make table query, it slows to a
crawl.

Does anyone know how to speed this up? I need to display the amounts sorted
in descending order in a report.

SELECT
[Your Table].[ID],
[Your Table].[AMT1]
WHERE
[Your Table].[AMT1] IN
(SELECT TOP 10
[Self].[AMT1]
FROM
[Your Table] AS [Self]
WHERE
[Self].[ID] = [Your Table].[ID])
Click to expand...
Click to expand...
 
I'm not sure I understand how this works, but it does. Thanks Gary.

Gary Walter said:
Randal said:
I am using the following query to get the top 10 for multiple IDs. This
query works very quickly as a select query with no sorting. If you sort it
in the query or in report or turn it into a make table query, it slows to a
crawl.

Does anyone know how to speed this up? I need to display the amounts sorted
in descending order in a report.

SELECT
[Your Table].[ID],
[Your Table].[AMT1]
WHERE
[Your Table].[AMT1] IN
(SELECT TOP 10
[Self].[AMT1]
FROM
[Your Table] AS [Self]
WHERE
[Self].[ID] = [Your Table].[ID])
Click to expand...
Hi Randall,

I am not sure this will help, but...
have you tested Brian Camire's
"count correlation" query technique?

SELECT
t1.ID,
t1.AMT1
FROM [Your Table] t1
WHERE
(SELECT COUNT(*)
FROM [Your Table] t2
WHERE t2.ID = t1.ID
AND
t2.AMT1 >= t1.AMT1)<=10;

I would be interested in knowing
if this helps.

Thanks,

Gary Walter
Click to expand...
 
I am using the following query to get the top 10 for multiple IDs. This
query works very quickly as a select query with no sorting. If you sort it
in the query or in report or turn it into a make table query, it slows to a
crawl.

Does anyone know how to speed this up? I need to display the amounts sorted
in descending order in a report.
Click to expand...

Put a non-unique index on AMT; sorting will ALWAYS take longer than
not sorting, since instead of returning records in whatever order
they're found on disk, Access must first find the smallest, then the
next and so on. However an Index can *dramatically* speed this up.
 
Back
Top