Indexes - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

Indexes

Author

Topic

kvangor
Starting Member

13 Posts

Posted - 2005-11-17 : 08:35:17

Hi,

We currently have a query that takes around a minute to run. Our sales table has almost 17 million records. When using the following statement an index scan is done on the invoice_sales table.

dbo.invoice_sales._fiscal_month In ( 9 )

If we add one or more months to the statement, SQL server does not use the index scan on the fiscal_month and the query takes well over two hours.

dbo.invoice_sales._fiscal_month In ( 9, 10, 11, 12 )

Is this the way the indexes are supposed to work or is there a possible issue?

Any help or advice would be greatly appreciated! We're on SQL Server 2003, SP4.

Thanks!

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2005-11-17 : 09:30:43

is the scan a clustered index scan ? that is basically a table scan.

is there an index on the fiscal month? there are alot of factors for
the optimizer to take into account. even the order of columns in the where will
change.

________________________________________________

Drinking German Beer... fun.
Listening to an accordian player play ACDC...priceless

kvangor
Starting Member

13 Posts

Posted - 2005-11-17 : 09:56:18

It's an index seek from a non-clustered index. The index invoice_fiscal is setup on the invoice_sales table, and the index includes fiscal_year and fiscal_month. fiscal_year is also being used in the query.

The index seek cuts the invoice_sales table down to 300,000 rows, while without it a bookmark lookup is done on the invoice_sales table. I hope this helps...

Thanks for your reply!

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2005-11-17 : 10:28:58

quote:
Originally posted by kvangor

It's an index seek from a non-clustered index. The index invoice_fiscal is setup on the invoice_sales table, and the index includes fiscal_year and fiscal_month. fiscal_year is also being used in the query.

The index seek cuts the invoice_sales table down to 300,000 rows, while without it a bookmark lookup is done on the invoice_sales table. I hope this helps...

Thanks for your reply!

Hmm, post the DDL for the table in question if you could...

________________________________________________

Drinking German Beer... fun.
Listening to an accordian player play ACDC...priceless

kvangor
Starting Member

13 Posts

Posted - 2005-11-17 : 10:44:21

How would I get the DDL?

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2005-11-17 : 11:26:00

right click on table-> Generate Sql Script -> (click preview) and there ya go.

________________________________________________

Drinking German Beer... fun.
Listening to an accordian player play ACDC...priceless

kvangor
Starting Member

13 Posts

Posted - 2005-11-17 : 11:38:45

Is this what you're looking for? As you can see it's a big table. I highlighted the columns in question...

Thanks again for your help!

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[invoice_sales]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[invoice_sales]
GO

