[thelist] New Products Query (WAS: Query Execution Tim...)
Rob Smith
rob.smith at lexjet.com
Tue May 2 08:59:22 CDT 2006
> But, we have no idea what Rob's actually trying to do (surprise)...
Fair enough. I'm running the following query that gets a list of all
products ordered by customer X for the first time. Is there a better way
to do this? I've scoured the internet for a short while and no luck:
SELECT ISNULL(lexjet_shopper.Company, lexjet_shopper.ContactFName + ' '
+ lexjet_shopper.ContactLName) AS Customer, lexjet_shopper.ContactFName
AS [First Name], lexjet_shopper.ContactLName AS [Last Name],
lexjet_shopper.commission_id AS REP, MIN(lexjet_receipt.order_num) AS
[INV #], MIN(lexjet_receipt.date_created) AS [First Order Date],
lexjet_region.region_value + ' ' + lexjet_segment.segment_value AS
Segment, lexjet_shopper.Market_Segment, lexjet_product.name AS Name
FROM lexjet_shopper INNER JOIN lexjet_receipt ON
lexjet_shopper.shopper_id = lexjet_receipt.shopper_id AND
lexjet_receipt.date_created >
(SELECT TOP 1 date_created FROM lexjet_receipt AS l_receipt WHERE
(l_receipt.shopper_id = lexjet_receipt.shopper_id) ORDER BY
date_created) INNER JOIN lexjet_region ON lexjet_shopper.region_id =
lexjet_region.region_id INNER JOIN lexjet_segment ON
lexjet_shopper.segment_id = lexjet_segment.segment_id INNER JOIN
lexjet_receipt_lineitem ON lexjet_receipt.order_id =
lexjet_receipt_lineitem.order_id INNER JOIN lexjet_product ON
lexjet_receipt_lineitem.pf_id = lexjet_product.pf_id AND
lexjet_receipt_lineitem.pf_id NOT IN
(SELECT l_receipt_item.pf_id
FROM lexjet_receipt_lineitem l_receipt_item INNER JOIN
lexjet_receipt l_receipt_3 ON l_receipt_item.order_id =
l_receipt_3.order_id AND l_receipt_item.order_id =
(SELECT TOP 1 order_id
FROM lexjet_receipt AS l_receipt_2
WHERE (l_receipt_2.shopper_id = lexjet_receipt.shopper_id)
ORDER BY date_created)
WHERE (l_receipt_3.shopper_id = lexjet_receipt.shopper_id))
GROUP BY lexjet_shopper.Company, lexjet_shopper.ContactFName,
lexjet_shopper.ContactLName, lexjet_shopper.commission_id,
lexjet_region.region_value, lexjet_segment.segment_value,
lexjet_shopper.Market_Segment, lexjet_product.name,
lexjet_shopper.shopper_id, lexjet_shopper.shopper_id,
lexjet_receipt.status
HAVING (lexjet_shopper.commission_id = 210) AND
(MIN(lexjet_receipt.date_created) > CONVERT(DATETIME, '2002-01-01
00:00:00', 102)) AND (lexjet_receipt.status <> 'Cancelled')
ORDER BY lexjet_shopper.Company, lexjet_shopper.ContactLName,
lexjet_shopper.ContactFName, [Inv #]
This query takes seconds to run in Excel, but will run what seems like
"forever" in SQL Server 2k/2005.
Do I use indexing? Not that I can tell
Do I use full-text catalogs? None that I know of
... full-text catalogs work great if they're constantly updated and
repopulated. It'd be neat to update them as new information arrives...
Computer Specs? The production is a dual-core 4GB, but is taxed as is so
I'm running this off a replicated 2005 box. It's a "Intel Xeon CPU
2.80Ghz, 2.79 Ghz, 4.00 GB SQL 2005 box dedicated to SRS, Data Mining,
SSIS, and Analysis Services and whatever else I can throw at it for
other data analysis type calculations.
Rob Smith
LexJet
rob.smith at lexjet.com
http://www.lexjet.com
(800)453-9538
(941)330-1210 Int'l
(941)330-1220 Fax
1680 Fruitville Road, 3rd Floor
Sarasota, FL 34236
More information about the thelist
mailing list