Performance Tuning of a database!

A report was recently created, and found that the speed was very slow. To the point where timeout is allowed. After troubleshooting, the problem was fixed to SQL. This was written by our predecessors and I read the stored procedure. Opening a stored procedure is simple, that is, a simple query. The problem cannot be found here. Therefore, it is found that the Stored Procedure query is a view. OK. The problem is definitely in this view.

Open the view. Wow. The original query statement can also exist in the following way. My 19 wide-screen LCD displays are full (the knowledge of elementary Chinese literature is easy to use ). There is no exception in viewing the query. Only one point is that there are six udfs in the where clause. I think the problem should be caused by the use of udfs IN THE WHERE clause. Let's take a closer look at this user-defined function, which is used to calculate the working time. The function itself has no problem. It should be a problem of where and user-defined functions. For example:

Select * from table where DBO. getworkdayformat ('', startdate, enddate)> 1

This is the case. I thought that since a user-defined function is used in the WHERE clause, I would like to propose it. For example, to create a new viewworkdaybase:

Select sysno, DBO. getworkdayformat ('', startdate, enddate) as workday from table

When querying again, write as follows:

Select * from table

Inner join viewworkdaybase on

Viewworkdaybase. sysno = table. sysno and

Viewworkdaybase. Workday> 1

Conclusion: some problems are not solved only by technology, but can be used flexibly.