Indexing Table Ported from Oracle | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Indexing Table Ported from Oracle

Hi All,
I have an app that contains a table. The table was copied from an Oracle DB. In Oracle the performance is fine. The table has 110 columns and contains 45K rows. Doing a SELECT getting all of the rows from about 45 of the columns in Query Analyzer takes about 6 minutes to complete. I have tried many different indexing scenarios and they have not improved the performance.
The way the table was ported from Oracle, all of the columns (except the primary key – which is an int) are either varchar or decimal types. A lot of the varchar columns actually contain numeric data.
Can anyone suggest a strategy for improving the response of this table?
TIA,
Sven

What do you see in the execution plan for this query? can you post the query? Bambola.
By what way you’re calling this query from SQL to Oracle? _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

In answer to Satya: Right now the table structure and content have just been copied into SQL Server using DTS. In production, we will probably use a DTS package to synchronize the SQL Server table with the Oracle table once a day.<br /><br />In answer to Bambola: <br /><font color="red"><b>Here’s the table structure:</b></font id="red"><br />CREATE TABLE [dbo].[PPP] (<br />[KEY] [int] NOT NULL, –PRIMARY KEY<br />
Code:
 [varchar] (12)  NOT NULL,<br />[NAME] [varchar] (35)  NOT NULL,<br />[SKU] [varchar] (14)  NOT NULL,<br />[BRAND_CODE] [varchar] (2)  NOT NULL,<br />[BRAND] [varchar] (35)  NOT NULL,<br />[SUB_BRAND_CODE] [varchar] (2)  NOT NULL,<br />[SUB_BRAND] [varchar] (35)  NOT NULL,<br />[CATEGORY_CODE] [varchar] (2)  NOT NULL,<br />[CATEGORY] [varchar] (35)  NOT NULL,<br />[PRODUCT_CATEGORY_CODE] [varchar] (2)  NOT NULL,<br />[PRODUCT_CATEGORY] [varchar] (35)  NOT NULL,<br />[SUB_CATEGORY_CODE] [varchar] (3)  NOT NULL,<br />[SUB_CATEGORY] [varchar] (35)  NOT NULL,<br />[CLASS_CODE] [varchar] (3)  NOT NULL,<br />[CLASS] [varchar] (35)  NOT NULL,<br />[SUB_CLASS_CODE] [varchar] (3)  NOT NULL,<br />[SUB_CLASS] [varchar] (35)  NOT NULL,<br />[TYPE_CODE] [varchar] (2)  NOT NULL,<br />[TYPE] [varchar] (35)  NOT NULL,<br />[ITEM_CODE] [varchar] (10)  NOT NULL,<br />[ITEM] [varchar] (35)  NOT NULL,<br />[SHADE_CODE] [varchar] (2)  NOT NULL,<br />[SHADE] [varchar] (20)  NOT NULL,<br />[WEIGHT] [decimal](9, 4) NULL,<br />[CURRENT_LIST_PRICE] [decimal](9, 4) NULL,<br />[CURRENT_STD_COST] [decimal](9, 4) NULL,<br />[DIMS_CODE] [varchar] (7)  NOT NULL,<br />[DIMS] [varchar] (35)  NOT NULL,<br />[STAGE] [varchar] (2)  NOT NULL,<br />[SHELFPACK_QUANTITY] [varchar] (5)  NOT NULL,<br />[CASE_QUANTITY] [varchar] (5)  NOT NULL,<br />[INACTIVE_DATE] [varchar] (10)  NOT NULL,<br />[SHELF_LIFE_IN_MONTHS] [varchar] (2)  NOT NULL,<br />[ADD_DATE] [varchar] (10)  NOT NULL,<br />[LAUNCH_DATE] [varchar] (10)  NOT NULL,<br />[UPDATE_DATE] [varchar] (10)  NOT NULL,<br />[STOP_SHIP_DATE] [varchar] (10)  NOT NULL,<br />[HAZARDOUS_CODE] [varchar] (1)  NOT NULL,<br />[HAZARDOUS] [varchar] (20)  NOT NULL,<br />[STATUS_CODE] [varchar] (1)  NOT NULL,<br />[STATUS] [varchar] (20)  NOT NULL,<br />[IN_OUT_CODE] [varchar] (1)  NOT NULL,<br />[IN_OUT] [varchar] (20)  NOT NULL,<br />[PREPACK_CODE] [varchar] (1)  NOT NULL,<br />[PREPACK] [varchar] (20)  NOT NULL,<br />[PROMOTION_CYCLE_CODE] [varchar] (10)  NOT NULL,<br />[PROMOTION_CYCLE] [varchar] (35)  NOT NULL,<br />[PROMOTION_DATE] [varchar] (10)  NOT NULL,<br />[OBSOLETE_CODE] [varchar] (1)  NOT NULL,<br />[OBSOLETE] [varchar] (20)  NOT NULL,<br />[PROFIT_CENTER] [varchar] (15)  NOT NULL,<br />[SHELFPACK_WEIGHT] [varchar] (7)  NOT NULL,<br />[CASE_WEIGHT] [varchar] (7)  NOT NULL,<br />[CASE_CUBIC_INCHES] [varchar] (7)  NOT NULL,<br />[SHELFPACK_CUBIC_INCHES] [varchar] (9)  NOT NULL,<br />[FREIGHT_CLASS] [varchar] (1)  NOT NULL,<br />[PALLET_CUBIC_INCHES] [varchar] (<img src='/community/emoticons/emotion-11.gif' alt='8)' />  NOT NULL,<br />[DENSITY] [varchar] (<img src='/community/emoticons/emotion-11.gif' alt='8)' />  NOT NULL,<br />[ALIAS] [varchar] (10)  NOT NULL,<br />[ITEM_SHADE] [varchar] (12)  NOT NULL,<br />[SPF_FACTOR_CODE] [varchar] (1)  NOT NULL,<br />[SPF_FACTOR] [varchar] (20)  NOT NULL,<br />[DISPLAY_ORDER_TYPE_CODE] [varchar] (1)  NOT NULL,<br />[PRIOR_LIST_PRICE] [decimal](9, 4) NULL,<br />[CURRENT_STD_LAB_COST] [decimal](9, 4) NULL,<br />[CURRENT_STD_MATL_COST] [decimal](9, 4) NULL,<br />[CURRENT_STD_OVHD_COST] [decimal](9, 4) NULL,<br />[CURRENT_PERM_DISP_COST] [decimal](9, 4) NULL,<br />[CURRENT_COUNTER_DISP_COST] [decimal](9, 4) NULL,<br />[CURRENT_NON_SALEABLE_COST] [decimal](9, 4) NULL,<br />[PRIOR_STD_MATL_COST] [decimal](9, 4) NULL,<br />[PRIOR_STD_LAB_COST] [decimal](9, 4) NULL,<br />[PRIOR_STD_OVHD_COST] [decimal](9, 4) NULL,<br />[PRIOR_PERM_DISP_COST] [decimal](9, 4) NULL,<br />[PRIOR_COUNTER_DISP_COST] [decimal](9, 4) NULL,<br />[PRIOR_NON_SALEABLE_COST] [decimal](9, 4) NULL,<br />[ABRV_UNIT_NAME] [varchar] (1)  NULL,<br />[ABRV_SHADE] [varchar] (10)  NOT NULL,<br />[UNIT_OF_MEASURE] [varchar] (2)  NOT NULL,<br />[BUYER_ID] [varchar] (1)  NULL,<br />[ABC_CLASS] [varchar] (1)  NOT NULL,<br />[DISCONTINUED_CODE] [varchar] (1)  NULL,<br />[DISCONTINUED] [varchar] (1)  NULL,<br />[VARIANCE_CODE] [varchar] (1)  NOT NULL,<br />[INACTIVE_CODE] [varchar] (1)  NULL,<br />[PICK_LOCATION] [varchar] (4)  NOT NULL,<br />[DEMAND_REPORT_CODE] [varchar] (1)  NULL,<br />[PRODUCT_LINE] [varchar] (1)  NULL,<br />[ROYALTY_CODE] [varchar] (2)  NOT NULL,<br />[NON_STOCK_CODE] [varchar] (1)  NOT NULL,<br />[EFFECTIVE_DATE] [varchar] (1)  NULL,<br />[CURRENT_WIP_DIFF] [decimal](9, 4) NULL,<br />[CURRENT_PROMO_LABOR] [decimal](9, 4) NULL,<br />[CURRENT_PROMO_OVERHEAD] [decimal](9, 4) NULL,<br />[CURRENT_PRODUCT_LOSS] [decimal](9, 4) NULL,<br />[CURRENT_DISPLAY_LOSS] [decimal](9, 4) NULL,<br />[ACTIVITY_CODE] [varchar] (20)  NULL <br />) <br /><br /><b><font color="red">Here's a query:</font id="red"></b><br />select ITEM_CODE, <br />CODE,<br />NAME, <br />ITEM, <br />TYPE, <br />IN_OUT_CODE,<br />ITEM_SHADE, <br />SHADE,<br />LAUNCH_DATE, <br />STOP_SHIP_DATE, <br />BRAND,<br />SUB_BRAND,<br />CATEGORY,<br />PRODUCT_CATEGORY,<br />SUB_CATEGORY,<br />CLASS,<br />NAME,<br />ITEM,<br />SUB_CLASS,<br />CURRENT_LIST_PRICE,<br />CURRENT_STD_COST,<br />CURRENT_STD_LAB_COST,<br />CURRENT_STD_MATL_COST,<br />CURRENT_STD_OVHD_COST,<br />CURRENT_PERM_DISP_COST,<br />CURRENT_COUNTER_DISP_COST,<br />CURRENT_NON_SALEABLE_COST,<br />PRIOR_STD_MATL_COST,<br />PRIOR_STD_LAB_COST,<br />PRIOR_STD_OVHD_COST,<br />PRIOR_PERM_DISP_COST,<br />PRIOR_COUNTER_DISP_COST,<br />PRIOR_NON_SALEABLE_COST,<br />PROFIT_CENTER,<br />SHELFPACK_WEIGHT,<br />CASE_WEIGHT,<br />CASE_CUBIC_INCHES,<br />SHELFPACK_CUBIC_INCHES,<br />FREIGHT_CLASS,<br />PALLET_CUBIC_INCHES,<br />DENSITY,<br />ALIAS,<br />WEIGHT from PPP<br /><br />If I run this query relying on the Query Optimizer, in the execution plan I get 100% of the cost on an Index Scan on the primary key clustered index. If I use a hint, I get Index Scans on the indexes specified in the hint. <br />I've tried a bunch of different combinations of indexes to no avail. If anyone can suggest anything to help, or point me in right direction, I'd really appreciate it.<br /><br />TIA

Have you reindexes the indexes, and being clustered index is involed just as a test drop and recreate that specified Clu.index. HTH _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

I’ve been running DBCC DBREINDEX on the table. That should be sufficient, shouldn’t it?
Yes, DBCC DBREINDEX will reindex all the indexes on the table. Since you are trying to get all the rows from the table, it will naturally go for a table scan (or Clustered Index scan) which is true in your case. And with the table of so many columns and 45K rows, I would think that it has to scan through 100s of thousands of pages for this query. And if the disk system is not efficient enough for this kind of scan, it may normally take this long. Enable IO statistics for the query and check how many physical, logical and read-ahead reads it is doing. Also, how much time is it taking if you run the same query in Oracle ?
TO check with Bambola’s response, have you checked thru Query Execution plan in QA.
Also try to drop the recreate the index. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

]]>