Max Query Join (1 Viewer)

[DPW88]

Hi, I've got an issue with a slow query. I'm using the MAX function in an initial query (detailed below) to obtain the highest VisitorSignInID - typically this completes within a second. Upon running the second query below to pull in additional field values from the underlying table with approximately 20k records, it takes approximately 20 seconds. Is there a way of speeding up this process?

SELECT VisitorSignInDetailsTBL.NameofVisitor, Max(VisitorSignInDetailsTBL.VisitorSignInID) AS MaxOfVisitorSignInID
FROM VisitorSignInDetailsTBL
GROUP BY VisitorSignInDetailsTBL.NameofVisitor
ORDER BY VisitorSignInDetailsTBL.NameofVisitor;

SELECT VisitorSignInDetailsTBL.NameofVisitor, VisitorSignInDetailsTBL.VehReg, Visitor_Search_Existing_Query_1.MaxOfVisitorSignInID
FROM VisitorSignInDetailsTBL INNER JOIN Visitor_Search_Existing_Query_1 ON VisitorSignInDetailsTBL.VisitorSignInID = Visitor_Search_Existing_Query_1.MaxOfVisitorSignInID
ORDER BY VisitorSignInDetailsTBL.NameofVisitor;

 

[theDBguy]

Perhaps you need to join on both the name and the id instead of just the id alone. Are you getting duplicate records?

 

[tvanstiphout]

Can we assume the usual:
1, When you say ID field, it is a numeric field
2. The tables have a primary key
3. In the Relationships window, enforced relations have been set up between the tables
4. VisitorSignInDetailsTBL.NameofVisitor is indexed
5. You have a decent computer and used Task Manager to confirm there are no CPU, Disk, and Network constraints

 

[DPW88]

Perhaps you need to join on both the name and the id instead of just the id alone. Are you getting duplicate records?

Made a fractional improvement but thanks for the suggestion

 

[DPW88]

Can we assume the usual:
1, When you say ID field, it is a numeric field
2. The tables have a primary key
3. In the Relationships window, enforced relations have been set up between the tables
4. VisitorSignInDetailsTBL.NameofVisitor is indexed
5. You have a decent computer and used Task Manager to confirm there are no CPU, Disk, and Network constraints

1. Yes, Autonumber
2. Both queries based on same table, yes it has PK
3. See 1
4. It is now, took it down to approx 18 seconds, thanks
5. 8 year old PC, 8 GB RAM running on Windows 8.1 - I should've included in my original post that it works within a second on another PC, apologies.

Just noted though it's only on start up and if I run Query 1, Query 2 only takes approximately one second to load. Should I run Query 1 in the background upon opening the database or simply bite the bullet and replace the PC?

 

[Gasman]

And what is the spec of that other PC?

 

[DPW88]

 

[DPW88]

The slow PC is on a network but the speed doesn't improve when both the front and back end are moved to the desktop

 

[CJ_London]

Your first query does not need order by - adds overhead but no benefit

where is the data ? On a local machine? A server?

 

[DPW88]

Data is usually on a network but it doesn't appear to be a contributing factor - placing the backend on the desktop doesn't make a difference.

Removed order by and it reduced by approximately 2 seconds, good shout but performance remains very poor on this particular aspect

 

[Gasman]

That Envy doesn't have an ssd, does it?

 

[CJ_London]

- placing the backend on the desktop doesn't make a difference.

I would expect it to make some difference - presume you relinked to the new location, or are you running these queries in the back end?

 

[CJ_London]

ust noted though it's only on start up

Perhaps you need to give the pc time to make all the connections before running the query

 

[CJ_London]

What is the purpose of this query? Very unusual to want to see 20,000 records. Normally I would expect to want so see todays data, or the last week or for a particular visitor

 

[DPW88]

That Envy doesn't have an ssd, does it?

I think so but I've used a third PC that doesn't and performance is similar

 

[DPW88]

I would expect it to make some difference - presume you relinked to the new location, or are you running these queries in the back end?

Yes, re-linked and simply testing on the slow PC desktop. Queries are located in the front-end

 

[DPW88]

Perhaps you need to give the pc time to make all the connections before running the query

I give it a try - if I run the first query in the background, in theory it should pull in the second query more quickly. Perhaps an issue with Windows 8 or slower PC spec all round

 

[DPW88]

What is the purpose of this query? Very unusual to want to see 20,000 records. Normally I would expect to want so see todays data, or the last week or for a particular visitor

Pulls in data for visitors that haven't signed in for some considerable amount of time. I've played around and deleted the majority of the records and it now takes 4 seconds - sacrifices functionality for what is still sub-standard performance.

 

[CJ_London]

Pulls in data for visitors that haven't signed in for some considerable amount of time.

‘Considerable amount of time’ is a bit vague.

you can reduce by having query 1 being

select distinct visitorname
From visitortbl
Where signindate >dateadd(‘yyyy’, -1,date())

which gives you all the visitors who sign in the las 12 months

then left join that to your visitor table to determine all the other users

 

[DPW88]

‘Considerable amount of time’ is a bit vague.

you can reduce by having query 1 being

select distinct visitorname
From visitortbl
Where signindate >dateadd(‘yyyy’, -1,date())

which gives you all the visitors who sign in the las 12 months

then left join that to your visitor table to determine all the other users

Previously, the amount of data was since the last archive (start of 2022). I've whittled it down to 180 days as per above suggestion and although it's not ideal (approx 7 seconds), it is a bit more palatable until the hardware is upgraded. Thanks CJ