October 19, 2011 at 2:24 pm
SQL Gurus,
I have a Order's table and it has around 80+ columns. The amount of data that is added to this is decent with around 100,000 records in a day. But the problem is, when I try to query this table with a date range, the query runs really slow. I know that the table is in 1NF and needs further normalization.
But my question is, if I were to optimize the performance on the existing table, what would be the best way out? I already thought about Clustered indexes, file partitions. So, please let me know what should I do about this?
October 19, 2011 at 2:28 pm
Can you provide an example of the query your are running? If possible and execution plan would help too.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 19, 2011 at 2:41 pm
Query sample
SELECT TOP 10 * FROM Sales.HOWLI_LOAD
WHERE SQL_POST_DATE BETWEEN '2011-10-15 00:00:00.000' AND '2011-10-19 00:00:00.000'
This is just the SSMS version of it. But in fact I use a query where the field names are listed (as opposed to a select * from). Please find the attached SQL actual execution plan.
October 19, 2011 at 2:50 pm
A NONCLUSTERED INDEX on SQL_POST_DATE might help if you don't have one.
October 19, 2011 at 2:55 pm
Agreed that a non clustered index would improve performance. I already have something of that sort on a table which is very similar to this one. But I could not find any notable differences in the behavior.
I am actively working on converting this table to a 3NF so that I can atleast say that database part is all ok. Perhaps then I can blame the resources to be slow 😛 But anyways, I will again create the NC index and post the SQLPLAN.
October 19, 2011 at 2:59 pm
Another suggestion would be to recompute statistics on that table on a regular basis, especially if you're adding 100,000 rows daily.
October 19, 2011 at 3:10 pm
I added a non clustered index on the SQL_POST_DATE, but that didnt make any difference. In fact the numbers went up on my new execution plan. Dont know what's going on with that (see attached).
Any straight forward way to recompile statistics? Any DMV/DMF?
October 19, 2011 at 3:22 pm
EXEC sp_updatestats will work for all tables in the database
UPDATE STATISTICS tablename will run for just the one table.
October 19, 2011 at 3:23 pm
do you have a clustered index?
can you post the new execution plan?
also the table definition?
October 19, 2011 at 3:35 pm
Here's my table definition. I dont have a clustered index in there and the only index that I got is on SQL_POST_DATE. Now as far as I know, this table is already in 1NF. I am taking it further till 3NF to ease the reporting time on MicroStrategy.
[ID]
,[Z_ID]
,[SQL_ACT_WGT]
,[SQL_ALLOW_AMT]
,[SQL_ALLOW_CODE]
,[SQL_ALLOW_PERC]
,[SQL_BASE_UM]
,[SQL_BATCH_ALLO_PRORATED_QTY]
,[SQL_BATCH_NO]
,[SQL_BATCH_PICK_CODE]
,[SQL_BILL_ADDR1]
,[SQL_BILL_TO]
,[SQL_BO_CNCL_QTY]
,[SQL_BROKER_CODE]
,[SQL_BUYER]
,[SQL_BUYER_CODE]
,[SQL_BUYER_NAME]
,[SQL_CATCH_WGT_FLG]
,[SQL_CATCH_WGTS]
,[SQL_CONV_PRICED_BY_TO_SOLD_BY]
,[SQL_CR_RTN_REASON_CODE]
,[SQL_CRR_CODE]
,[SQL_CRR_RET_DOLLAR]
,[SQL_CRR_RET_SA]
,[SQL_CRR_RET_STOCK]
,[SQL_CUBE_PER_SOLD_BY]
,[SQL_CUST_PROD_NO]
,[SQL_DEALS_BASE_UM]
,[SQL_DEALS_BEG_DATE]
,[SQL_DEALS_CALC_METHOD_CODE]
,[SQL_DEALS_END_DATE]
,[SQL_DEALS_EXT_OFF_INV]
,[SQL_DEALS_EXT_REB_AMT]
,[SQL_DEALS_ID]
,[SQL_DEALS_NOTICE_TO_PERFORM]
,[SQL_DEALS_OFF_INV_SALES_CATEG_CODE]
,[SQL_DEALS_REB_CALC_AMT]
,[SQL_DEALS_REB_SALES_CATEG_CODE]
,[SQL_DISC_AMT]
,[SQL_DISC_CLASS]
,[SQL_DISC_METHOD]
,[SQL_DISCOUNT_CODE]
,[SQL_ELEMENT_AMT]
,[SQL_ELEMENT_CODE]
,[SQL_EXT_COST]
,[SQL_EXT_SALES]
,[SQL_FEE_AMT]
,[SQL_FEE_CODE]
,[SQL_FRGHT_CODE]
,[SQL_FS_INV_QTY]
,[SQL_IMID]
,[SQL_J_B]
,[SQL_KIT_CODE]
,[SQL_LB_MARGIN]
,[SQL_LINE_AMT]
,[SQL_LINE_DESC]
,[SQL_LN_ITM_COMMENT]
,[SQL_LOT_NO]
,[SQL_LOT_QTY]
,[SQL_MKT_GRP_CODE]
,[SQL_OFF_INV_CALC_AMT]
,[SQL_ORD_QTY]
,[SQL_ORDER_NO]
,[SQL_ORG_ORD_QTY]
,[SQL_ORIG_ORD_NO]
,[SQL_ORIG_UNIT_PRICE]
,[SQL_PACK]
,[SQL_PM_CATEG_CODE]
,[SQL_PM_DESC]
,[SQL_PM_SUBCATEG_CODE]
,[SQL_PMID]
,[SQL_POLI_ITM_ID]
,[SQL_POST_DATE]
,[SQL_PREPRICE_FLG]
,[SQL_PRICE_FILE_ID]
,[SQL_PRICED_BY_UM]
,[SQL_PRICING_LVL_CODE]
,[SQL_PRIMARY_LOC]
,[SQL_PROD_CLASS_CODE]
,[SQL_PROD_CLASS_DESC]
,[SQL_PROD_LN_CODE]
,[SQL_PROD_PRICING_CODE]
,[SQL_RET_STOCK]
,[SQL_RETAIL_PRIC_CALC_AMT]
,[SQL_RETAIL_PRIC_FIXED_QTY]
,[SQL_RETAIL_PRIC_FIXED_QTY_AMT]
,[SQL_RETAIL_PRIC_UM_BASE]
,[SQL_RETAIL_PRICE_FIXED_QTY_AMT]
,[SQL_RETAIL_PRICING_METHOD]
,[SQL_RETAIL_UNIT_PRIC_PER_SHELF_UNIT]
,[SQL_RETAIL_UNIT_PRIC_PER_SOLD_BY]
,[SQL_SALES_COST]
,[SQL_SALES_COST_ELEM_ID]
,[SQL_SFD_EXT_DIST_FEE]
,[SQL_SFD_FS_PICK_BATCH_NO]
,[SQL_SFD_UNIT_DIST_FEE]
,[SQL_SHIP_DATE]
,[SQL_SHIP_TO]
,[SQL_SHIPPED_QTY]
,[SQL_SUB_PROD_PRICE_FLG]
,[SQL_SUBSTITUTED_LN]
,[SQL_TARE_WGT_PER_SOLD_BY]
,[SQL_TAX_AMT]
,[SQL_TAX_CODE]
,[SQL_TOTAL_CATCH_WGT]
,[SQL_TOTAL_DISC_AMT_PER_LN]
,[SQL_TRUE_UNIT_COST]
,[SQL_UNIT_PRICE_PER_PRICED_BY]
,[SQL_UNIT_PRICE_PER_SOLD_BY]
,[SQL_UNIT_WGT]
,[SQL_UPC_CODE]
,[SQL_VMID]
,[SQL_WHS_CODE]
,[SQL_WHS_DESC]
,[Division]
,[AddedBy]
,[AddedOn]
,[AW_POST_DATE]
,[BillTo]
,[SQL_SALESMAN]
October 19, 2011 at 3:38 pm
We need a create table DDL (you don't have data types). 😉
October 19, 2011 at 3:41 pm
LOL...My bad. Here's the DDL. The data types have been verified by my boss and unfortunately enough, he doesnt have any knowledge about SQL Server.
CREATE TABLE [Sales].[HOWLI_LOAD](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Z_ID] [nvarchar](254) NULL,
[SQL_ACT_WGT] [float] NULL,
[SQL_ALLOW_AMT] [nvarchar](254) NULL,
[SQL_ALLOW_CODE] [nvarchar](254) NULL,
[SQL_ALLOW_PERC] [nvarchar](254) NULL,
[SQL_BASE_UM] [nvarchar](254) NULL,
[SQL_BATCH_ALLO_PRORATED_QTY] [nvarchar](254) NULL,
[SQL_BATCH_NO] [nvarchar](254) NULL,
[SQL_BATCH_PICK_CODE] [nvarchar](254) NULL,
[SQL_BILL_ADDR1] [nvarchar](254) NULL,
[SQL_BILL_TO] [nvarchar](254) NULL,
[SQL_BO_CNCL_QTY] [nvarchar](254) NULL,
[SQL_BROKER_CODE] [nvarchar](254) NULL,
[SQL_BUYER] [nvarchar](254) NULL,
[SQL_BUYER_CODE] [nvarchar](254) NULL,
[SQL_BUYER_NAME] [nvarchar](254) NULL,
[SQL_CATCH_WGT_FLG] [nvarchar](254) NULL,
[SQL_CATCH_WGTS] [ntext] NULL,
[SQL_CONV_PRICED_BY_TO_SOLD_BY] [nvarchar](254) NULL,
[SQL_CR_RTN_REASON_CODE] [nvarchar](254) NULL,
[SQL_CRR_CODE] [nvarchar](254) NULL,
[SQL_CRR_RET_DOLLAR] [nvarchar](254) NULL,
[SQL_CRR_RET_SA] [nvarchar](254) NULL,
[SQL_CRR_RET_STOCK] [nvarchar](254) NULL,
[SQL_CUBE_PER_SOLD_BY] [nvarchar](254) NULL,
[SQL_CUST_PROD_NO] [nvarchar](254) NULL,
[SQL_DEALS_BASE_UM] [nvarchar](254) NULL,
[SQL_DEALS_BEG_DATE] [nvarchar](254) NULL,
[SQL_DEALS_CALC_METHOD_CODE] [nvarchar](254) NULL,
[SQL_DEALS_END_DATE] [nvarchar](254) NULL,
[SQL_DEALS_EXT_OFF_INV] [nvarchar](254) NULL,
[SQL_DEALS_EXT_REB_AMT] [nvarchar](254) NULL,
[SQL_DEALS_ID] [nvarchar](254) NULL,
[SQL_DEALS_NOTICE_TO_PERFORM] [nvarchar](254) NULL,
[SQL_DEALS_OFF_INV_SALES_CATEG_CODE] [nvarchar](254) NULL,
[SQL_DEALS_REB_CALC_AMT] [nvarchar](254) NULL,
[SQL_DEALS_REB_SALES_CATEG_CODE] [nvarchar](254) NULL,
[SQL_DISC_AMT] [nvarchar](254) NULL,
[SQL_DISC_CLASS] [nvarchar](254) NULL,
[SQL_DISC_METHOD] [nvarchar](254) NULL,
[SQL_DISCOUNT_CODE] [nvarchar](254) NULL,
[SQL_ELEMENT_AMT] [nvarchar](254) NULL,
[SQL_ELEMENT_CODE] [nvarchar](254) NULL,
[SQL_EXT_COST] [float] NULL,
[SQL_EXT_SALES] [float] NULL,
[SQL_FEE_AMT] [nvarchar](254) NULL,
[SQL_FEE_CODE] [nvarchar](254) NULL,
[SQL_FRGHT_CODE] [nvarchar](254) NULL,
[SQL_FS_INV_QTY] [nvarchar](254) NULL,
[SQL_IMID] [nvarchar](254) NULL,
[SQL_J_B] [nvarchar](254) NULL,
[SQL_KIT_CODE] [nvarchar](254) NULL,
[SQL_LB_MARGIN] [float] NULL,
[SQL_LINE_AMT] [nvarchar](254) NULL,
[SQL_LINE_DESC] [nvarchar](254) NULL,
[SQL_LN_ITM_COMMENT] [nvarchar](254) NULL,
[SQL_LOT_NO] [nvarchar](254) NULL,
[SQL_LOT_QTY] [nvarchar](254) NULL,
[SQL_MKT_GRP_CODE] [nvarchar](254) NULL,
[SQL_OFF_INV_CALC_AMT] [nvarchar](254) NULL,
[SQL_ORD_QTY] [nvarchar](254) NULL,
[SQL_ORDER_NO] [nvarchar](254) NULL,
[SQL_ORG_ORD_QTY] [nvarchar](254) NULL,
[SQL_ORIG_ORD_NO] [nvarchar](254) NULL,
[SQL_ORIG_UNIT_PRICE] [nvarchar](254) NULL,
[SQL_PACK] [nvarchar](254) NULL,
[SQL_PM_CATEG_CODE] [nvarchar](254) NULL,
[SQL_PM_DESC] [nvarchar](254) NULL,
[SQL_PM_SUBCATEG_CODE] [nvarchar](254) NULL,
[SQL_PMID] [nvarchar](254) NULL,
[SQL_POLI_ITM_ID] [nvarchar](254) NULL,
[SQL_POST_DATE] [nvarchar](254) NULL,
[SQL_PREPRICE_FLG] [nvarchar](254) NULL,
[SQL_PRICE_FILE_ID] [nvarchar](254) NULL,
[SQL_PRICED_BY_UM] [nvarchar](254) NULL,
[SQL_PRICING_LVL_CODE] [nvarchar](254) NULL,
[SQL_PRIMARY_LOC] [nvarchar](254) NULL,
[SQL_PROD_CLASS_CODE] [nvarchar](254) NULL,
[SQL_PROD_CLASS_DESC] [nvarchar](254) NULL,
[SQL_PROD_LN_CODE] [nvarchar](254) NULL,
[SQL_PROD_PRICING_CODE] [nvarchar](254) NULL,
[SQL_RET_STOCK] [nvarchar](254) NULL,
[SQL_RETAIL_PRIC_CALC_AMT] [nvarchar](254) NULL,
[SQL_RETAIL_PRIC_FIXED_QTY] [nvarchar](254) NULL,
[SQL_RETAIL_PRIC_FIXED_QTY_AMT] [nvarchar](254) NULL,
[SQL_RETAIL_PRIC_UM_BASE] [nvarchar](254) NULL,
[SQL_RETAIL_PRICE_FIXED_QTY_AMT] [nvarchar](254) NULL,
[SQL_RETAIL_PRICING_METHOD] [nvarchar](254) NULL,
[SQL_RETAIL_UNIT_PRIC_PER_SHELF_UNIT] [nvarchar](254) NULL,
[SQL_RETAIL_UNIT_PRIC_PER_SOLD_BY] [nvarchar](254) NULL,
[SQL_SALES_COST] [nvarchar](254) NULL,
[SQL_SALES_COST_ELEM_ID] [nvarchar](254) NULL,
[SQL_SFD_EXT_DIST_FEE] [nvarchar](254) NULL,
[SQL_SFD_FS_PICK_BATCH_NO] [nvarchar](254) NULL,
[SQL_SFD_UNIT_DIST_FEE] [nvarchar](254) NULL,
[SQL_SHIP_DATE] [date] NULL,
[SQL_SHIP_TO] [nvarchar](254) NULL,
[SQL_SHIPPED_QTY] [numeric](4, 0) NULL,
[SQL_SUB_PROD_PRICE_FLG] [nvarchar](254) NULL,
[SQL_SUBSTITUTED_LN] [nvarchar](254) NULL,
[SQL_TARE_WGT_PER_SOLD_BY] [nvarchar](254) NULL,
[SQL_TAX_AMT] [nvarchar](254) NULL,
[SQL_TAX_CODE] [nvarchar](254) NULL,
[SQL_TOTAL_CATCH_WGT] [nvarchar](254) NULL,
[SQL_TOTAL_DISC_AMT_PER_LN] [nvarchar](254) NULL,
[SQL_TRUE_UNIT_COST] [nvarchar](254) NULL,
[SQL_UNIT_PRICE_PER_PRICED_BY] [bigint] NULL,
[SQL_UNIT_PRICE_PER_SOLD_BY] [bigint] NULL,
[SQL_UNIT_WGT] [nvarchar](254) NULL,
[SQL_UPC_CODE] [nvarchar](254) NULL,
[SQL_VMID] [nvarchar](254) NULL,
[SQL_WHS_CODE] [nvarchar](254) NULL,
[SQL_WHS_DESC] [nvarchar](254) NULL,
[Division] [nvarchar](2) NULL,
[AddedBy] [nvarchar](6) NULL,
[AddedOn] [datetime] NULL,
[AW_POST_DATE] [datetime] NULL,
[BillTo] [nvarchar](30) NULL,
[SQL_SALESMAN] [nvarchar](40) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
October 19, 2011 at 3:43 pm
can you include the data types?
from looking at the execution plan it seems that sql_post_date is not a date.time data typem is this correct?
you should ideally have a clustered index on the table
October 19, 2011 at 3:45 pm
SQL_POST_DATE and AW_POST_DATE are one and the same. I realized that and change my query to use AW_POST_DATE which is indeed a datetime field. But still, there's no change to the execution plan. I also recompiled the statistics on the table. All indications to a bad table design???
October 19, 2011 at 3:51 pm
Yes it is a bad table design, not only the normalisation of it, but the fact that you have so many nvarchar and ntext columns.
Surely there is no need for these data types, some of the columns indicate that these columns hold amounts and codes. Do these really need to be unicode and that long?
and you should at least have a clustered index on your id column
Viewing 15 posts - 1 through 15 (of 58 total)
You must be logged in to reply to this topic. Login to reply