Let's take a look at how to optimize this sentence.

Source: Internet
Author: User
Let's take a look at how to optimize Delphi, Windows SDK, and API.
Http://www.delphi2007.net/DelphiDB/html/delphi_20061221153924184.html
Insert into MDB .. tbnv (counter_sno, in_no, trn_date, curr_no, item_no, account_no, trn_amt) Select isnull (counter_sno, true_sno), isnull (in_no, 0), '2017', isnull (curr_no, 0), isnull (item_no, 0), isnull (account_no, 0) from (select * From sqlsrv. tbac. DBO. tblsz_ht06 where trn_date = '201312') A right join (select distinct (true_sno) from sqlsrv. tbjd. DBO. tbocrresult where trn_date = '20140901' and packfile = '20160901' and Len (true_sno) = 12) B on counter_sno = true_sno


Where MDB .. tbnv is an access table used to set remote servers. All fields cannot be blank or have no default value, sqlsrv. tbac. DBO. tblsz_ht06 and sqlsrv. tbjd. DBO. tbocrresult is an SQL Server table on another server, where the number of records is about 3 million. Now it takes about two minutes and a half to run. I hope you can help me to see if it can shorten the time to 1 minute, I don't need to worry about adding the table indexes. I mainly want to see where this sentence can be optimized. Thank you!

Advance

A bunch of questions,
Simply put yourCodeChange to this pseudo code
Insert into tablename (c_name1, cname2, cname3. .. cname_n) Part 1
Select c_name1, cname2, cname3.. cname_n from Part 2
The third part of a new result set obtained from the associated Query

However, if you do not change the table structure or index, you can simply rely on statement modification to improve performance. The only thing you can change is the third part of the statement, that is, the part of the associated query, I don't know about the table, but I want to modify it so that the performance of the associated query is improved based on your table structure.

Select * and distinct (true_sno) cannot be optimized. Basically, there is no 3 million data to be optimized!

Try to remove your isnull and do not use the function in the column

Isnull is an operation on the result. Using a function does not affect the speed;
Replace select * With the select field list.

Thank you. This is not a problem. I have connected my database to the backup machine, so it is slow.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.