Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Newbie SQL question: Break SQL into different SELECTs or keep as one

Reply
Thread Tools

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

 
 
Jules
Guest
Posts: n/a
 
      07-14-2003
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)=3 );
 
Reply With Quote
 
 
 
 
Andrew Durstewitz
Guest
Posts: n/a
 
      07-14-2003
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.
 
Reply With Quote
 
 
 
 
Ray at
Guest
Posts: n/a
 
      07-14-2003
I'd do it all in one statement. Perhaps you should consider using a stored
procedure.

Ray at work

"Jules" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed) om...
> 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, --- trimmed excessively long query.



 
Reply With Quote
 
Bob Barrows
Guest
Posts: n/a
 
      07-14-2003
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

Jules wrote:
> 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)=3 );



 
Reply With Quote
 
Chris Barber
Guest
Posts: n/a
 
      07-14-2003
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.

"Bob Barrows" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Andrew Durstewitz wrote:
> > 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?
> >

> What if there are 100 fields in these two tables? Should he pull all 100
> across the wire when he only needs 26 of them?
>
> And even if there were only 27 fields between the two tables, using select

*
> would result in retrieving two columns containing the same data:

HOLDING_ID.
> This is wasteful if you ask me ... (I know: you didn't ask me )
>
> Bob "No selstar in production code" Barrows
>
>
>



 
Reply With Quote
 
Andrew Durstewitz
Guest
Posts: n/a
 
      07-15-2003
>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.
 
Reply With Quote
 
Jules
Guest
Posts: n/a
 
      07-15-2003
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


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Re: Python library for generating SQL queries [selects, alters,inserts and commits] Alec Taylor Python 5 10-15-2011 02:44 AM
Python library for generating SQL queries [selects, alters, insertsand commits] Alec Taylor Python 0 10-11-2011 12:08 PM
`if (!p ? i++ : 0) break;' == `if (!p){ i++; break;}' ? lovecreatesbea...@gmail.com C Programming 12 04-14-2008 07:59 AM
Break the string into different parts yezi C Programming 3 10-03-2005 06:24 PM
Filling two datagrids from two SELECTS in one DataSet DC Gringo ASP .Net 2 02-26-2004 09:56 PM



Advertisments