slide1 l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
BID203: Transitioning from ASE to IQ for Decision Support PowerPoint Presentation
Download Presentation
BID203: Transitioning from ASE to IQ for Decision Support

Loading in 2 Seconds...

play fullscreen
1 / 44

BID203: Transitioning from ASE to IQ for Decision Support - PowerPoint PPT Presentation


  • 364 Views
  • Uploaded on

BID203: Transitioning from ASE to IQ for Decision Support. Steven J. Bologna Principal Consultant Bologna@sybase.com August 15-19, 2004. The Enterprise. Unwired. The Enterprise. Unwired. Industry and Cross Platform Solutions. Manage Information. Unwire Information. Unwire People.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'BID203: Transitioning from ASE to IQ for Decision Support' - Thomas


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
slide1

BID203: Transitioning from ASE to IQ for Decision Support

Steven J. Bologna

Principal Consultant

Bologna@sybase.com

August 15-19, 2004

the enterprise unwired3
The Enterprise. Unwired.

Industry and Cross Platform Solutions

Manage

Information

Unwire

Information

Unwire

People

  • Adaptive Server Enterprise
  • Adaptive Server Anywhere
  • Sybase IQ
  • Dynamic Archive
  • Dynamic ODS
  • Replication Server
  • OpenSwitch
  • Mirror Activator
  • PowerDesigner
  • Connectivity Options
  • EAServer
  • Industry Warehouse Studio
  • Unwired Accelerator
  • Unwired Orchestrator
  • Unwired Toolkit
  • Enterprise Portal
  • Real Time Data Services
  • SQL Anywhere Studio
  • M-Business Anywhere
  • Pylon Family (Mobile Email)
  • Mobile Sales
  • XcelleNet Frontline Solutions
  • PocketBuilder
  • PowerBuilder Family
  • AvantGo

Sybase Workspace

outline
Outline
  • Overview
  • SQL Syntax
  • Useful Compatible/comparable commands
  • What’s different that you need to know!
  • Error Handling
  • Things to watch out for
  • Indexing 101
  • Fun tools that ASE Doesn’t have!
  • Utilities that you might want to build?
  • Tuning
  • Error Handling
  • SQL Tricks
  • Utilities that I built
  • Questions
sql syntax
SQL Syntax
  • Which SQL Dialect to choose?
    • Watcom SQL
    • Transact SQL (T-SQL)
  • Most customers choose Watcom
    • More Features
    • More Error Control
    • Utility programs written in Watcom
    • Slightly more ANSI SQL “standard”
  • For those who want to get up to speed quickly:
    • Choose T-SQL learning curve easier
    • Slightly less Error control (more later)
    • Can use all of IQ’s functionality with T-SQL
sql syntax6
SQL Syntax
  • Watcom/ASA has additional
    • Error Handling
    • Function Calls
    • This processed by ASA parsing
    • Flow Control
      • create function dbo.waitfor_delay( in s_tm integer )
      • begin
      • declare cmd varchar(200);
      • select ‘xp_cmdshell ’’sleep ’ + str(s_tm) + ’’’’ into cmd;
      • execute immediate cmd;
      • end;
useful compatible comparable commands
Useful Compatible/comparable commands
  • ASE IQ
  • Sp_help sp_columns/sp_table

sp__helptable**

sp_h*

  • Sp_configure sp_iqconfigure*

sp_iqcheckoptions

  • Dbcc sqltext sp_iqcontext
  • Sp_who sp_iqcontext, sa_conn_info,

IQ Utilities

  • Defncopy dbxtract (not exact)
  • Sp_helptext sp_helptext ‘owner.object’
  • Sp_helpindex sp__helpindex**
  • Kill ## drop connection ##
  • Sp_sysmon IQ UTILITIES, sa_eng_properties
  • Waitfor delay sleep x*
useful compatible comparable commands8
Useful Compatible/comparable commands
  • Dbcc checkdb() sp_iqcheckdb ‘check’
  • Isql dbiql
  • Sqsh sqsh (\set semicolon_hack=1)
useful compatible comparable system tables
Useful Compatible/comparable System Tables
  • Tables IQ
  • Sysobjects sysobjects view

SYS.SYSTABLE

SYS.SYSINDEX

  • Syscolumns SYS.SYSCOLUMN
  • Sysindexes SYS.SYSINDEX

SYS.SYSIXCOL

  • Syscomments SYS.SYSPROCEDURE

SYS.SYSTRIGGER

  • Sysprocesses IQ UTILTIES

sp_iqwho*

  • Sysconfigures DBA.SYSOPTIONDEFAULTS

SYS.SYSOPTION

* Wrote my own.

** Available at IQ User-group Website

what s different that you need to know
What’s different that you need to know!
  • Think lots of rows when doing operations
    • IQ works better when operating on lots of rows.
    • Don’t select 1 row all the time..
  • General rule of thumb
    • 100,000 rows per second on load/insert/update(general minimum)
    • Think Fast!!
  • Don’t use rowcounts with deletes!
    • If you want to do this
      • create temp table
      • Fill temp table
      • Do the delete against real table joining keys of temp table.
what s different that you need to know11
What’s different that you need to know!
  • SQL is syntax checked at run time
    • This can catch you at the wrong time.
    • Syntax usually catches first row not actual row of syntax
    • Single quotes vs. double quotes usually problem
    • Error like:
      • Select name from sysobjects where type = “U”
      • Msg 207, Level 16, State 0
      • ASA Error –143: Column ‘U’ not found
  • Set options are viewable by system tables
    • Sp_iqconfigure*
    • Temporary options not seen
    • DBA.SYSOPTIONDEFAULTS
    • SYS.SYSOPTION
temp tables
Temp tables
  • Three different types
    • Global temporary tables(ANSI 92 spec)
      • Exist across user connections for the Login
      • Persistent across Begin/end blocks.
      • Located in System tables
      • Must have Resource (for user), DBA (for another user)
      • Create global temporary table gt1( col1 int not null)
      • on commit preserve rows
    • Local temporary tables(ANSI 92 spec)
      • Not persistent across begin/end blocks.
      • Declare local temporary table lt1
      • (col1 int not null)
      • On commit preserve rows
    • # temp tables (ASE specific)
      • Not persistent across BEGIN/END blocks
      • These are more like “local temporary table” But in ASE these are
      • closer to “global temporary tables”
what s different that you need to know13
What’s different that you need to know!
  • Bcp from ASE to IQ
    • Format is slightly different
    • Need to append to end of list an extra delimiter
    • from this
      • 1^2^3
      • To this:
      • 1^2^3^
  • Might want to set up Insert from location
  • Alternatively set up CIS proxy tables
what s different that you need to know14
What’s different that you need to know!
  • No indexes in ASE when loading to get the fastest performance
  • IQ put all indexes on…
  • Indexes are loaded then “merged” on the fly.
    • Two stages
    • Selects can occur when data is loaded.
    • Remember 1 writer
things to watch out for
Things to watch out for
  • Since 1 Writer per table can run into exclusive table blocks
  • Happens on:
    • Load table
    • Inserts
    • Update
    • Deletes
    • Cause Rollbacks.
    • This increases the transaction time Wastes valuable resources.
    • Better to check or to acquire “dummy” lock on the table.
    • Created function:
      • Check_locks(‘owner.table’)
      • Similar to sp_iqlocks looking for ‘E’ & ‘W’
indexing 101
Indexing 101
  • The basics
    • Create table syntax:
      • Create table (column1 int not null)
    • Create index syntax
      • Create index_type index
      • on owner.table
      • (column1, … )
      • Delimited by ‘ ‘ - used for Word indexes
      • Index_type: CMP, HG, HNG, LF, WD, DATE, TIME, DTTM
indexing 10117
Indexing 101
  • What to add indexes on:
    • All join columns
      • Either LF or HG first
      • Then Add Date/Datetime, Time DATE,DTTM,TIME indexes
    • Special Columns
      • Cmp for comparing 2 columns
      • WD Index for “google” type searches
    • Don’t forget the PK or UNIQUE HG INDEX
    • HG,LF only type of UNIQUE index
