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


0
Josh
4/12/2001 1:46:05 PM
sybase.sqlserver.general 4269 articles. 0 followers. Follow

2 Replies
868 Views

Similar Articles

[PageSpeed] 31

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't see much contention on ASE 12.0 when I tested the creation of #temp tables, but your 
experience may differ.)

An alternative is to create permanent "temp" tables.  These can be either in your user 
database or even in the tempdb database.  You'll have to add a column (or two) to the temp 
table to differentiate rows belonging to different users.  And you may need to add code to 
your script/procedure/application to handle the new column.
-- 
Jim Egan [TeamSybase]
Senior Consultant
Sybase Professional Services

Get your free subscription to PowerTimes at http://www.powertimes.com
0
Jim
4/12/2001 3:39:23 PM
The contention is part of the problem, but the real problem is the
difference is the query plan.  What I do not understand is, if the select
statements are identical, but how I insert into the temp table is different,
how does this change the query plan.  And the difference in big.  The
performance is terrible.  The join order is different, the indices it uses
is different. It appears that the I running two different queries.  I
removed the insert part and review the query plan of just the select
statement and it change back to the select into( the good ) query plan.  Any
ideas?

Josh


"Jim Egan" <dbaguru@eganomics.com> wrote in message
news:MPG.153f7a18f70ad51a98b27e@forums.sybase.com...
> 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't see much contention on ASE 12.0 when I tested the creation of #temp
tables, but your
> experience may differ.)
>
> An alternative is to create permanent "temp" tables.  These can be either
in your user
> database or even in the tempdb database.  You'll have to add a column (or
two) to the temp
> table to differentiate rows belonging to different users.  And you may
need to add code to
> your script/procedure/application to handle the new column.
> --
> Jim Egan [TeamSybase]
> Senior Consultant
> Sybase Professional Services
>
> Get your free subscription to PowerTimes at http://www.powertimes.com


0
Josh
4/12/2001 3:57:06 PM
Reply:

Similar Artilces:

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

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 * vs. SELECT 1
I have always understood that when using the WHERE EXISTS clause, one codes it as WHERE EXISTS (SELECT * FROM .....). Lately, I've been told that performance is better if the it's coded as ...(SELECT 1 FROM ...) (or SELECT '' .. - that is, to select some literal). Everything I've read says that SELECT * is used with EXISTS because there's no result set returned - it only evaluates into true or false. I'm now curious - is there an advantage to writing SELECT 1 instead of SELECT * with EXISTS? If so, how much of an advantage, & why do all of the SQL b...

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

country selected vs language selected
Hi, looking for suggestions on how to handle the following with 2.0. in some cases, sites would be required to switch between countries and independantly, switch the language. For instance; a US english site would be presenting items available only in the US, but a user might wish to have a separate control to switch the language to spanish or whatever. Therefore, users may change countries to see what products are available in that country, but may wish to change the language while in the country specific site. Hope that makes sense. any feedback is appreciated. See if the following ar...

Select into vs Insert
Hi , I appriciate if someone calrifies. As per Sybase manuals(T-Sql), "Select into" is a two step process first step create the table and insert the data into table. Whether my assumption is correct or not? When creating the table Server will have lock on system tables, During the insertion of data it will not hold the lock on System tables. Here I would like to know what are other adavantages I can get by using "select into" In my devlopment I would like use more "select into"s than Create & Insert. Which reduces my logging pretty ...

ddl Selected Index vs Selected Value
Obviously I'm missing something...in all my debugging I find that when trying to set the selectedindex to my dropdownlist that all previous logic goes out the window! In standard VB, setting the selectedindex meant referring to it's option value. What I find now is there is a distinct index array collection not based on the listitem value. To summarize, I have a datagrid with template column that contains a read-only textbox for it's Item Template and a dynamic dropdownlist for it's Edit Item Template. When the edit hyperlink command is clicked, I grab the text value from the textbox a...

select * vs select field, which one is faster ?
Suppose we have a table of 50 fields, which command should we use to take the result faster : SELECT * FROM Employee SELECT employee.id, employee.name FROM Employee Thanks for help. -- Best regards, Ridwan Goldbase Technology Visit our Homepage : http://www.geocities.com/Wallstreet/Floor/5251/index.html http://members.tripod.com/~goldbase http://members.xoom.com/goldbase * Check out the Y2K (Year 2000 Problems - Millenium Bugs) The second one. In that case, only data for two columns is retrieved. -- - Eric Aling [TeamPS], Cypres Informatisering bv, T...

Direct Select Vs select from host variables
Hi all Question regarding what is bad regarding a select statement. I am using a stored procedure for this: Table contains 15 columns 1. select all 15 columns in a select statement select column1, column2 ..... from tablename 2. declare @column1 , @column 2 .... select @column1 = column1, @column2 = column2 ... from table select @column1, @column2 .... The reason is because, i need to pass one or more column values to another stored procedure for some access control validations. If I have access, then proceed with select. Which is not good and why? Thanx i...

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

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

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

Web resources about - Select into vs. insert ... select ... - sybase.sqlserver.general

United States House Select Committee on Energy Independence and Global Warming - Wikipedia, the free ...
but will work with the House standing committees with jurisdiction over climate change issues and develop recommendations on legislative proposals. ...

kotaku selects
Kotaku Selects

Facebook Testing ‘Select Ad Variations To Generate’ Option In Power Editor?
Facebook appears to be testing a new feature in Power Editor that allows users to more easily test different versions of their ads. continued… ...

Seventh Generation Selects 72andSunny After Review
Seventh Generation has named 72andSunny, New York, to lead strategy and brand development for its home and personal care products following a ...

Apple Selects Best Apps, Games of 2015
Apple revealed its “Best of 2015,” highlighting the best applications and games the iTunes App Store had to offer this year. Apple selected ...

Google Play offering select buy one, gift one movies for the holidays
... a few days ago, Google is giving you the chance to purchase a movie and gift that same movie to a friend for no extra charge. The selection ...

BMW selects two artists to create M6 GT3 art cars
Filed under: Design/Style , Motorsports , BMW , Coupe , Performance , Racing Artists Cao Fei and John Baldessari will create the 18th and 19th ...

US confidential note to select countries sets the terms for talks
... between developed and developing countries scrapped Days ahead of the launch of the Paris talks, the US has confidentially informed select countries ...

Cisco Selects Netformx Discovery to Power New Net Authenticate Assessment Service
Cisco Selects Netformx Discovery to Power New Net Authenticate Assessment Service Business Wire (press release) SAN JOSE, Calif.(BUSINESS WIRE)Netformx®, ...

‘King Georges’ Documentary Eaten Up By Sundance Selects
Sundance Selects has acquired Erika Frankel ’s documentary King Georges , a profile of the rise (and decline?) of famed chef Georges Perrier ...

Resources last updated: 12/13/2015 3:12:08 AM