My first outsourcing experience and income

In July of this year, I found my current company, which is not bad. The company is software outsourcing, and I am very excited to work for my first customer. Of course, there will be many differences in the division of labor between outsourced personnel and internal personnel of the customer company, because outsourcing personnel have time, and there will be no special time-consuming arrangement, it is also difficult to get familiar with modules. To learn the advantages of the customer's environment, you must make your own efforts. Here I will talk about my income over the past six months.

My work was not very fixed before March. I changed three companies in two and a half years. I can say that I would do it once a year. I won't say much about the reasons. My family has a difficult experience. Looking back at the customer's 6 months, the harvest was big. I personally think, hey.

1. Advances in database Optimization;

Note: during the interview, a lot of B/S technicians will ask you if you have experience in data optimization. At that time, I will ask you about the index category, as well as the main differences in use cases, I was not very useful at the time, and I could only answer a few words as I imagined. The small number of indexing applications is due to the small amount of data in previous companies, and few performance bottlenecks. It is better to have such an opportunity on the client side, although it is an old system, however, the data is big, and tens of millions of data items are not moved. I think this is the opportunity. Due to the adjustment of business needs, we need to modify the original query logic, this requires the re-creation of clustered indexes and the optimization of non-clustered indexes.

Business Requirements: query the order table orders by the departure time leavedate and order status: State, and associate the member table member at the same time by the Order table orders. The query conditions are leavedate and the agent ID: proxyid, order status. The data volume is more than 600 million. The association conditions are member ID and proxy ID, and an index is created.

Table Structure Description:

1: Order table, with more than 10 fields, including: generation time: createdate, departure time leavedate, member ID, and other fields; member ID, createdate, and associated clustered Index

2: Member table: more than 30 fields, including member ID and proxy ID. A joint index is created on the member ID and proxy ID.

Two table data volume description:

1: Order table, more than 12 million, and increase every day;
2: Member table: more than 6 million, the change speed is not too high.

My first practice: because there is an order by "createdate" in the query, I want to create a clustered index on this time field, but the effect is not obvious;

My second practice: because there is a "leavedate" in the condition, it is okay to create a clustered index on leavedate.

My third practice: Because there are order-based queries in the condition, why not create a composite index for "leavedate" and "order status"? The facts show that they are very effective;

My fourth practice: because the order table needs to be associated with the member table, there are also many Member tables, more than 600 million, so the association of large tables will naturally not be faster, because the Member table has many fields and a large amount of data, only three fields, member ID, proxy ID, and member name, are used for association, the Association condition is the member ID and proxy ID. Naturally, the indexes have been created on these two fields. I want to solve this problem through the index view. I created an index view that only contains these three columns, and found that the effect is average. Although there are fewer data columns, there are still so many data rows.

My fifth practice: Modify the member ID and proxy ID indexes of the member table, and apply the features of sql05IncludeInclude the user name in the index to form a covered index. So an index search is OK. The advantage of this is that the modification is minimal and the effect is good.

My sixth practice: persuade the business to change the original sorting "createdate" to "leavedate" that has created a clustered index, saving a lot of IO;

Summary: After my repeated rape, I finally achieved some results. I did not come up with this practice.

1: actually, in the second practice, zping told me to create a clustered index on the condition "leavedate". At that time, I did not know that the clustered index applies to columns that may have duplicate keys, only the auto-increment columns are suitable for creating clustered indexes. We are used to using auto-increment columns as the primary key. In this case, the primary key is already a clustered index.

2: dropping the index view is the result of discussion with the DBA on the customer side. Thank you very much for choosing DBA. The reasons are as follows:

First, the index view only reduces the number of columns and the number of data rows remains unchanged. All the results are not very obvious. I have proved that it will increase by about 15%.

Second, the index view requires additional system overhead, which is less effective than the improved performance.

3: The application include was found in the sql2005 technical insider translated by DBA. It gave me the knowledge of covering indexes.

In short, the above is my first SQL optimization experience. I wrote down more than 10 articles about the database during my work.ArticleAnd show your face again.

1:

Does sql2005 provide detailed performance analysis functions?

2:

Does sql2005 provide detailed performance analysis functions? (2)

3:

Does sql2005 provide detailed performance analysis functions? (3)

4:

Some minor aspects that are easily overlooked in SQL Development (I)

5:

Some minor aspects that are easily overlooked in SQL Development (2)

6:

Some minor aspects that are easily overlooked in SQL Development (3)

7:

Some minor aspects that are easily overlooked in SQL development (4)

8:

Some minor aspects that are easily overlooked in SQL development (5)

9:

How can I cope with the query problem of too much table data? (How to avoid joining large tables as much as possible)

10:

How to make full use of SQL Indexes

11:

SELECT query Principle

12:

Is there an injection vulnerability in the general paging stored procedure?

 

 

 

Second: Application of development tools,During development, the application of some gadgets will often greatly improve your work efficiency. The following is a recommended tool I have applied:

1: Nant tool, which is used when I pack a website on the client side. You can read this article for its application.

Smart gadgets

2: ue, text editor, much better than traditional notepad.

3: beyondcompare, file comparator, file comparison, very accurate.

4: Reg gate, SQL help tool.

5: log4net, a very powerful log component, is easy to configure and practical. It is strongly recommended.

Log4net log component Experience SharingThird: learning new technologiesLater, I came into contact with a site that has been applied to LINQ and learned a lot about the basics of LINQ. For more information, see the following article.

1:

Null-related issues and solutions in LINQ Learning

2:

Selectnames in LINQ to SQL

3:

Join in LINQ to SQL

4:

Group in LINQ to SQL

5:

Select

6:

Reprinted: how to update a database using LINQ to SQL

Fourth: Quality ImprovementIn the customer, it is clear that if you help others, you will get more. My colleagues and leaders at the customer side can't tell you what they are saying. If you don't understand anything, I'm very happy to give you some advice. Not everyone is a versatile person.ProgramPersonnel, so you need help. I also have a good place to learn, haha. Study hard and study every day.

Fifth: I have no time to learn the design model.Because I have been packing my website for a part of the time, and I will not talk about website packaging. It is not easy to understand. The articles I learned during this period are as follows:

1:

Old saying: proxy Mode

2:

Conventional discussion: Combination Mode

3:

Old saying: Template Method Mode

4:

Old saying: builder mode (how far is the design mode from us)

5:

Old saying: appearance Mode

6:

Old saying: single-piece Mode

7:

Old saying: Abstract Factory Model

8:

Old saying: decorator ModeSummary:I have gained a lot of knowledge and experience in the customer's six months, accumulated a lot of feelings with colleagues around me, people have feelings, and I feel a little sad when outsourcing expires, but we still have to go. We hope we can all make progress together.