0

I have a query in Sybase IQ 12.7 that looks like:

select ip from iplookup where ip in (select ip from persisted_info)

where iplookup is defined as a single column.

I saw the IN and subquery and decided that must be slow (full table scan) and that this would be preferred:

select lk.ip from iplookup lk, persisted_info ps where lk.ip = ps.ip

but I want to be careful and get some evidence to back me up. Sybase IQ does not support EXPLAIN PLAN and using SET STATISTICS TIME ON returns a syntax error.

To resolve this, I would like to know how to get either:

  1. timing information
  2. execution plan information

Edit: I am using sqsh from a Mac to talk to Sybase and I get extra information in the response when I add SET STATISTICS IO ON ahead of the query.

Spaceghost
  • 6,835
  • 3
  • 28
  • 42
  • Those two statements are not necessarily equivalent! –  Apr 03 '13 at 20:38
  • I know the two SQL statements are not necessarily equivalent but as I said I am trying to find some evidence. – Spaceghost Apr 03 '13 at 20:45
  • 1
    I'm not talking about performance, I'm talking about what they achieve. They can return completely different results! See here: http://sqlfiddle.com/#!12/fcf04/3 –  Apr 03 '13 at 20:45
  • +1 for sqlfiddle. However, in my tables, the IP column is the primary key.. – Spaceghost Apr 03 '13 at 20:53

1 Answers1

1

I think you're looking for (edited syntax, I was in ASE mode)

set TEMPORARY OPTION NOEXEC = on;
set TEMPORARY OPTION QUERY_PLAN = on;
set TEMPORARY OPTION QUERY_TIMING = on;

These write to the server log, which i'm not sure you have access to. If you can live without exact IO and timing statistics, then you won't need access to the server logs and can do what's below...

If you're on @@version 12.7 ESD3 or later, you should be able to do either of the following two queries and put the output into an .html or .xml file and view it.

--XML
SELECT GRAPHICAL_PLAN ('select * from t1 where ip in (select ip from t2)')
--HTML
SELECT HTML_PLAN ('select * from t1 where ip in (select ip from t2)')

Link for more info: http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc74603.1270/html/iqrbwin/CIHEFAIF.htm

Hotel
  • 1,361
  • 11
  • 13
  • I agree that the two queries should perform in a similar manner but the subquery seemed to lag whenever I tried it. Sadly, all three of these commands are not accepted by Sybase IQ 12.7.. the first returns a message about Transact-SQL and the other two are syntax errors. – Spaceghost Apr 03 '13 at 12:44
  • Do your iplookup.ip column have an identical datatype as your persisted_info.ip column? It looks like the optimizer should be doing a grouped leaf level correlated subquery in the "in" clause regardless. Do they both have "HG" indexes on them? sp_iqindex 'iplookup', 'ip' – Hotel Apr 03 '13 at 19:53
  • Thanks for the update. I am now waiting on the DBA for access to the plan. Yes, both columns have the same datatype and both have HG indexes. I saw a note in the Sybase Performance guide that the subquery should only have 1025 values in it.. but that might have been for user-provided values rather than query results. – Spaceghost Apr 03 '13 at 20:24
  • Accepting your answer because it tells me how to get all the information I asked for.. now just have to persuade DBA to enable things. – Spaceghost Apr 03 '13 at 21:09
  • I'd think the DBA's would be extremely happy you were spending the time to optimize your query, and be happy to grant the QUERY_PLAN_TEXT_ACCESS option! – Hotel Apr 03 '13 at 23:38
  • Let's hope! Thanks for your help. – Spaceghost Apr 04 '13 at 01:14