RE: ADO and bind variables (was RE: Performance improvement required :-))
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Pardee, Roy E Sent: Friday, June 13, 2003 20:10 To: Multiple recipients of list ORACLE-L Subject: ADO and bind variables (was RE: Performance improvement required :-)) This is interesting--if I use ADO with the ODBC provider (as the code does below), I get the same results. But if I use just ADO (that is, ms' OLE DB provider for oracle (MSDAORA.1)) then I don't get bind vars. (I'm doing INSERTs in my code, not SELECTs). I wonder if oracle's native OLE DB provider works any differently--I would bet that it does... Cheers, -Roy Roy, I tried with the Oracle provider (OraOLEDB.Oracle.1), and the bind variables work. Another reason to stick with the Oracle provider :-) Ciao Fuzzy :-) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Grant Allen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Performance improvement required :-)
Why can't you use bind variables? I thought using .Parameters method (property?) of ADODB.Command would use bind variables. What function, and where can't you use it? -Original Message- Sent: Friday, June 13, 2003 9:59 AM To: Multiple recipients of list ORACLE-L It's Friday, and I'm having a brain storm. Just to check: The developers are using ADO to connect to a VB application and want to pull back a record set. Using ADO means they can't use bind variables. Returning more than 1 record means they can't use a function (which WOULD use bind variables). Am I correct in saying that other than tuning the SQL (done that, it isn't a complex query) or playing around with Oracle session parameters, there isn't anything else I can do? (The query takes 1 second the first time it is run, but 10 ms after. It is used all the time in the callcentre to search postcodes.) TIA Craig Healey ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. Statements and opinions expressed in this e-mail may not represent those of the company. If you have received this email in error please notify [EMAIL PROTECTED] This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses (www.mimesweeper.com) *** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Craig Healey INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Seefelt, Beth INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Performance improvement required :-)
Title: RE: Performance improvement required :-) Craig, any query when run first time will take more time, because it has to do _all_ the work, i.e. do physical reads. Subsequent executions usually benefit from finding the required data blocks in buffer cache, thus minimizing physical reads and hence may be faster. Nothing wrong with that. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: Craig Healey [mailto:[EMAIL PROTECTED]] Sent: Friday, June 13, 2003 9:59 AM To: Multiple recipients of list ORACLE-L Subject: Performance improvement required :-) It's Friday, and I'm having a brain storm. Just to check: The developers are using ADO to connect to a VB application and want to pull back a record set. Using ADO means they can't use bind variables. Returning more than 1 record means they can't use a function (which WOULD use bind variables). Am I correct in saying that other than tuning the SQL (done that, it isn't a complex query) or playing around with Oracle session parameters, there isn't anything else I can do? (The query takes 1 second the first time it is run, but 10 ms after. It is used all the time in the callcentre to search postcodes.) TIA Craig Healey This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
Re: Performance improvement required :-)
Just guessing, but it seems as if the longer initial run is because the data is being pulled into the buffers. After that the query is being answered without disk i/o. See if you can cache the table and if that gets rid of the initial long run. Craig Healey c.healeyTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] @hhsuk.com cc: Sent by: Subject: Performance improvement required :-) ml-errors 06/13/2003 09:59 AM Please respond to ORACLE-L It's Friday, and I'm having a brain storm. Just to check: The developers are using ADO to connect to a VB application and want to pull back a record set. Using ADO means they can't use bind variables. Returning more than 1 record means they can't use a function (which WOULD use bind variables). Am I correct in saying that other than tuning the SQL (done that, it isn't a complex query) or playing around with Oracle session parameters, there isn't anything else I can do? (The query takes 1 second the first time it is run, but 10 ms after. It is used all the time in the callcentre to search postcodes.) TIA Craig Healey ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. Statements and opinions expressed in this e-mail may not represent those of the company. If you have received this email in error please notify [EMAIL PROTECTED] This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses (www.mimesweeper.com) *** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Craig Healey INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thomas Day INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Performance improvement required :-)
Why can't you use bind variables? I thought using .Parameters method (property?) of ADODB.Command would use bind variables. I thought it didn't. Any way of checking (other than to get the developers to try it? What function, and where can't you use it? Stored Procedure type function (i.e. user-written) called from VB. 'Cos it's a Stored Procedure it will use bind variables, but you can't return a result set to VB. Craig ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. Statements and opinions expressed in this e-mail may not represent those of the company. If you have received this email in error please notify [EMAIL PROTECTED] This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses (www.mimesweeper.com) *** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Craig Healey INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Performance improvement required :-)
Using REFCURSOR you can return result set from stored procedure. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Craig Healey Sent: 13. júna 2003 12:20 To: Multiple recipients of list ORACLE-L Why can't you use bind variables? I thought using .Parameters method (property?) of ADODB.Command would use bind variables. I thought it didn't. Any way of checking (other than to get the developers to try it? What function, and where can't you use it? Stored Procedure type function (i.e. user-written) called from VB. 'Cos it's a Stored Procedure it will use bind variables, but you can't return a result set to VB. Craig ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. Statements and opinions expressed in this e-mail may not represent those of the company. If you have received this email in error please notify [EMAIL PROTECTED] This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses (www.mimesweeper.com) *** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Craig Healey INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Performance improvement required :-)
I'm sure you can. You should see it in an ODBC trace log, or you can use trace events on the database. Here's a really simplistic test I did to verify it. I ran this VB code that executes a really dumb query that could not have come from anywhere else - SELECT DUMMY FROM DUAL WHERE DUMMY = 'X' but passed 'X' as a bind variable Private Sub Form_Load() Dim conn1 As New ADODB.Connection Dim cmd1 As New ADODB.Command Dim rs1 As New ADODB.Recordset Dim STRSQLSTRING As String Dim param1 As New Parameter strConnect = UID=produser;PWD=prodpass;DSN=WAREHOUSE; STRSQLSTRING = SELECT DUMMY FROM DUAL WHERE DUMMY = ? With conn1 .ConnectionTimeout = 0 .CommandTimeout = 0 .CursorLocation = adUseClient .Mode = adModeRead .Open strConnect End With If Err.Number Then MsgBox Err.Number Exit Sub End If With cmd1 .ActiveConnection = conn1 .CommandText = STRSQLSTRING .CommandType = adCmdText Set param1 = .CreateParameter(DummyValue, adChar, adParamInput, 1, X) param1.Value = X .Parameters.Append param1 Set rs1 = .Execute End With MsgBox rs1.Fields(DUMMY) End Sub Afterward, executed this on the database - SQL select sql_text from v$sqlarea where sql_text like 'SELECT DUMMY %'; SQL_TEXT SELECT DUMMY FROM DUAL WHERE DUMMY = :1 It shows the parameter was definitely passed as a bind variable. Check out this document on Metalink - Retrieving Record Set from Stored Proc Using ADO and VB (SCR 782) It appears to have an example of passing a cursor back to a recordset, though I've never tried it. HTH. Beth -Original Message- Sent: Friday, June 13, 2003 1:20 PM To: Multiple recipients of list ORACLE-L Why can't you use bind variables? I thought using .Parameters method (property?) of ADODB.Command would use bind variables. I thought it didn't. Any way of checking (other than to get the developers to try it? What function, and where can't you use it? Stored Procedure type function (i.e. user-written) called from VB. 'Cos it's a Stored Procedure it will use bind variables, but you can't return a result set to VB. Craig -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Seefelt, Beth INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
ADO and bind variables (was RE: Performance improvement required :-))
This is interesting--if I use ADO with the ODBC provider (as the code does below), I get the same results. But if I use just ADO (that is, ms' OLE DB provider for oracle (MSDAORA.1)) then I don't get bind vars. (I'm doing INSERTs in my code, not SELECTs). I wonder if oracle's native OLE DB provider works any differently--I would bet that it does... Cheers, -Roy Roy Pardee Programmer/Analyst/DBA SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Friday, June 13, 2003 11:05 AM To: Multiple recipients of list ORACLE-L I'm sure you can. You should see it in an ODBC trace log, or you can use trace events on the database. Here's a really simplistic test I did to verify it. I ran this VB code that executes a really dumb query that could not have come from anywhere else - SELECT DUMMY FROM DUAL WHERE DUMMY = 'X' but passed 'X' as a bind variable Private Sub Form_Load() Dim conn1 As New ADODB.Connection Dim cmd1 As New ADODB.Command Dim rs1 As New ADODB.Recordset Dim STRSQLSTRING As String Dim param1 As New Parameter strConnect = UID=produser;PWD=prodpass;DSN=WAREHOUSE; STRSQLSTRING = SELECT DUMMY FROM DUAL WHERE DUMMY = ? With conn1 .ConnectionTimeout = 0 .CommandTimeout = 0 .CursorLocation = adUseClient .Mode = adModeRead .Open strConnect End With If Err.Number Then MsgBox Err.Number Exit Sub End If With cmd1 .ActiveConnection = conn1 .CommandText = STRSQLSTRING .CommandType = adCmdText Set param1 = .CreateParameter(DummyValue, adChar, adParamInput, 1, X) param1.Value = X .Parameters.Append param1 Set rs1 = .Execute End With MsgBox rs1.Fields(DUMMY) End Sub Afterward, executed this on the database - SQL select sql_text from v$sqlarea where sql_text like 'SELECT DUMMY %'; SQL_TEXT SELECT DUMMY FROM DUAL WHERE DUMMY = :1 It shows the parameter was definitely passed as a bind variable. Check out this document on Metalink - Retrieving Record Set from Stored Proc Using ADO and VB (SCR 782) It appears to have an example of passing a cursor back to a recordset, though I've never tried it. HTH. Beth -Original Message- Sent: Friday, June 13, 2003 1:20 PM To: Multiple recipients of list ORACLE-L Why can't you use bind variables? I thought using .Parameters method (property?) of ADODB.Command would use bind variables. I thought it didn't. Any way of checking (other than to get the developers to try it? What function, and where can't you use it? Stored Procedure type function (i.e. user-written) called from VB. 'Cos it's a Stored Procedure it will use bind variables, but you can't return a result set to VB. Craig -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Seefelt, Beth INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pardee, Roy E INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
