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 faster or is my measurement flawed or the volume (about 20 rows) is too small to register a difference? Thank you for your help. Lubo Vitkov vitkov@un.org
|
0 |
|
Lubo_Vitkov
3/8/2002 1:11:07 PM
sybase.ase.performance+tuning
2395 articles. 
0 followers.
1 Replies
755 Views
If you are clling the proc 2700 times, you are ofsetting the reduction in logging against the extra work done to create & (implicitly) drop the table. All round, you are probably insering more than 20 rows into the system tables creating the table, then deleting them again! With such a small set of rows, I would suggest your best bet would be to create the temp table once, then truncate it each time before you populate it in your procedure. This will get rid of all that expensive DDL and replace it with logging 20 inserts and one truncate. Also, if the stored procedure is being called by more than one connection, you could be getting recompilation as the temporary table ids change. You could either generate multiple duplicate stored procedures, one for each connection calling it, or implement traceflag 299. If you decide to implement traceflag 299, you should be aware that it significantly changes server behaviour, particularly with respect to temporary tables; the server doesn't check tables' schemas, just assuming they are the same as last time it accessed the object with the same name in the same database. If this isn't true, you will get really nasty errors. -- Roger Broadbent Technical Consultant Wilco International Ltd <Lubo_Vitkov> wrote in message news:27D00745C01D68F400486DBB85256B76.00486DF285256B76@webforums... > 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 > faster or is my measurement flawed or the volume (about 20 rows) is too > small to register a difference? > > Thank you for your help. > > Lubo Vitkov > vitkov@un.org
|
|
0 |
|
Roger
3/8/2002 2:32:55 PM
Similar Artilces:
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...
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...
Selecting the multiple rows in GridView and inserting the selected rows to another Gridview & Performing some operations on it
Dear All, I have become mad for solving this problem. Please Help me. I have a Gridview bound to a datasource. I inserted the CheckBox Control in Templet to select certain rows. Gridview1 contains some products id & name.I want to insert the selected rows in to second Gridview and in the second Gridview I need to add two more colums AdvicedQty & AcceptedQty(TextBox Cotrol).The user will select few products in Gridview1 & clicks add which will be added another gridview then in second Gridview he enters the AdviceQty & AcceptedQty.After entering the data he cli...
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 ...
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...
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...
Performing sub select on XPathNavigator.Select() results
I have data that looks like this: <Stats> ... <Hits yyyy="2004" mm="02" dd="19" hh="00"> <FL>76</FL> <HP>24</HP> <EVNF>27</EVNF> <EVS>53</EVS> </Hits> <Hits yyyy="2004" mm="02" dd="19" hh="01"> <FL>75</FL> <HP>8</HP> <CSINF>21</CSINF> <CSIS>44</CSIS> <EVNF>7</EVNF> <EVS>21</EVS> ...
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, > > > >...
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...
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...
Selecting the multiple rows in GridView and inserting the selected rows to another Gridview & Performing some operations on it
Dear All, I have become mad for solving this problem. Please Help me. I have a Gridview bound to a datasource. I inserted the CheckBox Control in Templet to select certain rows. Gridview1 contains some products id & name.I want to insert the selected rows in to second Gridview and in the second Gridview I need to add two more colums AdvicedQty & AcceptedQty(TextBox Cotrol).The user will select few products in Gridview1 & clicks add which will be added another gridview then in second Gridview he enters the AdviceQty & AcceptedQty.After entering the data he cli...
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 ...
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...
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...
Performing sub select on XPathNavigator.Select() results
I have data that looks like this: <Stats> ... <Hits yyyy="2004" mm="02" dd="19" hh="00"> <FL>76</FL> <HP>24</HP> <EVNF>27</EVNF> <EVS>53</EVS> </Hits> <Hits yyyy="2004" mm="02" dd="19" hh="01"> <FL>75</FL> <HP>8</HP> <CSINF>21</CSINF> <CSIS>44</CSIS> <EVNF>7</EVNF> <EVS>21</EVS> ...
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, > > > >...
Web resources about - select into vs insert/select - sybase.ase.performance+tuning
Scottish Affairs Select Committee - Wikipedia, the free encyclopediaThe Scottish Affairs Select Committee is a select committee of the House of Commons in the Parliament of the United Kingdom . The remit of the ...
kotaku selectsKotaku Selects
Facebook Testing Featured Videos in Videos Sections of Select PagesFacebook confirmed to TechCrunch that it is testing a new design for the videos section of select pages – including ABC News and The New York ...
Apple Selects Best Apps, Games of 2015Apple 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 carsFiled 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®, ...
Cisco Selects Netformx Discovery to Power New Net Authenticate Assessment Service Business Wire (press release) SAN JOSE, Calif.(BUSINESS WIRE)Netformx®, ...
Cortana Exits Beta, Available for Select Android and Cyanogen OS Powered Devices
... , the digital assistant made to be the perfect companion for Windows users, has officially exited beta, made available to a number of select ...
... , the digital assistant made to be the perfect companion for Windows users, has officially exited beta, made available to a number of select ...
CyanogenMod gives select smartphones an Android 6 update optionLots of folks still don't have Android 6 "Marshmallow" on their shiny new phones, but owners of some older models can now it from CyanogenMod. ...
Resources last updated: 12/21/2015 5:19:04 AM