MS Access to SQL Server with optimizer hints in query or less selective optimizer (more details)

MS Access to SQL Server with optimizer hints in query or less selective optimizer (more details)

Post by it.. » Thu, 08 Oct 1998 04:00:00



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

-------------------- --------------------------------------------------------
-----------------------------------------------------------------------------
---
-----------------------------------------------------------------------------
---
-----------------------------------------------------------------------------
--- --------------- serial_number_x  nonclustered located on default
serial_number

er_query_x           nonclustered located on default
customer_number, maint_hours_code,
maint_days_code

No constraints have been defined for this object.

No foreign keys reference this table.

Remember, in the solution, the query cannot be changed!
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 ...

read more »

 
 
 

MS Access to SQL Server with optimizer hints in query or less selective optimizer (more details)

Post by Andrew Prosse » Thu, 08 Oct 1998 04:00:00



>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.

If you want to amend things on the server then make sure all your SQL is
encapsulated in a stored procedure, your Access app then calls this and it
is then a matter of tweaking your SQL in the procedure and your front end
remains the same

Regards
--
Andrew Prosser

 
 
 

1. Index, Query optimizer, and Index hint

Help please.

I have a table CDR with 20 columns:  BeginDate char(8), BeginTime char(8),
ACPID char(5), TNI ..........
An index iACPDate was created on column (ACPID, BeginDate).
The table has 12,831 rows(small table).

Here is the query:
              select * from CDR where ACPID = 'ABD1A' and BeginDate >
'20000401'
This query returns 27 rows. The showplan tells me that index iACPDate was
used.

However, with
              select * from CDR where ACPID = 'ABD1A' and BeginDate >
'20000301'
The query returns 96 rows. But the showplan tells me that a table scan was
used.

I am using SQL 7.0(no SP installed). I updated statistics and rebuilt
indexes. I still
got same results. Returning 96 rows in 12,831 rows needs a table scan? I
just don't
understand this. I tried Index turning wizard and Profiler, not much help.
If I use index
hint, I am always OK. What am I missing?

Thanks for your help.

Yang

2. CDP FAQ Monthly Update

3. Setting Optimizer-Hints for Query-Performance Improvement

4. configure lock scheme

5. Optimizer hints in PL/SQL's embedded SQL

6. Ideal SUN Server(s) Configuration for 4000 Users

7. Optimizer hints and PL/SQL blocks

8. Possible bug with Oracle Web Application Server 3.0.0.18.0

9. MS SQL Server optimizer info required