In those years, when I crossed the mountains, crossed the river, dug the hills, and hacked the troughs, I finally met the cross-Server Query

 

I am going to write some nonsense blogs. After thinking about it for a long time, I finally thought of a slightly literary title. I don't like it, so it's hard to pull it everywhere...

 

Let's talk about the scenario first. In order not to expose the business in minutes, we should replace it with letters. A bug was reported on the business side half A month ago, saying that we should filter A batch of data based on Condition A and Condition B,

When the range of time (condition C) is reduced, data can be filtered out and the (condition C) time is extended, so no data is available.

 

At first glance, the mud coal... It's amazing. We can pull data from to, but we cannot pull data from .... Then I carefully analyzed the code,

However, I don't know which programmer filters 20 pieces of data in the M library based on various conditions, because the B condition is in the N library, so he took the 20 pieces of data to the N database for filtering,

As a result, all the 20 pieces of data were violent, and finally they were taken to the front-end with an empty set... The problem with myam is finally understood,

 

In fact, it is quite tragic. I haven't encountered this problem for nearly four years, even though I have performed master-slave replication, cross-database, and Nima on the database of my previous company... No cross-service

... However, I was very excited when I found the problem that day. I turned to the beads and thought of a way to use multiple threads...

 

Because developers cannot access production, if you want to check it, you need to contact the second-line personnel... I am a lazy person, don't want to notify people by email, and then I assume that the data should

No more than one... Later, this should cause the solution to fail.

The procedure is as follows:

①: I assume that the data of a day will not exceed 5000. to speed up, I want to open several more threads. One thread will get pieces of tmd data. If it is, I will open it.

10 threads. Open 20 threads at, and the thread changes with data.

②: In this way, I can find out the number of orderids from the M and N databases separately, and then find an intersection in the two groups of orderids. Of course, this should be noted.

In terms of structure, this requires hash to be used for deduplication. The complexity is O (M + N). When I see that the built-in intersect of C # is first written into the hash, I just put

At least there will be no disgusting complexity of O (MN...

③: After the intersection is obtained, I now get a group of orderids, and then use these orderids to paging in the memory. After 20 orderids are retrieved, I will go to the master database M to find out what I really need.

Finally, send the data to the front-end...

After these ideas go on in my mind, I will write the code and pass the test environment... Then, it's a nightmare...

After a week, the business came over and said, "This is good. The original data cannot be found. Now you have reported an error to my page. I said," Dizzy, no, i'll check it out right away... Then

It is found that the execution time on the page is too long and iis times out... Alas, there's no way to do this. I went to the second line to check the data. Actually, there are data in the N database, so I have to open 52 threads and I am dizzy,

I don't know what the cpu usage in the production environment was like... Hard work... You have to continue to deduct these cumbersome code...

 

1: Cross-Server query using opendatasource

Just now, I also had a shabby ANALYSIS OF THE NEXT five where, 4 in the M database, and 1 in the N database, use opendatasource to join the inner of the remote server...

It may be because the company disabled this method for performance and security considerations and did not withdraw. This solution went bankrupt...

 

2: data redundancy

The muddy coal forced me into a desperate situation... Since the server cannot be crossed, it can only be used for data redundancy. No way, you can create a redundant table in the M database to replace the number of tables in the N database.

Data Import

 

<1> advance Redundancy

I want to add data to the XX table when inserting data on the web end. Although this is a good idea, there are too many web terminals. You know, if you want to change this small function

The workload on several web terminals is a little heavy. I still don't know whether other departments can cooperate with each other... In addition, my requirements are quite special. When the M library has some operations

The table generates data...

<2> post-event Redundancy

Later, I decided to use jobws to regularly fetch data from N databases every hour, and then store the data in the redundant table of M databases. Although the business side may not see the latest data for an hour,

But it doesn't matter. The data does not need to be real-time. As long as it is guaranteed to be the last day, It is enough. In the end, this kind of feasibility is determined... Tragedy... Ah, ah, ah...

 

Suddenly I found that my blog didn't seem to be so boring... It's okay, but this is also a very classic problem,If you have any tips, share them.

Ah... Thank you ....