When you send a query to the DB engine, the engine looks at the query and
decides what order is most efficient for pulling info from the tables and
then it gets the info. It does this every time you submit a query. The DB
engine also does this when the view is created. The main difference is that
it only has to do it once for the view. So, all queries on the vew will be
a tiny bit faster than 'raw' queries passed to the engine since the engine
won't have to perform as many calcualtions.
Also, if you are writing the same complexly joined queries over and over,
creating a view could save some typing time and, like you said, make your
queries a lot easier to read as well. :)
Todd Ashworth
Saber Corporation
(803) 327-0137 [111]
----- Original Message -----
From: "Daniel Lancelot" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Tuesday, January 30, 2001 7:25 AM
Subject: RE: SQL - concatenate subquery into one column?
| On a related note...
|
| I quite often use subqueries within a query, and was wondering if their
was
| any performance advantage (other than the obvious readability advantages)
of
| doing this within a view (and using aliases etc) or doing it within
| cfquery...
|
| I use SQL server.
|
| cheers
|
| Dan.
|
| -----Original Message-----
| From: David Shadovitz [mailto:[EMAIL PROTECTED]]
| Sent: 30 January 2001 07:00
| To: CF-Talk
| Subject: Re: SQL - concatenate subquery into one column?
|
|
| Hey, how's that salmon recovery coming along?
|
| What RDBMS are you using? Oracle supports an inline view. Your main
| query would be simply a view in the dept query. The cool thing is that
| the view can be used in the select statement. Something like this:
|
| <cfquery name="dept_query">
| SELECT Department, ID
| FROM a couple tables, (select ID, Number, Title, Status, etc. FROM a
| couple of tables WHERE etc.)
| WHERE etc.
| </cfquery>
|
| -David
|
|
| On Mon, 29 Jan 2001 15:59:43 -0800 "Maia, Eric" <[EMAIL PROTECTED]>
| writes:
| > In an Intranet application I'm working on, I have a main page that
| > presents
| > summary information in a long table. I have one SQL query that gets
| > the bulk
| > of the information (3 to 6 columns). However, in one of the columns,
| > I
| > provide a list of which departments relate to that record.
| >
| > Currently, I loop over the main query and make a separate department
| > query
| > for each record, then load the ValueList of that query into a
| > structure for
| > display in the appropriate column. This is very slow, since each
| > time a
| > record is added, it means another query for this page.
| >
| > I'd like to add this column as a subquery of the main query, but I
| > don't
| > know how I can concatenate the results of the subquery to get
| > multiple
| > values into one column.
| >
| > Any suggestions?
| > Thanks - Eric
| >
| > Here's the basic logic:
| >
| > <cfquery name="main_query">
| > SELECT ID, Number, Title, Status, etc.
| > FROM a couple of tables
| > WHERE etc.
| > </cfquery>
| >
| > <cfset stDepts= StructNew()>
| >
| > <cfloop query="main_query">
| > <cfquery name="dept_query">
| > SELECT Department
| > FROM a couple tables
| > WHERE ID = #main_query.ID#
| > etc.
| > </cfquery>
| > <cfset stDepts[main_query.ID] =
| > ValueList(dept_query.Department)>
| > </cfloop>
|
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
