New answers tagged

0

15K in 5 minutes is less than one per second - that is slow 100+ / second is reasonable The likely culprit is page splits and index fragmentation Give this a try On the insert sort by the order of the PK on the insert Put a fill factor of like 50 on all the indexes Try an insert If this work then tune the fill factor and add in index maintenance Maybe ...


0

If you are using INSERT INTO statement try to enclose everything into a single transaction block: Something like this for example: declare @i int set @i = 0 set nocount on BEGIN TRANSACTION while @i < 2000 begin insert into testdb(testcolumn) values (1) set @i = @i ...


0

Without more detail about the procedure[1] and the table it is inserting into we can offer any specific help. Some general advice though: If you are inserting the rows individually, try arrange it so the are inserted one large blocks, all at once if possible. Confirm that the procedure is in fact being slow due to its own activity rather than waiting for ...


0

It sounds like what you are looking for is a "unique" constraint: ALTER TABLE Table1 ADD UNIQUE(Series, Orders) That will cause an error to be raised when you try create a row (by inserting new or updating an existing one) that duplicates an existing combination of Series and Order. Depending on the rest of the database design, these columns might be a ...


-2

I guess it's a locking issue. While such inserts take so long use admin view below. The column HLD_CURRENT_STMT_TEXT shows the blocker statement (what cause the insert took so long): db2 select * from SYSIBMADM.MON_LOCKWAITS


4

Seems like you want - if table test has N rows - to insert N more rows, with the values calculated from the join between the existing rows in test and test2. You then need to use the INSERT ... SELECT syntax and not the INSERT ... VALUES. Assuming that test2 has a UNIQUE constraint on (RowID), then the following will insert (max) N rows to test: INSERT ...


0

According to your question the sample tables are follows: Table:ITEMS Field's Name Type ------------ ------ Serialno Number (PK) Qty Number Table:ORDERS Field's Name Type ------------ ------ Orderno Number (PK) Serialno Number (PK)(FK) Qty Number The following trigger ...


-1

No trigger is needed. If you SELECT from Stockitem the DBMS will take a shared lock on that table. This will prevent updates to this table since UPDATEs need an exclusive lock, which cannot be granted while the shared lock exists. So, in the application (or stored procedure or whatever) start a transaction (to ensure locks are held until we're finished) ...


2

In Postgres 9.5, the most elegant, simple, safe and fast solution is the new UPSERT implementation: INSERT INTO device (device_id) VALUES (1234567) ON CONFLICT (device_id) DO NOTHING; -- now we have either inserted the device or it was there already INSERT INTO data (device_id, data) VALUES (1234567, 'some data); Obviously, there must be a some kind of ...


0

I always try to register devices first and then only they can send data.But in your case ,you can try some thing like below if exists(select 1 from devices where deviceid=@deviceid) insert into datatable else insert into devicestable insert into datatable Exists is always faster,but you may to have see if the column which we check for existence is ...


-2

In my opinion Select exists should be good in terms of performance. As select is faster than insert in terms of performance.Select only reads data and return the result, The insert query must read that data, insert in the table, then it check for primary key and found guilty of duplicate, then it revert the changes and raise an error.


0

You need to have DELIMITER directives both before and after the CREATE TRIGGER statement, you need to end individual simple statements with semicolons, and you need to end the complete CREATE TRIGGER with the delimiter specified in the first DELIMITER directive: DELIMITER // CREATE TRIGGER subtract_quantity AFTER INSERT ON parts_used FOR EACH ROW BEGIN ...


1

USE SET IDENTITY_INSERT TABLEnAME ON Remember to turn off identity and that only one table can have identity_insert On at one time


3

Explicit identity insert require IDENTITY_INSERT property set to ON. SET IDENTITY_INSERT MyTable ON -- Statement Allows explicit values to be inserted into -- the identity column of a table. GO INSERT INTO MyTable(ID, Name, Description) VALUES (0, 'Special title', 'special item'); GO SET IDENTITY_INSERT MyTable OFF -- ...



Top 50 recent answers are included