We have been paying little attention to the execution sequence of Select statements in SQL, and we are very casual about the use of keywords. As for the efficiency problem, because the data volume in the table is not very large, so I am not very concerned about it.
We have been paying little attention to the execution sequence of Select statements in SQL, and we are very casual about the use of keywords. As
is not, but the full-text index, consumption of space. Like ' a% ' uses the index like '%a ' when querying with like '%a% ' without an index, the query time is proportional to the total length of the field value, so the char type is not used, but varchar. The full-text index is long for the value of the field. 9, DB server and Application server separation, OLTP and OL
like' % a' is used and like '% A %' is not used for the query, the query time is proportional to the total length of the field value, so the char type cannot be used, but varchar. Create a full-text index for a long field value.
9. Separate DB server and application server; Separate OLTP and OLAP.
10. Distributed partition view can be used to implement Database
.
Disk Configuration/Best practices:
In general, you should keep log files and data files separate for better performance. I/O characteristics for heavily loaded data files (including tempdb) are random reads. For log files, sequential access is required, unless the transaction needs to be rolled back.
The built-in disks can only be used for database log files because they have good performance for sequential I/O, but low on random I/O performance.
The data and log files of the database shou
column storage indexes. Of course, if we calculate non-clustered indexes of traditional tables, therefore, the row-store table requires more space. We use more than pieces of data for simple comparison, as shown in figure 7.
Figure 7. space occupied by different storage
The sample data in figure 7 is very small, but we can still see that the column storage space is nearly 2/3 less than the Row Storage without non-clustered indexes, which is not a huge improvement.
Simple Performance Comparison
). If the view selection list contains expression SUM (Somecolumn) and COUNT_BIG (somecolumn), SQL Server can calculate averages for queries that reference the view and specify AVG (Somecolumn).
The SUM function that references a nullable expression. Full-text predicate CONTAINS or FREETEXT. COMPUTE or COMPUTE by clause. If GROUP by is not specified, the View selection list cannot contain an aggregate expre
, just open the whole-----------------------------------------------------------------------------------------.
Heavy---statement execution order
QQ Group and people chatting when suddenly there is a group of friends said: I know the original statement to go index is filtered by the field of select! Plausible, very sure! Another group of friends asked about update? It looks like a small white question, but it really shocked me! So let's take a look at the execution order of the
When writing a statement today, execute it in the query analyzer.
It takes less than 10 s to use another method, which is less than 1 s. The colleague said that it is because of the SQL sentence execution sequence. I have seen a few moments before
I was a little impressed with Guan's book. I found some materials on the Internet and learned from it.
Logical Query Process
CopyCode The Code is as follows: (8) Select (9) distinct
(11) (1) From (3) (2) On
personally think that if most of the requests reach the second paradigm, the system will generate fewer columns and more tables, this reduces data redundancy and improves performance.
2,
Reasonable redundancy
It is almost impossible to design a system completely according to the standardization. Unless the system is very small, it is necessary to add redundancy in a planned manner after the standardization design.
Redundancy can be a redundant database, a redundant table, or a redundant field.
-making corresponds to the highest level in business intelligence, that is, the Excel Data Mining function provided by Microsoft.
For data mining in Excel, you need to install a plug-in: SQL server data mining tools add-ins for office.
Through the fourth article, you can see how to use Excel to implement the product recommendation function.
The fifth article undertakes the previous article, which mainly
entered in the numeric expression Rand returns the random float value between 0 and 1 round returns the numeric expression and rounding to the specified length or precision sign returns the positive (+ 1) of the given expression), zero (0) or negative (-1) Signs sin returns the given angle (in radians) in an approximate number (float) expression) Square returns the square of the given expression SQRT returns the square root of the given expression tan returns the tangent of the input exp
rebuilding the index, you cannot constantly control the percentage of padding.When the page density needs to be adjusted, the use of filltactor needs to be considered in the following ways:
In the case of an OLTP system (often added and removed), a lower fillfactor is required.
In the case of OLAP or other systems that are very stable (almost no additions and deletions), you need the highest possible fillfactor.
If the transaction sc
If you have a shopping website, how do you recommend products to your customers? This function is available on many e-commerce websites. You can easily build similar functions through the data mining feature of SQL Server Analysis Services.
This article mainly demonstrates how to organize data according to the requirements of tools, and then perform mining, prediction, and analysis in Excel.
In the pre
In SQL Server, I found that the online interpretations of the false offline are scattered. What exactly is the false offline interpretation? I have studied it at home over the past few days and collected a lot of online information. The Chinese translation is used as a false offline function, the English name is spool. In the "SQLSERVER enterprise platform management practices" written by instructor Xu, I m
The SQL Server 2008 Business Intelligence platform provides an extensible, optimal business intelligence platform for data integration, reporting, and analysis that enables companies to provide users with business intelligence when they need it ...
Consolidate and manage all your data
Using SQL Server 2008 Enterprise
Tags: blog http os io using strong AR for dataRecently my two library appeared, appeared more Cxpacket waits, looked for the information on the net. One of the SQL Server columnist's articles is good, but also solves some of my doubts, translation here.Translation is used only for the purpose of disseminating information.Original source: http://blog.sqlauthority.com/2011/02/06/
after installation. Agtsvcstartuptype= "Automatic";the startup type of integration Services. Issvcstartuptype= "Automatic";account for integration Services: domain \ User or SYSTEM account. ;issvcaccount= "Ntauthority\system";The name of the account that is used to run the Analysis Services service. ;assvcaccount= "Ntauthority\system";controls the service startup type setting after the service is created. ;assysadminaccounts= "Win-77v7g8tebrt\administrator";assvcstartuptype= "Automatic";the col
horizontal partition table, reduce the size of the table (Sp_spaceuse)3. Upgrading hardware4, according to the query criteria, index, optimize the index, optimize access mode, limit the data volume of the result set. Note that the fill factor is appropriate (preferably using the default value of 0). The index should be as small as possible, using a Lie Jian index with a small number of bytes (refer to the creation of the index), and do not Jianjian a single index on a limited number of values s
it is to increase I/O.2. Vertical and horizontal partition table, reduce the size of the table (Sp_spaceuse)3. Upgrading hardware4, according to the query criteria, index, optimize the index, optimize access mode, limit the data volume of the result set. Note that the fill factor is appropriate (preferably using the default value of 0). The index should be as small as possible, using a Lie Jian index with a small number of bytes (refer to the creation of the index), and do not Jianjian a single
understand that resources such as memory are more required for concurrent processing of serial processing. Whether to use parallelism or serial travel is automatically evaluated and selected by MsSQL. A single task is divided into multiple tasks and can be run on the processor. For example, if the sort, connection, scan, and group by statements of delayed queries are executed simultaneously, SQL SERVER det
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.