Newbie SQL question: Break SQL into different SELECTs or keep as one

[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));

 

[Andrew Durstewitz]

I didn't really read the whole select statement but can't you just do a
select * ? Is there a specific reason that you are creating this long
of a string?

-Andrew

* * * Sent via DevBuilder http://www.devbuilder.org * * *
Developer Resources for High End Developers.

 

[Ray at]

I'd do it all in one statement. Perhaps you should consider using a stored
procedure.

Ray at work

 

[Bob Barrows]

26 visits to the database opposed to 1? 26 recordsets vs. 1? Is there
really any question? Don't be silly! Of course you should make a single
trip to the database!!!

You can shorten this SQL string by using a short alias for each table in
your FROM clause. All those INFORMATION_HOLDING's really make the statement
very hard to read (for me at least), and increase the number of characters
you're sending across the network.

This looks like an Access query. If so, you should use a saved query - that
would really cut down on the network traffic!
Create a query in your Access database using the SQL you've already put
together. Save it, giving it a descriptive name such as "qGetData" ;-)

In asp, call it like this (cn is an already opened connection object):

set rs = createobject("adodb.recordset")
cn.qGetData rs

Simple, huh? If you use a parameter like this:
WHERE INFORMATION_HOLDING.INFORMATION_HOLDING_ID=[p1];

You can call it like this:
set rs = createobject("adodb.recordset")
cn.qGetData 38, rs

or, if you're getting the value from a form submission:
parmval=request.form("txtHoldingID")
set rs = createobject("adodb.recordset")
cn.qGetData parmval, rs

HTH,
Bob Barrows

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]![ABST
RACT_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_dat
e],"Single
Date: ","Date Range: From: ") AS qry_time_from_txt,
INFORMATION_HOLDING.BEGINNING_DATE,
IIf([INFORMATION_HOLDING]![beginning_date]=[INFORMATION_HOLDING]![ending_dat
e],"","
To: ") AS qry_time_to_txt,
IIf([INFORMATION_HOLDING]![beginning_date]=[INFORMATION_HOLDING]![ending_dat
e],"",[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));

 

[Chris Barber]

Its also a damn site faster to use specific field names - SQL doesn't have
to query the master for the field list and generate them itself.

26 out of 50+ fields will return in approx. 1/2 the time Vs. the select *
scenario (based on similar field lengths of course).

Chris.

 

[Andrew Durstewitz]

Oh, Andrew, don't steer him the wrong way man! :]

Ray at work

Sorry, I misread what he was trying to do. Today hasn't been a very
good "programming" Munday...

My bad...

-Andrew

* * * Sent via DevBuilder http://www.devbuilder.org * * *
Developer Resources for High End Developers.

 

[Jules]

Yes, this is a SQL string from an Access query. I will be putting the
Access db on our web server and then pulling down a series of related
fields using that SQL string. The string I posted is just for one record
which will be very long (about 10 printed pages from the HTML page that
will be configured from it). You will notice that the SQL string ends
with (abbreviated) WHERE ID=38 which will be replaced by a variable so
that the same information for a different ID can be pulled down with
http://example.com/data.asp?ID=38.

I will try to clean it up to make it easier to separate into the 26
different pieces.

Many thanks for everyone's contribution,

Jules