Frank Schönheit - Sun Microsystems Germany wrote:
The difference between ElementaryQuery and Query is as follows:
composer.Query = "SELECT * FROM table WHERE field = value"
MsgBox composer.Filter
' => "field = value"
composer.ElementaryQuery = "SELECT * FROM table WHERE field = value"
MsgBox composer.Filter
' => <empty string>
Thank you Frank,
That is one big difference there, and solves a dumb mistake on my part.
Here is an example of the BIG difference.
I have an existing Query Definition in a database. The query simply
joins 3 tables does prep work for both a search style form and a report.
The master table here is Patient and has 1003 records.
The baseline SQL in the query is:
SELECT
< columns >
FROM "MedDoctor", "Patient", "Psychiatrist"
WHERE "MedDoctor"."ID_Number" = "Patient"."MedDoctor_ID"
AND "Psychiatrist"."ID_Number" = "Patient"."Psychiatrist_ID"
So, I want to filter this base result set at runtime.
If I do this.( almost the real code )
Sub onMedDoctorListStatusChange( oEvent as object )
dim conn
dim qryDef
dim sComposer
conn = getConnection( "Clinic" )
qryDef = conn.queries.getByName( "qryPatientRpt" )
sComposer =
conn.createinstance("com.sun.star.sdb.SingleSelectQueryComposer")
sComposer.Query = qryDef.Command
sComposer.Filter = "MedDoctor.ID_Number = 1"
UpdateFrmPatientList( sComposer.Query )
sComposer.dispose
conn.dispose
End Sub
The result is a result set of 9,087 records and takes about 13 seconds
to run.
Whereas this:
Sub onMedDoctorListStatusChange( oEvent as object )
...
...
sComposer.ElementaryQuery = qryDef.Command
sComposer.Filter = "MedDoctor.ID_Number = 1"
UpdateFrmPatientList( sComposer.Query )
...
...
End Sub
returns 156 records in < 1 second.
Why?
The first example changes the SQL to:
SELECT
< columns >
FROM "MedDoctor", "Patient", "Psychiatrist" WHERE
"MedDoctor"."ID_Number" = 1
and the latter to
SELECT
< columns >
WHERE ( "MedDoctor"."ID_Number" = "Patient"."MedDoctor_ID" AND
"Psychiatrist"."ID_Number" = "Patient"."Psychiatrist_ID" )
AND ( "MedDoctor"."ID_Number" = 1 )
Repeat after me - Cartesian Result Sets are BAD...usually.
Till senere,
Drew
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