CREATE TABLE [dbo].[invoice_sales] (
[sales_seq] [bigint] NULL ,
[order_date] [datetime] NULL ,
[order_datetime] [datetime] NULL ,
[item] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[soldto_business_partner] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[zip_code] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[area] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[rate_date] [datetime] NULL ,
[rate_datetime] [datetime] NULL ,
[release_date] [datetime] NULL ,
[release_datetime] [datetime] NULL ,
[delivery_date] [datetime] NULL ,
[delivery_datetime] [datetime] NULL ,
[invoice_date] [datetime] NULL ,
[invoice_datetime] [datetime] NULL ,
[pick_date] [datetime] NULL ,
[pick_datetime] [datetime] NULL ,
[shipto_business_partner] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[invoice_business_partner] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[payby_business_partner] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[currency] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[carrier] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ship_location] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[company] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[return_reason] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[block_sid] [int] NULL ,
[block_reason] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[order_number] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[position_number] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sequence_number] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sales_order_type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sales_order_type_description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[origin] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sales_office] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[terms_of_delivery] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[terms_of_delivery_description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[contact] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[shipping_constraint] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[terms_of_payment] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[terms_of_payment_description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[payment_period] [int] NULL ,
[payment_period_type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cancelled] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[blocked_flag] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[direct_patient_delivery_flag] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[comp_account] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[direct_delivery_flag] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[custom_flag] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[backorder_flag] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[price_origin] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[order_line_type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cost] [money] NULL ,
[price] [money] NULL ,
[sale_amount] [money] NULL ,
[invoice_amount] [money] NULL ,
[revenue_amount] [money] NULL ,
[backorder_quantity] [int] NULL ,
[delivered_quantity] [int] NULL ,
[order_quantity] [int] NULL ,
[original_order_number] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[original_document_type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pricing_level] [int] NULL ,
[pricing_description] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[inventory_issue_status] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[lot_selection] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[exchange_rate_type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[order_priority] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[activity] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[procedure_mask] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[transaction_type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[invoice_number] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[customer_order_number] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[customer_position_number] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[customer_sequence_number] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[lot_number] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[shipment_id] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[order_promising_status] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[eye] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pairing] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[patient_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[serial_number] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[original_user] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[last_modified_user] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[tracking_number] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[original_prescription] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[shipped_prescription] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[specticle_prescription] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[options_flag] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[options_quantity] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[picklist_number] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fitting_history] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sharing] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[target_labeling] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[invoice_status] [int] NULL ,
[warehouse_status] [int] NULL ,
[bank_allocated_quantity] [int] NULL ,
[bank_remaining_quantity] [int] NULL ,
[bank_redeemed_quantity] [int] NULL ,
[bank_redeemed_amount] [money] NULL ,
[bank_purchase_amount] [money] NULL ,
[bank_unit_price] [money] NULL ,
[bank_rollover_quantity] [int] NULL ,
[bank_item] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[bank_type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[route] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[route_plan] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[tax_code] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ship_address_code] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[block_line] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[block_line_sid] [int] NULL ,
[tax_amount] [float] NULL ,
[pricing_hierachy] [float] NULL ,
[order_calendar_month] [int] NULL ,
[order_calendar_year] [int] NULL ,
[order_fiscal_month] [int] NULL ,
[order_fiscal_year] [int] NULL ,
[delivery_calendar_month] [int] NULL ,
[delivery_calendar_year] [int] NULL ,
[delivery_fiscal_month] [int] NULL ,
[delivery_fiscal_year] [int] NULL ,
[invoice_calendar_month] [int] NULL ,
[invoice_calendar_year] [int] NULL ,
[invoice_fiscal_month] [int] NULL ,
[invoice_fiscal_year] [int] NULL , [ordposseq] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DirectIndirect] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[complete] [int] NULL ,
[plant_number] [int] NULL ,
[warehouse_status_description] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[invoice_status_description] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[planned_delivery_date] [datetime] NULL ,
[address_code] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vendor_code] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[product_class] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[unit_quantity] [int] NULL ,
[lookthrough_cost] [float] NULL ,
[gl_company] [int] NULL ,
[gl_date] [datetime] NULL ,
[gl_transaction_sequence_number] [int] NULL ,
[fromto] [int] NULL ,
[gl_creation_date] [datetime] NULL ,
[fitxn_posting_datetime] [int] NULL ,
[fitxn_posting_date] [datetime] NULL ,
[gl_amount] [money] NULL ,
[gl_currency] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[gl_number_of_units] [int] NULL ,
[gl_posted_flag] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[gl_ledger_account] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[order_exchange_rate] [money] NULL ,
[currency_order_value] [money] NULL ,
[gl_transaction_type] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[gl_document] [int] NULL ,
[order_line_exchange_rate] [money] NULL ,
[pick_confirm_status] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[gl_fiscal_period] [int] NULL ,
[gl_fiscal_year] [int] NULL ,
[gl_integration_line] [int] NULL ,
[order_line_discount_amt] [money] NULL ,
[net_order_line_value] [money] NULL ,
[line_discount_amt] [money] NULL ,
[discount_percentage] [float] NULL ,
[transfer_price] [money] NULL ,
[ddt_number] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vat_code] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[baan_order_datetime] [datetime] NULL ,
[baan_invoice_datetime] [datetime] NULL ,
[special_instructions] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[original_order_type] [int] NULL ,
[original_document_numb] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[standard_baan_text] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[alternative_ship_address] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[hold_line_reason] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2005-11-17 : 12:18:23

whoa.

________________________________________________

Drinking German Beer... fun.
Listening to an accordian player play ACDC...priceless

kvangor
Starting Member

13 Posts

Posted - 2005-11-17 : 12:22:00

Yep, that's our fact table. Needless to say this covers a number of different departments within our organization and one would never have a need to see everything in the table.

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2005-11-17 : 12:22:18

so whats the primary key? I see sales_seq is NULL? (I always have the pk as the 1st column)

________________________________________________

Drinking German Beer... fun.
Listening to an accordian player play ACDC...priceless

