keys or Indexes ??

Last Post 30 Jun 2009 06:39 AM by JASBRYDEN. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
JASBRYDEN
New Member
New Member

--
26 Jun 2009 10:48 AM
I have a products table which I update.

Currently I have no keys on this table or any Indexes.

THis process takes about an hour to complete.

What do you think I should do and how to go about it.

Should I add keys or should I add Indexes......please help




INSERT INTO Products
(CompanyNo, DivisionNo, ClassNo, Sku, Division, Class, Category, Brand, Description, Description2, Description3, WeightPerCase, ICSUM, ICOUM, ConversionFactor, QuantityPerParent, StatusFlag, TaxFlag)
SELECT VINITMB.IFCOMP, VINITEM.ICDIV, VINITEM.ICCLS, VINITEM.ICITEM, VARDIVS.RODNAM, VARDICL.RNDCNM, VINUDEF.CCF003, VINUDEF.CCF004, VINITEM.ICDSC1, VINITEM.ICDSC2, VINITEM.ICDSC3, VINITEM.ICWGHT, VINITEM.ICSUM, VINITEM.ICOUM,
VINITEM.ICOPCE2, VINITEM.ICIUMU, VINITEM.ICDEL, VINITEM.ICTXBL
FROM VINITMB
INNER JOIN VINITEM ON VINITMB.IFITEM = VINITEM.ICITEM
LEFT JOIN VINUDEF ON VINITEM.ICITEM = VINUDEF.CCITEM
INNER JOIN VARDICL ON VARDICL.RNDIV = VINITEM.ICDIV AND VARDICL.RNCLAS = VINITEM.ICCLS AND VARDICL.RNCMP = VINITMB.IFCOMP
INNER JOIN VARDIVS ON VARDIVS.RODIV = VINITEM.ICDIV AND VARDIVS.ROCMP = VINITMB.IFCOMP
WHERE ((CAST(VINITMB.IFCOMP AS Char(2)) + '|' + VINITEM.ICITEM)
NOT IN
(SELECT CAST(CompanyNo AS Char(2)) + '|' + Sku FROM Products))
GROUP BY VINITMB.IFCOMP, VINITEM.ICDIV, VINITEM.ICCLS, VINITEM.ICITEM, VARDIVS.RODNAM, VARDICL.RNDCNM, VINUDEF.CCF003, VINUDEF.CCF004, VINITEM.ICDSC1, VINITEM.ICDSC2, VINITEM.ICDSC3, VINITEM.ICWGHT, VINITEM.ICSUM, VINITEM.ICOUM,
VINITEM.ICOPCE2, VINITEM.ICIUMU, VINITEM.ICDEL, VINITEM.ICTXBL
GO

UPDATE Products
SET Division = Brydens.dbo.fn_MixCase(VARDIVS.RODNAM, NULL),
DivisionNo = VINITEM.ICDIV, ClassNO = VINITEM.ICCLS,
Class = Brydens.dbo.fn_MixCase(VARDICL.RNDCNM, NULL),
Category = Brydens.dbo.fn_MixCase(VINUDEF.CCF003, NULL),
Brand = Brydens.dbo.fn_MixCase(VINUDEF.CCF004, NULL),
Description = Brydens.dbo.fn_MixCase(VINITEM.ICDSC1, NULL),
Description2 = Brydens.dbo.fn_MixCase(VINITEM.ICDSC2, NULL),
Description3 = Brydens.dbo.fn_MixCase(VINITEM.ICDSC3, NULL),
ICSUM = VINITEM.ICSUM,
ICOUM = VINITEM.ICOUM,
ConversionFactor = ICOPCE2,
QuantityPerParent = ICIUMU,
WeightPerCase = VINITEM.ICWGHT,
StatusFlag = VINITEM.ICDEL,
TaxFlag = VINITEM.ICTXBL
FROM Products
INNER JOIN VINITEM ON Products.Sku = VINITEM.ICITEM
LEFT JOIN VINUDEF ON VINITEM.ICITEM = VINUDEF.CCITEM
INNER JOIN VARDICL ON VARDICL.RNDIV = VINITEM.ICDIV AND VARDICL.RNCLAS = VINITEM.ICCLS AND VARDICL.RNCMP = Products.CompanyNo
INNER JOIN VARDIVS ON VARDIVS.RODIV = VINITEM.ICDIV AND VARDIVS.ROCMP = Products.CompanyNo
where companyno <> 2
GO

UPDATE Products
SET Brand = 'None'
From Products
WHERE Brand IS NULL OR LTRIM(Brand) = ' '

UPDATE Products
SET Category = 'None'
From products
WHERE Category IS NULL OR LTRIM(Category) = ' '

UPDATE Products
SET Class = 'None'
From Products
WHERE Class IS NULL OR LTRIM(Class) = ' '

UPDATE Products
SET Division = 'None'
From Products
WHERE Division IS NULL OR LTRIM(Division) = ' '
GO

UPDATE PRODUCTS
SET QUANTITYPERPARENT = 1
FROM PRODUCTS
WHERE QUANTITYPERPARENT = 0
and companyno <> 2
GO
UPDATE PRODUCTS
SET CONVERSIONFACTOR = 1
FROM PRODUCTS
WHERE CONVERSIONFACTOR = 0
and companyno <> 2
GO

UPDATE Products
SET CodeDescription = '[' + RTrim(Sku) + '] ' + RTrim(Description) + ' | ' + RTrim(Category)
From Products
Where Category <> 'None' and companyno <> '2'
go
UPDATE Products
SET CodeDescription = '[' + RTrim(Sku) + '] ' + RTrim(Description)
From Products
Where Category = 'N
JASBRYDEN
New Member
New Member

--
30 Jun 2009 06:39 AM
I am not familiar with that.

Could you explain.

I am using SQL 2000
You are not authorized to post a reply.

Acceptable Use Policy