web| Program
How to improve the efficiency of Web programs (the first "SQL article")
Many netizens do not like to use ASP to program, they always complained that the ASP program is too slow, inefficient. Prefer to use php,jsp and so on to write programs. In fact, can not from the "think" this point of view, but should be seen from the actual problem, ASP is really slow, then Microsoft's Station Why use ASP is not slow? PHP is really fast, but it is still an explanatory language. But the combination under Linux is better. JSP development is also not easy to where, and to the Jdbc-odbc Bridge to connect access to the library and so on, in this case, the efficiency is not high.
In fact, three languages have their own characteristics, that is, the advantages, shortcomings. As a programmer should not always blame the language is not good, but more should be the value of their own technology. So today we're not going to compare the three web languages who are good or bad, but how to improve the efficiency of program execution.
First: SQL statement
The most notable of the Web programs should be database operations. Today we'll talk about how to improve the efficiency of SQL statements. Suppose you have a table of users with the following structure, with ID as the primary key. There are already 10,000 records.
ID int 4 self-increasing
Name Char 10//Name
Age int 2//Ages
Sex Char 2//gender defaults to "male"
Address Nvchar 255//Addresses
Cash int 4//integral
Photo Char 2//Is there a photo default value of "No"
Photourl Nvchar 255//Photo path
Home we talk about tables and fields in the database problem, for the Web database objects, the number of natural tables, the better; the less the number of sections in the table, the better; the smaller the bytes in the field, the better.
For example, a sex field in the Users table can be used with a bit field for 0 or 1, you can define "0" as a male, and "1" is a female, so that you can reduce the number of bytes in char, so that a large number of records is still considerable.
Again, for example, the photo fields of a table can be removed, which can reduce the field and thus speed up the reading. Because the photo field is only used to determine if the user has a picture, and we can safely use the Photourl field to judge. If the value of the Photourl field is empty, it means that the user does not have a picture, otherwise, if there is, the Photourl field must have a value, also can complete the corresponding function.
For example, we want to display a record of the first 10 user names. Some netizens will do the following:
I=0
Set Rs=conn.execute ("SELECT * from users")
Do, not rs.eof and i<=10
I=i+1
Response.Write ("name") & "<br>"
Rs.movenext
Loop
This code can complete the above function, but if the actual program, the efficiency is too low. Because the following program but read all the records, that is, 10,000 ah, but we only need 10 Ah, if this is not too wasteful ah, of course, we can use MS SQL TOP command to complete this request, or MySQL in the limit command.
In addition, we only need the user name fields to show up, while the others do not need, and this netizen has used the "select *" command, read all the values of the field, it is very unnecessary, and in many fields, but also to increase the burden of implementation. Therefore, you should use the "select field" to do the appropriate action. So the above program can be modified to:
Set Rs=conn.execute ("Select Top name from users")
Do as not rs.eof
Response.Write rs ("name") & "<br>"
Rs.movenext
Loop
In this case, in a lot of records, the operation will be a lot faster.
In addition, when the operation is finished, do not need to do any more operations, be sure to close the database connection.
Set rs=nothing
Set conn=nothing
Some netizens said, the original is so ah, then he use the following code to view the users table can use the following code to complete.
Set rs=conn.execute ("Select name from users")
I=0
Do as not rs.eof
I=i+1
Rs.movenext
Loop
Set rs=nothing
In fact, the efficiency of this code is still very low. To know how many records there are in the table, it is not worth traversing all the records. We can use the following code to achieve this:
Set Rs=conn.execute ("SELECT count (ID) as num from users")
I=rs ("num")
Set rs=nothing
Is it simple, in fact, the count () command can count the number of records you want to query, and use where to add conditions.
If a user has to do several operations on a database at once, we also need to note after the operation of the lock table, with the lock operation after the process of unlocking the steps, with unlock, because if a user in the operation of the database, another user is also operating, it is easy to cause confusion of data, or even the wrong record number, have very serious consequences.
In the operation of ASP, there are two methods for database operation, one is to refer to Conn.execute stream directly, the other is to use Rs.Open operation. Both operations have their advantages, while in MS SQL operations, the former use more, because it is a single class operation, generally can not use rs.addnew or rs.update, because this operation to open the table write operations, such as Rs.Open Sql,conn, 3,3, the following 3 indicates that a write operation can be performed, and 1 is a read operation.
Finally, you should use the Where condition as much as possible and put multiple statements together. For example: Displays the user name of the first 10 male users ranked by integral.
Check out the following code for a netizen:
Set Rs=conn.execute ("SELECT * from users ' ORDER by cash desc")
I=0
If i<=10 and not rs.eof then
If rs ("sex") = ' male ' Then
Response.Write rs ("name") & "<br>"
I=i+1
End If
Rs.movenext
End If
Set rs=nothing
This code can also accomplish the above task, but the efficiency is too low, from the database read every sentence to make a judgment. The gender judgment can be put in the where statement, and the following is a look at the modified code:
Set Rs=conn.execute ("Select top name from users where sex= ' men ' ORDER by cash desc")
Do as not rs.eof
Response.Write rs ("name") & "<br>"
Rs.movenext
Loop
Set rs=nothing
Adding the top command mentioned above, the Select field name, and the Where condition judgment, not only make the program code much less, but also make the program more efficient.
In fact, improve the efficiency of the SQL language is still a lot, but these are beginners friends or intermediate friends will often encounter, but not the place. Hope that we can learn a lot, open-minded ideas, and constantly from the practice of experience.
Because my level is limited, if has the mistake and the insufficiency again unavoidably, do please in http://luyu.net this website forum to propose, lets us make progress together.