kvangor
Starting Member

13 Posts

Posted - 2005-11-17 : 13:47:38

The primary key is ordposseq, although it's not defined that way in SQL server.

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2005-11-17 : 13:53:43

Hmm, well, it should be defined and it should probably your clustered index as well. The possibility with adding more than a few records for your IN statement could be the optimizer is just realizing the quantity of reads just makes sense to do scans.

I am not an expert, but I try to help the optimizer make a good decision, starting with having a primary key, a clustered index on the pk (generally)

________________________________________________

Drinking German Beer... fun.
Listening to an accordian player play ACDC...priceless

Kristen
Test

22859 Posts

Posted - 2005-11-17 : 13:54:41

"(I always have the pk as the 1st column)"

Ours is always the 6th column

Kristen

kvangor
Starting Member

13 Posts

Posted - 2005-11-17 : 14:14:34

I still can't believe the optimizer would choose a much slower route after just adding a few values to a condition. Why would that cause it to go from an index scan to a table scan?

I appreciate everyone's input!

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2005-11-17 : 14:21:44

quote:
Originally posted by Kristen

"(I always have the pk as the 1st column)"

Ours is always the 6th column

Kristen

You must be a consultant!

________________________________________________

Drinking German Beer... fun.
Listening to an accordian player play ACDC...priceless

Kristen
Test

22859 Posts

Posted - 2005-11-17 : 14:23:40

"I still can't believe the optimizer would choose ... "

'It depends' ... if this is in an Sproc then the "parameter sniffing" is limited, and might not realise what is going on.

If its dynamic SQL it probably unwinds the IN(1,2,3,...) to a bunch of ORs, and at some point it probably decides there are to many to bother with an INDEX. In your particular example

invoice_sales._fiscal_month BETWEEN 9 AND 12

would probably perform better - but I doubt your real-word data ranges are always ascending-contiguous!

It is also possible that the Statistics for the table are stale, and updating them will cause SQL to pick a better query plan.

Kristen

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2005-11-17 : 14:24:13

quote:
Originally posted by kvangor

I still can't believe the optimizer would choose a much slower route after just adding a few values to a condition. Why would that cause it to go from an index scan to a table scan?

I appreciate everyone's input!

Ok, my thinking is that the fiscal month thing has a very small amount of different values. (1-12 if you use the same calendar I do). and I am thinking if you incorporate more than a value or two, its going to just go check all of it since there is a few k of records to deal with.

can you post the FROM clause through the Where clause portion of this monstrocity so I can have alook, (i don't need to see all 300 fields in the select)

________________________________________________

Drinking German Beer... fun.
Listening to an accordian player play ACDC...priceless

Kristen
Test

22859 Posts

Posted - 2005-11-17 : 14:42:00

"You must be a consultant!"

Ha! Well .. there is the Create Date. And the User. And then the Update Date, and User. And then we increment an "EditNumber" on each save to allow optimistic WHERE clauses in UPDATE statements. And then we are at Column 6 ...

But the column names for the first 5 are prefixed with a "z" - so any tool providing alphabetized sorting of column names puts them last ... well, that was the theory behind it!

Kristen

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2005-11-17 : 14:44:27

Ah, the z ordering technique! I see that here too.

My last job had people using aTableName, bTableName to keep things ordered... One of my favorites..

________________________________________________

Drinking German Beer... fun.
Listening to an accordian player play ACDC...priceless

Kristen
Test

22859 Posts

Posted - 2005-11-17 : 14:46:10

And of course you need to be using an accent-sensitive collating sequence so you can squeeze an after-thought between aTablename and bTablename

Kristen

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-11-17 : 15:20:48

I wouldn't really call that a fact table, and that is a big part of your problem. In a "normal" star schema, your query would just scan the Date dimension for the fiscal_month, and then join to the Fact table and needed Dimensions for the rest of the data you need.

The design you have is a flat table where the Fact table and related Dimension tables have been rolled up into one table. Fact tables should only have measures, along with the foreign keys to the related Dimension tables. The Dimension tables should contain most of what you have in your "fact" table. This means that your query is scanning a huge table and orders of magnitude more data, and there really isn't a very good fix for it.

Of course, fixing this is probably a huge job, so I doubt that you can do much about it now. It is a good illustration of the penalty for a bad data mart design.

CODO ERGO SUM

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources