On Tue, Mar 31, 2009 at 2:36 PM, Matt Jarvis <[email protected]> wrote: >> -----Original Message----- >> From: [email protected] >> [mailto:[email protected]] On Behalf Of Stephen Russell >> Sent: Tuesday, March 31, 2009 10:31 AM >> To: [email protected] >> Subject: Re: SQL Server 2K - Index Tuning Wizard >> >> >> Getting a pricey tool for server management will tell you >> beyond all doubts that some of your process are causing >> problems. Is it Inserts with index problems on a specific >> table? Do you have a transaction that is locking tables within a SP? > > (DISCLAIMER: I didn't write any of this code) > > A Scheduled Job fires off a job where one of the steps is a stored > procedure which looks like: > > --------------------------------------------------------------- > spCustSummarizeShippers: > > CREATE proc dbo.spCustSummarizeShippers > as > delete from Shippers > > INSERT INTO Shippers > ( Prodclass, [Date], > Shipper, ShiptoID, [SO#], > [Invoiced?], [Quoted$], > [Shipped $], [Invoiced $] ) > > SELECT * FROM vCustSummarizeShippers > > If @@Error = 0 > select 0 > else > select @@Error > GO > > --------------------------------------------------------------- > Definition of vCustSummarizeShippers: > > CREATE View dbo.vCustSummarizeShippers > as > SELECT > soitem.fprodcl AS Prodclass, > sorels.fduedate AS [Date], > shmast.fshipno AS Shipper, > shmast.fshptoaddr AS ShiptoID, > shmast.fcsono AS [SO#], > 0 AS [Invoiced?], > Round( > SUM([sorels].[FORDERQTY]*[FUNETPRICE]),2 ) AS [Quoted$], > Round ( > Sum([FSHIPQTY] *[FUNETPRICE]), 2) AS [Shipped $], > Round ( > > Sum(sorels.finvamount),2) AS [Invoiced $] > > > FROM > > m2mdata01.dbo.soitem soitem > INNER JOIN (( > m2mdata01.dbo.shitem shitem > INNER JOIN m2mdata01.dbo.shmast shmast > ON > shitem.fshipno = shmast.fshipno) > INNER JOIN m2mdata01.dbo.sorels sorels > ON shmast.fcsono = sorels.fsono) > ON (soitem.fsono = sorels.fsono) > AND (soitem.finumber = sorels.finumber) > > WHERE > shmast.fconfirm='Y' > AND sorels.finumber=substring([FSOKEY],7,3) > AND shmast.fcsono IS NOT NULL > > GROUP BY > soitem.fprodcl, > sorels.fduedate, > shmast.fshipno, > shmast.fshptoaddr, > shmast.fcsono > --------------------------------------------------------------- > > Basically the Index Tuning Wizard just wants to do indexes in the > obvious places, where they already exist.... > ------------------------------
So what is wrong with the view? I see this line as a possible fixer AND sorels.finumber=substring([FSOKEY],7,3) I would consider making a computed column for the substring([FSOKEY],7,3) and make that part of an existing index already involved in the JOIN between tables. That will generate a tablescan doing the substring comparison. -- Stephen Russell Sr. Production Systems Programmer Web and Windows Development Independent Contractor Memphis TN 901.246-0159 _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[email protected] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
