HELP Query via ADO never return but in access takes less millisecs

R

RobinFinlow

Please help! Does anyone have any ideas ?

Due to requirement changes I needed to change a query, so I tested it in
access and this query return in millisecs.

However from ADO (Delphi I'm sorry to say) the same query launches and send
my Dual Proc machione into 1 cpu @ 100% and never returns.


SELECT TOP 500 inputdata.*, Specialties.Code, Specialties.DescriptionMain,
Specialties.DescriptionTreatment
FROM (InputData INNER JOIN Specialties ON inputdata.Specialty =
Specialties.Code) INNER JOIN InputData AS InputData_1 ON
(inputdata.AuditInputId = InputData_1.AuditInputId) AND
(inputdata.EpisodeNumber = InputData_1.EpisodeNumber)
WHERE ( (inputdata.AuditInputId=9) and (inputdata.auditrecord=0) and
(InputData_1.Auditrecord=1) );
 
R

RobinFinlow

Correction, the query does return but takes about 20 minutes and correctly
returns 4 records in this case.

In access the query is faster than you can see 0.1 secondss perhaps -
returning the same 4 records..

Inputdata has around 80k records, specialty 1k records. Both are indexed on
join fields.

Cheers
Robin
 
R

RobinFinlow

Michel Walsh said:
Can you post the SQL statement (text) of your query?
Click to expand...
SELECT TOP 500 inputdata.*, Specialties.Code,
Specialties.DescriptionMain,
Specialties.DescriptionTreatment
FROM (InputData INNER JOIN Specialties ON inputdata.Specialty =
Specialties.Code) INNER JOIN InputData AS InputData_1 ON
(inputdata.AuditInputId = InputData_1.AuditInputId) AND
(inputdata.EpisodeNumber = InputData_1.EpisodeNumber)
WHERE ( (inputdata.AuditInputId=9) and (inputdata.auditrecord=0) and
(InputData_1.Auditrecord=1) );
 
M

Michel Walsh

When you say Access returns within few msec, do you mean from its data view
or AFTER a MOVELAST which forces the query to be completed before
continuing. It may be that Access displays stuff, while not having finish
the query, in design data view. With a Movelast, that forces Access to
complete the execution, maybe what Delphi does in the first place.



I would change your query to:


SELECT ...
FROM ((SELECT * FROM inputData
WHERE inputdata.AuditInputId=9
and inputdata.auditrecord=0
) As x INNER JOIN
(SELECT * FROM inputData
WHERE inputdata.AuditinputID=0
AND Auditrecord=1
) As y ON x.audInputrecord = y.audinputrecord
AND x.episodeNumber = y.episodeNumber
) INNER JOIN Specialities AS s
ON x.speciality = s.code


(if I didn't make a typo). The idea is to remove the maximum record out of
consideration BEFORE making the join implying the table inputData. That
should make the query runs faster.




Hoping it may help,
Vanderghast, Access MVP
 
R

RobinFinlow

Cheers for your help, that certainly works.
Interestingly in access it is slightly slower, but from the application it
is way faster!
 
J

Jamie Collins

When you say Access returns within few msec, do you mean from its data view
or AFTER a MOVELAST which forces the query to be completed before
continuing. It may be that Access displays stuff, while not having finish
the query, in design data view. With a Movelast, that forces Access to
complete the execution, maybe what Delphi does in the first place.
Click to expand...

That's the native behaviour of ADO (nothing to do with Delphi) and is
why you can rely on RecordCount (though cursor type etc may cause it
to be -1) without navigating the end of the recordset i.e. you are
thinking in terms of DAO. Remember, in the Access user interface the
query builder tool thing shows its results on a form that uses a *DAO*
recordset, hence the difference in behaviour.

Jamie.

--
 
Top