option(force order) within a view 1

[fluteplr]

SQL 2000, I have a really horrible view that takes about 10 minutes to run. If I copy the view code and run it in query analyser (the select statement part only) and add
option(force order) I can lower that time to 50 seconds.

Here is the problem, the option(force order) syntax appears to be invalid within a view.

I can get the same funtionality by doing the query
select * from myview option(force order) however that brings up the next problem, the view is being called by crystal reports and I can't get Crystal to save that part of the sql code. I am posting this in the Crystal forum also.

So does anyone know if there is a workaround for doing the force in the view?

Changing to a stored proc, is a possiblity but one I am trying to avoid since it would involve changes to a large number of really nasty crystal reports and the interface my program uses to call them.

 

[fluteplr]

One more thing. I have tried various table index hints and none of them have much impact. I think the problem is that this is a view of several other views and sql server is not figuring out the correct order to do the joins in, it seems to be picking the table with the least selective criteria first because it has an available index, as apposed to the views that do not, even though they are much more selective based on the results describled above.

 

[MapMan]

Hi fluteplr,

One huge thing to check when using SQL 2000 is that all comparisons to fields use the same explicit datatype. Such as, when joining two tables, make sure the datatypes of the fields in the "ON" statement match. If they don't SQL won't use the index for them. This includes fields in the "WHERE" clause.

To give you an example of how important this is, we had a query that was taking around 30-45 seconds to process. Then I checked to make sure the datatypes matched on the join fields and they didn't. After making the change, the query takes less than 1 second now.

HTH, MapMan

Assume nothing, question everything, be explicit not implicit, and you'll always be covered.

 

[fluteplr]

Did all that already all my tables and views join on integer data types. But thanks anyway.

 

[MapMan]

Something else to try is to put fields that are included in an index at the beginning of your SELECT statement and avoid using "*" for the fields to return. Putting indexed fields first, speeds up the execution plan. Plus, the wider the recordset, the longer the query.

FWIW, MapMan

Assume nothing, question everything, be explicit not implicit, and you'll always be covered.

 

[fluteplr]

No actually it doesn't. The query optimizer takes care of that for you. The problem in this case is the query optimizer is getting fooled. The problem is not the plan taking too much time the problem is the join order the optimizer is choosing. If you don't actually have an answer to the question I posed, while I appreciate your attempts, they are not actually helping.

 

[TomGrumbling]

Two Suggestions:
1. USE an ADO (ODBC) datasource with Crystal Reports and you write your own SQL in Enterprise Manager and paste it into the ado query window. I usually select 1 field from 1 table and then click edit the SQL and paste in my entire query. If you need to edit they SQL in the future you may have to click on Validate Database instead of Show SQL to get back to the SQL Select statement.


2. Use the (NOLOCK) option with table
Keeps SQL Server from locking a table or being
locked out of a table. This is fine for reporting,
never for updating.

SELECT *
FROM tablename (NOLOCK)
WHERE...