FreeTDS Development Group

["Pickett, David" <David.Pickett AT phlx.com>]

Hi,

If an outperforming workaround or two would help:

1. The subquery could, and maybe should for speed and one time compile
resource savings, be embedded in a stored procedure.

2. Many client side subqueries should be embedded in the parent select or,
best of all, be joins or outer joins.  I recommend the ANSI join syntax,
which is both more explicit and often supports different outer behavior when
you move predicates between the where and in clauses.  For mental clarity
and plan visualization, I suggest all outer joins not 'full' be 'left' (or
all right if you are a Hebrew or Arabic thinker).  Start with the tables
always there, and then tack on columns from 'maybe tables' to the right.  I
visualize it like these text lines, left justified with a ragged right
margin.  Outer joins to outer tables should be legal.  I have seen right and
left joins mixed produce strange results.

I am a bit stale/weak on the current Sybase/SQL Server syntax support, but
Oracle and DB2 have 'subquery in the from clause', which I call 'inline
view', where a subquery, especially an aggregate subquery, can be run in
parentheses and treated like a table.  This can relieve you from some
client-side subquery situations.  You can aggregate on a smaller result set
and then join to it after, to add any additional rows and columns.

Client side subqueries are usually because the programmer is more
comfortable in C than SQL, a personal challenge, or to overcome tuning
problems by effectively forcing a nested plan.  There are generally
proprietary tuning extensions to SQL that allow you to tune the plan.  If
necessary, I recommend ordering tables (forceorder) as very simple, terse,
easy to maintain and effective.  Of course, joins work best if YOU have a
good plan, and the server is just discovering or improving on it.

A properly executed join will *far* outperform client side nested
subqueries, and you will have a *lot* less code in a higher language.

Best regards,

David

-----Original Message-----
From: ZIGLIO, Frediano, VF-IT [mailto:Frediano.Ziglio AT vodafone.com]
Sent: Friday, December 02, 2005 11:05 AM
To: FreeTDS Development Group
Subject: Re: [freetds] Two statements on one connection


> 
> Dear All,
> 
> I am using freeTDS 0.63 and unixODBC under Redhat 7.1. Is it 
> OK to use two 
> statements on one connection? The below code gives me 
> 'invalid cursor state' 
> error while trying to do the second SQLExexDirect(). The code 
> works fine for 
> Oracle and MySQL drivers. What should I do if I want to 
> achieve the below 
> goal(e.g. fetch one table inside loopof fetching another table)?
> 
> 
> .......
>      V_OD_erg=SQLExecDirect(V_OD_hstmt,"select 
> employeeid,firstname,hiredate,
> salary,dsc FROM employees", SQL_NTS);
>     SQLAllocHandle(SQL_HANDLE_STMT, V_OD_hdbc, &V_OD_hstmt2);
>     SQLBindCol(V_OD_hstmt2,1,SQL_C_CHAR,&V_OD_buffer2,150,&V_OD_err);
>     V_OD_erg=SQLExecDirect(V_OD_hstmt2,"select firstname from 
> table2 ", 
> SQL_NTS);
>     V_OD_erg=SQLFetch(V_OD_hstmt);
>     while(V_OD_erg != SQL_NO_DATA)
>     {
>          printf("row(%d) Result: [%d] [%s] [%s] [%f] [%s]\n",
>             i,V_OD_id,V_OD_buffer,V_OD_date,V_OD_double,V_OD_dsc);
>         V_OD_erg=SQLFetch(V_OD_hstmt2);
>         printf("second Result: [%s]\n", V_OD_buffer2 );
>         V_OD_erg=SQLFetch(V_OD_hstmt);
>         i++;
>    };
> 
> Regards
> 
> Yanxin
> 

Short reply:
  use two connections

Long reply:
  mssql by default (using normal queries) do not allow two active
statement on same connection. This for TDS protocol limit. There is
however an option witch is the use of cursors. Enabling cursor would
allow you (for instance) to do a select and use results for another
query using same connection. However currectly FreeTDS odbc driver do
not provide cursors support...

freddy77
_______________________________________________
FreeTDS mailing list
FreeTDS AT lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds