> -----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....
Matt Jarvis
Programmer/DBA
King | Retail Solutions
Electronic Privacy Notice. This e-mail, and any attachments, contains
information that is, or may be, covered by electronic communications privacy
laws, and is also confidential and proprietary in nature. If you are not the
intended recipient, please be advised that you are legally prohibited from
retaining, using, copying, distributing, or otherwise disclosing this
information in any manner. Instead, please reply to the sender that you have
received this communication in error, and then immediately delete it. Thank you
in advance for your cooperation.
_______________________________________________
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.
