Post reply

SQL 2000 View using a datasource of SQL 2005 but not using the index

  • November 18, 2008 at 2:17 am

    #215346

    I have a 7 million line table (indexed on field Order_ID, but not PK as multiple order lines) on a SQL 2005 server.

    When I do a query in SQL 2005 on Order_ID, it returns the results in about 2 seconds which I am happy with. The problem is when I link a View in SQL 2000 to the SQL 2005 table and do the same query on the same Order_ID, it takes 2 mins to find the result.

    I am assuming therefore that it is not using the Index on SQL 2005. Is there away around this?

    Thanks

    Andy

  • November 18, 2008 at 2:44 am

    #899734

    due to network problem, if SQL 2000 is exists in another location.

  • November 18, 2008 at 3:06 am

    #899741

    It is only returning between 1 and 5 records per enquiry, and the servers are linked. In addition they are on the same hub in the computer room. So generally the speed between the two servers is very good.

  • November 18, 2008 at 3:35 am

    #899753

    Andy Howell (11/18/2008)

    It is only returning between 1 and 5 records per enquiry, and the servers are linked. In addition they are on the same hub in the computer room. So generally the speed between the two servers is very good.

    you can do some analysis on both servers, for example:

    * check the update statistics for tables used in view.

    * create a non clustered index on the table.

    * just access the table from SQL 2005 Management Studio (select * from ;

    and possible run some DBCC commands on the servers (in offline mode).

    ex: DBCC UPDATEUSAGE ( )

    for more information see SQL BOL.

    Hope to work fine.

  • November 18, 2008 at 5:10 am

    #899803

    Two things to check ..

    What is the data type of all columns in the index?

    Are the two databases collations the same?

    Reason for asking is if the columns are char/varchar etc and the collations not identical then rather than the where clause being evaluated on the remote server, it will ship all the data to the local one and evaluate there.

    If you can post the full DDL and linked server options it would help.

    The execution plan will also show you which bit of the query is being executed locally and which remotely.

    Mike

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply