Remote query parameterization problem.

• deepak.a

  SSCertifiable

  Points: 5330

  More actions

  March 25, 2011 at 1:14 am

  #235759

  Hi All,

  In stored procedure i have used the remote query it tooks very long time insert records almost 20 - 22 mins. Query is as below

  INSERT INTO <table name>(col1,col2....coln) -- Local server

  SELECT col1,col2....coln

  FROM [Remote Server].[ Remote Database].<table1>

  INNER JOIN [Remote Server].[ Remote Database].<table2> SAQ ON <cond 1> = <cond 1>

  WHERE <Condition>

  the table1 contains total of 9-9.5 million records and table2 contains total of 8 millions records ,

  after the filter condition (join and where clause) it will return around 30 to 40 records.both the join condition column and where clause colmns are having indexes.

  Generally, the above statement should be returning one or zero rows from the remote database.

  The problem is execution plan changes and SQL Server estimates that 1 million plus rows will satisfy the query.As a result the remote query is not properly parameterized , too many rows are requested and it takes a few minutes to retrieve all data.Recompiling the stored procedure when this happens resolves the issue.But is there any other way to reslove the above issue to make proper parameterization.

  Thanks and regards

  Deepak.A

• deepak.a

  SSCertifiable

  Points: 5330

  More actions

  March 25, 2011 at 6:55 am

  #1303535

  Hi stewart,

  Thanks for you quick reply

  for the first option it is difficult to use because in where clause we are passing the variables to filter the records.

  for second option --

  I have some more remote query in that stored procedure almost around 18 to 20 tables i'm inserting from other different source tables.

  Is there any other way we can do this ?

  Thanks & regards

  Deepak.A