Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

linked server query performance degraded Expand / Collapse
Author
Message
Posted Monday, February 17, 2014 4:07 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 8:11 AM
Points: 232, Visits: 818
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
Post #1542030
Posted Monday, February 17, 2014 4:58 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:38 AM
Points: 2,858, Visits: 5,132
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1542052
Posted Monday, February 17, 2014 5:39 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Thursday, September 25, 2014 7:58 AM
Points: 1,460, Visits: 3,008
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
Post #1542060
Posted Monday, February 17, 2014 5:39 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 12:16 AM
Points: 752, Visits: 1,323
did you check store procedure execution time on the source server i-e how much time it takes to execute ?


Post #1542061
Posted Monday, February 17, 2014 7:29 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 9:27 PM
Points: 13,776, Visits: 28,178
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
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1542120
Posted Monday, February 17, 2014 9:04 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 8:11 AM
Points: 232, Visits: 818
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
Post #1542184
Posted Monday, February 17, 2014 9:14 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 9:27 PM
Points: 13,776, Visits: 28,178
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
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1542191
Posted Monday, February 17, 2014 9:17 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 8:11 AM
Points: 232, Visits: 818
Thanks for all the help. As I said using temp table did resolve the issue
Post #1542193
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse