remote server select passed through/insert ... select not

This statement is passed through to a remote oracle server:

select p.product_id,
  cast(so.fixed_std_time as int) + cast(so.fixed_std_time_hh as int)*60 +
cast(so.fixed_std_time_dd as int)*1440 as fixed_std_time_minutes,
  cast(cast(socextgauge.operation_char_val as decimal(14,7))*.0000001 as
decimal(7,7)) as gauge,
  cast(socpass.operation_char_val as int) as passnumber,
  cast(socalloy.operation_char_val as char(6)) as alloy
from ppsprod.product p
join ppsprod.standard_operation so
  on p.product_id=so.product_id
     and required_res_1='811'
join ppsprod.standard_op_char socextgauge
  on so.product_id=socextgauge.product_id and
so.standard_op_no=socextgauge.standard_op_no
     and so.alternate_pref=socextgauge.alternate_pref and
so.op_proc_no=socextgauge.op_proc_no
     and socextgauge.product_char_id=1 and socextgauge.product_char_type='S'
join ppsprod.standard_op_char socpass
  on so.product_id=socpass.product_id and
so.standard_op_no=socpass.standard_op_no
     and so.alternate_pref=socpass.alternate_pref and
so.op_proc_no=socpass.op_proc_no
     and socpass.product_char_id=4 and socpass.product_char_type='S'
join ppsprod.standard_op_char socalloy
  on so.product_id=socalloy.product_id and
so.standard_op_no=socalloy.standard_op_no
     and so.alternate_pref=socalloy.alternate_pref and
so.op_proc_no=socalloy.op_proc_no
     and socalloy.product_char_id=18 and socalloy.product_char_type='S'

An insert into a local temporary table from the select is not:

insert into bronermillstandards_byproduct_t
(product_id, fixed_std_time_minutes, gauge, passnumber, alloy)
select p.product_id,
  cast(so.fixed_std_time as int) + cast(so.fixed_std_time_hh as int)*60 +
cast(so.fixed_std_time_dd as int)*1440 as fixed_std_time_minutes,
  cast(cast(socextgauge.operation_char_val as decimal(14,7))*.0000001 as
decimal(7,7)) as gauge,
  cast(socpass.operation_char_val as int) as passnumber,
  cast(socalloy.operation_char_val as char(6)) as alloy
from ppsprod.product p
join ppsprod.standard_operation so
  on p.product_id=so.product_id
     and required_res_1='811'
join ppsprod.standard_op_char socextgauge
  on so.product_id=socextgauge.product_id and
so.standard_op_no=socextgauge.standard_op_no
     and so.alternate_pref=socextgauge.alternate_pref and
so.op_proc_no=socextgauge.op_proc_no
     and socextgauge.product_char_id=1 and socextgauge.product_char_type='S'
join ppsprod.standard_op_char socpass
  on so.product_id=socpass.product_id and
so.standard_op_no=socpass.standard_op_no
     and so.alternate_pref=socpass.alternate_pref and
so.op_proc_no=socpass.op_proc_no
     and socpass.product_char_id=4 and socpass.product_char_type='S'
join ppsprod.standard_op_char socalloy
  on so.product_id=socalloy.product_id and
so.standard_op_no=socalloy.standard_op_no
     and so.alternate_pref=socalloy.alternate_pref and
so.op_proc_no=socalloy.op_proc_no
     and socalloy.product_char_id=18 and socalloy.product_char_type='S'

Why not? With a passthrough, the statement executes fast. Without
passthrough, it never finishes. I eventually go home.

Clay



0
Clay
7/9/2002 3:17:27 PM
sybase.sqlanywhere.general 32637 articles. 4 followers. Follow

1 Replies
605 Views

Similar Articles

[PageSpeed] 34

So how about setting up a work table on the Oracle side? Populate the work
table within Oracle and then have the insert on the ASA side reference the
work table.

--
-----------------------------------------------
Robert Waywell
Sybase Adaptive Server Anywhere Developer - Version 8
Sybase Certified Professional

Sybase's iAnywhere Solutions

Please respond ONLY to newsgroup

EBF's and Patches: http://downloads.sybase.com
 choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

"Clay Atkins" <catkins@spcmg.com> wrote in message
news:MggaU11JCHA.869@forums.sybase.com...
> This statement is passed through to a remote oracle server:
>
> select p.product_id,
>   cast(so.fixed_std_time as int) + cast(so.fixed_std_time_hh as int)*60 +
> cast(so.fixed_std_time_dd as int)*1440 as fixed_std_time_minutes,
>   cast(cast(socextgauge.operation_char_val as decimal(14,7))*.0000001 as
> decimal(7,7)) as gauge,
>   cast(socpass.operation_char_val as int) as passnumber,
>   cast(socalloy.operation_char_val as char(6)) as alloy
> from ppsprod.product p
> join ppsprod.standard_operation so
>   on p.product_id=so.product_id
>      and required_res_1='811'
> join ppsprod.standard_op_char socextgauge
>   on so.product_id=socextgauge.product_id and
> so.standard_op_no=socextgauge.standard_op_no
>      and so.alternate_pref=socextgauge.alternate_pref and
> so.op_proc_no=socextgauge.op_proc_no
>      and socextgauge.product_char_id=1 and
socextgauge.product_char_type='S'
> join ppsprod.standard_op_char socpass
>   on so.product_id=socpass.product_id and
> so.standard_op_no=socpass.standard_op_no
>      and so.alternate_pref=socpass.alternate_pref and
> so.op_proc_no=socpass.op_proc_no
>      and socpass.product_char_id=4 and socpass.product_char_type='S'
> join ppsprod.standard_op_char socalloy
>   on so.product_id=socalloy.product_id and
> so.standard_op_no=socalloy.standard_op_no
>      and so.alternate_pref=socalloy.alternate_pref and
> so.op_proc_no=socalloy.op_proc_no
>      and socalloy.product_char_id=18 and socalloy.product_char_type='S'
>
> An insert into a local temporary table from the select is not:
>
> insert into bronermillstandards_byproduct_t
> (product_id, fixed_std_time_minutes, gauge, passnumber, alloy)
> select p.product_id,
>   cast(so.fixed_std_time as int) + cast(so.fixed_std_time_hh as int)*60 +
> cast(so.fixed_std_time_dd as int)*1440 as fixed_std_time_minutes,
>   cast(cast(socextgauge.operation_char_val as decimal(14,7))*.0000001 as
> decimal(7,7)) as gauge,
>   cast(socpass.operation_char_val as int) as passnumber,
>   cast(socalloy.operation_char_val as char(6)) as alloy
> from ppsprod.product p
> join ppsprod.standard_operation so
>   on p.product_id=so.product_id
>      and required_res_1='811'
> join ppsprod.standard_op_char socextgauge
>   on so.product_id=socextgauge.product_id and
> so.standard_op_no=socextgauge.standard_op_no
>      and so.alternate_pref=socextgauge.alternate_pref and
> so.op_proc_no=socextgauge.op_proc_no
>      and socextgauge.product_char_id=1 and
socextgauge.product_char_type='S'
> join ppsprod.standard_op_char socpass
>   on so.product_id=socpass.product_id and
> so.standard_op_no=socpass.standard_op_no
>      and so.alternate_pref=socpass.alternate_pref and
> so.op_proc_no=socpass.op_proc_no
>      and socpass.product_char_id=4 and socpass.product_char_type='S'
> join ppsprod.standard_op_char socalloy
>   on so.product_id=socalloy.product_id and
> so.standard_op_no=socalloy.standard_op_no
>      and so.alternate_pref=socalloy.alternate_pref and
> so.op_proc_no=socalloy.op_proc_no
>      and socalloy.product_char_id=18 and socalloy.product_char_type='S'
>
> Why not? With a passthrough, the statement executes fast. Without
> passthrough, it never finishes. I eventually go home.
>
> Clay
>
>
>


0
Robert
7/9/2002 4:17:31 PM
Reply:

Similar Artilces:

insert select and select into
Hello, I am using temp tables to generate a report. I created most of the temp tables and then use insert select. I found for one, when I use insert select, I get a different query plan than if I use select into. The select into get a much better performance. The problem is I get dead locks on the statement. Is there anything I can do to change this behavior? Thanks, Josh Manfred ...

SELECT from SELECT
Hi! I remember seeing an example where you can do a select from a nested select, but I cannot seem to make it work. So it would be something like this: select max( name ) from ( select name from customer union select name from vendor ) Was I dreaming I saw that? Thanks! D In article <4500243f@forums-2-dub>, daniel_AT_infrontsoftware_DOT_com@N0SPAM.com says... > Hi! > > I remember seeing an example where you can do a select from a nested > select, but I cannot seem to make it work. No, you weren't. Try (UNTESTED!) adding a...

