| Author |
Topic |
|
hmdsadeghian
Starting Member
4 Posts |
 Posted - 2010-02-12 : 03:14:50
|
| Hi.I write a Query to insert one record between records.This Query is :Update T1 Set T1.Id=T1.id+1 from Table1 T1 Where T1.id>5 This query is ok.But in Many Records Such as 200000 Records is very slow.how to write another Query? |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-12 : 03:40:18
|
| I don't think so if there are 200,000 records to re-number.We have a Sequence Number on some of our tables - so that we can present them in a manually-prescribed order.We leave gaps of 10 between the numbers, to allow inserts, and when a block is full we renumber it "locally" to make more space, rather than renumbering the whole 200,000 rows.When it becomes very crowded we renumber the whole table +10 - but that is very rare. |
 |
|
|
hmdsadeghian
Starting Member
4 Posts |
Posted - 2010-02-12 : 09:44:15
|
| Hi.thanks for your Reply.i Developed an accounting system.when users that work with accounting sometimes need to insert a voucher between vouchers Because in an accounting system,vouchers have to sort with date and Voucher number.i have vouchers between 2010/1/1 to 2010/1/20. Now i insert a voucher with date 2010/1/15.i must inesrt a record between vouchers that my number and date still sorting.Thanks |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-12 : 09:47:45
|
quote:
Originally posted by hmdsadeghian
Hi.thanks for your Reply.i Developed an accounting system.when users that work with accounting sometimes need to insert a voucher between vouchers Because in an accounting system,vouchers have to sort with date and Voucher number.i have vouchers between 2010/1/1 to 2010/1/20. Now i insert a voucher with date 2010/1/15.i must inesrt a record between vouchers that my number and date still sorting.Thanks
you shouldnt need to do insert in between other two records actually. SQL table doesnt have concept of first or last by itself. So wherever you do insert provided you save the date as 2010/1/15 you wont have difficulty in retrieving them in order provided you use order by date in your queries. So even if its an old voucher you can insert it as last record but just making sure you're putting correct retrospective date value.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-12 : 10:05:58
|
If you need a sequence number you could use floating point. So you have 1,2,3,4,... now add 3.5 to "insert" one in the sequence - much like you would probably write "3(a)" on it if doing it in the old days with a quill-pen  |
|
|
|
hmdsadeghian
Starting Member
4 Posts |
Posted - 2010-02-12 : 12:12:24
|
| Hi.Thanks.I know that sort data by order by date. but my problem is i need sort with date and voucher Number. Voucher Number Must serial.I cannot use Floating number because in Accounting number of voucher is integer and cannot float.now i insert vouchers sequence and with every date. when user sort by date and voucher,first backup database. then i create a temp table and insert all data from master table into this table. then sort by date and one by one insert into master table by new number and sequence. but this way with a many vouchers is very slow and if system restared my data is damaged and user must restore the database.Thanks. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-12 : 14:17:36
|
| "I cannot use Floating number because in Accounting number of voucher is integer and cannot float"But, surely, "in accounting" if you have issued a Voucher you cannot just renumber all the vouchers and insert a new one? You have to create a "12345(a)" type number, or somesuch. |
|
|
|
hmdsadeghian
Starting Member
4 Posts |
Posted - 2010-02-12 : 14:33:34
|
| thanks.But i must Sort numbers.because My customers First enter all vouchers and then Sort by number and date.now maybe enter all vouchers, they need enter another voucher between vouchers. Have you suggest another way to solve this problem?have a good time. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-13 : 03:43:33
|
| "Have you suggest another way to solve this problem?"No, if you are going to update 200,000 rows that is how long it is going to take.There are probably a handful of workarounds, but I doubt any of them will be faster:Export the table (with modified Voucher numbers), truncate, re-importInsert into new table (with modified Voucher numbers), drop original table and renameI can't see those being any faster, but their logging requirements might be less.You may have triggers firing on the update (maybe they don't need to do anything, in which case they could be improved to ignore a chance only to the Voucher column)Maybe you have Indexes / Foreign keys, or indexed Views, on the Voucher column, and that is slowing down the update.You could drop and recreate such indexes / FKeys, but then any concurrent processes will be mucked about, and you may get data added which breaks the referential integrity ... all best avoided, except for tuning up what is already there. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-13 : 03:44:23
|
| Actually I wonder if the Query plan will show any trigger activity etc for the Update? If so that would be worth posting here. |
|
|
|
|
Insert Between Records