Strange SQL Server 7.0 / FOXPRO 6 problem.

[bernardmanning]

Hi,

I've got a pretty strange problem using SQL Server 7.0 sp4 along with Foxpro 6.0

I have a foxpro remote view that includes an index hint, eg ;

Select sum(something),SomeidField from table1 
  inner join table2 (INDEX (MyReqdIndex)) on table1.field1 = table2.field2 where someIdField = 43 group by SomeIDField.

If I cut and paste this statement into SQL 2000, it uses the hint and you can see it in the estimated execution plan.

If I do the same in SQL 7.0 , it runs ko, but I recieve a warning telling me that it isn't going to use the hint.

Now the problem comes when I send this statement via a remote view from foxpro to SQL 7.0

I receive a error '1526 Connectivity error: FORM. ODBC Error: 8118' when I try to use the view, which is indicating that the column 'SomeidField' is invalid as it's not an arregate function and is not in the group by list, which of course it is.

When I run SQL profiler against this remote view to see what is being sent to SQL server 7.0 I see this kind of thing ;

Select sum(something),SomeidField from table1 
  inner join table2 on table1.field1 = table2.field2 where 1 = 0

As you can see the group by has gone and the where clause has changed.

Does anybody know what's going on?

I've tried this with the latest service packs for fox, sql and the latest version of mdac to ensure the odbc drivers are up to date.

The weird thing is if I run this remote view against a sql server 2000 database set to 7.0 compatibility it works fine!!!

Many thanks in advance. Bernard

 

[mspratt]

Hi Bernard,

I am still reasonably clueless when it comes to SQL, so I can't be much help.

I have to ask though, just what is a "index hint"?

Regards,

Mike

 

[bernardmanning]

Hi Mike,

A query hint in sql server forces the query optimiser in SQL server to use a certain index in order to perform a join etc

You sometimes find the estimated execution plan shows that sql server is going to use a table scan, which is slow, in order to retrieve records from a table.

What you really want to see is an index seek. In order to force SQL server to use an existing index which would result in an index seek you can use a query hint.

Usually using hints are not necessary as the query optimiser usually does a good job of selecting the correct index.

Thanks, Bernard.

 

[mspratt]

Hi Bernard,

So
(INDEX (MyReqdIndex))
is the index hint.

Thanks for the clarification. I hadn't heard of it before.

Regards,

Mike

 

[zhavic]

What you really want to see is an index seek

I am not agree with that.
The query optimizer will create the best execution plan
and that plan can contain more index or table scans, and it still will be more eficient as that with index hint.

Note, that execution plan depends on number of rows in tables used in query,
or more accurately, it depends on count of occurences of expressions used in where and join clause.
Query optimizer uses 'statistics' to choice a best indexes.

You can look for list of statistics for your tables, using this:

sp_helpstats 'your_table'

Also try to look at result of this:

sp_autostats 'your_table'

If your_table has some records, there must be some date in Last_Updated column for each statistics.

If there are nulls in Last_Updated column, you need to update statistics for that table, but it may take more time:

UPDATE STATISTICS your_table WITH FULLSCAN

Than run sp_autostats 'your_table' again. If there is still null dates, and you have some rows in your_table, than you have broken statistics and you have to recreate your table. ( I has that situation in my database, all queries was too slow. I was recreated my tables, update statistics and than it was fast )

Without updated statistics, the query optimizer could not create the best execution plan, so it may decide to not use index.

Before you decide to use index hint, try to look on execution plan in query analyzer, if there are not some missing statistics, also try to look on your tables using sp_autostats ( as I wrote above )

Also you can use Index tuning wizard in Query Analyzer to get the best indexes for your queries. ( but it works only if there are not broken statistics )

You can also look into BOL for statistics:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_05_72r9.asp

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.