Performance on query

R

RichardO

Hi, I have this query that runs for minutes. I have SQL Server experience but
somehow in Access I get confused. Does anyone know how to improve the
performance? Here is the query:

"SELECT Codes.Code as CodeId, '',StringHeader.Reviewed as EnglishString, ''
as TranslatedString,StringHeader.Length as MaxLength,0 as
Status,StringHeader.Description FROM Codes INNER JOIN StringHeader ON
Codes.Id = StringHeader.CodeId where Codes.Id not in (SELECT String.CodeId
FROM Languages INNER JOIN [String] ON Languages.Id = String.LanguageId WHERE
(((Languages.Extension)='FRE'))) and StringHeader.AreaId=5 UNION ALL SELECT
Codes.Code, StringHeader_Sent.Reviewed, StringHeader.Reviewed,
String.Translated,
StringHeader.Length,Switch([StringHeader_Sent.Reviewed]<>[StringHeader.Reviewed],1,True,2),StringHeader.Description
FROM (StringHeader INNER JOIN Codes ON StringHeader.CodeId = Codes.Id) INNER
JOIN (([String] INNER JOIN Languages ON String.LanguageId = Languages.Id)
INNER JOIN StringHeader_Sent ON (String.CodeId = StringHeader_Sent.CodeId)
AND (String.LanguageId = StringHeader_Sent.LanguageId)) ON Codes.Id =
String.CodeId WHERE (((Languages.Extension) = 'FRE') and
(NoTranslationRequired =0)) ORDER BY 6,
1;"
 
J

Jerry Whittle

You'll already done the first thing that I would suggest and that would be
UNION ALL.

Is Languages.Extension indexed? It's in the Where clauses so I'd make sure
it's indexed.

What happens when you remove the Order By at the end? If it runs faster, do
you really need it? For example if the query is used for a report, you don't
need it. Rather you need to do the sorting in the report.

Since it is a Union query, how does each half run alone? That might tell you
where the slowdown occurs.

Sometimes converting a "not in" statement to NOT EXISTS can really work
wonders. Often the reverse is also true. That's why if I have either in a
slow query, I try the opposite just to see. I believe that the following
will work:

WHERE Not Exists (SELECT "X"
FROM Languages INNER JOIN [String]
ON Languages.Id = String.LanguageId
WHERE Languages.Extension='FRE'
AND StringHeader.AreaId=5
AND Codes.Id = String.CodeId)
 
R

RichardO

Thanks the not exists worked.

Jerry Whittle said:
You'll already done the first thing that I would suggest and that would be
UNION ALL.

Is Languages.Extension indexed? It's in the Where clauses so I'd make sure
it's indexed.

What happens when you remove the Order By at the end? If it runs faster, do
you really need it? For example if the query is used for a report, you don't
need it. Rather you need to do the sorting in the report.

Since it is a Union query, how does each half run alone? That might tell you
where the slowdown occurs.

Sometimes converting a "not in" statement to NOT EXISTS can really work
wonders. Often the reverse is also true. That's why if I have either in a
slow query, I try the opposite just to see. I believe that the following
will work:

WHERE Not Exists (SELECT "X"
FROM Languages INNER JOIN [String]
ON Languages.Id = String.LanguageId
WHERE Languages.Extension='FRE'
AND StringHeader.AreaId=5
AND Codes.Id = String.CodeId)
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

RichardO said:
Hi, I have this query that runs for minutes. I have SQL Server experience but
somehow in Access I get confused. Does anyone know how to improve the
performance? Here is the query:

"SELECT Codes.Code as CodeId, '',StringHeader.Reviewed as EnglishString, ''
as TranslatedString,StringHeader.Length as MaxLength,0 as
Status,StringHeader.Description FROM Codes INNER JOIN StringHeader ON
Codes.Id = StringHeader.CodeId where Codes.Id not in (SELECT String.CodeId
FROM Languages INNER JOIN [String] ON Languages.Id = String.LanguageId WHERE
(((Languages.Extension)='FRE'))) and StringHeader.AreaId=5 UNION ALL SELECT
Codes.Code, StringHeader_Sent.Reviewed, StringHeader.Reviewed,
String.Translated,
StringHeader.Length,Switch([StringHeader_Sent.Reviewed]<>[StringHeader.Reviewed],1,True,2),StringHeader.Description
FROM (StringHeader INNER JOIN Codes ON StringHeader.CodeId = Codes.Id) INNER
JOIN (([String] INNER JOIN Languages ON String.LanguageId = Languages.Id)
INNER JOIN StringHeader_Sent ON (String.CodeId = StringHeader_Sent.CodeId)
AND (String.LanguageId = StringHeader_Sent.LanguageId)) ON Codes.Id =
String.CodeId WHERE (((Languages.Extension) = 'FRE') and
(NoTranslationRequired =0)) ORDER BY 6,
1;"
Click to expand...
Click to expand...
 
Top