SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


linked server query performance degraded


linked server query performance degraded

Author
Message
Sqlsavy
Sqlsavy
SSC Eights!
SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)

Group: General Forum Members
Points: 903 Visits: 868
Hi,

I have a linked server and a SQL job is running from the source server. The stored procedure was fetching records from the destination table and inserting into a table at source.
Earlier source table had only 10 fields and the sp was taking 20 minutes to complete .But the same table has been modified and now has 70 columns and the same procedure is taking 48 hours to run. I feel number of columns in the table is an issue and I feel based on the new columns we should look at indexes .But my dev team is not convinced that new columns is a problem they are saying source table(insert) doesn’t have any index on it so the same query should work without any issues.

I still feel that number of columns from 10 to 70 is a huge leap for a linked server to fetch the data from destination server and that’s why job is taking so long to run.
How I can improve the performance of the sp any suggestions please?

Regards
Eugene Elutin
Eugene Elutin
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12686 Visits: 5478
Number of columns is not linked directly to the problem, as if these additional 60 columns will contain no data, the size of moved data between servers will not change much.
You might have 60 BIT columns added, which would just add maximum 8 bytes to each row, or just one column with VARCHAR(MAX) and each row in your table will contain 2Gb of data in this new column, then...

So, what about change in data volumes in your case?

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Carolyn Richardson
Carolyn Richardson
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2917 Visits: 3538
I'd ask about the stored procedure, what is it doing? Is there a simple select or is it a cursor. what is the table definition. You just havn't presented enough information for anyone to give you any real help.

Carolyn

Facts are stubborn things, but statistics are more pliable - Mark Twain
Carolyn
SQLServerSpecialists
twin.devil
twin.devil
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5428 Visits: 2692
did you check store procedure execution time on the source server i-e how much time it takes to execute ?
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)

Group: General Forum Members
Points: 99609 Visits: 33014
It really depends on how and where those 70 columns are coming from. You say it's a query to a linked server, are you joining a bunch of other tables, or is it a completely denormalized table that now has 70 columns? How are you querying the linked server? Through four part names? You may just be seeing the entire data set returned locally and then filtered (assuming there's any filtering going on). And, as others have pointed out, it really depends on what's in those 70 columns just how much data is getting moved.

Your developers really think that moving 1 byte is just as fast as move 2? And moving 100 is just as fast as moving 100 million? May be time to get new developers.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Sqlsavy
Sqlsavy
SSC Eights!
SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)

Group: General Forum Members
Points: 903 Visits: 868
Yes it is bunch of destination table fileds joined to the source table fields but not a denormalized table though. This was working fine until there were only 10 fields in table A but as soon as it is increased to 70 fields sp took ages to complete

Let me explain the code

At source there is table A and at destination table B,C and so on with 2 million records

BEGIN TRY
BEGIN TRANSACTION

DELETE FROM table A --- 500,000 records

INSERT INTO table A
SELECT filelds from table a and table b and so on
LEFT JOIN and
LEFT JOIN … and
WHERE ….

ORDER BY …
COMMIT TRANSACTION

END TRY

BEGIN CATCH
ROLLBACK TRANSACTION

Instead of permanent table A I advised developer to use temporary table and it was much quicker any thoughts why taht might be
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)

Group: General Forum Members
Points: 99609 Visits: 33014
Sorry, based on such a general and vague set of information, I can't even begin to guess where the issues may be.

Look at the execution plan to understand how it's resolving your issues.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Sqlsavy
Sqlsavy
SSC Eights!
SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)

Group: General Forum Members
Points: 903 Visits: 868
Thanks for all the help. As I said using temp table did resolve the issue
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search