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

Discussion in 'ASP General' started by Jules, Jul 14, 2003.

  1. Jules

    Jules Guest

    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));
    Jules, Jul 14, 2003
    #1
    1. Advertising

  2. 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.
    Andrew Durstewitz, Jul 14, 2003
    #2
    1. Advertising

  3. Jules

    Ray at Guest

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

    Ray at work

    "Jules" <> wrote in message
    news:...
    > 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.
    Ray at, Jul 14, 2003
    #3
  4. Jules

    Bob Barrows Guest

    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)=38));
    Bob Barrows, Jul 14, 2003
    #4
  5. Jules

    Chris Barber Guest

    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" <> wrote in message
    news:...
    > 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
    >
    >
    >
    Chris Barber, Jul 14, 2003
    #5
  6. >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.
    Andrew Durstewitz, Jul 15, 2003
    #6
  7. Jules

    Jules Guest

    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!
    Jules, Jul 15, 2003
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. DC Gringo
    Replies:
    2
    Views:
    2,283
    Nikhil Patel
    Feb 26, 2004
  2. yezi

    Break the string into different parts

    yezi, Oct 3, 2005, in forum: C Programming
    Replies:
    3
    Views:
    286
    John Bode
    Oct 3, 2005
  3. Replies:
    12
    Views:
    933
  4. Alec Taylor
    Replies:
    0
    Views:
    124
    Alec Taylor
    Oct 11, 2011
  5. Alec Taylor
    Replies:
    5
    Views:
    192
    alex23
    Oct 15, 2011
Loading...

Share This Page