Skip to Content
author's profile photo Former Member
Former Member

Table Comparison Performance

I have an issue with one of our table comparisons in that it is hanging after processessing about 8000 rows of data which complete in approximatly 40 seconds. the process is comparing 35,000 rows against 2.5 million rows in the target table. The source and target tables are in the same database (forced via a data transfer).The comparison method is on row by row select and the comparison key has been indexed on the target table. checking the database where the process is running then all we have is a select statement from the source and

Code:

FETCH API_CURSOR000000000000BDBA

is the other query that is running (the characters at the end are different)

the waits on the queries are all ASYNC_NETWORK_IO but these seem to clear quickly enough.

I should note that the database is SQL Server 2005 and i am running Data Services 12.2.3.3

I should also note that up to 2 days ago this process worked without issue (if a little slow) but there are dependencies on this job completing so several other processes cannot be started due to this. I have also tried the sorted input method but to no avail, the Primary Key on the source is a varchar and i believe this could be the issue with the sorted input option.

Any help would be greatly appreciated.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • author's profile photo Former Member
    Former Member
    Posted on Jan 09, 2013 at 04:57 PM

    Hi,

    What problems are you facing with the sorted input option? This should work with a varchar PK.

    If you view the optimised SQL you should see the ORDER BY statements for both the source query and the table comparison query - are these the same?

    Regards

    Nigel

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.