A script I was working on. Your thoughts and opinions?

Question

A script I was working on. Your thoughts and opinions?

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

shannonjk SSCarpal Tunnel Points: 4286 More actions · Answer 1

I am working on a copy I moved into my development environment and before I even started the code I added the indexes I figured were necessary 😉

Link to my blog http://notyelf.com/

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

shannonjk (8/9/2009)
I am working on a copy I moved into my development environment and before I even started the code I added the indexes I figured were necessary 😉

In that case, do you have any indexes on the table? Do you have a clustered index? Do you have a primary key?

Any chance of you posting or attaching the CREATE TABLE statement along with any code for the indexes present and any triggers that may be present?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

shannonjk SSCarpal Tunnel Points: 4286 More actions · Answer 3

Here is the code that creates the table. There are no primary keys but here is the create table and index code. (Note on the create table, I did not create these tables so be gentle with me on their structure :-))

CREATE TABLE [dbo].[Order_Table](

[Division_id] [tinyint] NOT NULL,

[Customer_id] [int] NOT NULL,

[Salesperson_id] [smallint] NOT NULL,

[Item_id] [int] NOT NULL,

[Currency_id] [smallint] NOT NULL,

[Warehouse_id] [tinyint] NOT NULL,

[CustomerOrderType_id] [tinyint] NOT NULL,

[HighestStatus_id] [tinyint] NOT NULL,

[LowestStatus_id] [tinyint] NOT NULL,

[DeliveryMethod_id] [smallint] NOT NULL,

[DeliveryTerm_id] [tinyint] NOT NULL,

[OrderDate] [decimal](8, 0) NULL,

[CustomerOrder_Number] [nchar](10) NULL,

[RequestedDeliveryDate] [decimal](8, 0) NULL,

[ConfirmedDeliveryDate] [decimal](8, 0) NULL,

[CustomerOrder_LineNumber] [decimal](9, 0) NULL,

[CustomerOrder_LineSuffix] [decimal](9, 0) NULL,

[OrigOrderedQty_BasicUOM] [decimal](9, 2) NULL,

[OrderedQty_BasicUOM] [decimal](9, 2) NULL,

[RemainingQty_BasicUOM] [decimal](9, 2) NULL,

[AllocatedQty_BasicUOM] [decimal](9, 2) NULL,

[PickingListQty_BasicUOM] [decimal](9, 2) NULL,

[DeliveryQty_BasicUOM] [decimal](9, 2) NULL,

[InvoicedQty_BasicUOM] [decimal](9, 2) NULL,

[SalesPrice] [decimal](17, 6) NULL,

[NetPrice] [decimal](17, 6) NULL,

[CostPrice] [decimal](17, 6) NULL,

[LineAmount] [decimal](17, 6) NULL,

[OverrideCommissionRate] [decimal](17, 6) NULL,

[OrderRevision_Flag] [bit] NULL,

[OrderNo_Customer] [nvarchar](20) NULL,

[CustomerOrderStop_Code] [int] NULL,

[Responsible] [nvarchar](10) NULL,

[ContactMethod_id] [tinyint] NOT NULL,

[ChangeDate] [decimal](8, 0) NULL,

[CustomerAddress_id] [int] NOT NULL,

[PaymentMethod_id] [tinyint] NOT NULL,

[Payer] [nvarchar](10) NULL,

[Priority] [int] NULL,

[CustomerPurchaseDate] [decimal](8, 0) NULL,

[FirstDeliveryDate] [decimal](8, 0) NULL,

[LastDeliveryDate] [decimal](8, 0) NULL,

[InvoiceRecipient] [nvarchar](10) NULL,

[TermsText] [nvarchar](36) NULL,

[PackagingTerms] [nvarchar](3) NULL,

[NetMargin_Amount] [decimal](9, 2) NULL,

[NetMargin_Percent] [decimal](9, 2) NULL,

[ListMargin_Amount] [decimal](9, 2) NULL,

[ListMargin_Percent] [decimal](9, 2) NULL,

[FreightA] [decimal](9, 2) NULL,

[FreightB] [decimal](9, 2) NULL,

[TaxAmount] [decimal](9, 2) NULL,

[OrderAddress_id] [int] NOT NULL,

[Item_Code] [nvarchar](15) NULL,

[Customer_Code] [nchar](10) NULL,

[SalesPerson_Code] [smallint] NULL,

[ServiceCharge_Code] [smallint] NULL,

[ServiceCharge_Description] [nvarchar](50) NULL,

[Order_Status] [nchar](2) NULL,

[NetLine_Amount] [decimal](11, 2) NULL,

[GrossLine_Amount] [decimal](9, 2) NULL,

[Order_Cost] [decimal](10, 2) NULL,

[Order_Margin] [decimal](11, 2) NULL,

[Order_Margin%] [decimal](9, 2) NULL,

[TotalDiscount_Amount] [decimal](9, 2) NULL,

[TotalDiscount_Amount%] [decimal](9, 2) NULL,

[Entered_Date] [decimal](8, 0) NULL,

[Rec_StartDate] [decimal](8, 0) NULL,

[Rec_EndDate] [decimal](8, 0) NULL,

[IsActive] [nchar](1) NULL,

[Batch_id] [smallint] NULL,

[Record_Sequence] [int] NOT NULL

) ON [PRIMARY]

CREATE UNIQUE CLUSTERED INDEX [cIdx_FSO_Test] ON [dbo].Order_Table

(

[CustomerOrder_Number] ASC,

[CustomerOrder_LineNumber] ASC,

[CustomerOrder_LineSuffix] ASC,

[ConfirmedDeliveryDate] ASC,

[OrderedQty_BasicUOM] ASC,

[SalesPrice] ASC,

[NetPrice] ASC,

[CostPrice] ASC,

[Item_Code] ASC,

[Rec_StartDate] ASC,

[Rec_EndDate] ASC

)

CREATE NONCLUSTERED INDEX ncIdx_FSO_Stage ON dbo.Order_Table

(

Record_Sequence

,[CustomerOrder_Number]

,[CustomerOrder_LineNumber]

,[CustomerOrder_LineSuffix]

,[Rec_StartDate]

,[Rec_EndDate]

)

Link to my blog http://notyelf.com/

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

shannonjk (8/10/2009)
Here is the code that creates the table. There are no primary keys but here is the create table and index code. (Note on the create table, I did not create these tables so be gentle with me on their structure :-))

Heh... thanks for the warning. The table looks like some legacy tables I inherited so very well understood, as well.

Thanks for the code... lemme see what I can do.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

shannonjk SSCarpal Tunnel Points: 4286 More actions · Answer 5

Thank you for the help Jeff I appreciate it 😀

Link to my blog http://notyelf.com/