Well, so far my query looks like this
SELECT derived_out.txtManufacturer, derived_in.txtRMA,
derived_in.txtPanelType, derived_out.ReturnedQty, derived_in.RxQ,
derived_in.RxGood, derived_in.RxBad,
derived_in.RxExch, derived_in.logClosed
FROM
(SELECT TOP 100 PERCENT tblReturnedPanels.txtRMA,
tblReturnedPanels.txtPanelType,
SUM(tblReturnedPanelsDetail.intUnverifiedQty) AS RxQ,
SUM(tblReturnedPanelsDetail.intGoodQty) AS RxGood,
SUM(tblReturnedPanelsDetail.intBadQty) AS RxBad,
SUM(tblReturnedPanelsDetail.intExchQty) AS RxExch,
tblReturnedPanels.logClosed
FROM tblReturnedPanels INNER JOIN tblReturnedPanelsDetail
ON
tblReturnedPanels.intRMA_ID =
tblReturnedPanelsDetail.intRmaID
GROUP BY tblReturnedPanels.txtRMA,
tblReturnedPanels.txtPanelType, tblReturnedPanels.logClosed
ORDER BY tblReturnedPanels.txtRMA,
tblReturnedPanels.txtPanelType) AS derived_in
LEFT OUTER JOIN
(SELECT TOP 100 PERCENT txtManufacturer, txtShipRef, txtPanelType,
COUNT(txtPanelType) AS ReturnedQty
FROM tblPanels
GROUP BY txtShipRef, txtPanelType, txtManufacturer
HAVING (NOT (txtShipRef IS NULL))
ORDER BY txtShipRef, txtPanelType) AS derived_out
ON derived_in.txtRMA = derived_out.txtShipRef AND
derived_in.txtPanelType = derived_out.txtPanelType
The number of rows returned by the second sub-query is about 37K, and the
final result is about 5k rows
In the final version it will be further filtered, this is just me trying to
work out my initial dataset
Use of tempdb has long confused me, well at least the few months I have been
involved in SQL it has. There seems to be little guidance about when it
should be used in preference to subqueries as above.
Are there performance issues to be considered, or is it just a preference
thing?
Alec Wood
-----Original Message-----
From: Jaime E. Maccou
[mailto:mssqldba-ezmlmshield-x21654001.[Email address protected]
Sent: 31 March 2008 13:17
To: LazyDBA Discussion
Subject: RE: Derived table, or Tempdb
It depends on many factors
Number of tables involved
Number of rows returned
Data return
Your criteria
Jaime E. Maccou
mailto:Jaime E. Maccou
"Our lives begin to end the day we become silent about things that matter."
- Dr. Martin Luther King, Jr.
"The mind does not take its complexion from the skin..."
- Frederick Douglass
There is always a way to do it better . . . find it!" Thomas A. Edison
DBA Ethical Rules
1. Don’t Lie
2. Don’t Cheat
3. Don’t Steal
4. Treat everyone fairly
5. Treat everyone equally"
-----Original Message-----
From: Alec Wood
[mailto:mssqldba-ezmlmshield-x24296348.[Email address protected]
Sent: Monday, March 31, 2008 5:44 AM
To: LazyDBA Discussion
Subject: Derived table, or Tempdb
Hi All
I have to produce a report monthly for a customer
Doing so will require producing a few temporary tables.
Is it best practice to use derived tables (nested queries) or to create the
tables in tempdb and then run a query on them
Alec Wood
No virus found in this outgoing message.
Checked by AVG.
Version: 7.5.519 / Virus Database: 269.22.1/1350 - Release Date: 30/03/2008
12:32
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
No virus found in this incoming message.
Checked by AVG.
Version: 7.5.519 / Virus Database: 269.22.1/1350 - Release Date: 30/03/2008
12:32
No virus found in this outgoing message.
Checked by AVG.
Version: 7.5.519 / Virus Database: 269.22.1/1350 - Release Date: 30/03/2008
12:32
MS Sql Server LazyDBA home page