select into vs insert/select
I am looking for ways to speed up a procedure, which as a part of an overnight processing is called about 3000 times. The procedure begins by inserting about 20 rows into a temp table created outside of the proc. I thought that this may be better done by a select into statement because it is minimally logged etc. I am running the two versions of the statement (insert/select and select into) 2700 times on a quiet server and it seems that the insert/select is a bit faster, a second or two. Any ideas what I'm doing wrong? Is it my assumption about select into being fas...

Select into vs. insert ... select ...
Hello, I am using temp tables to generate a report. I created most of the temp tables and then use insert select. I found for one that when I use insert select I get a different query plan that if I use select into. The select into get a much better performance. The problem is I get dead lock on the statement. Is there anything I can do to change this behavior? Thanks, Josh Manfred SELECT INTO #temp is a non-logged operation. This can provide a performance increase. However, one downside is that you may end up with a lot of contention on system tables. (I didn...

Why is the select saying selected after an item is selected?
This was a whitelisting issue due to a software upgrade at hostgator..  Nothing wrong with my code..Scot King SEO Software Tool | Search Engine Optimization"Get your page rank high"  huh? It is smart to put your question in the body and not just in the title. Have no idea what your question is or if it even a question.Eric Pascarello.com | Twitter epascarello | LinkedIn use thisddl.options[ddl.selectedIndex].selected = false; //Clear the selection ddl.options[1].selected = trueMAKMark as Answer if this reply helps youMVP ASP/ASP.NetASP.Net Hosting : Host DepotMy Sit...

Select to remote server
Hi all, This is the situation : Server_one Server_two login access: usr_one login access: usr_two pass access: usr_one pass access: usr_two ---------------------------------------------------------------------------- -------------------------------- database_one database_two ---------------------------------------------------------------------------- -------------------------------- table_one table_two id_user_table_one int id_user_table_two int name_user_table_one varchar(30) name_user_table_two varchar(30) ----------------------------------...

does an insert on xforms-select event necessarily cancel select?
Hi, i would like to invoke an insert (on another data node) when the user selects an item in a select form control. however with an insert the normal select doesn't work anymore. i searched through the spec's and i couldn't find any reason why this should block the normal capture/ bubbling of xforms-select and cancel subsequent events... is this a current limitation of the plugin or did i miss something in the spec? thanks in advance claude On Feb 10, 3:14=A0am, bernstein <clauden...@gmail.com> wrote: > Hi, > > i would like to invoke an insert...

