A talk on programming optimization

Programming | optimization

1. In SQL, if you select a record with a field that is not empty, there are two ways to write it
where Columnexample <> '
Or is
Where columnexample is not null

After testing, the latter is several times faster than the former (Columnexample is indexed)

2. In ASP, the use of GetRows and do not use the GetRows and directly using the record to loop the call, the two are actually different, the following is the test
Number of call records: 484

Using GetRows, and then using an array to display, it was found that a single flower spent about 620 milliseconds on the GetRows operation. It took a total of 711 milliseconds.

It takes a total of 931 milliseconds to loop through the recordset directly

So it's recommended that you use GetRows, especially if you want to display a lot of return records, but it takes up a fraction of the temporary memory.

When using the recordset directly, most of the time is spent on the movement of the cursor, probably accounting for more than 90%

3. Thinking about count in sql

Recently I to a large database programming, found that my section of the program no matter how to optimize the database, how to optimize the source program, execution completed at least need
More than 600 milliseconds, but not a paragraph only takes more than 100 milliseconds. The following is a conditional constraint on two pieces of code (Agentid already indexed):

1. Where Agentid = 0 takes more than 600 milliseconds
2. Where Agentid > 0 only takes more than 100 milliseconds

It was really weird that I started looking for the source of the time, Yiku, and I found the Count function, which took nearly 500 milliseconds to
The total number of records, the Agentid value of the database analysis, and found that Agentid 98% of the value is 0, appears to match the more records, Count
The longer it will take.
Then I thought, I don't know whether SQL will automatically inverse calculation, that is, it first calculates the number of lines that do not conform, and then calculates the return:
1. Where Agentid < 1 because Agentid is not worth 0, so is the same condition
The final time spent is still more than 600 milliseconds, without any required.

So there is only one solution, that is manual, and if the total number of records is known, only records that do not meet the criteria need to be calculated, and the total is reduced
The total number of lookup records can be obtained by going against the record.

The following is a time test for several count:

Count (*) returns unconditionally with a total record of 145539, which is just 100 milliseconds.

The Count (*) where name is not Null and Agent = 0 returns that the record has 145530 and takes 431-441 milliseconds
(The only time after the removal of the name is isn't null is to execute 110)

Count (*) where name is not Null return record 145539, takes 100-110 milliseconds

All of the above test Agentid are allowed null values