Can Jet handle this? Access crashes with this query.

[darbid]

I have someone helping me with ZERO Jet experience, but lots of oracle experience.

There are 3 tables. SRF - CONNECT - EM

The idea is to get the latest decision_date from SRF, but if that is not available because there are only rows returned from the EM table then the latest decision_date from it instead.

What I have below cause access to crash. Each table has about 10,000 rows in it at the moment and the CONNECT about 20,000.

Please forgive me if I have not set this out properly.

SELECT srf1.akz, srf1.decision_number, srf1.decision_date
FROM SRF as srf1
WHERE srf1.decision_date = (SELECT max(srf1.decision_date) 
                                                        FROM SRF srf2 
                                                        WHERE srf1.akz = srf2.akz 
                                                        GROUP by srf2.akz)
UNION SELECT em1.akz, em1.decision_number, em1.decision_date
FROM EM AS em1
WHERE em1.decision_date = (SELECT max(em1.decision_date) 
                                                        FROM EM em2 
                                                        Where em1.akz = em2.akz 
                                                        GROUP BY em2.akz) 
                                                        and not exists (SELECT 1 
                                                                        FROM CONNECT jt 
                                                                        WHERE em1.akz = jt.em_akz)
ORDER BY srf1.decision_date;

I would appreciate some help.

 

[vbaInet]

It should be able to, but SELECT 1 should be SELECT TOP 1.

 

[darbid]

It should be able to, but SELECT 1 should be SELECT TOP 1.

Access then throws a syntax error for the 2 sub selects which I do not understand.

 

[vbaInet]

Either one of:

and not (exists (SELECT TOP 1 
                 FROM CONNECT jt 
                 WHERE em1.akz = jt.em_akz))


and (exists (SELECT TOP 1 
             FROM CONNECT jt 
             WHERE em1.akz = jt.em_akz)) = False

 

[darbid]

I will give it a go. I think I pulled the "Thread" trigger a little early. I think I have some fundamental problems in what I want to do.

I will come back to you in a day or two.

Thank you.

 

[vbaInet]

Oki doki darbid. I'll keep an eye out.

 

[darbid]

hi vbaInet,

We did a few little changes, but it simply crashed or would take 30min to run. In the end we added a few indexes and it now is running in about 10 seconds. So right now this thread is complete.

But as I am not good at SQL I will be back.

Thank you for your help.

 

[vbaInet]

Happy to hear all is well now.

 

[LPurvis]

Glad it's sorted. Just a couple of points.

Certainly Indexes would have been the most important consideration to address. Thankfully they have been.
Jet itself does tend to better optimise ANSI joins to Non-ANSI (also known as Theta) joins. i.e. in the FROM clause rather than WHERE.
Although it's not a hard and fast rule, it's worth trying at times when you're looking for the best performance. (I know that should be always - but particularly when you're trying to get 30 mins down too 10 secs. ;-)
In this case I'd expect little if any difference. (Not In comparisons are terrible and a Frustrated Outer Join usually wins - though Not EXISTS does actually optimise well.)

For example:

SELECT srf1.akz, srf1.decision_number, srf1.decision_date
FROM SRF srf1 
    INNER JOIN (SELECT srf1.decision_date, Max(srf2.decision_date) As MaxDate
                     FROM SRF srf2 
                     GROUP by srf2.akz) X
    ON srf1.decision_date = X.MaxDate AND srf1.akz = X.akz 

UNION 
SELECT em1.akz, em1.decision_number, em1.decision_date
FROM EM em1 INNER JOIN (SELECT Max(em2.decision_date) As MaxDate
                                                       FROM EM em2 
                                                       GROUP BY em2.akz) Y
WHERE em1.decision_date = Y.MaxDate AND em1.akz = Y.akz 
AND Not EXISTS (SELECT 1 
                        FROM CONNECT jt 
                        WHERE em1.akz = jt.em_akz)
ORDER BY decision_date

I'm not surprised you ended up with the statement you had. Oracle prefers the Non-ANSI join as I understand it (I can reproduce my Oracle knowledge on the back of a postage stamp). SQL Server tends not to have a stark preference.

As mentioned, occasionally this isn't the case even for Jet. For example I've left the Not EXISTS test rather than construct a frustrated outer join.

Oh and on the EXISTS front - the query was indeed OK as is.
The "SELECT 1" wasn't making any attempt to select the number of rows.
"SELECT TOP 1 FROM TableName" would actually fail to parse as there must be fields specified.
When testing existence of a row, the actual value returned is irrelevant.
Hence a lot of developers will perform a Select upon a fixed dummy value, e.g. 1 (I tend to use Null myself.)
You can have
WHERE Not EXISTS (SELECT 'A great big house' FROM TableName...
and it matters not.

Finally - if you're wanting duplicates eliminated, then great. Otherwise UNION ALL will always outperform UNION.

Cheers.

 

[vbaInet]

A much optimised query Leigh. I didn't even think to convert that into an INNER JOIN.

Oh and on the EXISTS front - the query was indeed OK as is.
The "SELECT 1" wasn't making any attempt to select the number of rows.
"SELECT TOP 1 FROM TableName" would actually fail to parse as there must be fields specified.

I missed the fact that they were no fields being selected