I want to generate 30,000 cards and each card must be duplicate check with database. In my card, there are 2 things. Serial No and CardID. If any card already exists then I generate another card id but with the same serial no.
So how faster way I can generate 30,000 card with duplicate check? Which one I have made application, it takes about 25 minutes to insert.
Suppose, now I come to my site and want to generate 30,000 cards. Card Contains Serial No and Card ID(Password). SerialNo is in sequentially ordered. If There is any duplicate record in database for Card ID, then I need to generate another Card ID for that
Serial No. So Serial No will not be disorder.
What I have done now- I generate one card Serial No and Card ID then I check in Database if EXISTS or not. If EXISTS it returns to me value OtherWise it insert into database. If EXISTS and return a value then I generate another Card ID for the same Serial
No.
Now it takes too much time around 25-30 minutes to generate.
Is there any way to make it faster and good performance.
Are you telling about Serial no? Because Serial No has its own format with character also. I need to check for the Card ID if duplicate or not which one I have newly generate. Moreover, Card ID is also alpha numeric.
Yes. But for the Serial No no problem. It will not be duplicated. It is coming from code behind. But I aware for the Card ID. Because it is randomly generate and may be it generate duplicate record. So I need to regenerate the Card ID for the same Serial
No.
faisal.cse
Member
131 Points
318 Posts
Insert Large Amount Of Data in Sql Server 2005 Database With every time duplicate Check
Feb 06, 2011 06:23 AM|LINK
I want to generate 30,000 cards and each card must be duplicate check with database. In my card, there are 2 things. Serial No and CardID. If any card already exists then I generate another card id but with the same serial no.
So how faster way I can generate 30,000 card with duplicate check? Which one I have made application, it takes about 25 minutes to insert.
I need faster way.
Please advice
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 07:31 AM|LINK
Is this a one time generation or you must be able to do it from an application regulary?
Radu Enuca | Blog
faisal.cse
Member
131 Points
318 Posts
Re: Insert Large Amount Of Data in Sql Server 2005 Database With every time duplicate Check
Feb 06, 2011 08:22 AM|LINK
Suppose, now I come to my site and want to generate 30,000 cards. Card Contains Serial No and Card ID(Password). SerialNo is in sequentially ordered. If There is any duplicate record in database for Card ID, then I need to generate another Card ID for that Serial No. So Serial No will not be disorder.
What I have done now- I generate one card Serial No and Card ID then I check in Database if EXISTS or not. If EXISTS it returns to me value OtherWise it insert into database. If EXISTS and return a value then I generate another Card ID for the same Serial No.
Now it takes too much time around 25-30 minutes to generate.
Is there any way to make it faster and good performance.
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 09:48 AM|LINK
yes, in database we identity property
it will automatically increses the value By given value..
so u no need to check it..
it will automatically generates..
Many Thanks,
Gouse Baig.
faisal.cse
Member
131 Points
318 Posts
Re: Insert Large Amount Of Data in Sql Server 2005 Database With every time duplicate Check
Feb 06, 2011 09:51 AM|LINK
Are you telling about Serial no? Because Serial No has its own format with character also. I need to check for the Card ID if duplicate or not which one I have newly generate. Moreover, Card ID is also alpha numeric.
So, how can make it faster?
Please advice.
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 10:02 AM|LINK
then do one thing,
make Card ID column as Priimary key.
Many Thanks,
Gouse Baig
faisal.cse
Member
131 Points
318 Posts
Re: Insert Large Amount Of Data in Sql Server 2005 Database With every time duplicate Check
Feb 06, 2011 10:35 AM|LINK
Now One thing, SerialNo will not be duplicate. I have made Card ID primary key.
But the question is, I have to generate another new Card ID for the same Serial No
Such as,
Serial No Card ID
SH123 123 --> Inserted
SH124 123 (Again Come)
Now I have to generate another card id for SH124 Serial No.
Please advice for faster 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 10:53 AM|LINK
I cannot understand ur query..
U want both Serial No and Card ID unique???
Many Thanks,
Gosue Baig.
faisal.cse
Member
131 Points
318 Posts
Re: Insert Large Amount Of Data in Sql Server 2005 Database With every time duplicate Check
Feb 06, 2011 11:10 AM|LINK
Yes. But for the Serial No no problem. It will not be duplicated. It is coming from code behind. But I aware for the Card ID. Because it is randomly generate and may be it generate duplicate record. So I need to regenerate the Card ID for the same Serial No.
Thanks in advance Mr. Gosue 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:16 AM|LINK
Do like this...
Serial No Card ID
SH123 123
SH124 124
//suppose 125 deleted
SH126 126
make Card No as Primary key and Serial No as SH+Card No.
suppose Card No is 143 then Serial No is SH+143=SH143..
I hope u like this solution,
If so please mark as ANSWER.
Many Thanks,
Gouse BAig.