Inserting Master and DEtail Tables
-
Is it better to use cursors or is it better to use multiple stored procs to do the same. Because the same thing I can achieve by using Cursor in stored proc. But its really really slow. Like may be its processing 23000 records per 40 minutes. I need to process 1 million records. And as you said If I call the database for each insert, isnt it going to be more back and forth traffic from the application database server vice versa. I tried my best to avoid cursors, only one way is to use function but I have to use my connection with some special permissions to execute Insert command within UDF. It seems its not easy in the organization. I am trying to put the insert logic in the stored proc and calling that stored proc within fuction but it is asking for the below permission. I am not sure if I can really do that in this way but when I am trying it failed to execute with the below error. Then I went for the Cursor approach but its running for too long. Please if you can please advice me something, I have to finish it as soon as possible.
EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE WITH OVERRIDEConfiguration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
Msg 5808, Level 16, State 1, Line 2
Ad hoc update to system catalogs is not supported.Thanks & Regards, Abdul Aleem Mohammad St Louis MO - USA
If you are inserting 1m records through a UI then you have bigger problems, I'd seriously look at your business logic. Where are you getting your 1m records from? Why are you inserting then one at a time? Nothing is going insert 1m single records with any acceptable speed. You need to look at how to get the bulk data into your database and then process the data in bulk!
Never underestimate the power of human stupidity RAH
-
If you are inserting 1m records through a UI then you have bigger problems, I'd seriously look at your business logic. Where are you getting your 1m records from? Why are you inserting then one at a time? Nothing is going insert 1m single records with any acceptable speed. You need to look at how to get the bulk data into your database and then process the data in bulk!
Never underestimate the power of human stupidity RAH
Hi, I am getting the records from two different databases and I have to put them into another database. I have to run a nightly job to perform this operation. I am not saying I am inserting one record at a time, I am doing it using cursor right now. But its taking too long time. Is there any other approach to do it. The insert process has to have the logic that I explained in the thread.
Thanks & Regards, Abdul Aleem Mohammad St Louis MO - USA
-
Hi, I am getting the records from two different databases and I have to put them into another database. I have to run a nightly job to perform this operation. I am not saying I am inserting one record at a time, I am doing it using cursor right now. But its taking too long time. Is there any other approach to do it. The insert process has to have the logic that I explained in the thread.
Thanks & Regards, Abdul Aleem Mohammad St Louis MO - USA
indian143 wrote:
I am not saying I am inserting one record at a time, I am doing it using cursor right now
Yes you are, a cursor implies you insert the master then insert the detail and loop to the next master! How are you transporting from the 2 databases to your DB? Idea Set up a view that joins the master/detail in the source systems and load all the records into a staging table in the destination DB, then process the inserts in bulk using 2 queries (if the DBs are linked then use the view direct).
Never underestimate the power of human stupidity RAH
-
indian143 wrote:
I am not saying I am inserting one record at a time, I am doing it using cursor right now
Yes you are, a cursor implies you insert the master then insert the detail and loop to the next master! How are you transporting from the 2 databases to your DB? Idea Set up a view that joins the master/detail in the source systems and load all the records into a staging table in the destination DB, then process the inserts in bulk using 2 queries (if the DBs are linked then use the view direct).
Never underestimate the power of human stupidity RAH
Is it ok if I do it using xp_CmdShell or OpenRowSet functions, because I need to have insert logic in my function. If there is any other approach without using these three CmdShell, OpenRowSet, Cursor, please give me that advice. staging is not an option for me right now.
Thanks & Regards, Abdul Aleem Mohammad St Louis MO - USA
-
Is it ok if I do it using xp_CmdShell or OpenRowSet functions, because I need to have insert logic in my function. If there is any other approach without using these three CmdShell, OpenRowSet, Cursor, please give me that advice. staging is not an option for me right now.
Thanks & Regards, Abdul Aleem Mohammad St Louis MO - USA
indian143 wrote:
staging is not an option for me right now.
IMHO you are screwed, your thinking is too small, no method that processes a single record at a time is going to perform when attempting to process 1m records! It seems like you are being artificially constrained by policy/political issues not technical ones. If you can't use a staging process on the target DB then create a staging DB on the same server that does the processing and shoves the data into the target DB. I have no experience with either openrowset or xp_cmdshell in a data loading context, never used them for that - ever!
Never underestimate the power of human stupidity RAH
-
indian143 wrote:
staging is not an option for me right now.
IMHO you are screwed, your thinking is too small, no method that processes a single record at a time is going to perform when attempting to process 1m records! It seems like you are being artificially constrained by policy/political issues not technical ones. If you can't use a staging process on the target DB then create a staging DB on the same server that does the processing and shoves the data into the target DB. I have no experience with either openrowset or xp_cmdshell in a data loading context, never used them for that - ever!
Never underestimate the power of human stupidity RAH
I am sorry, I didnt mean to discard your advice. But I am unable to decide. Time is very less for me. Can I use SSIS package for this. If it is can you please let me know how to do by just some psuedo code. I know SSIS a little bit but I am not an expert. Thanks in advance.
Thanks & Regards, Abdul Aleem Mohammad St Louis MO - USA
-
I am sorry, I didnt mean to discard your advice. But I am unable to decide. Time is very less for me. Can I use SSIS package for this. If it is can you please let me know how to do by just some psuedo code. I know SSIS a little bit but I am not an expert. Thanks in advance.
Thanks & Regards, Abdul Aleem Mohammad St Louis MO - USA
A little pseudo code is not going to help you design an SSIS package to do this job. Besides my knowledge of SSIS is possibly less than your! You need to resolve you transport issues (ie how am I going to get 1m records from 2 DBs into 1) before deciding on the tool to use! 1 record at a time is not going to cut it!
Never underestimate the power of human stupidity RAH
-
A little pseudo code is not going to help you design an SSIS package to do this job. Besides my knowledge of SSIS is possibly less than your! You need to resolve you transport issues (ie how am I going to get 1m records from 2 DBs into 1) before deciding on the tool to use! 1 record at a time is not going to cut it!
Never underestimate the power of human stupidity RAH
Ok :). Is it possible by using TempTable or Table Variable. If it can, please give me some Idea. I have to implement some logic in the Import process. The logic is if there is an existing value field in the Destination table I have to use that value only in the EntityId foreign key place, if there is no value field exists then I have to insert a record in the other Primary key table and use that same primary key value into this record insert. If that can happen by using temp table then that would really help me. Its kind of really complex situation. Please if you can help me by giving some idea that would help me a lot. Thanks in advance.
Thanks & Regards, Abdul Aleem Mohammad St Louis MO - USA
-
A little pseudo code is not going to help you design an SSIS package to do this job. Besides my knowledge of SSIS is possibly less than your! You need to resolve you transport issues (ie how am I going to get 1m records from 2 DBs into 1) before deciding on the tool to use! 1 record at a time is not going to cut it!
Never underestimate the power of human stupidity RAH
Hi, I didnt want to create another thread for this again so I am reusing the existing thread. Sorry if it causes inconvenience to you. I have implemented as you said, there are around 3 million records. I created a staging table for loading the data temporary. Now I have to insert or update all this data in to the actual table and delete data from staging. Now the problem that I am facing is, for certain conditions only we have to insert data but for certain conditions I have to update the data depending upon some logic. Everything I tried has failed or took enormously huge amount of time except SSIS that too only if I use just loading with mapped columns. But that is not going to help me a lot because I have to decide insert or update depending upon some logic. My questions here are: 1. Can I write logic in SSIS to update if the record already exists and certains columns are same, if so how can I do that. 2. If I incorporate logic into SSIS, can the performance be same as simple import process. Because the simple import process in SSIS uses the bulk insert mechanism with batch inserts of records. I tried in the following way, but it is taking a huge amount of time to insert 3 million records. Like 7 or 8 hrs.
UPDATE [IdentityResolutionService].[dbo].[Entity]
SET [SystemOfRecordId] = t2.[SystemOfRecordId]
from [IdentityResolutionService].[dbo].[Entity] as t1
inner join
(select DISTINCT [EntityId], SystemOfRecordId from IdentityResolutionService.dbo.Identifier_Staging where EntityId
in (select EntityId from [IdentityResolutionService].[dbo].[Entity])) as t2
on t1.EntityId=t2.EntityId
WHERE t2.EntityId = t1.EntityId and t1.SystemOfRecordId=@ACESSystemOfRecordIdINSERT INTO [IdentityResolutionService].[dbo].[Entity]
([EntityId]
,[SystemOfRecordId])
select DISTINCT [EntityId], [SystemOfRecordId] from IdentityResolutionService.dbo.Identifier_Staging where EntityId
not in (select EntityId from [IdentityResolutionService].[dbo].[Entity])
and [SystemOfRecordId] not in (select [SystemOfRecordId] from [IdentityResolutionService].[dbo].[Entity])Thanks in advance.
Thanks & Regards, Abdul Aleem Mohammad St Louis MO - USA
-
Hi, I didnt want to create another thread for this again so I am reusing the existing thread. Sorry if it causes inconvenience to you. I have implemented as you said, there are around 3 million records. I created a staging table for loading the data temporary. Now I have to insert or update all this data in to the actual table and delete data from staging. Now the problem that I am facing is, for certain conditions only we have to insert data but for certain conditions I have to update the data depending upon some logic. Everything I tried has failed or took enormously huge amount of time except SSIS that too only if I use just loading with mapped columns. But that is not going to help me a lot because I have to decide insert or update depending upon some logic. My questions here are: 1. Can I write logic in SSIS to update if the record already exists and certains columns are same, if so how can I do that. 2. If I incorporate logic into SSIS, can the performance be same as simple import process. Because the simple import process in SSIS uses the bulk insert mechanism with batch inserts of records. I tried in the following way, but it is taking a huge amount of time to insert 3 million records. Like 7 or 8 hrs.
UPDATE [IdentityResolutionService].[dbo].[Entity]
SET [SystemOfRecordId] = t2.[SystemOfRecordId]
from [IdentityResolutionService].[dbo].[Entity] as t1
inner join
(select DISTINCT [EntityId], SystemOfRecordId from IdentityResolutionService.dbo.Identifier_Staging where EntityId
in (select EntityId from [IdentityResolutionService].[dbo].[Entity])) as t2
on t1.EntityId=t2.EntityId
WHERE t2.EntityId = t1.EntityId and t1.SystemOfRecordId=@ACESSystemOfRecordIdINSERT INTO [IdentityResolutionService].[dbo].[Entity]
([EntityId]
,[SystemOfRecordId])
select DISTINCT [EntityId], [SystemOfRecordId] from IdentityResolutionService.dbo.Identifier_Staging where EntityId
not in (select EntityId from [IdentityResolutionService].[dbo].[Entity])
and [SystemOfRecordId] not in (select [SystemOfRecordId] from [IdentityResolutionService].[dbo].[Entity])Thanks in advance.
Thanks & Regards, Abdul Aleem Mohammad St Louis MO - USA
Abdul, My questions here are: 1. Can I write logic in SSIS to update if the record already exists and certains columns are same, if so how can I do that. 2. If I incorporate logic into SSIS, can the performance be same as simple import process. Because the simple import process in SSIS uses the bulk insert mechanism with batch inserts of records. The answer to your question isn't as simple as you may think. In the first place, your use of the phrase "can I" adds to the problem because it seems that you are very much an SSIS novice. so let me approach this in anothe way. 1. An SSIS package can be created that would handle your problem. The trick is to use the MERGE statement that is available in T-SQL. You can also use a pair of SQL statements such as those that you provided in your most recent post. Howver, your SQL code may be impacting our performance somewhat because you are using the IN (SELECT....) instead of using the INNER JOIN approach to determining which rows to update. 2. A straight forward bulk load is always going to be the fastest choice; however, since you need to UPDATE some rows and INSERT others, that option isn't available to you,so there's no point in worrying about that. ;-) That being said, though, if this is your first SSIS package, I would suggest that you create a temp table into which you the INSERT both updated versions of the existing rows and then the new rows (with the IDENTITY INSERT ON, so that you can keep the IDs of the existing rows), and then truncate the existing table and reload it from the temp table. ;-)