Tag Info

Hot answers tagged

68

Column order does matter so if (and only if) the column orders match you can for example: insert into items_ver select * from items where item_id=2; Or if they don't match you could for example: insert into items_ver(item_id, item_group, name) select * from items where item_id=2; but relying on column order is a bug waiting to happen (it can change, as ...


17

Your syntax is almost good, needs some parenthesis around the subqueries and it will work: INSERT INTO bar (description, foo_id) VALUES ( 'testing', (SELECT id from foo WHERE type='blue') ), ( 'another row', (SELECT id from foo WHERE type='red' ) ); Tested at SQL-Fiddle Another way, with shorter syntax if you have a lot of values to insert: ...


17

One common approach: Disable / drop indexes / constraints on target table. INSERT dbo.[Target] WITH (TABLOCKX) SELECT ... With credit to JNK of course, you can do the above in batches of n rows, which can reduce the strain on the transaction log, and of course means that if some batch fails, you only have to-start from that batch. I blogged about this ...


14

Plain INSERT INSERT INTO bar (description, foo_id) SELECT val.description, f.id FROM ( VALUES ('testing', 'blue') ,('another row', 'red' ) ,('new row1', 'purple') -- purple does not exist in foo, yet ,('new row2', 'purple') ) val (description, type) LEFT JOIN foo f USING (type); The use of a LEFT [OUTER] JOIN ...


12

Suppose I have to export data from one server to another. Best is to use IF you want all data use Backup / Restore; BCP OUT & BCP IN or SSIS IF you want subset of data (some tables only) use SSIS or BCP OUT & BCP IN TO move data, depending on the amount/size of data and n/w bandwidth, Linked server will kill the performance. Executing ...


11

Use a view that excludes the virtual columns to do the manipulation. I've just tested this & it works: create view v_tq84_virtual_test_with as ( select col_1, col_2, col_3, col_4 from tq84_virtual_test_with ); declare r v_tq84_virtual_test_with%rowtype; begin select * into r from v_tq84_virtual_test_with where col_2 = 8; r.col_4 := r.col_4 - 2; ...


11

Why no clustered index? Why no primary key? This is most likely your problem: you don't have any order to the table (in the sense of, say, an IDENTITY column) this you are inserting into a heap See http://stackoverflow.com/q/5094400/27535 (SO) http://sqlblog.com/blogs/tibor_karaszi/archive/2008/08/14/are-inserts-quicker-to-heap-or-clustered-tables.aspx ...


11

As the other answers already indicate SQL Server may or may not explicitly ensure that the rows are sorted in clustered index order prior to the insert. This is dependant upon whether or not the clustered index operator in the plan has the DMLRequestSort property set (which in turn depends upon the estimated number of rows that are inserted). If you find ...


11

I have set up a test for checking the options. I'll include the code below, which can be run in psql on a linux/Unix box (simply because for the sake of clarity in the results, I piped the output of the setup commands to /dev/null - on a Windows box one could choose a log file instead). I tried to make different results comparable by using more than one ...


10

