October 23, 2008 at 10:12 am
i have a table of 53.5 million rows with 66 fields and 3 indexes. i need to know which column would be more efficient for indexing as just finding order quantities/day 910 rows took 48:33 minutes
select [order date], sum([system qty]) from dbo.WD_Order_detail_QTD group by [order date]
October 23, 2008 at 10:22 am
For that particular query, an index on [order date] INCLUDE [system qty] will help. If you're after more general recommendations, post the table structure, the index definitions and the most common queries that are run on the table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 23, 2008 at 10:47 am
thanks Gila here is what i have in my table and indexes.
[Order Date] [smalldatetime] NULL,
[Order Num] [int] NULL,
[Item Num] [varchar](20) NULL,
[Item Short Desc] [varchar](100) NULL,
[Order Qty] [int] NULL,
[Item Class Short Desc] [varchar](100) NULL,
[Parent Item Class] [varchar](20) NULL,
[System Flag] [varchar](10) NULL,
[Class] [varchar](20) NULL,
[Class 2C] [varchar](20) NULL,
[Class 2C Desc] [varchar](100) NULL,
[Subclass Code] [varchar](1) NULL,
[Product Desc] [varchar](100) NULL,
[Brand Desc] [varchar](100) NULL,
[Product Line ID] [varchar](20) NULL,
[Product Line Desc] [varchar](100) NULL,
[Product Line Parent ID] [varchar](20) NULL,
[Processor Desc] [varchar](100) NULL,
[Processor Parent Desc] [varchar](100) NULL,
[Processor Speed In Mhz] [int] NULL,
[System Qty] [int] NULL,
[Ship Qty] [int] NULL,
[Inv Date] [smalldatetime] NULL,
[Local Order Status Code] [varchar](10) NULL,
[Local Order Type Code] [varchar](10) NULL,
[Revenue Type Code] [varchar](10) NULL,
[Total Base Rev Disc IBU] [real] NULL,
[Total Base Margin IBU] [real] NULL,
[Total Service Rev Disc IBU] [real] NULL,
[Total Service Margin IBU] [real] NULL,
[Total Defer Rev Disc IBU] [real] NULL,
[Total Defer Margin IBU] [real] NULL,
[Total SnP Rev Disc IBU] [real] NULL,
[Total SnP Rev Retail IBU] [real] NULL,
[Total SnP Margin IBU] [real] NULL,
[Base Freight Rev Disc IBU] [real] NULL,
[Base Freight Cost IBU] [real] NULL,
[Ups Svc Ra Cost Rev Disc IBU] [real] NULL,
[Base Ra Cost Rev Disc IBU] [real] NULL,
[Partner Rev Disc IBU] [real] NULL,
[Partner Margin IBU] [real] NULL,
[DB Periph Rev Disc IBU] [real] NULL,
[DB Periph Margin IBU] [real] NULL,
[Spares Rev Disc IBU] [real] NULL,
[Spares Margin IBU] [real] NULL,
[SnP CFI Rev Disc IBU] [real] NULL,
[SnP CFI Margin IBU] [real] NULL,
[Def SnP Rev Disc IBU] [real] NULL,
[Def SnP Margin IBU] [real] NULL,
[Def Svc Rev Disc IBU] [real] NULL,
[Def Svc Margin IBU] [real] NULL,
[Salesrep Dept] [int] NULL,
[Salesrep Num] [int] NULL,
[Salesrep Name] [varchar](100) NULL,
[Sub Channel Desc] [varchar](50) NULL,
[Location Num] [int] NULL,
[costcomp_diff_calc_df] [real] NULL,
[Transaction Date] [datetime] NULL,
[Transaction Date Week] [int] NULL,
[Customer Num] [int] NULL,
[Order Header Last Update Date] [smalldatetime] NULL,
[Fiscal_Year_Ord] [int] NULL,
[Fiscal_Quarter_Ord] [int] NULL,
[Fiscal_Month_Ord] [int] NULL,
[Fiscal_Week_Ord] [int] NULL,
[Gross Net Partial] [varchar](15) NULL
NONCLUSTERED INDEX [idx_detail_QTD] ON [dbo].[WD_Order_detail_QTD]
(
[Order Num] DESC,
[Order Date] DESC,
[Inv Date] DESC,
[Local Order Status Code] ASC,
[Sub Channel Desc] ASC,
[Location Num] ASC,
[Salesrep Num] ASC
)
NONCLUSTERED INDEX [idx_item_short_desc] ON [dbo].[WD_Order_detail_QTD]
(
[Item Short Desc] ASC
)
NONCLUSTERED INDEX [idx_product_line] ON [dbo].[WD_Order_detail_QTD]
(
[Product Line Parent ID] ASC,
[Class 2C] ASC
)
October 23, 2008 at 11:48 am
No Primary Key or Clustered Index? (just checking, to be sure)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 23, 2008 at 12:30 pm
Before you worry about indexing or anything else, I would strongly recommend that you consider normalising that table. You have massive amounts of redundant data and several sets of completely unrelated columns. Splitting the table out will reduce the row size and reduce the overall amount of data (I dread to think how many copies of the Product Description you have, and how many different descriptions there are for one ID)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 23, 2008 at 12:38 pm
oohhh i dunno, add in a couple of LOB columns and you'd be well away LOL
certainly could do with a little huh hum normalisation 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply