March 7, 2008 at 7:39 am
Hi All
I wrote the following script. Its gets list of tables left joint to compare data between 2 table and insert the difference.
My problem is its taking for ever to Run.
What could be the problem.
CREATE PROCEDURE [dbo].[Update_D_Tables_InBlake_DBN12]
AS
BEGIN
DECLARE @TableName VARCHAR(255)
DECLARE @sql VARCHAR(MAX)
DECLARE @Column VARCHAR(1000)
DECLARE @ID VARCHAR(255)
DECLARE TableCursor CURSOR FOR
SELECT Table_Name FROM INFORMATION_SCHEMA.TABLES
WHERE Table_Name in ('D_Address',
'D_AddressBankaccount',
'D_AddressCustomer',
'D_AddressEmployer',
'D_Application',
'D_ApplicationCustomerPri',
'D_ApplicationCustomerSec',
'D_ApplicationDetail',
'D_ApplicationDetailStatusHistory',
'D_ApplicationDetailUpdateHistory',
'D_ApplicationHistory',
'D_ApplicationQuestions',
'D_ApplicationSendReports',
'D_ApplicationStatusHistory',
'D_BankAccount',
'D_BlackHorseBranch',
'D_BlackHorseResponses',
'D_BrokerReferralInfo',
'D_BrokerWorkflow',
'D_Calls',
'D_CallTable',
'D_CCJs',
'D_CitiResponses',
'D_Contact',
'D_ContactCustomer',
'D_ContactEmployer',
'D_Customer',
'D_DataExtracts',
'D_DNCList',
'D_DuplicateApps',
'D_DuplicateMatches',
'D_EDLResponses',
'D_Employer',
'D_ExistingCredit',
'D_FinancialCustomer',
'D_FinancialStatements',
'D_HomeownerDetails',
'D_IncompleteResults',
'D_IncompleteRules',
'D_LenderOtherDetail',
'D_LenderOwnerRoles',
'D_LenderPlans',
'D_LenderResponse',
'D_LenderSender_Report',
'D_LenderStatusRole',
'D_LenderStatusRoleAssign',
'D_LoanDetails',
'D_Mortgage',
'D_Occupations',
'D_OccupationsCustomer',
'D_PhoneLogin',
'D_PhoneLogin_Skills',
'D_ProvidentResponses',
'D_ReportAllianceLeicHistory',
'D_SkillSet',
'D_Tenancy',
'D_Users',
'D_WelcomeResponses'
)
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @Line VARCHAR(1000)
SET @Line = ''
SET @ID = ''
SELECT top 1 @ID = @ID + QUOTENAME(c.Column_Name)
FROM INFORMATION_SCHEMA.TABLES T
JOIN INFORMATION_SCHEMA.COLUMNS C
ON t.Table_Name = c.Table_Name
WHERE t.Table_Name = @TableName
SELECT @Line = @Line + 'b.'+ QUOTENAME(c.Column_Name) + ' , '
FROM INFORMATION_SCHEMA.TABLES T
JOIN INFORMATION_SCHEMA.COLUMNS C
ON t.Table_Name = c.Table_Name
WHERE t.Table_Name = @TableName
SELECT @Column = SUBSTRING(@Line, 1, LEN(@Line) -1)
SELECT @sql = SUBSTRING(@Line, 1, LEN(@Line) -1)
SELECT @sql = 'INSERT'+ ' '
SELECT @sql = @sql + 'INTO Staging.dbo.'+ @TableName +' ' + 'SELECT' + ' ' + @Column
SELECT @sql = @sql + ' '+ 'FROM MyAPOLLO_101.dbo.'
SELECT @sql = @sql + @TableName + ' ' +'B' + ' '
SELECT @sql = @sql + 'LEFT JOIN' + ' '
SELECT @sql = @sql + 'Staging.dbo.'
SELECT @sql = @sql + @TableName + ' ' +'C'+ ' '
SELECT @sql = @sql + 'ON' + ' '
SELECT @sql = @sql + 'B.'+ @ID + ' '+ '=' + ' ' + 'C.'+ @ID + ' '
SELECT @sql = @sql + 'WHERE ' + ' ' + 'C.'+ @ID + ' ' + 'IS NULL' --Edited 04/03
EXEC (@Sql)
PRINT @TableName
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
END
March 7, 2008 at 7:57 am
How many rows are in the tables? Don't forget that Cursors operate row by row and are therefore slow.
March 7, 2008 at 8:01 am
Hi
Well I tested with more than 50 000 rows. But the strange thing is that even if there is no data its still hangs for more 40min
March 8, 2008 at 5:39 am
When was the last time you reindexed or updated the statistics on these tabels?
Also, have you tried running this as native SQL code and not in a stored procedure? I had a problem the other day when running code in a stored procedure, but it ran fast in native SQL. This was down a problem known as Parameter Sniffing. Might be worth a look.
March 8, 2008 at 8:32 am
First, are you including all the tables in your database in the IN list? If you are you should get rid of the IN and use where TABLE_CATALOG = [dbname] that will allow indexes to be used properly. A long IN list usually causes a scan which it does in your case. If you are not listing all your tables then you may want to create a table with that stores the table list so you can join on it. Then you can also add tables later without changing your code.
Are you doing this on a regular basis or is this a 1 time thing? If this is something that will be running often, wouldn't it make sense to write the individual inserts once, even using this procedure to just create the statements, and then run the statements statically instead of dynamically recreating them every time? If you use the table I mentioned above you could even store the statements there, generate them dynamically and then run them from a cursor on that table.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 9, 2008 at 1:39 am
This looks like a replication task - copies missing data from one database to another.
So if there's a lot of data in any database, it takes time to do a join and to copy data.
Why don't you use replication?
March 9, 2008 at 3:01 am
Hi Chris
I did try running in native SQL its doesn't make that much of difference.
Hi Jack
I'm not including all the tables its just the one named in the IN. I'm doing this on regular basis data has to be very close to live at modt 15min different. I'm not clear when you say writting the single insert once. I kind of like the idea of creating the table and then stores the table list so that I can join on it. Can you please elaborate on that.
Hi Robert
I would love to impliment replication but I've 60 GB of free space with is also running Full and TransactionLogs Backup files. Implimenting a Transactiona replication I think that might cause a problems.
March 9, 2008 at 11:03 am
When I say run the single inserts once is, instead of running this stored procedure with the cursor and and dynamic sql, run it once to create the insert statements as they won't change unless you change the table definitions. Then create separate sp's for each insert, that you can call from a single sp if you like. This will allow the server to create and cache the execution plan(s) and should result in better performance. With the cursor and dynamic SQL you are recompiling the SP every time.
As for a table to store the "replicated" tables. I would define it like this:
Create table dbo.replicated_tables
(
table_name sysname Primary Key Clustered Not Null,
insert_statement varchar(Max) or insert_sp varchar(50)
)
If you add the second column you can use a cursor to loop through that to call the sp's or run the insert statement. Also, if you want to automate re-generation of the insert you could use DDL triggers to recreate the insert_statement/insert_sp whenever the table is modified.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 10, 2008 at 12:10 am
I don't think that that cursor is the bottleneck. I ran a similar sp but replaced exec with print and it executed in practically no time for a hundred of tables.
When the amount of data grows, such inserts from joins are a bottleneck, so as Jack suggested, create triggers that replicate data. They're triggered when needed, so you always have accurate data in replicated database and demand a lot less from the server.
March 10, 2008 at 12:30 am
Thankx Guys
I will try to use what you suggested.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply