J
Jules
Hi:
I have to grab a number of fields (26) from a database on our website
and the SELECT statement is very long (see below).
What I am more comfortable with doing is a SELECT for each item (I
think I can do that quite easily) but it means that I have to issue 26
select statements to the database. What method will result in less
drain on the server: one large SELECT or a series of smaller SELECTs?
Any other comments would be appreciated.
Thanks,
Jules
------- Select statement below
SELECT INFORMATION_HOLDING.INFORMATION_HOLDING_ID,
[INFORMATION_HOLDING]![TITLE_NAME] & " (" &
[INFORMATION_HOLDING]![ACRONYM_NAME] & ")" AS qry_report_title,
Left([INFORMATION_HOLDING]![ABSTRACT_DESCR],IIf(InStr([INFORMATION_HOLDING]![ABSTRACT_DESCR],"
(author")>0,InStr([INFORMATION_HOLDING]![ABSTRACT_DESCR],"
(author")-1,0)) AS qry_author,
Right([INFORMATION_HOLDING]![ABSTRACT_DESCR],Len([INFORMATION_HOLDING]![ABSTRACT_DESCR])-4-InStr([INFORMATION_HOLDING]![ABSTRACT_DESCR],")"))
AS qry_abstract, INFORMATION_HOLDING.PURPOSE_DESCR,
IIf(IsNull([INFORMATION_HOLDING]![access_constraint_descr]),"No access
constraints",[INFORMATION_HOLDING]![access_constraint_descr]) AS
qry_access_constr, INFORMATION_HOLDING.USE_CONSTRAINT_DESCR,
IIf([INFORMATION_HOLDING]![beginning_date]=[INFORMATION_HOLDING]![ending_date],"Single
Date: ","Date Range: From: ") AS qry_time_from_txt,
INFORMATION_HOLDING.BEGINNING_DATE,
IIf([INFORMATION_HOLDING]![beginning_date]=[INFORMATION_HOLDING]![ending_date],"","
To: ") AS qry_time_to_txt,
IIf([INFORMATION_HOLDING]![beginning_date]=[INFORMATION_HOLDING]![ending_date],"",[INFORMATION_HOLDING]![ending_date])
AS qry_time_to_period, INFORMATION_HOLDING.TIME_COVERAGE_COMMENT,
INFORMATION_HOLDING.PROGRESS_DESCR,
INFORMATION_HOLDING.MAINT_UPDATE_FREQUENCY_DESCR,
INFORMATION_HOLDING.GEOGRAPHIC_COMPLETENESS_DESCR,
INFORMATION_HOLDING.GRID_COORDINATE_SYSTEM_NAME,
INFORMATION_HOLDING.MAP_PROJECTION_NAME,
INFORMATION_HOLDING.HORIZ_GEODETIC_DATUM_NAME,
INFORMATION_HOLDING.ALTITUDE_DATUM_NAME,
INFORMATION_HOLDING.FEATURE_HORIZ_POS_ACCRY_DESCR,
INFORMATION_HOLDING.FEATURE_VERT_POS_ACCRY_DESCR,
INFORMATION_HOLDING.METADATA_CURRENCY_DATE,
INFORMATION_HOLDING.METADATA_REVIEW_DATE,
INFORMATION_HOLDING.ADDL_METADATA_LOCATION_DESCR,
INFORMATION_HOLDING.ADDL_METADATA_POINTER_DESCR, "The geographic
extent of this record is defined by " &
IIf(Left([USER_LAYER_NAME],2)="To","the following
Township(s)",IIf(Left([USER_LAYER_NAME],2)="Pr","the boundaries of the
Province of Ontario",IIf(Left([USER_LAYER_NAME],2)="Re","the following
Region(s) and/or County(ies)",IIf(Left([USER_LAYER_NAME],2)="PO","a
user-defined polygon","the following rectangular bounding
coordinates")))) & "." AS Geofeature_type
FROM INFORMATION_HOLDING LEFT JOIN PRIMARY_GEOFEATURE ON
INFORMATION_HOLDING.INFORMATION_HOLDING_ID =
PRIMARY_GEOFEATURE.INFORMATION_HOLDING_ID
WHERE (((INFORMATION_HOLDING.INFORMATION_HOLDING_ID)=38));
I have to grab a number of fields (26) from a database on our website
and the SELECT statement is very long (see below).
What I am more comfortable with doing is a SELECT for each item (I
think I can do that quite easily) but it means that I have to issue 26
select statements to the database. What method will result in less
drain on the server: one large SELECT or a series of smaller SELECTs?
Any other comments would be appreciated.
Thanks,
Jules
------- Select statement below
SELECT INFORMATION_HOLDING.INFORMATION_HOLDING_ID,
[INFORMATION_HOLDING]![TITLE_NAME] & " (" &
[INFORMATION_HOLDING]![ACRONYM_NAME] & ")" AS qry_report_title,
Left([INFORMATION_HOLDING]![ABSTRACT_DESCR],IIf(InStr([INFORMATION_HOLDING]![ABSTRACT_DESCR],"
(author")>0,InStr([INFORMATION_HOLDING]![ABSTRACT_DESCR],"
(author")-1,0)) AS qry_author,
Right([INFORMATION_HOLDING]![ABSTRACT_DESCR],Len([INFORMATION_HOLDING]![ABSTRACT_DESCR])-4-InStr([INFORMATION_HOLDING]![ABSTRACT_DESCR],")"))
AS qry_abstract, INFORMATION_HOLDING.PURPOSE_DESCR,
IIf(IsNull([INFORMATION_HOLDING]![access_constraint_descr]),"No access
constraints",[INFORMATION_HOLDING]![access_constraint_descr]) AS
qry_access_constr, INFORMATION_HOLDING.USE_CONSTRAINT_DESCR,
IIf([INFORMATION_HOLDING]![beginning_date]=[INFORMATION_HOLDING]![ending_date],"Single
Date: ","Date Range: From: ") AS qry_time_from_txt,
INFORMATION_HOLDING.BEGINNING_DATE,
IIf([INFORMATION_HOLDING]![beginning_date]=[INFORMATION_HOLDING]![ending_date],"","
To: ") AS qry_time_to_txt,
IIf([INFORMATION_HOLDING]![beginning_date]=[INFORMATION_HOLDING]![ending_date],"",[INFORMATION_HOLDING]![ending_date])
AS qry_time_to_period, INFORMATION_HOLDING.TIME_COVERAGE_COMMENT,
INFORMATION_HOLDING.PROGRESS_DESCR,
INFORMATION_HOLDING.MAINT_UPDATE_FREQUENCY_DESCR,
INFORMATION_HOLDING.GEOGRAPHIC_COMPLETENESS_DESCR,
INFORMATION_HOLDING.GRID_COORDINATE_SYSTEM_NAME,
INFORMATION_HOLDING.MAP_PROJECTION_NAME,
INFORMATION_HOLDING.HORIZ_GEODETIC_DATUM_NAME,
INFORMATION_HOLDING.ALTITUDE_DATUM_NAME,
INFORMATION_HOLDING.FEATURE_HORIZ_POS_ACCRY_DESCR,
INFORMATION_HOLDING.FEATURE_VERT_POS_ACCRY_DESCR,
INFORMATION_HOLDING.METADATA_CURRENCY_DATE,
INFORMATION_HOLDING.METADATA_REVIEW_DATE,
INFORMATION_HOLDING.ADDL_METADATA_LOCATION_DESCR,
INFORMATION_HOLDING.ADDL_METADATA_POINTER_DESCR, "The geographic
extent of this record is defined by " &
IIf(Left([USER_LAYER_NAME],2)="To","the following
Township(s)",IIf(Left([USER_LAYER_NAME],2)="Pr","the boundaries of the
Province of Ontario",IIf(Left([USER_LAYER_NAME],2)="Re","the following
Region(s) and/or County(ies)",IIf(Left([USER_LAYER_NAME],2)="PO","a
user-defined polygon","the following rectangular bounding
coordinates")))) & "." AS Geofeature_type
FROM INFORMATION_HOLDING LEFT JOIN PRIMARY_GEOFEATURE ON
INFORMATION_HOLDING.INFORMATION_HOLDING_ID =
PRIMARY_GEOFEATURE.INFORMATION_HOLDING_ID
WHERE (((INFORMATION_HOLDING.INFORMATION_HOLDING_ID)=38));