How to write an efficient SQL script ASP. NET

How to write an efficient SQL script II

12. View

Use a view as few as possible, and its efficiency is relatively low. Operations on a view are slower than operations on a table. You can replace it with stored procedure. In particular, do not use nested views. nested views increase the difficulty of searching for original data. Let's look at the essence of the View: it is an optimized SQL statement stored on the server that has produced a query plan. When retrieving data from a single table, do not use a view pointing to multiple tables. Read data directly from the view that only contains the table. Otherwise, unnecessary overhead is added, the query is disturbed. to speed up View query, MSSQL adds the View index function.
If the data size of the created view is large and operations are frequent, we recommend that you use an indexed view.

13. Group by having
Generally, redundant rows can be removed before group by having clauses, so try not to use them for row removal. Their execution sequence should be optimal as follows: Select WHERE clause Selects all appropriate rows, group by is used to group statistical rows, and having clause is used to remove redundant groups. In this way, the consumption of group by having is small, and queries are fast. Grouping and having large data rows consumes a lot of resources. If the purpose of group by is not to include computing, but to group, it is faster to use distinct.
14. The data types defined by parameters in the stored procedure should be the same as the data types defined in the conditions.
1) Select fcandidateid from tcandidate where fcredentialstype = '1'
2) Select fcandidateid from tcandidate where fnation = 1
The fcredentialstype field is an integer in the tcandidate table. If the first method is SQL, an implicit function is converted to cast ('1' as INT.
If a field is of the fload type, for example, if the FSCORE field is of the float type, the condition statement should be written like this (if the student with a score of 60 or higher is selected ), this is the reason for writing:
Exit FSCORE> 60.0. Instead of FSCORE> 60
There is an implicit conversion.
Although these are all trivial matters, if you pay attention to each step, the performance will be improved when you increase the performance :)

15. The test results are as follows:
1. Select tcandidate and tcandidatedetail for multiple tables. Each table has 3040000 data records.
Select T. fcandidateid, T. fcandidatename, D. fhealth, D. faddress
From tcandidate as t
Inner join tcandidatedetail as d
On T. fcandidateid = D. fcandidateid
And T. fcandidatename = 'tim1000322'
2. query other information of the user by name
3. Average query result time: 19 seconds
Bytes ---------------------------------------------------------------------------------------------------------------
1. Select tcandidate and tcandidatedetail for multiple tables. Each table has 3040000 data records.
Select T. fcandidateid, T. fcandidatename, D. fhealth, D. faddress
From tcandidate as t
Inner join tcandidatedetail as d
On T. fcandidateid = D. fcandidateid
And T. fcredentialsid = '000000'
2. query other information of the user based on the identity card ID of the examinee.
3. Average query result time: 19 seconds
1. In the tcandidate and tcandidatedetail tables, tuserinfo each has more than 3040000 records of data.
Select T. fcandidateid, T. fcandidatename, D. fhealth, D. faddress
From tcandidate as t
Inner join tcandidatedetail as d
On T. fcandidateid = D. fcandidateid
Inner join tuserinfo as u
On U. flogoncode = T. fcandidatename
Where U. flogoncode = 'tim1090122'
2. log on to the examinee and use the flogoncode in the tuserinfo table to obtain detailed information.
3. Average Time: 28 seconds
Bytes ---------------------------------------------------------------------------------------------------------------
1. obtain information about the user through the login flogoncode, and update some information about the examinee.
Update tcandidate
Set fcandidatecode = fcandidatecode + '_ U'
From tcandidate as t
Join tuserinfo as u
On T. fcandidatecode = U. flogoncode
Where U. flogoncode = 'tim1090123'
2. obtain the information of the examinee through the logoncode on the examinee's website and update some information.
3. Average Time: 28 seconds
Bytes ---------------------------------------------------------------------------------------------------------------
1. delete a record in the tuserinfo table based on the fcandidatecode in the tcandidate table
Delete from tuserinfo
From tuserinfo as u
Inner join tcandidate as t
On T. fcandidatecode = U. flogoncode
Where T. fcandidatecode = 'tim1096'
2. delete records
3. Average Time: 20 seconds
Bytes ---------------------------------------------------------------------------------------------------------------
1. Update candidate records
Update tcandidatedetail
Set faddress = faddress + 'beijing'
From tcandidatedetail as t
Join tcandidate as d
On T. fcandidateid = D. fcandidateid
Where D. fcandidatename = 'tim1090168'
2. Update the examinee's details based on the examinee's name.
3. Average Time: 20 seconds

Add comments | fixed link | reference announcement (0) | write logs
Design Guidelines for Application Performance
Http://msdn.microsoft.com/ SQL /2000/learn/perf/default.aspx? Pull =/library/en-US/dnpag/html/scalenetchapt03.asp

Add comments | fixed link | reference announcement (0) | write logs
How to restore and backup database by using store procedure

If exists (
Select * From sysobjects
Where and xtype = 'P'
)
Begin
Drop proc pr_backup_db
End

Go

/* Back up the database */
Create proc pr_backup_db
@ Flag varchar (10) Out,
@ Backup_db_name varchar (128 ),
@ Filename varchar (1000) -- path + file name
As
Declare @ SQL nvarchar (4000), @ par nvarchar (1000)
Select @ par = '@ filename varchar (1000 )'
Select @ SQL = 'backup database' + @ backup_db_name + 'to disk = @ filename with init'
Execute sp_executesql @ SQL, @ par, @ filename
Select @ flag = 'OK'
Go
If exists (
Select * From sysobjects
Where and xtype = 'fn'
)
Begin
Drop function fn_getfilepath
End
Go

/* Create a function and obtain the file path */
Create Function fn_getfilepath (@ filename nvarchar (260 ))
Returns nvarchar (260)
As
Begin
Declare @ file_path nvarchar (260)
Declare @ filename_reverse nvarchar (260)
Select @ filename_reverse = reverse (@ filename)
Select @ file_path = substring (@ filename, 1, Len (@ filename) + 1-charindex ('/', @ filename_reverse ))
Return @ file_path
End
Go

If exists (
Select * From sysobjects
Where and xtype = 'P'
)
Begin
Drop proc pr_restore_db
End
Go

Create proc pr_restore_db/* Restore database */
@ Flag varchar (20) Out,/* indicates the running status of the process, which is the input parameter */
@ Restore_db_name nvarchar (128),/* Name of the data to be restored */
@ Filename nvarchar (260)/* path for storing the backup file + name of the backup file */
As
Declare @ proc_result tinyint/* return the system stored procedure xp_mongoshell running result */
Declare @ loop_time smallint/* number of cycles */
Declare @ max_ids smallint/* @ maximum number of IDS columns in the TEM table */
Declare @ file_bak_path nvarchar (260)/* original database storage path */
Declare @ flag_file bit/* file storage mark */
Declare @ master_path nvarchar (260)/* database master file path */
Declare @ SQL nvarchar (4000), @ par nvarchar (1000)
Declare @ SQL _sub nvarchar (4000)
Declare @ SQL _cmd nvarchar (4000)
/*
Determine the validity of the @ filename file format to prevent invalid file names such as D: or C://.
The parameter @ Filename must contain '/' and does not end '/'.
*/
If right (@ filename, 1) <> '/' and charindex ('/', @ filename) <> 0
Begin
Select @ SQL _cmd = 'dir' + @ filename
Exec @ proc_result = Master .. xp_cmdshell @ SQL _cmd, no_output
If (@ proc_result <> 0)/* Code returned by the system stored procedure xp_cmdshell: 0 (successful) or 1 (failed )*/
Begin
Select @ flag = 'not exist'/* the backup file does not exist */
Return/* exit process */
End
/* Create a temporary table and save the result set consisting of a list of databases and log files contained in the backup set */
Create Table # TEM (
Logicalname nvarchar (128),/* logical name of the file */
Physicalname nvarchar (260),/* Physical name of the file or operating system name */
Type char (1),/* data file (d) or log file (l )*/
Filegroupname nvarchar (128),/* Name of the file group containing the file */
[Size] numeric (20, 0),/* current size (in bytes )*/
[Maxsize] numeric (20, 0)/* maximum allowed size (in bytes )*/
)
/*
Create a table variable. The table structure is basically the same as that of a temporary table.
There are two more columns,
Column IDS (auto-increment Number Column ),
Column file_path: path for storing files
*/
Declare @ TEM table (
IDS smallint identity,/* auto-increment Number Column */
Logicalname nvarchar (128 ),
Physicalname nvarchar (260 ),
File_path nvarchar (260 ),
Type char (1 ),
Filegroupname nvarchar (128)
)
Insert into # TEM
Execute ('Restore filelistonly from disk = ''' + @ filename + '''')
/* Import the temporary table to the table variable and calculate the corresponding path */
Insert into @ TEM (logicalname, physicalname, file_path, type, filegroupname)
Select logicalname, physicalname, DBO. fn_getfilepath (physicalname), type, filegroupname
From # TEM
If @ rowcount> 0
Begin
Drop table # TEM
End
Select @ loop_time = 1
Select @ max_ids = max (IDS)/* @ maximum number of IDS columns in the TEM table */
From @ TEM
While @ loop_time <= @ max_ids
Begin
Select @ file_bak_path = file_path
From @ TEM where IDs = @ loop_time
Select @ SQL _cmd = 'dir' + @ file_bak_path
Exec @ proc_result = Master .. xp_cmdshell @ SQL _cmd, no_output
/* System stored procedure xp_mongoshell return code value: 0 (successful) or 1 (failed )*/
If (@ proc_result <> 0)
Select @ loop_time = @ loop_time + 1
Else
Break/* The original storage path of the data file before backup is not found. Exit the loop */
End
Select @ master_path =''
If @ loop_time> @ max_ids
Select @ flag_file = 1/* The original storage path of the data file before backup exists */
Else
Begin
Select @ flag_file = 0/* The original storage path of the data file before backup does not exist */
Select @ master_path = DBO. fn_getfilepath (filename)
From master.. sysdatabases where
End
Select @ SQL _sub =''
/* Type = 'D' indicates the data file, and type = 'l' indicates the log file */
/* @ Flag_file = 1 when the new database file is stored in the original path, otherwise the storage path is the same as the master database path */
Select @ SQL _sub = @ SQL _sub + 'move ''' + logicalname + ''' '''
+ Case type
When 'd 'then case @ flag_file
When 1 then file_path
Else @ master_path
End
When 'l' then case @ flag_file
When 1 then file_path
Else @ master_path
End
End
+ Case type
When 'd 'then @ restore_db_name + '_' + logicalname + '_ data. MDF '','
When 'l' then @ restore_db_name + '_' + logicalname + '_ log. ldf '','
End
From @ TEM
Select @ SQL = 'Restore Database @ db_name from disk = @ filename'
Select @ SQL = @ SQL + @ SQL _sub + 'replace'
Select @ par = '@ db_name nvarchar (128), @ filename nvarchar (260 )'
Print @ SQL
Execute sp_executesql @ SQL, @ par, @ db_name = @ restore_db_name, @ filename = @ filename
Select @ flag = 'OK'/* operation successful */
End
Else
Begin
Select @ flag = 'file type error'/* parameter @ filename input format Error */
End

-- Backup database test_database
Declare @ FL varchar (10)
Execute pr_backup_db @ FL out, 'test _ database', 'c:/test_database.bak'
Select @ fl
-- Restore the database. The input parameter is incorrect.
Declare @ FL varchar (20)
Exec pr_restore_db @ FL out, 'sa ', 'c :/'
Select @ fl

-- Restore the database, that is, create a copy of the database test_database test_db
Declare @ FL varchar (20)
Exec pr_restore_db @ FL out, 'test _ db', 'c:/test_database.bak'
Select @ fl

This article is transferred from
Http://q.163.com/dotnet/blog/lujian811/10190113200702522722718/all/#10190113200702522722718