February 17, 2014 at 4:07 am
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
February 17, 2014 at 4:58 am
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?
February 17, 2014 at 5:39 am
did you check store procedure execution time on the source server i-e how much time it takes to execute ?
February 17, 2014 at 5:39 am
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
February 17, 2014 at 7:29 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 17, 2014 at 9:04 am
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
February 17, 2014 at 9:14 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 17, 2014 at 9:17 am
Thanks for all the help. As I said using temp table did resolve the issue
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply