Re: ODBC SQLColums is very slow
From: Gerard H. Pille <ghp_at_skynet.be>
Date: Tue, 20 Jan 2009 19:15:50 +0100
Message-ID: <497614d6$0$2847$ba620e4c_at_news.skynet.be>
Charles Hooper schreef:
> Also, you might consider switching from an ODBC connection string to
> an OLEDB connection string. While tracing a couple applications here,
> we found a couple SQL statements in trace files which were not
> submitted by the application code, yet were taking considerable
> execution time (mostly on the CPU), for instance:
> PARSING IN CURSOR #7 len=289 dep=0 uid=31 oct=3 lid=31 tim=4568301442
> hv=1545094011 ad='53a90f48'
> SELECT '', b.owner, b.table_name, b.column_name, b.position,
> b.constraint_name FROM ALL_CONSTRAINTS a, ALL_CONS_COLUMNS b WHERE
> (a.constraint_name = b.constraint_name AND a.constraint_type = 'P'
> AND b.table_name='MY_TABLE' AND b.owner='MY_OWNER' ) ORDER BY b.owner,
> b.table_name, b.position
> END OF STMT
> PARSE
> #7:c=203125,e=202117,p=0,cr=8,cu=0,mis=1,r=0,dep=0,og=1,tim=4568301435
> BINDS #7:
> EXEC #7:c=0,e=302,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=4568302326
> WAIT #7: nam='SQL*Net message to client' ela= 5 driver id=1413697536
> #bytes=1 p3=0 obj#=13451 tim=4568302483
> FETCH
> #7:c=78125,e=81673,p=0,cr=9733,cu=0,mis=0,r=1,dep=0,og=1,tim=4568384281
>
> In several cases, switching the connection string from ODBC to OLEDB
> dropped the login time for those applications from 2+ seconds to
> nearly instantaneous, as SQL statements like the above were not
> submitted to the database with the OLEDB connection.
>
> Charles Hooper
Date: Tue, 20 Jan 2009 19:15:50 +0100
Message-ID: <497614d6$0$2847$ba620e4c_at_news.skynet.be>
Charles Hooper schreef:
> Also, you might consider switching from an ODBC connection string to
> an OLEDB connection string. While tracing a couple applications here,
> we found a couple SQL statements in trace files which were not
> submitted by the application code, yet were taking considerable
> execution time (mostly on the CPU), for instance:
> PARSING IN CURSOR #7 len=289 dep=0 uid=31 oct=3 lid=31 tim=4568301442
> hv=1545094011 ad='53a90f48'
> SELECT '', b.owner, b.table_name, b.column_name, b.position,
> b.constraint_name FROM ALL_CONSTRAINTS a, ALL_CONS_COLUMNS b WHERE
> (a.constraint_name = b.constraint_name AND a.constraint_type = 'P'
> AND b.table_name='MY_TABLE' AND b.owner='MY_OWNER' ) ORDER BY b.owner,
> b.table_name, b.position
> END OF STMT
> PARSE
> #7:c=203125,e=202117,p=0,cr=8,cu=0,mis=1,r=0,dep=0,og=1,tim=4568301435
> BINDS #7:
> EXEC #7:c=0,e=302,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=4568302326
> WAIT #7: nam='SQL*Net message to client' ela= 5 driver id=1413697536
> #bytes=1 p3=0 obj#=13451 tim=4568302483
> FETCH
> #7:c=78125,e=81673,p=0,cr=9733,cu=0,mis=0,r=1,dep=0,og=1,tim=4568384281
>
> In several cases, switching the connection string from ODBC to OLEDB
> dropped the login time for those applications from 2+ seconds to
> nearly instantaneous, as SQL statements like the above were not
> submitted to the database with the OLEDB connection.
>
> Charles Hooper
Hallo Charles,
we're plagued by third party applications sending those kind of queries time and again, while the authors deny any involvement. My conclusion was also that ODBC had to be the culprit. Every two seconds they query what indexes are defined on the same table.
So, where can we switch from ODBC to OLEDB? Or do we depend on the third party for that change?
Thanks,
Gerard Received on Tue Jan 20 2009 - 12:15:50 CST