indexing 10118
Indexing 101
  • Remember:
    • Always get FP index
    • IQ indexes always on individual columns
    • Except PK which is on Multiple columns
    • HG is only index on multiple columns
  • IQ UNIQUE clause
  • Create table x1( col1 int not null IQ UNIQUE 255)
    • Tried several tests… vs. not specifying the value.
    • Slight performance differences (within 5%)
    • So do you forget the UNIQUE clause? Hmmm
    • Remember can only specify this at create table time!!
    • To reset the value you must move table/drop recreate with higher value
    • This saves storage space by adjusting the size of storage.
    • If you know the value set it. Try to get some sample data!
    • Specify when # unique less than 65536
indexing 10119
Indexing 101
  • So far..
    • 50-80% of the time there is a missing index
    • 10% Rewrite the SQL
    • 10% help the optimizer
    • 1% something Else
  • Optimizer is VERY good in IQ.
    • Have to give it HG or LF to know cardinality.
    • More with Utility programs.
    • Can create FK/Join indexes to speed the indexes in IQ.
    • Have to “update” join indexes via
    • ‘Synchronize join index join_index_name’
indexing 10120
Indexing 101
  • So
    • 1 FP by default
    • Join columns either HG OR LF
    • Date,Date-time, TIME used in where critera add DATE, DTTM, TIME
    • If compare in where clause then CMP
    • PK create UNIQUE HG index
    • HNG as needed
    • Could end up with 3 or 4 indexes on join columns.
fun tools that ase doesn t have
Fun tools that ASE Doesn’t have!
  • Contains (for word indexes)
  • Graphical Performance Plan (See ASE 15.0 for comparable )
  • DSS type Query Engine (NTILE, RANK, Dense_Rank, Variance & more coming!)
  • Insert from Location
  • Create index on-line!
  • I can Log all SQL Commands!
  • Watch SQL that is running.
  • Create Function
  • Timer functions
utilities that you might want to build
Utilities that you might want to build?
  • Monitoring of processes?
    • Sp_iqcontext
    • Sp_iqtransaction
    • Sa_conn_info
    • May want to combine to form sp_who variant
    • Monitoring of space per table
    • Sp_iqspaceinfo
    • Wait for Locks?
      • Check_locks(table_name)*
utilities that you might want to build23
Utilities that you might want to build?
  • Monitoring of IQ indexes
    • Looking for LF indexes that are over 1000-5000 unique values going to 10,000
    • Select count(distinct column) from table
    • At 10,000 will get out of unique values
  • Monitoring of total space consumed
    • Sp_iqstatus
  • SQL log scanner programs
    • To look at the IQ logs and look for slow operations.
tuning
Tuning
  • Set options to view query plan.

set option sa.Query_Detail='on'

set option sa.Query_Name='my_query_name'

set option sa.Query_Plan='on'

set option sa.Query_Plan_After_Run='on'

set option sa.Query_Plan_As_HTML='on'

set option sa.Query_Timing='on'

  • How to monitor speed. OLD ASE trick. #1
    • declare @dtm datetime
    • select @dtm = getdate()
    • STATEMENT
    • select datediff(ms,@dtm,getdate())
tuning25
Tuning
  • Old ASE trick #2
    • timex isql
  • Isql –p –Usa –SIQSERVER –Ppassword –isql_cmd

[25] asiqdemo..1> select count(*) from sysobjects

[25] asiqdemo..2> go

count(*)

-----------

570

(1 row affected)

Clock Time (sec.): Total = 0.001 Avg = 0.001 (1228.50 xacts per sec.)

  • Look at the log file..
    • Times rounded to nearest second.
    • Load time every 100,000 rows
    • Possibly up to 2 passes.
tuning26
Tuning
  • SQL timings a bit off in the error log.
  • best to time statements
  • This is with:
    • getdate() and datediff
    • Run 2 times for stability.
  • QUERY_TEMP_SPACE_LIMIT
    • When hit this this means that the optimizer may not know enough detail.
    • It is estimating lots of data and lots of rows resulting in Large QUERY_TEMP_SPACE_LIMIT
tuning watch log file
Tuning- watch log file.
  • Watch the timing of individual SQL Statement
  • Goes Through Phases
  • Begin
  • Commit
  • Post commit
  • Like this:
  • Insert:

2004-06-30 08:06:48 0000000002 Txn 39581

2004-06-30 08:06:49 0000000002 Cmt 39582

2004-06-30 08:06:49 0000000002 PostCmt

tuning watch log file28
Tuning- watch log file.
  • Update:

2004-06-30 08:09:25 0000000002 Txn 39583

2004-06-30 08:09:25 0000000002 Update Started:

2004-06-30 08:09:25 0000000002 sa.x1

2004-06-30 08:09:25 0000000002 [20895]: Update Pass 1 completed in 0 seconds.

2004-06-30 08:09:25 0000000002 [20895]: Update Pass 2 completed in 0 seconds.

2004-06-30 08:09:25 0000000002 [20896]: Update for 'sa.x1' completed in 0 second

s. 1 rows updated.

2004-06-30 08:09:26 0000000002 Cmt 39584

2004-06-30 08:09:26 0000000002 PostCmt

Note all the same Connection: 0000000002

Same format: Txn

Cmt

PostCmt

tuning watch log file29
Tuning- watch log file.
  • DELETE:

2004-06-30 08:11:32 0000000002 Txn 39587

2004-06-30 08:11:32 0000000002 [20917]: Delete of 1 rows started for table:

2004-06-30 08:11:32 0000000002 [20919]:

Delete of 1 rows completed for table: sa.x1, 0 seconds.

2004-06-30 08:11:32 0000000002 Cmt 39588

2004-06-30 08:11:32 0000000002 PostCmt

  • Load Table

In table 'sa.history', the full width insert of 15 columns will begin

at record 1.

2004-04-08 00:10:29 0000000013 Insert Started:

2004-04-08 00:10:29 0000000013 sa.history

2004-04-08 00:10:29 0000000013 [20897]: 1000 Rows, 0 Seconds (this will continue…)

2004-04-08 00:10:30 0000000013 [20895]: Insert Pass 1 completed in 1 seconds.

2004-04-08 00:10:30 0000000013 [20895]: Insert Pass 2 completed in 0 seconds.

2004-04-08 00:10:30 0000000013 [20834]:

7866 records were inserted into 'sa.history'.

2004-04-08 00:10:31 0000000013 [20896]: Insert for 'sa.history' completed in 2 seconds. 7866 rows inserted.

tuning selects
Tuning - Selects
  • Some of the basics
    • Look for 0.400000 in the HTML Plan
    • Look for 0.200000
    • Look for 0.100000
    • Look for 0.800000
    • Look for Large Result set in the output
    • IQ likes HASH based Plans
    • Look for the “LEAF” that is running the longest. This may be right or Wrong… But is a good first step.
    • Look for FP index with no other index type available. (good for Improvement)
  • Example HTML output for tuning.
tuning selects31
Tuning - Selects
  • Sample Plan- Lets look at some.
tuning usefulness constant
Tuning – Usefulness Constant
  • Usefulness # of values
  • constant in "in list"

9 1 -3

8 4-100

7 500

6 1014

  • Example Partial Query plan:
  • Usefulness # of values
  • constant in "in list"
  • 9 1 -3 Right outer join(Hash)
  • 8 4 Right outer join(Hash)
  • 8 5-100 outer join (sort merge)
  • 7 500 Left outer Join (Hash)
  • 6 1014 Left outer join (Hash)
tuning load table
Tuning – Load table
  • What to look for
    • Rows going in
    • Commit phase 1
    • Commit phase 2
    • Speed that I have seen on this:
    • Phase 1:
    • Rows Seconds Average
    • 18713458 233 80315 rows/sec (slow side)
    • 287614309 1471 195,522 rows/sec
    • Phase 2
    • 8713458 19 458603 rows/sec
    • 287614309 758 379,438 rows/sec
loading
Loading
  • Might want to set up IQ to ASE CIS proxy tables

CIS FROM IQ to ASE

    • Add server to Interfaces file
    • Add Server to system tables
    • Add external login

CIS from ASE to IQ

    • Add Server to Interfaces file
    • Add Server to sysservers
    • Add External login
    • Use master
    • Grant connect to “public” or “user”
tuning deletes
Tuning - Deletes
  • HG_DELETE_METHOD
    • 0, 1, 2
    • 0 is default
    • 1 is “small” method
    • 2 is large method
  • Personal Experience
    • 1 seems to be a bit faster..(for what I was doing)
    • 12.6 will change this
error handling
Error Handling
  • This is a fun one!
    • Better to use SQLCODE, SQLSTATE than @@error
  • This will be changing a bit..
  • Error handling within Procedures is slightly different than outside
    • Better to create stored procedure than
    • But you DBA’s out there have to be able to script!!
  • Set options
    • ON_TSQL_ERROR T-SQL
    • ON_ERROR DBISQL
error handling t sql
Error Handling T-SQL
  • SET TEMPORARY OPTION SA.ON_TSQL_ERROR=

‘CONDITIONAL’

is “on exception resume” set?

yes= continue

No = exit procedure

‘CONTINUE’

‘STOP’

  • Insert into x1(col1, col2) values (1,2,4)
  • select @cd = SQLCODE, @err = @@error, @st=SQLSTATE
  • NOTE:
    • SQL Batches operate differently than stored procedure (today)
error handling watcom ansi
Error Handling Watcom/ANSI
  • CREATE PROCEUDRE
  • ON EXCEPTION RESUME
  • if SQLCODE <> 0 then
  • return( NULL )
  • end if;
  • Set option On error=‘stop’ or ‘Continue’
sql tricks
SQL Tricks
  • Materialized Tables
    • Relatively new feature:
    • Create select statement inside a “FROM” clause
    • Select table_name from SYS.SYSTABLE st ,(

Select Select name from sysobjects where type = ‘U’) as t2

Where st.name = t2.table_name

  • Create function then use it in a SQL statement
    • Handy for reusing the function inside SQL code.
    • Remember that ASA will probably parse this.
    • So make sure runs fast before put function on.
sql tricks40
SQL Tricks
  • Where datepart(day,column) = 23
  • In ASE this would be slow!!
  • IN IQ
    • Put a DATE, TIME, DATETIME index on and watch the statement speed up!
    • So date functions are allowed in IQ.
    • Much better indexing methods!!
    • And this is at least 100X faster!!!
utilities that i built
Utilities that I built
  • Sp_configure / sp_iqconfigure
    • Constantly looking up the configuration values
    • Wanted to know what was set for a particular user
  • Sp_who /sp_w
    • Sp_iqcontext was too long! Wanted short output
utilities that i built42
Utilities that I built
  • New version of sp_help / sp_h
    • Needed to. See DDL and not FP indexes
    • Example of output
    • New version is in the works. shorter output
  • Sample Output

column_name data_type scale width NULL cardinality

------------------------------ --------------- ------ ----------- ---- ---------------

c1 int 0 4 0 3

c2 int 0 4 1 0

(2 rows affected)

TYPE INDEX_NAME ordered_list

---- ----------------------------------------- --------------------------------

HG x1_HG_c1 c1

utilities manuals that are out there
Utilities/Manuals that are out there!
  • http://www.odscompanies.com/iqug/iqug.html
    • Web site with good utilities and some quick info
  • http://www.sypron.nl/asiq_qref.html
    • IQ quick reference guide
    • Other IQ quick information
  • http://sybooks.sybase.com/onlinebooks/group-iq/iqg1250e
    • IQ online manuals
  • Code Exchange
    • Code Exchange for IQ
questions
Questions?
  • You can reach me at:
    • 1000 town Center
    • Suite 1800
    • Southfield MI 48075
  • bologna@sybase.com
    • Ask for code