select in the select statement
Hi, I am trying to add another select statement in my original/outer/overall select statement. Something like: Select 1, 2, 3 = (Select # from Table1 Where....) From Table 2 Where.... Does anyone know the standard way to write this or is there any other alternative that can perform the same behavior. Much thanks!! Kenny That's database dependent. In oracle you would use: select A.* from ( select * from mytable) A -- Terry Dykstra (TeamSybase) Please state PB / OS / DB versions in your post. MySybase http://my.sybase.com/mysybase "Kenny Tran" <...

Select in the select clause
How to use another select clause within a select clause itself. Like; select col1,col2,(select colx from table b) from table a . The above statement is throwing syntax error Thanks, Anandkumar B CGVAK Software & Exports Ltd. ( 0422 2434491/92 http://www.cgvakindia.com "anand_group" <anand@cgvakindia.com> wrote: > How to use another select clause within a select clause itself. > Like; > select col1,col2,(select colx from table b) from table a . > The above statement is throwing syntax error Ensure that subquery returns only one row. And...

SELECT . FROM ( SELECT .. FROM )
Hi, In application we generate very effective SQL statements that are understandable by MS SQL Server and Oracle but give syntax errors on Sybase. I am talking about SELECT . FROM ( SELECT .. FROM a_table). For example: select 1 from (select abc_id from abc) or select 1 from (select abc_id from abc union all select abc_id from bcd) Is there any way to overcome this limitation? Any help will be greatly appreciated. Leonid Borisov Tefensoft On Wed, 15 May 2002 02:09:52 -0700, Leonid Borisov wrote: > Hi, > > > >...

Select option selection
I'm created a gridview of websites, in one column, and use the gridview's 'selection' featue in the next column. When I click the 'selection' link I tried to get the website by using: Dim key As String = grvPrivacy.Rows(e.RowIndex).Cells(1).Text but it said it was not part of the webcontrol for selection. How do I get cell so that I can read it? thanks   Looks like your grabbing the select option and not the first cell  try  Dim key As String = grvPrivacy.Rows(e.RowIndex).Cells(0).TextwerD, MCSD VB.Net Hi, gmcghee: There are several ways...

Bug in SELECT from SELECT ?
I have a table intwb_items CREATE TABLE intwb_items ( wb_id integer not null, prod_id integer not null, qty numeric(12,5) not null, primary key(wb_id, prod_id) ) How can I obtain sum of 10 greatest "qty"? I tried to execute query: SELECT sum(qty) FROM ( SELECT top 10 qty FROM intwb_items ORDER BY qty DESC )as tmp But it raise error: "Syntax error or access violation: near 'ORDER' in ... FROM intwb_items [ORDER]" The same query but without "ORDER BY" works perfectly, but "TOP N" without "ORDE...

passing multiple select (selection list on a form) to html datawindow.
How do I pass a multi-select list box items on a form to a html datawindow as a page parameters? appreciate if someone can help. Thanks Syed. I found several ways to do this on www.irt.org if you want to search there for details. Try using client scripting and pass multiple parameters, one for each selected element in the list box. -- Larry Cermak [Team Sybase] Corporate Technology Partners, Inc. www.ctpartners.com Web DataWindow Articles (http://sdn.sybase.com/sdn/appdev/get_doc.stm?loc=1001708) TechWave2000 Presentations AM34, Extending the Web DataWindow (Mon-T...

how do I select certain record in sql server? and how do I select record randomly?
how do I select certain record in sql server? like I want to select the record 30how do I select record randomly? Hello, usually in each table in the database there is a primary key or unique key. Usually this key is an indentity, that is increased by a number of your choice. So, can't you select the record where ID = 30 ? Don't you have such a key ?   regardsBilal Hadiar, MCP, MCTS, MCPD, MCTMicrosoft MVP - Telerik MVP To get a random row, you can order by NEWID: SELECT TOP 1 * FROM Tbl ORDER BY NEWID() NEWID produces a pseudo-random GUID -- so ordering by it will give y...

Web resources about - remote server select passed through/insert ... select not - sybase.sqlanywhere.general

National Security Profiling Is a No-Brainer
Calm down and think, America. While everyone's undies are in a bunch over Donald Trump's proposal for a Muslim immigration moratorium, it is ...

Martin Freeman discusses Benedict Cumberbatch's theatre rants
Martin, pictured with Benedict Cumberbatch for the Sherlock special, is taking a sideways swipe at fellow stars who can be ‘pompous’ and overestimate ...

Quentin Tarantino Delivers Shrieking UFC-Style Intros for ‘Hateful Eight’ Cast at Hollywood Premiere
The December 2012 premiere for “Django Unchained” was canceled in the wake of the Newtown, Connecticut, but three years later not even the threat ...

Google Pixel C Tablet Now Available For Purchase
Last week, we reported that the Google Pixel C tablet was rumored for launch in a week’s time – and it seems that this particular rumor is definitely ...

Uber is looking into launching two new ridesharing services
Uber announced on Tuesday that it will trial a pair of new ridesharing services in the Seattle and Chicago metro areas. The first, called UberHOP, ...

TOM FRIEDMAN’S UTOPIA: Breathing the Air in Red China is Like Smoking 40 Cigarettes a Day
By Nick Sorrentino The free market is often blamed for pollution. The the unbridled greed of capitalists is the reason rivers flow red, forests ...

Judge Throws out Kansas Man's Murder Conviction
Judge overturns Kansas man's conviction in sister-in-law's 1999 killing

HTC delays Vive VR launch until April, plans to make 7K dev kits available in early 2016
Sam Machkovech / Ars Technica : HTC delays Vive VR launch until April, plans to make 7K dev kits available in early 2016 — HTC officially delays ...

Chicago Releases Video of Police Using a Taser on Man Who Later Died
ABC News Chicago Releases Video of Police Using a Taser on Man Who Later Died ABC News The Chicago Police Department has released a three-year-old ...

Yahoo reportedly gets serious about selling its core business
Yahoo appears to have more than just a casual interest in selling itself off. Both CNBC and the New York Times claim that the web giant has ruled ...

Resources last updated: 12/9/2015 4:35:20 AM