Some ideas: Inject some GO commands every thousand or few thousand lines. Then instead of one ginormous batch it is broken up into multiple batches. Change your individual INSERT statements to INSERT ... VALUES () with a thousand sets each. Use transactions and commit and/or checkpoint gratuitously (again, every 1000 inserts or so is probably a good place ...


10

Nothing is wrong with your table definition. (Except hat I would use jos_content_id or something instead of the non-descriptive column name id. And I probably would use text instead of varchar(50). Your INSERT statement is the problem. With your id column defined as serial, you shouldn't insert manual values for id. Those may collide with the next value ...


9

The reason is very simple. When you insert a row into MyISAM, it just puts it into the server's memory and hopes that the server will flush it to disk at some point in the future. Good luck if the server crashes. When you insert a row into InnoDB it syncs the transaction durably to disk, and that requires it to wait for the disk to spin. Do the math on ...


9

I have three different ideas that I mentioned in a comment above. Here is a little elaboration on at least one of them (which you are stuck on due to self-diagnosed tunnel vision). Well, you could calculate the number of rows that make up 10% beforehand, and then compare that in your batch. I was thinking about this but we don't really need to ...


9

If you don't specify an order by clause your RDBMS is free to return the results as it chooses. Usually it will return the results in the order it can retrieve the records the fastest. Since you have the column codGenLedger as the clustered key the records will be stored ordered by codGenLedger physically on disk (unless your indexes are fragmented, but ...


8

It the optimiser decides it would be more efficient to sort the data prior to insert, it will do so somewhere upstream of the insert operator. If you introduce a sort as part of your query, the optimiser should realise that the data is already sorted and omit doing so again. Note the execution plan chosen may vary from run to run depending on the number of ...


8

No, you don't need to gave the columns in the same order. Not least, table order may not reflect actual on-disk order (this is 100% true for SQL Server, and I'm sure MySQL is the same) Unless your OCD itch needs scratched


8

Are there any other snags I should be aware of that might result in an insert, update, or deletion of a record not incrementing this value? ora_rowscn is always incremented when a row changes - but in a default configuration it can also be incremented when a row does not change If you need to check the whole table for udates, one method is to use ...


8

It seems pretty easy: postgres=# create table inet_test (address inet); CREATE TABLE postgres=# insert into inet_test values ('192.168.2.1'); INSERT 0 1 postgres=# insert into inet_test values ('192.168.2.1/24'); INSERT 0 1 postgres=# select * from inet_test; address ---------------- 192.168.2.1 192.168.2.1/24 (2 rows)


7

The ORDER BY clause in the SELECT statement is redundant. It is redundant because the rows that will be inserted, if they need to be sorted, are sorted anyway. Let us create a test case. CREATE TABLE #Test ( id INTEGER NOT NULL ); CREATE UNIQUE CLUSTERED INDEX CL_Test_ID ON #Test (id); CREATE TABLE #Sequence ( number INTEGER NOT NULL ); INSERT ...


7

Three ways. Either IGNORE duplicate errors: INSERT IGNORE ... ; -- without ON DUPLICATE KEY or try to do a redundant update when there is a duplicate: INSERT ... ON DUPLICATE KEY UPDATE id = id ; or check for duplicates before inserting: INSERT INTO requests (id, ctg, msg, nick, filled, dated, filldate) SELECT (NULL, ...


7

You are dealing with a deadlock, not a performance bottleneck issue. If you have a thousand new records per hour, you are far far far away from reaching MySQL limits. MySQL can handle at least 50 times your load. Deadlocks are cause by application code and are not the database server's fault. Deadlocks can not be fixed on the MySQL server side, except in ...


7

Suppose you have mydb.mytb and you want to create mydb.mytbcopy I have five(5) approaches to doing this copy APPROACH #1 In the mysql client, run the following USE mydb CREATE TABLE mytbcopy LIKE mytb; INSERT INTO mytbcopy SELECT * FROM mytb; APPROACH #2 MYSQL_USER=root MYSQL_PASS=rootpassword MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}" mysql ...


7

Looking at your problem from an SSIS perspective I feel the reason this may have taken so long is that you didn't have batching on. This can lead to too many rows filling the SSIS pipeline and can hinder your SSIS performance as a result. What you need to do is alter your rows per batch setting and possibly your maximum insert commit size. Now what you set ...


7

SQL Server does not "rebalance the tree" as a periodic event. I have last heard this term in the context of Oracle. All that SQL Server does it increase the tree height when necessary. This is an event that happens only a few times in the entire existence of a B-tree. In a DML heavy workload there can be many small tree adjustments called page splits. These ...


7

Are there any reasons for periodic/cyclic slow-down of insert performance? Yes. check point events. With a write intensive workload, big RAM server, as you describe, a large number of 'dirty' pages accumulate in memory. At the predetermined checkpoint interval all these dirty pages get written to disk, causing a spike of IO requests. This in turn slows ...


6

The code you have inherited is broken - and it always has been broken. That re-factoring you'd like to avoid needs to be done. There is no alternative to an explicit order by to guarantee the sort order of a result set and there never has been. Who knows if the code you inherited always returned rows in the order the original developer 'expected' or not.


6

Some things you can look at... Reduce the batch size from 10000 to something smaller, like 2000 or 1000 (you didn't say how large your row size is). Try turning on IO Stats to see just how much IO the FK lookups are taking. What is the waiting caused by when the insert it happening (master.dbo.sysprocesses)? Lets start here and see where we go.


6

Did you try CREATE TABLE contacts ( id int auto_increment primary key, name varchar(20), network_id int, network_contact_id int, UNIQUE KEY (`network_id`, `network_contact_id`) );


6

Getting the max(ora_rowscn) will require a full table scan each time you do it. It may be faster just to refresh the entire cache each time. It sounds like you need a way to notify the other service that a change took place and what the change was. You could maintain a log table with a column that indicates which system needs to consume the change. The ...


6

Apparently you inserted rows into that table without using the sequence and that's why they are out of sync. You need to set the correct value for the sequence using setval() select setval('context_context_id_seq', (select max(context_id) from context)); Then the next call to nextval() should return the correct value. If the column is indeed defined as ...



Only top voted, non community-wiki answers of a minimum length are eligible