A discussion post on improving SQL query

IDN (keyword), product name, product quantity...
Table B, with fields: IDN, a_idn (record the keywords of table A), process, work hours...
Table A has a one-to-many relationship with table B,
I want to get the details of table A and the total work hours related to table B.
Sele AA. * from a aa left Outer Join
(Select a_idn, sum (Working Hours) from B group by a_idn) BB on AA. IDN = BB. a_idn
In this way, although the results I want are obtained, the speed is very slow, especially when other tables need to be connected in this way. A layer-by-layer connection causes extremely slow speeds and more than two thousand pieces of data, it takes seven or eight minutes to get the result. this is definitely not the case.
How can I write data to increase the query speed? Hope you can pick up

No, I think it should be done within 1 s. There is nothing else in this query.
What else do you mean by more than two thousand rows: Table A or table B?

The statement is not complex.

Table A has more than two thousand pieces of data added with conditions. Today, when the customer calls and says, the query prompts a timeout link. I found that
This is the problem. The left link is set to six layers. One layer is slower than the other, and the last layer times out!

Of course, if you set a layer of left link, the slow link is slower, but the link will time out if you set more links !!! Is there any improvement?
There is no way to use loops ~~~~~~

Try View

Is your business logic complicated? It should not be 7 to 8 minutes. If not, you should create a temporary table and put the intermediate results in it.

...... If the link between a simple table and a table is fast, but the link to the group by statistical result to the left is obviously slow, I will try to make the statistics into a view and then the link will be faster.

My tables are not complex, that is, the [customer, processing type, project name] of the primary table is recordedCodeTo obtain Chinese information, you must obtain the information in the other three comparison tables. There are two statistical results: the number of arrivals and the processing time, which are respectively calculated from the other two tables.
So the left link is set to six layers...

A view does not improve the efficiency. It is only used to control permissions or query formats.

Do not use left Outer Join
Low Efficiency
Write it like this.
Sele AA. * from a AA,
(Select a_idn, sum (Working Hours) from B group by a_idn) BB where AA. IDN = BB. a_idn

Sele AA. * from a AA,
(Select a_idn, sum (Working Hours) from B group by a_idn) BB where AA. IDN = BB. a_idn
There is a problem in writing this way. You cannot display all the data in table.

Can I use stored procedures?

Is it so exaggerated? I feel that SQL statements are not complex. I have written more complicated SQL statements, and it is not so slow when I use the cursor. Otherwise, use a temporary table to record the total working hours of Table B in the temporary table.

In this case, there is a general solution: You can create one or more temporary tables based on the situation, because each temporary table has a small amount of data, so there will be no database connection timeout, if the data volume is large and the execution time is long, you can use the progress bar to identify the progress.

Yes.
Select a. Product Name, A. Product Quantity sum (B. Work hours)
From table A, Table B
Where a. IDN = B. a_idn
Group by A. Product Name, A. Number of Products

It is not complex. It may be because the left link is too many, especially when it is connected to the group by subquery, it will affect the query speed.
I have never dared to use a temporary table,
Select * into # TMP from table,
If multiple users in the network query at the same time, will the execution of this statement conflict? Will temporary tables be deleted automatically?

The session will be deleted after the session ends.

Temporary tables are recommended to be avoided by Microsoft.
Try optimizing the index
Use the query analyzer's execution plan to see where the slowness is.

I tried it. The left link speed of a common table is very fast, that is, the left link with the group by subquery will affect the query speed,
It seems that there is no good way to do it. I still made a loop ~~~~~

Sele AA. *, B. a_idn, B. sum (Working Hours) from a aa left join B group by a_idn on AA. IDN = BB. a_idn

Is the loop faster? Group by is slow. You need to scan the table according to the index and then sum the value. What are the number of records in Table B?

Table B contains 35824 data records and the last 35677 data records after group ,.........

Sele AA. *, B. a_idn, B. sum (Working Hours) from a aa left join B group by a_idn on AA. IDN = BB. a_idn
Let me try again. Is left join the same as left outer join?

Sele AA. *, B. a_idn, B. sum (Working Hours) from a aa left join B group by a_idn on AA. IDN = BB. a_idn
I tried it once in this format, and the prompt "there is a syntax error near the keyword 'group. ", No.

Is there only 3 million data records in Table B, so you can try to insert the association result of table A and table B to the temporary table first, and then use the temporary table for the following 4 associations, it should be faster.

What if subquery is used in select? For example:
Sele AA. *, (select sum (Working Hours) from B where AA. IDN = B. a_idn) as working hours
From a aa
(It may be slower !)
Subqueries are generally slow!

Table A contains 37316 records. The number of data records depends on the condition.
Similar to table B, table C contains nearly 60 thousand data records ....
An SQL statement can be done, and it's annoying now!

For sqlserver2000
You can use
Declare @ temp table (A varchar (10), B INT)
Insert into @ temp select a, B from AB where a = 'A'
Such temporary tables optimize nested queries

What if subquery is used in select? For example:
Sele AA. *, (select sum (Working Hours) from B where AA. IDN = B. a_idn) as working hours
From a aa
(It may be slower !)
Subqueries are generally slow!
--------------------------
I tried it. 30 thousand pieces of data are quite fast, and there are many, thank you !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Thank you for your help! Thank you very much! The answer to yeskert1 solves my big problem, or I will rewrite the code!

Then, the author removed all the nested group by statements. There should be no difference in your writing & subqueries, but a group by statement is missing.

There are 30 thousand rows in Table A and 30 thousand rows in Table B. The simple left Outer Join speed is very fast, that is, the speed of using group by is significantly reduced,

Yeskert1's answer: I tried it very quickly. How can I fix it and paste it back and try again? I just slowed down, but it is faster than the original timeout, at least it will not time out.

Ha, I found a strange phenomenon. Using yeskert1, adding conditions is slower than without conditions,

It is strange to say that only two thousand pieces of data can be added with conditions. It should be faster.

The SQL query analyzer is used. If no conditions are added, the results are displayed in 14 seconds. The conditions are added, and the results are not displayed after 1 minute and a half.
Bid in tears ~~~~~~~~~~~~~~~~~~~ '

Paste the complete statement.

> Adding a condition is slower than not adding a condition,
This is possible, because the conditions must have a comparison process, and string comparison is time-consuming. Group by a_idn causes slowness.
In general, you should add indexes to the table to improve the speed.

Can I post the complete statement?

Select *
From (select C. *, isnull (D. wcsl, 0) as wcsl
From (select a. *, isnull (A. sl * B. zgs, 0) as zgs
From (select a. *, isnull (B. xzlx, '') as xzlx_sm
From (select C. *, isnull (D. qym, '') as qym
From (select a. *, isnull (B. XMM, '') as XMM
From ww_wwjl a left Outer Join
Xmk B on A. xm_dir = B. DIR) C left Outer Join
Ww_khdak D on C. khdm = D. khdm) A left Outer Join
Ww_xzlx B on A. xzlx = B. xzlx_path) A left Outer Join
(Select wwjl_idn, sum (GS) as zgs
From ww_wwjl_gs
Where (sbname <> '@ # $ % ')
Group by wwjl_idn) B on A. IDN = B. wwjl_idn) C left Outer Join
(Select wwjl_idn, sum (dhsl) as wcsl
From ww_wwdh
Where dhrq> = '2017-01-01 'and dhrq <= '2017-12-31'
Group by wwjl_idn) D on C. IDN = D. wwjl_idn) derivedtbl
Where (Cj = 'mechanical Lesson 4 ') and (wwrq> = '2017-01-01') and (wwrq <= '2017-12-31 ')
Order by xm_dir, th

------------------
There are six tables in total:
Ww_wwjl is the primary table, which is left connected to the following five tables
| ----> Xmk (ww_wwjl.xm_dir = xmk. DIR)
| ----> Ww_khdak (ww_wwjl.khdm = xmk. khdm)
| ----> Ww_xzlx (ww_wwjl.xzlx = xmk. xzlx_path)
| ----> Ww_wwjl_gs (sum (GS) group by wwjl_idn | ww_wwjl.idn = ww_wwjl_gs.wwjl_idn)
| ----> Ww_wwdh (sum (dhsl) group by wwjl_idn | ww_wwjl.idn = ww_wwjl_gs.wwjl_idn)

I wonder if I have made it clear ~~~~

Big Head! Too complicated! [:(]

-_-|, Loop

Select a. *, B ....
From ww_wwjl a left join xmk B on A. xm_dir = B. dir
Left join ww_khdak C on A. khdm = C. khdm
Left join ww_xzlx D on A. xzlx = D. xzlx_path
Left join (select wwjl_idn, sum (GS) as zgs
From ww_wwjl_gs where (sbname <> '@ # $ % ')
Group by wwjl_idn) e on A. IDN = E. wwjl_idn
Left join (select wwjl_idn, sum (dhsl) as wcsl
From ww_wwdh
Where dhrq> = '2017-01-01 'and dhrq <= '2017-12-31'
Group by wwjl_idn) F on A. IDN = f. wwjl_idn
Where (Cj = 'mechanical Lesson 4 ') and (wwrq> = '2017-01-01') and (wwrq <= '2017-12-31 ')
Order by xm_dir, th

Doud2006 Thank you, but if the number of qualified records is large, it will be slow and slow.

Are all your associated fields indexed? Do you try not to write unnecessary fields and appear less? * is the result correct? Is there no repeated Association?

No, I have no index. I have no index. Only the IDN of ww_wwjl is the keyword.
The number of records is correct and there is no repeated Association

Full table scan without indexes. Of course, it is slow. You can add indexes to all the fields associated with the six tables. If these tables are not updated frequently, insert is not included. The where condition should also be indexed as much as possible;

If I add an index, will it affect my other query statements ??????

It's off duty. I'll try it tomorrow. Thank you.

The index improves the query efficiency. When data changes, the index needs to be updated, which reduces the update efficiency.

I am not very familiar with indexing. I have found some information on the Internet and I still have a lot to understand.
Does the unique values check whether it is a cluster index?

In non-clustered indexes, data is physically stored on the data page at random. During range search, you must perform a table scan to find all rows in this range.

Under the cluster index, data is physically stored on the data page in order, and duplicate values are arranged together. Therefore, you can first find the start and end points of this range during range search, in addition, only data pages are scanned within this range, which avoids large-scale scanning and improves the query speed.

So there is no difference between non-clustered indexes and useless indexes? For example, if I want to calculate the total working hours and group by, I need to use the Cluster Index, right?

You can check the sqlserver2000 help create index. The explanation is clear.

I tried it. I performed a clustered index on the fields that require group by, which is much faster! I wonder if it will affect other operations! I want to test it again. However, I have to thank you first. But I have already closed the post. I don't know if there is any other way to add extra points to you. doud2006

However, I have a problem. Now I only have one field group by. If I still have other fields that need group by, but the clustered index can only be performed once, can I only put the created index onProgramAh? Is it advisable to frequently Delete the clustered index?