This is a list of some techniques used successfully at several different sites.
Getting Maximum Index Usage
1) Verify tables have had "update statistics" on them ;
Verify tables have had "sp_recompile" on them.
2) Verify any declared variables have the same data
type as their corresponding columns - this is a common
pitfall.
3) Force index usage as follows, with a hint:
from customer (index idx_customer2)
4) Use SET TABLE COUNT
Example: set table count 6
Then, compile the procedure, in the same session.
5) If temp tables are being used, put the temp table
creation statements in one procedure, and the
processing SQL in another procedure. This allows
the optimizer to form a query plan on the already
established tables.
Example:
proc_driver calls proc_create_temp_tables
then,
proc_driver calls proc_generate_report
General SQL Programming
- Plan for growth. Assume the driver table doubled or tripled in size; would
the report still function ?
- Avoid dumb comparisons in the where clause, like
where @emp_id > 0
- use "WHERE EXISTS ( )" rather than "WHERE NOT EXISTS"
- use "!=" rather than "<>"
- use "IS NOT NULL" rather than "<>NULL"
- use "IS NULL" rather than "=NULL"
- avoid distinct if possible ; see cursor loop option below
- use meaningful names for temp tables ... don't use #temp (lame)
Report Structure Approaches
1) Single query
Single query reports are rare - usually they involve getting a simple list
together.
- Don't try to 'shoehorn' SQL into one statement. Shorter programs are
great for C or Perl applications, but this is not the case in SQL.
Think "Bigger is Better" (and more maintainable).
- Keep queries from using more than four tables if possible.
2) Cursor on driver table(s), with IF..THEN processing in loop
Using a cursor for complex reports almost always increases performance
when large tables and a lot of joins are involved.
- Keep cursor queries from using more than two tables if possible,
make sure this query performs well on its own.
- Try to have a unique key of some sort available within the tables involved.
Strange results have been known to occur when a cursor is scanning
rows that are exactly alike.
- Don't use cursors for updating.
- Use IF statements for filtering results even further. In most cases:
A code construct like the one below is better than cramming the
logic in a where clause.
IF <cond-a>
BEGIN
IF <cond-b> and <cond-c>
.....
ELSE
....
END
3) Set processing without cursors
This technique should be attempted when even a cursor construct fails to
achieve the desired performance.
Basically, the driver query is re-run with each iteration of the loop.
Sample, with cursor:
declare cursor1 cursor for
select emp_id, last_name, salary
from employee
open cursor1
fetch cursor1 into @emp_id, @last_name, @salary
while (@@sqlstatus = 0)
begin
< processing >
fetch cursor1 into @emp_id, @last_name, @salary
end
close cursor1
Sample, with set processing:
select @emp_id = 0, @loop = 1
while (@loop > 0)
begin
set rowcount 1
select
@emp_id = emp_id,
@last_name = last_name,
@salary = salary
from employee
where emp_id > @emp_id
order by 1
select @loop = @@rowcount
set rowcount 0
if @loop > 0
begin
< processing >
end
end
Transaction Log Filling Up ?
If the transaction log is filling up, for tempdb or the main database, there
is likely something wrong with the report logic.
Things to check:
- Instead of repetitively updating each row, can the values be obtained
ahead of time, and then inserted with a single transaction ?
- Are the "joined" updates occuring on each row once ? When updating
using a join statement, make sure that the tables in question
are joined in a way that avoids duplicate rows. Try running the
SQL statement as a SELECT - check it out.
- Are you cramming 500,000 rows from a temp table into a db table ? bTry elminating the temp table.
- Create indexes on updated/inserted tables after the fact.
- Use "set rowcount" along with "waitfor delay" if log problems persist
*** A proper DBA will never extend the log segment, based on the needs of a single process.
Forcing an index in a query
Sometimes it is necessary to force the index when the optimizer has incorrect statistics.
-- Example: Force the idx_emp index in this query
select t1.emp_id, t1.emp_name, t2.status
from employee t1 (index idx_emp),
audit_flags t2
where t2.emp_id = t2.emp_id
go
|
Isolation Levels
Setting the isolation levels can eliminate contention problems, when reports
are run on the same database as the online application.
Three flavors to choose from, for troublesome reports, queries, and updates.
1)
select cus_id
from customer_location
where cus_id< 1000000
at isolation read uncommitted
-- > Allows table to be read (ala Oracle) even when update page locks are pending.
2)
select cus_id from
customer_location noholdlock
where cus_id< 1000000
-- > Allows big queries to run without locking pages / tables.
3)
/* For updates: */
-- Session #1
begin transaction
update invoice_item
set discount_amt = 0
where invoice_id < 2000000
commit transaction
-- Session #2
set transaction isolation level 0
Queries against invoice_item in session #2 will NOT be blocked, even before the commit occurs.
Updates against invoice_item in session #2 where pages included in the Session #1 transaction WILL be blocked, before the commit occurs.
*** Mixing 1 & 2 from above is not recommended |
Transact SQL: Finding duplicate rows in a table
This example finds cargo records with have duplicate destination ids.
3> select cargo_id, dest_id
4> from routing t1
5> where
6> ( select count(*)
7> from routing t2
8> where t2.dest_id = t1.dest_id ) > 1
9>
10> go
Using Temporary Tables
Temp tables allow developers to create and scan tables within a stored procedure - and
have the tables totally isolated from all other database connections. This is very
valuable when results need to be processed several times within a loop, or when
a complex result set is expected (like a crosstab). Note that temp table transactions
are logged within tempdb (exception: select into create statements).
create proc proc_gen_report (@region_id integer) as
declare @total money
/* standard create */
create table #rpt (
store_id integer not null,
store_cd char(5) not null,
inv_count integer not null,
total_sales money not null
)
/* create using select into - make sure 'select into' is turned on */
select t1.cus_id, t1.cus_name, sum(t2.inv_amount) 'inv_summary'
into #cus_invoices
from customer t1, invoice t2
where t2.cus_id = t1.cus_id
/* Processing occurs, using temp table(s) where needed. */
/* Temp tables can be used in joins, aggregates, updates, etc. */
drop table #rpt
drop table #cus_invoices
return
go
Safely delete a large number of rows without blowing the t-log
It is key to monitor the transaction log during large deletes.
Controlled delete, for large data sets, allows transaction log to clear.
The syntax for this operation is different for ASE 15 ... be sure to comment
out the 12.X code if you are using ASE 15.X
--This example: 9000 rows in a batch, transaction log
--needs to clear after 200mb is allocated between batches
select @@servername, db_name()
go
set nocount on
go
set rowcount 9000
go
declare @rows integer,
@trows integer,
@mb money
select @rows=1,@trows=0
while @rows > 0
begin
delete from invoice_history
select @rows = @@rowcount
select @trows = @trows + @rows
select @trows, 'tLog=', @mb
waitfor delay '00:00:02'
-- ASE 12.X
select @mb1 = ( data_pgs (8, doampg) ) * (@@maxpagesize/1000.0) / 1000
from sysindexes where id = 8
-- ASE 15.X
select @mb1 = ( ( data_pages (db_id(), 8, doampg) ) * (@@maxpagesize/1000.0) / 1000.0)
from sysindexes where id = 8
while @mb > 200
begin
waitfor delay "00:02:00"
-- ASE 12.X
select @mb1 = ( data_pgs (8, doampg) ) * (@@maxpagesize/1000.0) / 1000
from sysindexes where id = 8
-- ASE 15.X
select @mb1 = ( ( data_pages (db_id(), 8, doampg) ) * (@@maxpagesize/1000.0) / 1000.0)
from sysindexes where id = 8
select @mb "trans log MB"
end
end
go
select 'Table is now empty:', 'invoice_history', count(1) from invoice_history
Running SQL within a script
This script accepts a sybase command as a parameter, and executes it.
#!/usr/bin/ksh
#------------------------------------------------------
# File: sybexec
# Process Sybase command, output goes to std output
# Parameter: SQL command, in quotes
#
# Sample call: sybexec "sp_helpdb billing_db"
#------------------------------------------------------
intfile=/apps/sybase/interfaces
eval /apps/sybase/bin/isql -Sserver -I$intfile -Ujsmith -Pyankees << finis
$1
go
finis
Shared memory setting
Below are the examples for setting shared memory in Linux for Sybase.
For Solaris, you need to modify /etc/system
echo 134217728 > /proc/sys/kernel/shmmax
echo 999999999 > /proc/sys/kernel/shmmax
|
|
|
|
|
|
No comments:
Post a Comment