How to speed up SQL execution speed?
1. Sort, or Join in SELECT statement
If you have sorting and connection, you can first select data into a temporary table, and then processes the temporary table. Because the temporary table is built in memory, it is much more faster than the table for the disk is built.
Such as:
SELECT TIME_RECORDS. *, CASE_NAME
From time_records, Outer Cases
WHERE TIME_RECORDS.CLIENT = "AA1000"
And Time_Records.case_no = cass.case_no
ORDER by time_records.case_no
This statement returns 34 sorted records for 5 minutes for 42 seconds. and:
SELECT TIME_RECORDS. *, CASE_NAME
From time_records, Outer Cases
WHERE TIME_RECORDS.CLIENT = "AA1000"
And Time_Records.case_no = cass.case_no
INTO TEMP FOO;
Select * from fo Order by case_no
Returns 34 records, just 59 seconds.
2. Use Not in or NOT EXISTS statements
The following statement looks no problem, but it might perform very slow:
SELECT CODE from Table1
WHERE CODE NOT IN (SELECT CODE "
If you use the following method:
Select Code, 0 Flag
From table1
INTO TEMP TFLAG;
then:
Update Tflag Set Flag = 1
WHERE CODE IN (SELECT CODE
From table2
Where tflag.code = table2.code
;
then:
SELECT * FROM
TFLAG
WHERE flag = 0;
It seems that it will cost longer, but you will find that it is not the case.
In fact, this way is efficient faster. It is possible that the first method will be very fast, that is, in the case of establishing an index on each field, but it is obviously not a good attention.
3. Avoid using too much "or"
If possible, try to use OR as much as possible:
WHERE A = "B" OR a = "c"
Compare
WHERE A in ("B", "C")
slow.
Sometimes even Union will be faster than OR.
4. Use the index.
Establish an index on all the fields of Join and Order By.
Establish an index in most of where in WHERE.
Where datecol> = "this / date" and datecol <= "That / Date"
Compare
WHERE DATECOL BETWEEN "this / date" and "this / date" slow
How do I use a SQL query in the shell script?
The following is a script running under SH / KSH. In Online, if you want to update statistics with a number of databases. This script is not very good. Because this script can only handle tables in the database without handling a large number of tables at the same time.
example:
# Update_em
# Run Update Statistics ON A Table by Table Basis
#
Database = $ 1IF [-z "$ database"]
THEN
Echo "Update_em DBNAME"> & 2
EXIT 1
Fi
ISQL $ Database - Output to Pipe "Cat" without headings Select "Update Statistics for Table", TabName, ";" From system repables where tabid> = 100 Order by tabname; EOF EXIT 0 Maybe you have noticed that the return value of Exit is not the same for different ISQL, so this is not very reliable, instead of checking the return value to check the return value to a file, then This file is GREP "error". E.g: # Generate the data Isql -qr < stage.rep 2> $ stage.err Database $ DATABASE DATABASE; SELECT ... ! # Check for errors IF grep -i "error" $ stage.err> / dev / null THEN ... ERROR_HANDLER ... Fi Why can't you create a view for a stroke generated field? Question: Why can't I create a view: Create View TST AS SETVAL COUT From Orders Where Ship_charge> 0; Answer: You should write this: Create View TST (COUT) AS Select Ship_charge - TotVal From Orders Where Ship_charge> 0; How to only select some data in the database (for example, 10%). Problem: If you want to get a part of the data returned by a SELECT statement, for example: Select Firstname, Lastname, City, State From bigdatabase Where stat = "tx" Reply: There is a way to return an approximation, just add: And rowid = (Trunc (rownc (rowid / x) * x) Where x represents 1 / x of the total record you want to return. It should be noted that this method can only return an approximate value, and the data in the table is continuing in the physical distribution. How to create a temporary table that is fully consistent with a table structure and permanent table. For example: CREATE TEMP TABLE MyTemp (Prodno Like Product.Prodno DESC LIKE PRODUCT.DESC) You can use the following statement: Select Prodno, Desc from Product WHERE ROWID = -1 INSERT INTO TEMP MYTEMP How do I change the value generated by the SERIAL type next time insertion operation? We know that the field of the serial type is an integer field that is automatically added, so how can I control the value of the next serial type field. I want the next inserted serial type than the default value, you can use: ALTER TABLE TABNAME MODIFY (SER_COL_NAME SERIAL) Want to the next inserted serial type is small than the default value, first need to re-set the serial type to 1: INSERT INTO TABLE (SERIAL_COLUMN) Values (2147483647); INSERT INTO TABLE (SERIAL_COLUMN) VALUES (0); - Re-started! .... Then execute Alter Table (like the above practices). How to terminate the execution of the SQL script when an error occurs? If you have created a SQL script and use the following way in the UNIX command line to execute this script:
