Huge prestanda problem!

[Niklas Östergren]

Hi!

I´m using a pretty large but farly straight forward SELECT quiery as a
recordsource for my main form and until reasentöy have this been working
without any problem.

Today I have tryed to populate my application with the old data that we have
in our old db (apr. 1200 records in one table, 1500 in another and so on).
Not a lot of records as you can see but enough to give me problem with
prestanda of this quiery (see below).

In this quiery I have an expression (see below):
DMax("[MemberShipEndDate]";"tblMemberValidation";"[tblMemberValidation].[fkMemberID]
= " & [MemberID] & " AND [MemberShipEndDate] >= Date()")

If I remove this expression in the quiery then it runs fast as it should.
But I need this so my Q is:
- Can I do this in another faster way so the query run´s the way it should?

Her´s the complete query (qryValidMemberShip):
==============================================================
SELECT qryAllPerson.PersonID, qryAllPerson.FirstName, qryAllPerson.LastName,
qryAllPerson.NickName, qryAllPerson.DoB, qryAllPerson.SocialSecurityNo,
qryAllPerson.Sex, qryAllPerson.OwnAddress, qryAllPerson.[C/OPerson],
qryAllPerson.Street, qryAllPerson.fkZipCode, qryAllPerson.WebAddress,
qryAllPerson.Department, qryAllPerson.Attendance, qryAllPerson.PersonNote,
qryAllPerson.[Select], qryAllPerson.City, qryAllPerson.Country,
qryAllPerson.LastUpDated, tblMemberValidation.MemberValidID,
tblMemberValidation.fkMemberID, tblMemberValidation.EntryFeePayed,
tblMemberValidation.VerNo, tblMemberValidation.MemberShipStartDate,
tblMemberValidation.MemberShipEndDate, tblLookUpMemberShipType.MemberType,
tblMemberValidation.SMCMember, tblMember.MemberID, tblMember.MemberNo,
tblMember.MemberCardSent, qryAllPerson.fkNewMemberShipEntryID,
tblMemberValidation.fkMemberShipTypeID
FROM tblMember RIGHT JOIN (tblLookUpMemberShipType RIGHT JOIN (qryAllPerson
RIGHT JOIN tblMemberValidation ON qryAllPerson.PersonID =
tblMemberValidation.fkPersonID) ON tblLookUpMemberShipType.MemberShipTypeID
= tblMemberValidation.fkMemberShipTypeID) ON tblMember.MemberID =
tblMemberValidation.fkMemberID
WHERE
(((tblMemberValidation.MemberShipEndDate)=DMax("[MemberShipEndDate]","tblMemberValidation","[tblMemberValidation].[fkMemberID]
= " & [MemberID] & " AND [MemberShipEndDate] >= Date()")));
=================================================================

And her´s the complete quiery of (qryAllPerson):
===============================================================
SELECT tblPerson.PersonID, tblPerson.LastName, tblPerson.FirstName,
tblPerson.NickName, tblPerson.DoB, tblPerson.SocialSecurityNo,
tblPerson.Sex, tblPersonAddress.OwnAddress, tblPersonAddress.[C/OPerson],
tblPersonAddress.Street, tblPersonAddress.fkZipCode, tblLookUpZipCode.City,
tblLookUpZipCode.Country, tblPerson.WebAddress, tblPerson.Department,
tblPerson.Attendance, tblPerson.PersonNote, tblPerson.LastUpDated,
tblPerson.[Select], tblPersonFile.FileID, tblPersonFile.Active,
tblPerson.fkNewMemberShipEntryID
FROM ((tblLookUpZipCode RIGHT JOIN tblPersonAddress ON
tblLookUpZipCode.ZipCode = tblPersonAddress.fkZipCode) RIGHT JOIN tblPerson
ON tblPersonAddress.PersonAddressID = tblPerson.fkPersonAddressID) LEFT JOIN
tblPersonFile ON tblPerson.PersonID = tblPersonFile.fkPersonID
WHERE (((tblPersonFile.Active) Is Null Or (tblPersonFile.Active)=0))
ORDER BY tblPerson.LastName, tblPerson.FirstName;
==============================================================

I´m not wery good at SQL so I´l realy need help here!

TIA
// Niklas

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Use a subquery instead of aggregate domain function. Subqueries can use
indexes (faster) domain functions cannot (slower).

WHERE
tblMemberValidation.MemberShipEndDate =
(SELECT Max(MemberShipEndDate) FROM tblMemberValidation
WHERE fkMemberID >= ?.MemberID AND MembershipEndDate >= Date())

You should put aliases on your tables 'cuz it makes the query easier to
read AND you can refer to tables in different parts of the
query/subqueries by the correct alias.

In my WHERE example replace the '?' in front of MemberID with the alias
of the table in the main query that holds the necessary MemberID column.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQd2Qk4echKqOuFEgEQIldACgwP9mHw0A3y06Dcfsr/Z6XZfXXcoAnAuq
8ScplFGZQM9H8dCF+fC7nCHK
=SRmK
-----END PGP SIGNATURE-----

Hi!

I´m using a pretty large but farly straight forward SELECT quiery as a
recordsource for my main form and until reasentöy have this been working
without any problem.

Today I have tryed to populate my application with the old data that we have
in our old db (apr. 1200 records in one table, 1500 in another and so on).
Not a lot of records as you can see but enough to give me problem with
prestanda of this quiery (see below).

In this quiery I have an expression (see below):
DMax("[MemberShipEndDate]";"tblMemberValidation";"[tblMemberValidation].[fkMemberID]
= " & [MemberID] & " AND [MemberShipEndDate] >= Date()")

< snip >

 

[Niklas Östergren]

When I´m talking about huge prestanda problem I mean that it takes between
4 - 7 seconds to open the quiery and much longer to open the form about 15 -
25 sec.

It´s a unbound form with a subform which is populated with the records. I do
have som code in the form´s Current_Event and I have to take a closer look
at this as well. But to start with I have to get the quiery to open much
faster, which it does if I remove the expression of DMax...

TIA!
// Niklas

Hi!

I´m using a pretty large but farly straight forward SELECT quiery as a
recordsource for my main form and until reasentöy have this been working
without any problem.

Today I have tryed to populate my application with the old data that we
have in our old db (apr. 1200 records in one table, 1500 in another and so
on). Not a lot of records as you can see but enough to give me problem
with prestanda of this quiery (see below).

In this quiery I have an expression (see below):

DMax("[MemberShipEndDate]";"tblMemberValidation";"[tblMemberValidation].[fkMemberID]
= " & [MemberID] & " AND [MemberShipEndDate] >= Date()")

If I remove this expression in the quiery then it runs fast as it should.
But I need this so my Q is:
- Can I do this in another faster way so the query run´s the way it
should?

Her´s the complete query (qryValidMemberShip):
==============================================================
SELECT qryAllPerson.PersonID, qryAllPerson.FirstName,
qryAllPerson.LastName, qryAllPerson.NickName, qryAllPerson.DoB,
qryAllPerson.SocialSecurityNo, qryAllPerson.Sex, qryAllPerson.OwnAddress,
qryAllPerson.[C/OPerson], qryAllPerson.Street, qryAllPerson.fkZipCode,
qryAllPerson.WebAddress, qryAllPerson.Department, qryAllPerson.Attendance,
qryAllPerson.PersonNote, qryAllPerson.[Select], qryAllPerson.City,
qryAllPerson.Country, qryAllPerson.LastUpDated,
tblMemberValidation.MemberValidID, tblMemberValidation.fkMemberID,
tblMemberValidation.EntryFeePayed, tblMemberValidation.VerNo,
tblMemberValidation.MemberShipStartDate,
tblMemberValidation.MemberShipEndDate, tblLookUpMemberShipType.MemberType,
tblMemberValidation.SMCMember, tblMember.MemberID, tblMember.MemberNo,
tblMember.MemberCardSent, qryAllPerson.fkNewMemberShipEntryID,
tblMemberValidation.fkMemberShipTypeID
FROM tblMember RIGHT JOIN (tblLookUpMemberShipType RIGHT JOIN
(qryAllPerson RIGHT JOIN tblMemberValidation ON qryAllPerson.PersonID =
tblMemberValidation.fkPersonID) ON
tblLookUpMemberShipType.MemberShipTypeID =
tblMemberValidation.fkMemberShipTypeID) ON tblMember.MemberID =
tblMemberValidation.fkMemberID
WHERE
(((tblMemberValidation.MemberShipEndDate)=DMax("[MemberShipEndDate]","tblMemberValidation","[tblMemberValidation].[fkMemberID]
= " & [MemberID] & " AND [MemberShipEndDate] >= Date()")));
=================================================================

And her´s the complete quiery of (qryAllPerson):
===============================================================
SELECT tblPerson.PersonID, tblPerson.LastName, tblPerson.FirstName,
tblPerson.NickName, tblPerson.DoB, tblPerson.SocialSecurityNo,
tblPerson.Sex, tblPersonAddress.OwnAddress, tblPersonAddress.[C/OPerson],
tblPersonAddress.Street, tblPersonAddress.fkZipCode,
tblLookUpZipCode.City, tblLookUpZipCode.Country, tblPerson.WebAddress,
tblPerson.Department, tblPerson.Attendance, tblPerson.PersonNote,
tblPerson.LastUpDated, tblPerson.[Select], tblPersonFile.FileID,
tblPersonFile.Active, tblPerson.fkNewMemberShipEntryID
FROM ((tblLookUpZipCode RIGHT JOIN tblPersonAddress ON
tblLookUpZipCode.ZipCode = tblPersonAddress.fkZipCode) RIGHT JOIN
tblPerson ON tblPersonAddress.PersonAddressID =
tblPerson.fkPersonAddressID) LEFT JOIN tblPersonFile ON tblPerson.PersonID
= tblPersonFile.fkPersonID
WHERE (((tblPersonFile.Active) Is Null Or (tblPersonFile.Active)=0))
ORDER BY tblPerson.LastName, tblPerson.FirstName;
==============================================================

I´m not wery good at SQL so I´l realy need help here!

TIA
// Niklas

 

[Niklas Östergren]

OK!

Thank´s a lot I´ll try this out and will get back with the result.

Thank´s a lot for fast help!

// Niklas

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Use a subquery instead of aggregate domain function. Subqueries can use
indexes (faster) domain functions cannot (slower).

WHERE
tblMemberValidation.MemberShipEndDate =
(SELECT Max(MemberShipEndDate) FROM tblMemberValidation
WHERE fkMemberID >= ?.MemberID AND MembershipEndDate >= Date())

You should put aliases on your tables 'cuz it makes the query easier to
read AND you can refer to tables in different parts of the
query/subqueries by the correct alias.

In my WHERE example replace the '?' in front of MemberID with the alias
of the table in the main query that holds the necessary MemberID column.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQd2Qk4echKqOuFEgEQIldACgwP9mHw0A3y06Dcfsr/Z6XZfXXcoAnAuq
8ScplFGZQM9H8dCF+fC7nCHK
=SRmK
-----END PGP SIGNATURE-----

Hi!

I´m using a pretty large but farly straight forward SELECT quiery as a
recordsource for my main form and until reasentöy have this been working
without any problem.

Today I have tryed to populate my application with the old data that we
have in our old db (apr. 1200 records in one table, 1500 in another and
so on). Not a lot of records as you can see but enough to give me problem
with prestanda of this quiery (see below).

In this quiery I have an expression (see below):

DMax("[MemberShipEndDate]";"tblMemberValidation";"[tblMemberValidation].[fkMemberID]
= " & [MemberID] & " AND [MemberShipEndDate] >= Date()")

< snip >

 

[Niklas Östergren]

Wow what a big difference!

I´m realy surprised and now realise that I do HAVE to learn more about SQL!
Do you know any good book about it? I have been working with Access, more or
less, since 1999 and know a little about SQL. But I allways use Design view
when I build query´s and then I copy and paste the SQL-string into the
VBA-code if needed. That´s my knowledge about SQL. And I have a swedish book
about SQL but it´s not so good. I prefere english books!

Thank´s a lot MGFoster!

// Niklas

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Use a subquery instead of aggregate domain function. Subqueries can use
indexes (faster) domain functions cannot (slower).

WHERE
tblMemberValidation.MemberShipEndDate =
(SELECT Max(MemberShipEndDate) FROM tblMemberValidation
WHERE fkMemberID >= ?.MemberID AND MembershipEndDate >= Date())

You should put aliases on your tables 'cuz it makes the query easier to
read AND you can refer to tables in different parts of the
query/subqueries by the correct alias.

In my WHERE example replace the '?' in front of MemberID with the alias
of the table in the main query that holds the necessary MemberID column.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQd2Qk4echKqOuFEgEQIldACgwP9mHw0A3y06Dcfsr/Z6XZfXXcoAnAuq
8ScplFGZQM9H8dCF+fC7nCHK
=SRmK
-----END PGP SIGNATURE-----

Hi!

I´m using a pretty large but farly straight forward SELECT quiery as a
recordsource for my main form and until reasentöy have this been working
without any problem.

Today I have tryed to populate my application with the old data that we
have in our old db (apr. 1200 records in one table, 1500 in another and
so on). Not a lot of records as you can see but enough to give me problem
with prestanda of this quiery (see below).

In this quiery I have an expression (see below):

DMax("[MemberShipEndDate]";"tblMemberValidation";"[tblMemberValidation].[fkMemberID]
= " & [MemberID] & " AND [MemberShipEndDate] >= Date()")

< snip >

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The only book I can think of, off the top of my head, is Hernandez's
_SQL Queries for Mere Mortals_ (a copy at this URL):

http://www.overstock.com/cgi
bin/d2.cgi?PAGE=PRODUCT&PROD_ID=193116&fp=F&kid=67245&cid=46822

I've never read it, but I liked his _Database Design for Mere Mortal's_.
Perhaps, this query book is just as good.

A more advanced query book _Joe Celko's SQL For Smarties_ has some good
stuff.

For "everyday" query writing I get a lot of my ideas from this newsgroup
and other's (comp.databases.ms-access and
microsoft.public.sqlserver.programming).

Read books on other RDBMS - Oracle, SQL Server, etc. I've got _Transact
SQL Programming_ by Kline, Gould & Zanevsky; O'Reilly publishing.
_SQL Server 6.5 Programming Unleashed_, published by Sams (probably out
of print).
_The Complete Reference: SQL_ by Groff & Weinberg, published by Osborne
(not that good in query building, but had a CD of trial versions of 5
RDBMS - most recent version has fewer RDBMS).

Good luck.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQd2YWIechKqOuFEgEQLMsACeO3dW4rthiOv9erRa7/G524VdsCgAoKTJ
i/kOZSADYhJ5pJbZC//iQtga
=4KTY
-----END PGP SIGNATURE-----

 

[Niklas Östergren]

Thank´s a lot!

// Niklas

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The only book I can think of, off the top of my head, is Hernandez's
_SQL Queries for Mere Mortals_ (a copy at this URL):

http://www.overstock.com/cgi
bin/d2.cgi?PAGE=PRODUCT&PROD_ID=193116&fp=F&kid=67245&cid=46822

I've never read it, but I liked his _Database Design for Mere Mortal's_.
Perhaps, this query book is just as good.

A more advanced query book _Joe Celko's SQL For Smarties_ has some good
stuff.

For "everyday" query writing I get a lot of my ideas from this newsgroup
and other's (comp.databases.ms-access and
microsoft.public.sqlserver.programming).

Read books on other RDBMS - Oracle, SQL Server, etc. I've got _Transact
SQL Programming_ by Kline, Gould & Zanevsky; O'Reilly publishing.
_SQL Server 6.5 Programming Unleashed_, published by Sams (probably out
of print).
_The Complete Reference: SQL_ by Groff & Weinberg, published by Osborne
(not that good in query building, but had a CD of trial versions of 5
RDBMS - most recent version has fewer RDBMS).

Good luck.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQd2YWIechKqOuFEgEQLMsACeO3dW4rthiOv9erRa7/G524VdsCgAoKTJ
i/kOZSADYhJ5pJbZC//iQtga
=4KTY
-----END PGP SIGNATURE-----