Re^3: How can I improve the sql statements applying efficiency

With a little more information, we might be able to provide a bit better guidance. A couple of unsubstantiated notes:

If the SQL is deleting and then inserting records with the same primary key, then it may be better to modify the script generating the SQL to use update statements.

If the data you're inserting always comes from other tables, it may be that the SQL is a particular operation record-by-record instead of treating the records as a set. For example, if you're updating the price column, then:

update the_table set price=price*1.2 where material='iron'
update the_table set price=price*1.1 where material='plastic'
[download]

is much better than

update the_table set price=17.54 where the_key=123
update the_table set price=1.50 where the_key=125
update the_table set price=15.00 where the_key=126
...etc...
[download]

If you're performing different types of transformations, it may be better to operate on columns instead of rows. For example:
```
update the_table set price=price*2 where material='iron'
update the_table set drop_ship=1 where is_heavy=1 or is_large=1
[download]
```
If you can provide a dozen or so records (sanitized, of course), we could look things over and see if we can offer suggestions.

...roboticus

Comment on Re^3: How can I improve the sql statements applying efficiency Select or Download Code

Replies are listed 'Best First'.
Re^4: How can I improve the sql statements applying efficiency by littlehorse (Sexton) on Jul 25, 2010 at 07:38 UTC
roboticus Thanks for your reply, but in my case using UPDATE statement is not good, since the changed fields vary for different records, I mean for the 1st record, maybe the changed fields is the first 3 ones, but for the 2nd record, maybe the changed fields are the last 3 ones. It will be tedious to find out which fields have changed for each record and then write out an update statement. For the 3 tables I have mentioned, the tbl_3 is almost a combine of the tbl_1 and tbl_2 on each primary key. Just like: `tbl_1: key col1 col2 --- ---- ---- 1 abc 123 2 xyz 987 tbl_2: key col3 col4 --- ---- ---- 1 bcd 12.5 2 zzz 11.1 tbl_3: key col1 col2 col3 col4 --- ---- ---- ---- ---- 1 abc 123 bcd 12.5 2 xyz 987 zzz 11.1` [download] Inspired by your bcp advice, I am just thinking whether I can bcp out tbl_1 and tbl_2, and then process the two bcp files to construct the bcp file for tbl_3. It should be much faster then inserting the sql statements.	[reply] [d/l]

Replies are listed 'Best First'.

Re^4: How can I improve the sql statements applying efficiency
by littlehorse (Sexton) on Jul 25, 2010 at 07:38 UTC

roboticus

Thanks for your reply, but in my case using UPDATE statement is not good, since the changed fields vary for different records, I mean for the 1st record, maybe the changed fields is the first 3 ones, but for the 2nd record, maybe the changed fields are the last 3 ones. It will be tedious to find out which fields have changed for each record and then write out an update statement.

For the 3 tables I have mentioned, the tbl_3 is almost a combine of the tbl_1 and tbl_2 on each primary key. Just like:

tbl_1:
key  col1 col2
---  ---- ----
1    abc  123
2    xyz  987

tbl_2:
key  col3 col4
---  ---- ----
1    bcd  12.5
2    zzz  11.1

tbl_3:
key  col1 col2 col3 col4
---  ---- ---- ---- ----
1    abc  123  bcd  12.5
2    xyz  987  zzz  11.1
[download]

[reply]
[d/l]