1 / 44

BID203: Transitioning from ASE to IQ for Decision Support

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.

Thomas
Download Presentation

BID203: Transitioning from ASE to IQ for Decision Support

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. BID203: Transitioning from ASE to IQ for Decision Support Steven J. Bologna Principal Consultant Bologna@sybase.com August 15-19, 2004

  2. The Enterprise. Unwired.

  3. 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

  4. 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

  5. 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

  6. 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;

  7. 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*

  8. Useful Compatible/comparable commands • Dbcc checkdb() sp_iqcheckdb ‘check’ • Isql dbiql • Sqsh sqsh (\set semicolon_hack=1)

  9. 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

  10. 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.

  11. 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

  12. 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”

  13. 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

  14. 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

  15. 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’

  16. 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

  17. 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

  18. 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

  19. 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’

  20. 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.

  21. 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

  22. 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)*

  23. 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.

  24. 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())

  25. 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.

  26. 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

  27. 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

  28. 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

  29. 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.

  30. 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.

  31. Tuning - Selects • Sample Plan- Lets look at some.

  32. 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)

  33. 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

  34. 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”

  35. 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

  36. 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

  37. 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)

  38. Error Handling Watcom/ANSI • CREATE PROCEUDRE • ON EXCEPTION RESUME • if SQLCODE <> 0 then • return( NULL ) • end if; • Set option On error=‘stop’ or ‘Continue’

  39. 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.

  40. 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!!!

  41. 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

  42. 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

  43. 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

  44. Questions? • You can reach me at: • 1000 town Center • Suite 1800 • Southfield MI 48075 • bologna@sybase.com • Ask for code

More Related