November 24, 2003 at 9:53 am
Couldn't think of a better subject title here and am hoping to find some good advice.
I have a third party application that has a particular feature that is running slow. The vendor has not been very helpful (if you only knew who it was!!). I have monitored within the database and the query in question takes 16/1000 of a second. Not a problem. Each time this query is executed data is transferred via ODBC to the application. I would like to test/verify the performance of the ODBC connection. I took the DSN name out and have eliminated the DNS lookup. I have a good idea the problem is with their application but getting them to look at it is like...well...Oh, how I love Network Associates...NOT!!
Does anyone have any idea how I could/should monitor the data transferring across the ODBC connection?
"Keep Your Stick On the Ice" ..Red Green
November 24, 2003 at 1:35 pm
Hmm.. that is a tricky one. Especially for a third-party app... I found the following link pretty informative though:
http://www.sql-server-performance.com/odbc_oledb.asp
It lists some things that were contrary to some of my assumptions.
I am very interested to hear what you end up doing.
-Dan B
November 24, 2003 at 1:39 pm
I'll keep you posted as to my progress. I had already turned logging on and didn't find anything conclusive 🙁 Thanks for the input. Ironically enough I was just on that sight looking for a DBCC command!! If you think of anything else...please share 🙂
"Keep Your Stick On the Ice" ..Red Green
November 25, 2003 at 7:46 am
Some options to view the data being transferred to the ODBC application are:
- If you are using a DSN, turn on ODBC tracing. The data captured will vary slightly depending upon the level of MDAC installed. To turn ODBC tracing on:
* Start -> Control Panel -> Administrative Tools -> Data Sources (ODBC)
* Click the "Tracing" tab, select a location for the trace file, and click "Start Tracing Now"
Two snippets of the SQL.LOG file are below. They show the SQL statement and one of the return columns.
ODBCTE32 eac-fc4EXIT SQLExecDirect with return code 0 (SQL_SUCCESS)
HSTMT 00942660
UCHAR * 0x00158B18 [ -3] "select * from employee\ 0"
SDWORD -3
ODBCTE32 eac-fc4EXIT SQLFetch with return code 0 (SQL_SUCCESS)
HSTMT 00942660
ODBCTE32 eac-fc4ENTER SQLGetData
HSTMT 00942660
UWORD 1
SWORD 1 <SQL_C_CHAR>
PTR 0x00158B18
SQLLEN 100
SQLLEN * 0x0012FC30
ODBCTE32 eac-fc4EXIT SQLGetData with return code 0 (SQL_SUCCESS)
HSTMT 00942660
UWORD 1
SWORD 1 <SQL_C_CHAR>
PTR 0x00158B18 [ 7] "9011281"
SQLLEN 100
SQLLEN * 0x0012FC30 (7)
- If you are not using a DSN, you can use a network monitor. There are network monitor included in the Windows Platform SDK. Third party network monitors are available on the internet (e.g. http://www.ethereal.com/download.html). A disadvantage of using a network monitor, is you will be seeing the basic TDS. The network monitors in the Windows Platform SDKs are TDS aware.
Mike B.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply