Re: [Iodbc-macosx] Performance questions

[Tim H. <th...@op...>]

On 04/23/2012 04:43 PM, Maury Markowitz wrote:
> I am a data integration guy and silo-breaker. In the past I used SQL Server/ODBC, MS Access, a fast LAN and running on a Windows box. Now I am attempting to learn all of this over again using Firebird/iODBC, Excel, a SSL/VPN internet connection, and a Mac.
>
> Apparently, I'm a glutton for punishment.
>
>
> I am running simple queries against a relatively small database - the db is perhaps 400 MB, and the queries return a few hundred rows. On the PC/SQLS/LAN this would be instantaneous, but the Mac/FB/WAN, performance is fairly slow. I'm trying to understand the reasons for this.
>
> I have tried the same basic query using two front-ends, iODBC Demo using OpenLink's driver, and RazorSQL using JDBC and Firebird's native driver. In both cases the performance is about equal, which *may* suggest that the local machine and/or drivers are not an issue. Likewise, I have tried this both from work and home, on two very fast machines and connections, and again the performance seems about the same.
>
> I have noticed something that I believe is the root cause, but I don't know what it is.
>
> I'll pick the most obvious case: "select * from part". The table has about 850 rows and about 20 columns. The last time I ran it (just now) it took about 75 seconds end-to-end. During this time the outbound channel was slowly ticking up at about 1 kbps. Then *poof*, back comes the table. Razor's diagnostics reports that the query took 1.5 seconds!
>
> Now it's possible this is an indexing or similar DB problem. However, the same basic problem effects all queries to one degree or another. The total end-to-end time is seconds, the query time is about 1 second, and the difference is this period where the local machine dribbles out data to the server.
>
> So, it *appears* to suggest there is some sort of handshaking going on before the query hits the server. What this is, and why this would be different times for different queries, is a mystery to me.
>
> Is anyone familiar with this?

There's a lot of things that could potentially happen in 75s. One of the 
more obvious things is: if you're using a hostname to access the remote 
end, does DNS respond rapidly? What if you use its IP# instead?

If it's quick enough with the db running locally, then it's unlikely to 
be the db at fault - optimising indexes, while certainly useful, may be 
premature with negligible gain in the face of those 75 seconds.

850 rows of 20 columns is not huge, unless you've got blobs in there.

As a rule, RDMBSs run quickest when you let the engine do most of the 
computation and just pull back the rows required. For example, if you've 
got a query with two tables joined by a field, from which you only want 
some of the results, the worst thing an application could do is select * 
from each table, compute the join and filter itself - pulling all the 
data across the network with no scope for optimisation. Similarly, doing 
a complete prepare+execute+txn commit cycle for every row in a large 
insert would be hugely inefficient - using prepared statements with 
parameterized queries, batched into large transactions, wins hands down.

There's a couple of ways of profiling this further.
1) tcpdump and wireshark - get everything between client, nameservers 
and RDBMS host

2) ODBC tracing: see http://www.iodbc.org/dataspace/iodbc/wiki/iODBC/FAQ
or just add
   [ODBC]
   Trace = 1
   TraceFile = /tmp/odbctrace.log
   Debug = 1
   DebugFile = /tmp/odbcdebug.log

to your odbc.ini / $ODBCINI file to show how the application's behaving.

HTH,


~Tim
-- 
Tim Haynes
Product Development Consultant
OpenLink Software
<http://www.openlinksw.com/>
<http://twitter.com/openlink>