[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.dotnet.framework.odbcnet

ODBC REAL SLOW!!!!

Joeblack

5/31/2002 1:48:00 AM

I have a VB .NET application using ODBC (Microsoft.data.odbc)
It takes 72 seconds to get the result... (only 14 rows) so something is
very very wrong here...

Dim myConnString2 As String = "Provider=MSDASQL;Persist
Security Info=False;DSN=myDSN;UID=####;PWD=####"

Try

informix = New OdbcConnection(myConnString2)

and it opens fine...and here's the select... and THIS is what takes 72
seconds...
connection is over TCP using INFORMIX 3.3 32 BIT

Dim cmd As New OdbcCommand()
Dim da As New OdbcDataAdapter()
Dim ds As New DataSet()
Dim drw As DataRow

Dim F1 As String, f2 As String, f3 As String

Try
cmd = informix.CreateCommand
cmd.CommandText = "select * from groupdetail"
da.SelectCommand = cmd
da.Fill(ds, "GroupX")
Dim tblGroups As DataTable
tblGroups = ds.Tables("GroupX")
For Each drw In tblGroups.Rows
F1 = Trim(drw(0))
f2 = Trim(drw(1))
f3 = Trim(drw(2))
MsgBox(F1)
MsgBox(f2)
MsgBox(f3)
Next
Catch ex As Exception
'my eror code
End Try
da.Dispose()

Any suggestions, i have a SQL2000 connection trunning just fine in the same
app...


slewis@caerus.net
3 Answers

(Hussein Abuthuraya(MSFT))

5/31/2002 2:20:00 AM

0

Joeblack

5/31/2002 3:56:00 AM

0

(a) The connection procedure takes 2 seconds.

(b) There is only 14 records in the table.

(c) Same thing happens when not using stored procedure, just select * from
table_name

(d) Using Server Studio 3.5 for Informix - (like iSQL fror SQL2k) takes 4
seconds to process...

(5) Delay is consistent, if I execute the select begind a command button, 50
seconds everytime.

(6) I when i watch for communication (looking at blinking LED on Task Bar
below) its blank for 30 or so seconds, then its like the select gets sent,
and data comes back.

I'm going to try running a trace on ODBC...

shawn
slewis@caerus.net

"Hussein Abuthuraya(MSFT)" <HussAbOnline@microsoft.com> wrote in message
news:T#EWxEECCHA.2144@cpmsftngxa08...
> Hi,
>
> Well, you are comparing SQL Server to Informix on what bases. Do they
have the same amount of data, Indices, etc? Are you using the same .NET
Data provider?
>
> Does it happen only on first filling only? What about subsequent fillings
for the same query?
>
> You need to break your code into two parts to see where it takes longer
than usual. If you measure the time to open the connection only and then
measure the time to
> execute the query then you will get an indication to what causes the
delay. You may have several million records that your query need to pick
the 14 records from. It maybe it
> takes longer to open the connection and then it runs the query fast.
>
> I'm sure you are aware of this but using SqlClient to go to SQL Server is
the fastest way because it uses TDS protocole that talks to SQL Server
directly. Using the ODBC
> NET data provider must be slower because it has to go through the ODBC
OLEDB provider first then use the ODBC driver, then ODBC manager. It has to
use several layers
> before it reaches the database and the same happen on the way back, so it
is expected to be slower but how much depends on several factors.
>
>
> Thanks,
> Hussein Abuthuraya
> Microsoft Developer Support
>
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> Are you secure? For information about the Microsoft Strategic Technology
Protection Program and to order your FREE Security Tool Kit, please visit
> http://www.microsoft.co....
>

Joeblack

5/31/2002 4:13:00 AM

0

Well,

I feel like an idiot... The ODBC Trace was already running....

And the log file was at 3Gb... Stopped it... and speed is back...

:(

slewis@caerus.net

"Hussein Abuthuraya(MSFT)" <HussAbOnline@microsoft.com> wrote in message
news:T#EWxEECCHA.2144@cpmsftngxa08...
> Hi,
>
> Well, you are comparing SQL Server to Informix on what bases. Do they
have the same amount of data, Indices, etc? Are you using the same .NET
Data provider?
>
> Does it happen only on first filling only? What about subsequent fillings
for the same query?
>
> You need to break your code into two parts to see where it takes longer
than usual. If you measure the time to open the connection only and then
measure the time to
> execute the query then you will get an indication to what causes the
delay. You may have several million records that your query need to pick
the 14 records from. It maybe it
> takes longer to open the connection and then it runs the query fast.
>
> I'm sure you are aware of this but using SqlClient to go to SQL Server is
the fastest way because it uses TDS protocole that talks to SQL Server
directly. Using the ODBC
> NET data provider must be slower because it has to go through the ODBC
OLEDB provider first then use the ODBC driver, then ODBC manager. It has to
use several layers
> before it reaches the database and the same happen on the way back, so it
is expected to be slower but how much depends on several factors.
>
>
> Thanks,
> Hussein Abuthuraya
> Microsoft Developer Support
>
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> Are you secure? For information about the Microsoft Strategic Technology
Protection Program and to order your FREE Security Tool Kit, please visit
> http://www.microsoft.co....
>