Re: using Win32::ODBC - what's fast?

From: Fred (noemail_at_#$&&!.net)
Date: 10/29/04 

Date: Fri, 29 Oct 2004 07:02:43 -0400

On Thu, 28 Oct 2004 22:30:14 -0400, Matt Garrish wrote:

> Are all your tables indexed on the select column to maximize for speed?

They are indexed on that column, but good point.

> Why are you reading all 70,000 unique ids into memory?

I thought it would be faster than doing it each time. Even with the index,
it just *seemed* exspensive, but I see what you mean about the connection.
( and the where clause!!)

>It would be faster
> just to execute a select statement each time you need to check if an sku
> already exists. I've never used the Win32::ODBC module, but if you were
> using the DBD driver you could just check whether the unit id exists
> like so: (untested)
>
> sub check_exists {
>
> my ($tid, $unitid) = @_;
>
> my $sel_sth = $dbh->prepare("SELECT sku from $tid WHERE sku = ?") or
> die
> $dbh->errstr();
>
> $sel_sth->execute($unitid) or die $dbh->errstr();
>
> if ($sel_sth->fetchrow_array) {
> $sel_sth->finish();
> return 1;
> }
>
> return 0;
>
> }
>
> Remember that most of the overhead involved in database access is in
> setting up the connection. Once you have that connection, querying the
> database should be very fast (assuming your data is well structured and
> indexed).
>
> Matt

I see now that including the where cluase leverages the power of the
index.. and individual selects start looking a lot better. Can't wait to
to test and thanks very much for your insight! Plus I'll try out the
driver you mention and test the diff. In the beginning when I was learning
perl, (circa 6 months ago) Win32::ODBC was the first thing I found tor
acessing MS DB's... so I learned it and never looked back. Bad practice in
'theory' but in real life when people want things done yesterday.... Thank
you again!

Fred

Relevant Pages

  • Re: Design / best practices question
    ... what do you think the overhead is to instantiate a SQL connection? ... What I am challenging here is whether the creation of the SQL connection is really a problem. ... Under load, the pool will retain all of the connection objects, so the overhead, per call, is in the millisecond range at max and more likely to be microseconds. ... On heavily used apps, ADO.NET is constantly pulling from the connection pool, so the instantiation time is reduced tremendously. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Design / best practices question
    ... values to the database. ... what do you think the overhead is to instantiate a SQL connection? ... even with sudden heart failure on your system. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: only 11Mb/s with bcm43xx on F5?[Scanned]
    ... But because there is the overhead of encryption and decryption, ... but I do not expect it to be as fast as the wired connection. ... overhead with the wireless connection if you are using WEP or WPA. ...
    (Fedora)
  • Re: Login, Connections and Forms
    ... I don't know your reasons for SQL Server authentication, ... A good approach to this would be to create a database access layer, ... Microsoft Security Application block via two methods - ... connection string, ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Import data from multiple acces db
    ... How can I change the filename for an Access Connection? ... The really problem is that the database access is ... > My idea is to insert database location on an sql server table and use this ... > I try to made a dts but i cannot succeed in. ...
    (microsoft.public.sqlserver.dts)