Because Serial No cannot be break. There is no relation between Serial No and Card ID. Both are independent. I have made serial No and Card ID both primary key.
So please advice how to make faster way with good performance
1. Make SerialNo PK and CardID unique and generate a typed dataset for it
2.Fill the table with data from the database if there is data
3. Generate the rows in a loop and add them to the DataTable. If the CardID is duplicated catch the error and try again until you have all your 30k cards generated. (in this way you'll not have any duplicates)
4.Get an array of data rows (DataRow[]) from the table that contains the newly inserted rows.
5.Use SQLBulkCopy to insert them into the database. Here is a sample of how to work with it:
Your biggest performance hit will be when you generate the cards. Try to tweak that part until you get an acceptable result. But by using SQLBulkCopy the whole process will complete faster.
Please click 'Mark as Answer' if my reply has assisted you
ok let me clear the steps that you have described to do:
I have already some data in database.
So,
1. I will make typed dataset for SerialNo and CardID
2. I will fill data table with the records from database
3. After that I will start to generate 30K data and add this record in separate data table.
4. Now the question is, the record may be duplicate in current generation or may be previous saved record?
As you have said to get data rows() , is it means the newly generated record will be inserted into the same data table which contains saved data from database?
An example you need to work on it, this not as per your logic...
It takes around less than 20 mins to insert records. Optimize the columns and SP, the insertion time will be less.
USE [MyDB]
GO
/****** Object: Table [dbo].[CardMaster] Script Date: 02/06/2011 21:34:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CardMaster](
[id] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[SerialNo] [varchar](max) NULL,
[CardID] [varchar](max) NULL,
[SerialType] [varchar](2) NULL,
[SerialAmt] [numeric](18, 0) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE CardMasterUpdation
@inval varchar(2),
@inamt varchar(max)
AS
declare @SerialNo as varchar(max),
@CardNo as varchar(max),
@cnt numeric(18, 0),
@Flag numeric(18, 0)
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN
SET @Flag = 1
WHILE (@Flag <= 30000)
BEGIN
SELECT @CardNo = SUBSTRING(CONVERT(varchar(255), NEWID()), 0, 7)
IF NOT EXISTS(SELECT @CardNo FROM dbo.CardMaster WHERE CardID = @CardNo)
BEGIN
SELECT @cnt = COUNT(1) + 1 FROM dbo.CardMaster WHERE serialType = @inval;
SET @SerialNo = @inval + @inamt + '0' + cast(@cnt as varchar)
INSERT INTO dbo.CardMaster (SerialNo, CardID, SerialType, SerialAmt) VALUES (@SerialNo, @CardNo, @inval, @inamt)
SET @Flag = @Flag + 1
END
END
END
END
GO
exec CardMasterUpdation 'RH', '20'
Mark as Answer, on the posts reply's that helped you !!!.
3. After that I will start to generate 30K data and add this record in separate data table
Same as the one you fill with data from the database.
faisal.cse
Now the question is, the record may be duplicate in current generation or may be previous saved record?
As you have said to get data rows() , is it means the newly generated record will be inserted into the same data table which contains saved data from database?
Will not be duplicated because you don't let it to be. You don't insert a row if there is another CardID with the same value.
But I totally agree with @suthish nair that this will be faster to do in the database if you have the CardID generation already implemented as a SQL function
Please click 'Mark as Answer' if my reply has assisted you
faisal.cse
Member
132 Points
331 Posts
Re: Insert Large Amount Of Data in Sql Server 2005 Database With every time duplicate Check
Feb 06, 2011 11:47 AM|LINK
No.
Because Serial No cannot be break. There is no relation between Serial No and Card ID. Both are independent. I have made serial No and Card ID both primary key.
So please advice how to make faster way with good performance
Thanks in advance Mr. Gouse Baig.
ReenuGouse
Participant
1395 Points
385 Posts
Re: Insert Large Amount Of Data in Sql Server 2005 Database With every time duplicate Check
Feb 06, 2011 11:58 AM|LINK
If u dont delete any card then my previous solution is suggestable..
see brother, give me ur full description of problem so that I can give u an advice..
If i give u an idea, u r telling other issue..
so give me full description if u dnt feel it is confidential..
I am working for u only for free, i am not paid..
Many Thanks, Gouse Baig.
ReenuGouse
Participant
1395 Points
385 Posts
Re: Insert Large Amount Of Data in Sql Server 2005 Database With every time duplicate Check
Feb 06, 2011 12:04 PM|LINK
Ok, do one thing set identity property for Card No. it will automatically increments..no problem with it..
coming to Serial No.. read last inserted record's Card No and increment it by 1 then concatinate it with SH.
then insert Serial No any how Card No automatically increments..
but very first record u should give Serial No seperately..
Many Thanks,
Gouse Baig.
faisal.cse
Member
132 Points
331 Posts
Re: Insert Large Amount Of Data in Sql Server 2005 Database With every time duplicate Check
Feb 06, 2011 12:12 PM|LINK
Ok. See.
I want to generate 30,000 cards from asp.net application. In card there are 2 fields. One is Serial No and other one is Card ID (Password).
Serial No format is RH20000001 where RH is type, next 20 is Card Amount and rest are the sequential order like:
RH20000001, RH20000002, RH20000003, RH20000004, RH20000005 ....... RH20030000
Now come to Card ID, it is randomly generate.
Suppose,
Serial No Card ID
RH20000001 325874
RH20000002 5CHA58
RH20000003 325874 ----> (it is duplicate. So need to generate another card id but the serial no should be the same. No change in serial no)
RH20000004 385254
RH20000005 3AACC4
RH20000006 5CHA58 ----> (it is duplicate. So need to generate another card id but the serial no should be the same. No change in serial no)
.....
RH20030000 852145
I think you have got it now. If you have any question, ask me.
Please advice to make faster to get good performance for generate.
raduenuca
All-Star
24675 Points
4250 Posts
Re: Insert Large Amount Of Data in Sql Server 2005 Database With every time duplicate Check
Feb 06, 2011 01:04 PM|LINK
I suggest you do the following:
1. Make SerialNo PK and CardID unique and generate a typed dataset for it
2.Fill the table with data from the database if there is data
3. Generate the rows in a loop and add them to the DataTable. If the CardID is duplicated catch the error and try again until you have all your 30k cards generated. (in this way you'll not have any duplicates)
4.Get an array of data rows (DataRow[]) from the table that contains the newly inserted rows.
5.Use SQLBulkCopy to insert them into the database. Here is a sample of how to work with it:
http://www.codeproject.com/KB/database/TransferUsingSQLBulkCopy.aspx
Your biggest performance hit will be when you generate the cards. Try to tweak that part until you get an acceptable result. But by using SQLBulkCopy the whole process will complete faster.
Radu Enuca | Blog
suthish nair
All-Star
15176 Points
3304 Posts
Re: Insert Large Amount Of Data in Sql Server 2005 Database With every time duplicate Check
Feb 06, 2011 01:28 PM|LINK
The best way is do it on backend itself.
Create a Stored Procedure with your logics and insert into table.
This will be a faster than from passing values from a asp.net page.
Just call this SP from the page with any inputs and insert all 30000 records in table in single shot.
faisal.cse
Member
132 Points
331 Posts
Re: Insert Large Amount Of Data in Sql Server 2005 Database With every time duplicate Check
Feb 06, 2011 01:30 PM|LINK
ok let me clear the steps that you have described to do:
I have already some data in database.
So,
1. I will make typed dataset for SerialNo and CardID
2. I will fill data table with the records from database
3. After that I will start to generate 30K data and add this record in separate data table.
4. Now the question is, the record may be duplicate in current generation or may be previous saved record? As you have said to get data rows() , is it means the newly generated record will be inserted into the same data table which contains saved data from database?
Please advice for the above question.
Thanks in advance Mr. Raduenuca
faisal.cse
Member
132 Points
331 Posts
Re: Insert Large Amount Of Data in Sql Server 2005 Database With every time duplicate Check
Feb 06, 2011 01:34 PM|LINK
I have done the same as you have said Mr. suthish nair but it is taking too much time around 25-30 minutes.
suthish nair
All-Star
15176 Points
3304 Posts
Re: Insert Large Amount Of Data in Sql Server 2005 Database With every time duplicate Check
Feb 06, 2011 03:05 PM|LINK
An example you need to work on it, this not as per your logic...
It takes around less than 20 mins to insert records. Optimize the columns and SP, the insertion time will be less.
raduenuca
All-Star
24675 Points
4250 Posts
Re: Insert Large Amount Of Data in Sql Server 2005 Database With every time duplicate Check
Feb 06, 2011 06:06 PM|LINK
Same as the one you fill with data from the database.
Will not be duplicated because you don't let it to be. You don't insert a row if there is another CardID with the same value.
But I totally agree with @suthish nair that this will be faster to do in the database if you have the CardID generation already implemented as a SQL function
Radu Enuca | Blog