by I'm having a problem with the SQL Server 6.5 optimizer. It's just too dumb.
The problem is, we want to fix the query from the back end without having to
make a single change to the front end application (which is MS Access 2.0).
In other words, how can we tell the optimizer to use an index that has very
poor selectivity (about 7%). It's selectivity is poor because about
one-third of the rows has a null value in the customer_number field.
Normally, the application will send a query (listed below). The query takes
about 3 minutes. Access 2.0 times out. Of course, I can build a clustered
index, but it is more important for updates to this table work fast than for
this query to run. So we must use a non-clustered index. The product table
has just under 1 million rows. You will see at the end of this that the
optimizer makes a very bad choice when left to itself. Note the I/O reads
with and without the optimizer hint. Please let me know if you have any
thoughts on any possible solutions. Thanks.
Here's the information:
QUERY: SELECT dbo.customer.customer_name , dbo.product.customer_number ,
dbo.product.sales_order , dbo.product.item_number , dbo.product.item_sequence
, dbo.product.serial_number , dbo.product.model , dbo.product.product_status
, dbo.product.lease_type , dbo.product.lease_suffix , dbo.product.quantity ,
dbo.product.billto_number , dbo.product.product_desc , dbo.product.shipdate ,
dbo.product.shipto_number , dbo.product.install_date ,
dbo.product.last_invoice_date , dbo.product.invoice_number ,
dbo.product.customer_po , dbo.product.city , dbo.product.state ,
dbo.product.tax_exempt , dbo.product.lease_rate ,
dbo.product.lease_start_date , dbo.product.bill_flag , dbo.product.bill_term
, dbo.product.lease_renew_date , dbo.product.lease_renew_term ,
dbo.product.lease_expire_date , dbo.product.termination_date ,
dbo.product.maintenance_rate , dbo.product.maint_start_date ,
dbo.product.warranty_code , dbo.product.warranty_days ,
dbo.product.maintenance_term , dbo.product.maint_renew_date ,
dbo.product.maint_type_code , dbo.product.maint_days_code ,
dbo.product.maint_hours_code , dbo.product.sales_RAD ,
dbo.product.package_number , dbo.product.charge_account ,
dbo.maint_hours.maintenance_hours , dbo.maint_days.maintenance_days FROM
dbo.product,dbo.maint_hours,dbo.maint_days,dbo.customer WHERE
((((dbo.product.maint_hours_code = dbo.maint_hours.maint_hours_code ) AND
(dbo.product.maint_days_code = dbo.maint_days.maint_days_code ) ) AND
(dbo.customer.customer_name LIKE 'SUPERVAL%' ) ) AND
(dbo.customer.customer_number = dbo.product.customer_number ) ) ORDER BY
dbo.product.model ,dbo.product.state ,dbo.product.city
Product Table SETUP (notice the indexes):
Name Owner
Type
When_created
------------------------------ ------------------------------
----------------------------------------------------------------------
---------------------------
product dbo user
table Aug 18 1998
6:39PM
Data_located_on_segment
------------------------------
default
Column_name Type Length Prec
Scale Nullable TrimTrailingBlanks
FixedLenNullInSource
------------------------------ ------------------------------ ------ -----
----- ----------------------------------- -----------------------------------
-----------------------------------
customer_number char
10 yes
yes yes
sales_order char
7 yes
yes yes
item_number int 4 10
0 yes (n/a)
(n/a)
item_sequence int 4 10
0 yes (n/a)
(n/a)
serial_number char
30 yes
yes yes
model char
25 yes
yes yes
part_number char
15 yes
yes yes
product_status char
1 yes
yes yes
lease_type char
1 yes
yes yes
lease_suffix char
1 yes
yes yes
quantity int 4 10
0 yes (n/a)
(n/a)
billto_number char
9 yes
yes yes
product_desc char
30 yes
yes yes
shipdate datetime
8 yes
(n/a) (n/a)
shipto_number char
2 yes
yes yes
contract_term int 4 10
0 yes (n/a)
(n/a)
where_used int 4 10
0 yes (n/a)
(n/a)
install_date datetime
8 yes
(n/a) (n/a)
last_invoice_date datetime
8 yes
(n/a) (n/a)
invoice_number char
6 yes
yes yes
customer_po char
15 yes
yes yes
city char
25 yes
yes yes
state char
2 yes
yes yes
tax_exempt char
3 yes
yes yes
lease_rate money 8 19
4 yes (n/a)
(n/a)
lease_start_date datetime
8 yes
(n/a) (n/a)
bill_flag char
1 yes
yes yes
bill_term int 4 10
0 yes (n/a)
(n/a)
lease_renew_date datetime
8 yes
(n/a) (n/a)
lease_renew_term int 4 10
0 yes (n/a)
(n/a)
lease_expire_date datetime
8 yes
(n/a) (n/a)
termination_date datetime
8 yes
(n/a) (n/a)
maintenance_rate money 8 19
4 yes (n/a)
(n/a)
maint_start_date datetime
8 yes
(n/a) (n/a)
warranty_code char
1 yes
yes yes
warranty_days int 4 10
0 yes (n/a)
(n/a)
maintenance_term int 4 10
0 yes (n/a)
(n/a)
maint_renew_date datetime
8 yes
(n/a) (n/a)
maint_type_code char
1 yes
yes yes
maint_days_code char
1 yes
yes yes
maint_hours_code char
1 yes
yes yes
maintenance_zone char
1 yes
yes yes
service_RAD char
3 yes
yes yes
sales_RAD char
3 yes
yes yes
package_number char
6 yes
yes yes
company_number char
2 yes
yes yes
change_package char
10 yes
yes yes
charge_account char
9 yes
yes yes
customer_name varchar
40 yes
yes no
warranty_expiration_date datetime
8 yes
(n/a) (n/a)
Identity Seed
Increment
------------------------------ ------------------------------------------
------------------------------------------
No identity column defined. (null)
(null)
index_name index_description
index_keys
-------------------- -------------------------------------------------------- er_query_x nonclustered located on default No constraints have been defined for this object. No foreign keys reference this table. Remember, in the solution, the query cannot be changed! read more »
---------------------------------------------------------------------------
---
---------------------------------------------------------------------------
---
---------------------------------------------------------------------------
--- --------------- serial_number_x nonclustered located on default
serial_number
customer_number, maint_hours_code,
maint_days_code
When run, the query returns the following statistics:
STEP 1
The type of query is INSERT
The update mode is direct
Worktable created for ORDER BY
FROM TABLE
dbo.product
Nested iteration
Table Scan
FROM TABLE
dbo.maint_hours
Nested iteration
Table Scan
FROM TABLE
...