[thelist] New Products Query (WAS: Query Execution Tim...)

Ken Schaefer Ken at adOpenStatic.com
Tue May 2 20:49:48 CDT 2006


You need to look at:
a) indexes
and
b) DB statistics (whether they are up-to-date or not). 

That is pretty much all set-based calculation, and it should run just fine
provided your server isn't already overloaded.

:  Do I use indexing? Not that I can tell

As a general rule, you'd want clustered indexes on your primary key values,
especially if they are "identity" type columns where new records you add
aren't intermingled with existing record values (which would result in page
splitting).

Again, look at your query execution plan. If you have excessive table scans,
you will want to investigate adding indexes to potentially avoid that.

Cheers
Ken

--
My IIS Blog: www.adOpenStatic.com/cs/blogs/ken
Tech.Ed Boston 2006 See you there: Everything the web administrator needs to
know about MOM 2005

:  -----Original Message-----
:  From: thelist-bounces at lists.evolt.org [mailto:thelist-
:  bounces at lists.evolt.org] On Behalf Of Rob Smith
:  Sent: Tuesday, 2 May 2006 11:59 PM
:  To: thelist at lists.evolt.org
:  Subject: [thelist] New Products Query (WAS: Query Execution Tim...)
:  
:  > 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
:  
:  --
:  
:  * * Please support the community that supports you.  * *
:  http://evolt.org/help_support_evolt/
:  
:  For unsubscribe and other options, including the Tip Harvester
:  and archives of thelist go to: http://lists.evolt.org
:  Workers of the Web, evolt !



More information about the thelist mailing list