Results 1 to 3 of 3
  1. #1
    Join Date
    May 2009
    Posts
    18

    Unanswered: ODBC driver - running extra queries?

    I'm in the process of migrating from MSSQL2000Std to PostgreSQL8.3. Its going great. I've started running some performance comparisons (both via ODBC driver, through ASP.NET). In doing so, I found the following (returning a single row) took an average of .078 seconds on PG and .047 on MSSQL:

    Code:
    SELECT Suburbs.Suburb_ID, Suburbs.Name, Suburbs.Code, Suburbs.State_ID, States.Short_Name 
    FROM Suburbs INNER JOIN States ON Suburbs.State_ID = States.State_ID 
    WHERE lower(Suburbs.Name) like 'timbucktoo%' 
    AND Suburbs.State_ID IN (SELECT State_ID FROM jbAccount_States_Used WHERE jbAccount_ID = 51) 
    ORDER BY Suburbs.Name, (CASE WHEN Suburbs.State_ID = 7 THEN 0 ELSE 1 END);
    Part of the reason is that MSSQL seems to be connection pooling, and PG isn't, which I'm investigating. Indexes on "lower(Suburbs.Name)" are implemented and confirmed to be working.

    However during my investigation, I ran some packet sniffers, and discovered that ODBC seems to be surrounding every single query I run with the following SQL:

    Code:
    select oid, typbasetype from pg_type where typname = 'lo'
    
    <wait for response>
    
    <my own SQL query>
    
    <wait for response>
    
    select n.nspname, c.relname, a.attname, a.atttypid, t.typname, a.attnum, a.attlen, a.atttypmod, a.attnotnull, c.relhasrules, c.relkind, c.oid, d.adsrc 
    from (((pg_catalog.pg_class c inner join pg_catalog.pg_namespace n on n.oid = c.relnamespace and c.oid = 39248) 
    inner join pg_catalog.pg_attribute a on (not a.attisdropped) and a.attnum > 0 and a.attrelid = c.oid) 
    inner join pg_catalog.pg_type t on t.oid = a.atttypid) 
    left outer join pg_attrdef d on a.atthasdef and d.adrelid = a.attrelid and d.adnum = a.attnum order by n.nspname, c.relname, attnum
    
    <wait for response>
    
    select n.nspname, c.relname, a.attname, a.atttypid, t.typname, a.attnum, a.attlen, a.atttypmod, a.attnotnull, c.relhasrules, c.relkind, c.oid, d.adsrc 
    from (((pg_catalog.pg_class c inner join pg_catalog.pg_namespace n on n.oid = c.relnamespace and c.oid = 39213) 
    inner join pg_catalog.pg_attribute a on (not a.attisdropped) and a.attnum > 0 and a.attrelid = c.oid) 
    inner join pg_catalog.pg_type t on t.oid = a.atttypid) 
    left outer join pg_attrdef d on a.atthasdef and d.adrelid = a.attrelid and d.adnum = a.attnum order by n.nspname, c.relname, attnum
    
    <wait for response>
    It seems to run the second query once for each table references in my SQL. Eg. My SQL joined Suburbs and States tables, so it ran it for each table.

    Presumably it is downloading the table schema for each of the tables and fields relevant to my query, but why on earth should this require a second query to be run? I confirmed that MSSQL only requires a single request/response which presumably includes all the data and schema.

    Any ideas? Is this a peculiarity of the ODBC driver, or my configuration? I really can't think of any good reason for it, and I'm concerned about it's impact on critical queries, and especially complex queries (eg. running an SQL statement joining 10 tables will incur another 10 queries, which is absurd).

    Thanks in advance,
    -Brendan
    Last edited by brendan.hill; 06-03-09 at 06:42.

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Possibly, altering a connection option in the ODBC setup will have an effect here. pg options are usually set on the conservative side... In particular, take a look at the Parse Statements option, as it looks as if this may be what's causing the extra metadata retrieval. Also, with the logging option, you can have the ODBC driver log its internal actions (in addition to ODBC logging,) which may provide more insight.

    On a side note, OleDB is typically more efficient than ODBC. You may want to take a look at this. There is at least one commercial implementation of OleDB for pg, in addition to that included with the pg installation (which, unfortunately, seems to have several bugs associated with its use.) I also believe that Core Lab (crlab.com) has some native client drivers available for postgreSQL (dbExpress driver.)
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    May 2009
    Posts
    18
    Quote Originally Posted by loquin
    Possibly, altering a connection option in the ODBC setup will have an effect here. pg options are usually set on the conservative side... In particular, take a look at the Parse Statements option, as it looks as if this may be what's causing the extra metadata retrieval. Also, with the logging option, you can have the ODBC driver log its internal actions (in addition to ODBC logging,) which may provide more insight.

    On a side note, OleDB is typically more efficient than ODBC. You may want to take a look at this. There is at least one commercial implementation of OleDB for pg, in addition to that included with the pg installation (which, unfortunately, seems to have several bugs associated with its use.) I also believe that Core Lab (crlab.com) has some native client drivers available for postgreSQL (dbExpress driver.)
    Thanks for the pointers. I checked them out, although I need to explore further.

    I've sort of gotten around the problem for now by using connection pooling - it only runs these schema queries once per connection. (eg. subsequent queries calling the same tables don't get re-schema'ed).

    I was spooked off OleDb originally by suggestions it might not be completely reliable, however ODBC performance seems to meet our needs.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •