ODBC Query Performance Discrepancy

Question

ODBC Query Performance Discrepancy

Nic Thomas 20

Hello,

We have an application which uses ODBC to access an SQL Server database. There is one query which takes more than two minutes in our application. In SQL Server Management Studio and in SQLEditor it takes about two seconds. SQLEditor is a program written by me which like our application uses ODBC to access the database (I assume SQL Server Management Studio uses a different interface to access an SQL Server database).

I created a sample program in C++(odbctest.txt) which uses the ODBC calls used by the application to reproduce the problem. The query which takes so much time is the one starting at line 132 in sample program. Do you see any ODBC calls which may cause the difference in time?

Here is the information about the tables which are accessed in the query:



•	CUSTOMERWOLEADS
120928 rows
columns:
– unique constraint on ROWID (uniqueidentifier) not null unique,
– PERSONNO varchar(20) not null,
– U_PERSONNO varchar(20) default ' ' not null,
constraints:
– constraint PK_CUSTOMERWOLEADS primary key(PERSONNO),
– constraint UQ__CUSTOMER__97BD02EA2DA6F6BC unique(ROWID)
indexes:
– nonclustered index IDX$CUSTOMER$FULLTEXT on CUSTOMERWOLEADS(PERSONNO)
– nonclustered index IDX$CUSTOMERWOLEADS$U_PERSONNO on CUSTOMERWOLEADS(U_PERSONNO)
•	SD$ADDRESS$CONTACT$PHONE
1,664,133 rows
columns:
– ROWID uniqueidentifier rowguidcol default newid() not null,
– SD$ADDRESS$CONTACT$PHONE int identity not null,
– PERSONID varchar(1),
– PERSONNO varchar(20),
– ADDRESSROWID uniqueidentifier not null,
– ADDRESSNO varchar(20) not null,
– ADDRESSORDER int,
– SALUTATION varchar(80),
– NAME1 varchar(100),
– NAME2 varchar(80),
– NAME3 varchar(80),
– COUNTRY varchar(3),
– POSTCODE varchar(20),
– CITY varchar(40),
– STREETPOBOX varchar(80),
– CONTACTROWID uniqueidentifier,
– CONTACTNO varchar(20),
– CONTACTORDER int,
– LASTNAME varchar(80),
– FIRSTNAME varchar(80),
– MIDDLENAMES varchar(80),
– FULLNAME varchar(255),
– SALUTATIONTITLE varchar(2000),
– DEPARTMENT varchar(80),
– EMAILROWID uniqueidentifier,
– EMAILID int,
– EMAILADDRESS varchar(80),
– PHONEROWID uniqueidentifier,
– PHONENUMBERID int,
– PHONENUMBERFULL varchar(40),
– PHONENORMALIZED varchar(255),
– MOBILEROWID uniqueidentifier,
– MOBILENUMBERID int,
– MOBILENUMBERFULL varchar(40),
– MOBILENORMALIZED varchar(255),
– FAXROWID uniqueidentifier,
– FAXNUMBERID int,
– FAXNUMBERFULL varchar(40),
– FAXNORMALIZED varchar(255),
constraints:
– PK_SD$ADDRESS$CONTACT$PHONE primary key(SD$ADDRESS$CONTACT$PHONE)
– UQ__SD$ADDRE__97BD02EA03EDA9C2 unique(ROWID)
indexes:
– nonclustered index IDX_SD$ADDRESS$CONTACT$PHONE$ADDRESSNO on SD$ADDRESS$CONTACT$PHONE(PERSONID,PERSONNO,ADDRESSNO)
– nonclustered index IDX_SD$ADDRESS$CONTACT$PHONE$CONTACTNO on SD$ADDRESS$CONTACT$PHONE(PERSONID,PERSONNO,CONTACTNO)
•	CUSTOMER
columns:
– ROWID uniqueidentifier rowguidcol default newid() not null,
– PERSONNO varchar(20) not null,
– CREATEDON varchar(8),
– CREATEDBY varchar(4),
– DATUMLETZTEAB varchar(8),
– DATUMLETZTEAN varchar(8),
– DATUMLETZTERE varchar(8),
– DIVERSER int,
– PRINTTYPEITEMNOS int,
– EUIDENTNO varchar(20),
– ACCOUNTDEBTOR varchar(20),
– KZNUTZUNGKTODIVERSE decimal(38,10),
– KZEINTRAGTAGEBUCH decimal(38,10),
– KZEINTRAGVKLISTE decimal(38,10),
– CALCONLYCOMPLETEDELIVERY int,
– IDCONSOLIDATEDINVOICE int,
– KZZAHLUNGSBEDINGUNGEN decimal(38,10),
– KORROSPONDENZBUCH decimal(38,10),
– CREDITLIMIT decimal(38,10),
– MWSTKZ varchar(4),
– REMINDERCODE int,
– MINIMUMQUANTITYSURCHARGE int,
– NOTE varchar(4000),
– NRLETZTEAB varchar(20),
– NRLETZTEAN varchar(20),
– NRLETZTERE varchar(20),
– PRICESINCLVAT int,
– PREISGRUPPENNR varchar(4),
– DISCOUNTPRINT decimal(38,10),
– SPECIALINVOICEITEMS int,
– LANGUAGECODE varchar(4),
– PARTIALSHIPMENTALLOWED int,
– TRANSPORTINSURANCE int,
– SALESREVENUE decimal(38,10),
– VERPACKUNGSKOSTEN decimal(38,10),
– VERSANDARTKZ varchar(8),
– WERTLETZTEAB decimal(38,10),
– WERTLETZTEAN decimal(38,10),
– WERTLETZTERE decimal(38,10),
– PAYMENTBEHAVIOUR varchar(20),
– DELIVERYBLOCKFLAG int,
– CURRENCYCODE varchar(4),
– MWSTNR varchar(20),
– PERSONVATKEY varchar(20),
– GOODVATKEY varchar(20),
– TOPNO varchar(20),
– INTERNALNUMBER varchar(20),
– COSTUNIT varchar(8),
– VERSANDZONE varchar(20),
– FIELD02 varchar(80),
– FIELD03 varchar(80),
– FIELD04 varchar(80),
– FIELD05 varchar(80),
– FIELD06 varchar(80),
– FIELD07 varchar(80),
– FIELD08 varchar(80),
– FIELD09 varchar(80),
– FIELD10 varchar(80),
– FIELD01 varchar(80),
– FORMDIRECTORY varchar(260),
– FIELD12 varchar(80),
– FIELD13 varchar(80),
– FIELD14 varchar(80),
– FIELD15 varchar(80),
– FIELD16 varchar(80),
– FIELD17 varchar(80),
– FIELD18 varchar(80),
– FIELD19 varchar(80),
– FIELD20 varchar(80),
– FIELD11 varchar(80),
– NUMINVOICES int default 1 not null,
– PRICESINCLVATCASHSALE int,
– U_ACCOUNTDEBTOR varchar(20) default ' ' not null,
– U_PERSONNO varchar(20) default ' ' not null,
– CHECKBOX1 varchar(1) default '0' not null,
– CHECKBOX2 varchar(1) default '0' not null,
– CHECKBOX3 varchar(1) default '0' not null,
– MODIFICATIONTIME datetime2,
– PRIVATE int,
– U_FIELD08 varchar(80) default ' ' not null,
– U_FIELD01 varchar(80) default ' ' not null,
– DB_PERSONNO varchar(40),
– FIELD16$OLD varchar(80),
– FIELD21 varchar(80),
– FIELD22 varchar(80),
– FIELD23 varchar(80),
– FIELD24 varchar(80),
– FIELD25 varchar(80),
– FIELD26 varchar(80),
– FIELD27 varchar(80),
– FIELD28 varchar(80),
– FIELD29 varchar(80),
– FIELD30 varchar(80),
– FIELD31 varchar(80),
– FIELD32 varchar(80),
– FIELD33 varchar(80),
– FIELD34 varchar(80),
– FIELD35 varchar(80),
– FIELD36 varchar(80),
– FIELD37 varchar(80),
– FIELD38 varchar(80),
– FIELD39 varchar(80),
– FIELD40 varchar(80),
– FIELD41 varchar(80),
– FIELD42 varchar(80),
– FIELD43 varchar(80),
– FIELD44 varchar(80),
– FIELD45 varchar(80),
– FIELD46 varchar(80),
– FIELD47 varchar(80),
– FIELD48 varchar(80),
– FIELD49 varchar(80),
– FIELD50 varchar(80),
– U_INTERNALNUMBER varchar(20) default ' ' not null,
– U_FIELD18 varchar(40) default ' ' not null,
– U_FIELD24 varchar(40) default ' ' not null,
– PROCESSINGLOCK varchar(1),
– ACCOUNTDEBTOR$OLD varchar(20),
– ACCOUNTDEBTOR$TMP varchar(20),
– ACCOUNTID varchar(36),
– U_NOTE varchar(40) default ' ' not null, 
constraints:
– PK_CUSTOMER primary key(PERSONNO)
– UQ__CUSTOMER__97BD02EA171EFDCC unique(ROWID)
indexes:
– nonclustered index CUSTOMER$ACCOUNTDEBTOR on CUSTOMER(ACCOUNTDEBTOR)
– nonclustered index CUSTOMER$ANGELEGTAM on CUSTOMER(CREATEDON)
– nonclustered index CUSTOMER$FIELD04 on CUSTOMER(FIELD04)
– nonclustered index CUSTOMER$FIELD05 on CUSTOMER(FIELD05)
– nonclustered index CUSTOMER$FIELD06 on CUSTOMER(FIELD06)
– nonclustered index CUSTOMER$FIELD09 on CUSTOMER(FIELD09)
– nonclustered index CUSTOMER$FIELD20 on CUSTOMER(FIELD20)
– nonclustered index CUSTOMER$FIELD24 on CUSTOMER(FIELD24)
– nonclustered index CUSTOMER$INTERNALNUMBER on CUSTOMER(INTERNALNUMBER)
– nonclustered index CUSTOMER$U_ACCOUNTDEBTOR on CUSTOMER(U_ACCOUNTDEBTOR)
– nonclustered index CUSTOMER$U_FIELD01 on CUSTOMER(U_FIELD01)
– nonclustered index CUSTOMER$U_FIELD08 on CUSTOMER(U_FIELD08)
– nonclustered index CUSTOMER$U_FIELD18 on CUSTOMER(U_FIELD18)
– nonclustered index CUSTOMER$U_FIELD24 on CUSTOMER(U_FIELD24)
– nonclustered index CUSTOMER$U_INTERNALNUMBER on CUSTOMER(U_INTERNALNUMBER)
– nonclustered index CUSTOMER$U_NOTE on CUSTOMER(U_NOTE)
– nonclustered index CUSTOMER$U_PERSONNO on CUSTOMER(U_PERSONNO)
– nonclustered index IDX$CUSTOMER$DB_PERSONNO on CUSTOMER(DB_PERSONNO)
•	SD$LICENSEINFO
columns:
– ROWID uniqueidentifier rowguidcol default newid() not null,
– SD$LICENSEINFO int not null,
– PERSONNO varchar(20) not null,
– VIP varchar(1) not null,
– MAINTENANCECONTRACT int not null,
– NOCHARGE varchar(1) not null,
– NOCHARGECHECKCOMMENT varchar(4000),
– TOTALPRICE decimal(38,10) not null,
– MAINTENANCEPERCENTAGEAUTO decimal(38,10) not null,
– MAINTENANCEPERCENTAGEMANUAL decimal(38,10),
– MAINTENANCECONTRACTUNTIL varchar(8),
– MAINTENANCECHARGEDUNTIL varchar(8),
– MAINTENANCEFEE decimal(38,10) not null,
– MAINTENANCECONTRACT$OLD varchar(1),
– MAINTENANCECONTRACTFROM varchar(8),
– MAINTENANCECONTRACTDURATION int,
– SUPCONTACTFIRST varchar(20),
– SUPCONTACTSECOND varchar(20),
– SURCHARGE decimal(38,10),
– NEXTINVOICE varchar(8),
– MAINTENANCECONTRACTSTART varchar(8),
– U_PERSONNO varchar(20) default ' ' not null,
– PRICEADJUSTMENTFROM varchar(8),
– PRICEUPDATE varchar(8),
constraints:
– constraint CK__SD$LICENS__NOCHA__5655817C check([NOCHARGE]='1' OR [NOCHARGE]='0'),
– constraint CK__SD$LICENSEI__VIP__5749A5B5 check([VIP]='1' OR [VIP]='0'),
– constraint CK_SD$LICENSEINFO_MAINTCONTR check([MAINTENANCECONTRACT]>=(0) AND [MAINTENANCECONTRACT]<=(4)),
– constraint PK_SD$LICENSEINFO primary key(SD$LICENSEINFO),
– constraint UQ__SD$LICEN__97BD02EA341129F9 unique(ROWID),
– constraint UQ_SD$LICENSEINFO$PERSONNO unique(PERSONNO)
indexes:
– nonclustered index SD$LICEN$MAINTENANCECONTRACT$A on SD$LICENSEINFO(MAINTENANCECONTRACTUNTIL)
– nonclustered index SD$LICENSEI$MAINTENANCECONTR$A on SD$LICENSEINFO(MAINTENANCECONTRACT)
– nonclustered index SD$LICENSEINFO$TOTALPRICE on SD$LICENSEINFO(TOTALPRICE)
– nonclustered index SD$LICENSEINFO$U_PERSONNO on SD$LICENSEINFO(U_PERSONNO)
•	KUNGRP
columns:
– PERSONNO varchar(20) not null,
– KUNDE varchar(4000),
– INTERES varchar(4000),
– PERSART varchar(700),
– BUCHHALT varchar(700),
– BRANCHE varchar(4000),
– VERKAUFA varchar(4000),
– TDATENB varchar(4000),
– NEWSLET varchar(4000),
– VERWENDG varchar(4000),
– PREISE varchar(4000),
– U_KUNDE varchar(749) default ' ' not null,
– U_INTERES varchar(749) default ' ' not null,
– U_PERSART varchar(700) default ' ' not null,
– U_BUCHHALT varchar(700) default ' ',
– U_BRANCHE varchar(749) default ' ' not null,
– U_VERKAUFA varchar(749) default ' ' not null,
– U_TDATENB varchar(749) default ' ' not null,
– U_NEWSLET varchar(749) default ' ' not null,
– U_VERWENDG varchar(749) default ' ' not null,
– U_PREISE varchar(749) default ' ' not null,
– GÜLTIGKE varchar(80),
– OPTINO varchar(80),
– BETRIEBS varchar(80),
– U_GÜLTIGKE varchar(80) default ' ',
– U_OPTINO varchar(80) default ' ',
– U_BETRIEBS varchar(80) default ' ',
– AVVDSGV varchar(80),
– U_AVVDSGV varchar(80) default ' ' not null,
– AVANTIMKMU varchar(80),
– U_AVANTIMKMU varchar(80) default ' ' not null,
– ROWID uniqueidentifier default newid() not null,
– U_PERSONNO varchar(20),
– STANDORT varchar(80),
– U_STANDORT varchar(80) default ' ' not null,
constraints:
– PK_KUNGRP primary key(PERSONNO)
indexes:
– unique nonclustered index IDX$KUNDGRP$PROGPERSARTPERSNR on KUNGRP(KUNDE,PERSART,PERSONNO)
– nonclustered index IDX$KUNGRP$PERSART$PERSONENNR on KUNGRP(PERSART,PERSONNO)
– nonclustered index KUNGRP$U_AVANTIMKMU on KUNGRP(U_AVANTIMKMU)
– nonclustered index KUNGRP$U_BRANCHE on KUNGRP(U_BRANCHE)
– nonclustered index KUNGRP$U_BUCHHALT on KUNGRP(U_BUCHHALT)
– nonclustered index KUNGRP$U_GÜLTIGKE on KUNGRP(U_GÜLTIGKE)
– nonclustered index KUNGRP$U_INTERES on KUNGRP(U_INTERES)
– nonclustered index KUNGRP$U_PERSART on KUNGRP(U_PERSART)
– nonclustered index KUNGRP$U_PERSONNO on KUNGRP(U_PERSONNO)
– nonclustered index KUNGRP$U_PROGRAMM on KUNGRP(U_KUNDE)
– nonclustered index KUNGRP$U_TDATENB on KUNGRP(U_TDATENB)
– nonclustered index KUNGRP$U_VERKAUFA on KUNGRP(U_VERKAUFA)
– nonclustered index KUNGRP$VERWENDG on KUNGRP(VERWENDG)

I created a full text index using the following statements:

exec sp_fulltext_catalog 'SdFullTextCatalog', 'create'

exec sp_fulltext_table 'SD$ADDRESS$CONTACT$PHONE', 'Create', 'SdFullTextCatalog', 'UQ__SD$ADDRE__97BD02EA03EDA9C2' -- UQ__SD$ADDRE__97BD02EA03EDA9C2 is the unique constraint of the ROWID column

exec sp_fulltext_column 'SD$ADDRESS$CONTACT$PHONE', 'MOBILENORMALIZED', 'add', 0x0407

exec sp_fulltext_column 'SD$ADDRESS$CONTACT$PHONE', 'PHONENORMALIZED', 'add', 0x0407

Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2023-12-05T22:34:57.5766667+00:00

You first need to determine, is the query taking a long time, or is it the processing taking a long time? You could use Profiler to trace the application, see the execution time of the queries on the SQL Server side.
Nic Thomas 20 Reputation points

2023-12-06T06:23:03.53+00:00

Here are the times I was able to measure in my sample application:
SQLPrepare: < 4 ms
SQLExecute: <= 124675 ms
SQLFetch: < 4 ms
Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2023-12-06T21:50:25.9466667+00:00

Yeah, but what Duration do you see in Profiler? Presumably, most of the time for SQLExecute is spent in the database, but since you say that it is fast in SSMS, and you also get this behaviour with a free-standing application, I want to rule out that there is something going on in the client.
Rühl, Wolfgang 0 Reputation points

2023-12-07T12:47:50.7366667+00:00

I checked the output of SQL Server Profiler. Unfortunately Profiler shows me the start time of the execution of the query (Exec Prepared SQL: 13:39:25) but neither an end time nor a duration. I only can tell you the start time of the next output line of SQL Server Profiler (Cursor Close, which is what our application executes immediately after the call to SQLExecute: 13.41:07). I checked the times with the demo application attached to this topic; it is a single threaded console application which doesn't do anything else than doing a lot of ODBC calls one of which is the call to SQLExecute. The times I measured yesterday were the times of Visual Studio Debugger (I pressed F10 "execute next statement" on the line containing the call to SQLExecute; after having executed the statement Visual Studio Debugger tells me the time used to execute it).
Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2023-12-07T21:49:53.95+00:00

It sounds that you may have not selected the correct events. You need RPC:Completed and SQL:BatchCompleted. It seems that you selected the corresponding Starting events. If you use the template "Standard (default)", you get the events you need.

Would it be possible for you to share the trace file? (To upload it here, you would need to change the extension to something which is accepted by the forum software.)
Rühl, Wolfgang 0 Reputation points

2023-12-08T06:18:35.6+00:00

OK, I selected the SQL-Events but not the RPC-Events. Now I selected the correct events, and I received the duration of the query. Interestingly this time the query took only about 71 seconds (which is still much more than 2 seconds). This is the trace file (the file extension renamed from .trc to .txt): query-2023-12-08.txt
Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2023-12-08T22:11:15.02+00:00
OK, we have confirmed that execution in SQL Server is slow.

On my web site, I have an article entitled Slow in the Application - Fast in SSMS? which discusses situations like the one you are seeing. However, I am not sure that any of the reasons I discuss in the article applies to your case. The most common reason for this effect is parameter sniffing, but your query has no parameters.

Can you try running the below and tell me how it goes when it comes to performance? When I tested this, the call to sp_cursorfetch failed, but it is sp_cursorexecute that is of interest.

DECLARE @sql nvarchar(MAX) = '<your query here>' declare @p1 int, @p2 int, @p3 int, @p4 int, @p5 int, @p6 int EXEC sp_cursorprepare @p1 OUTPUT, NULL, @sql, 1, @p5 OUTPUT, @p6 OUTPUT SELECT @p1, @p5,@p6 EXEC sp_cursorexecute @p1, @p2 OUTPUT, @p3 OUTPUT, @p4 OUTPUT, @p5 OUTPUT SELECT @p2, @p3, @p4, @p5 EXEC sp_cursorfetch @p1, 2, 1, 100 EXEC sp_cursorunprepare @p1

If it runs swiftly, add the command

SET DATEFORMAT YMD

on top. If it runs slow now, try running the query directly, without the mumbo-jumbo above.

By the way, I note that you have SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED in your program. This is very bad. This essentially means Mr SQL Server, just send me some rows, but I don't care too much if the result is correct or not. With READ UNCOMMITTED, all sorts of bad things can happen. Not only may you read uncommitted inconsistent data, but you may also fail to chunks of read committed data or read some data twice.
Rühl, Wolfgang 0 Reputation points

2023-12-12T06:58:36.18+00:00
Here are the results (of course, I replaced the "<your query here>" with the text of my query):

Execution in Management Studio
(0 rows affected)
(1 row affected)
(0 rows affected)
(1 row affected)
Message 16909, step 16, status 1, procedure sp_cursorfetch, row 1 [batch start row 0]
sp_cursorfetch: The value specified for cursor ID (40000001) is invalid.
Execution time: 1 minute 18 seconds (without SET DATEFORMAT ymd)
No result set was displayed.

Execution in SQLEditor (with SET DATEFORMAT ymd)
Execution time: 1.19 minutes
Fetch time: 0 microseconds
No rows selected
Fetch time: 31 microseconds
No rows selected
Fetch time: 0 microseconds
1 row selected: @p2 = 1073741826, @p3, @p4, and @p5: null

Execution in the test application:
56758 milliseconds (without SET DATEFORMAT ymd)
77874 milliseconds (with SET DATEFORMAT ymd)
I measured the execution time using Visual Studio debugger (pressing <F10> on the line "CHECK (SQLExecute (stmt));" and looking at the time displayed by the debugger).
Rühl, Wolfgang 0 Reputation points

2023-12-12T08:32:10.3233333+00:00

Let me add one word about "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED": The query is used to display a list to allow the user to select a row. So this query must not block. I don't care about if the query returns uncommitted data or not; the important thing is that the user doesn't have to wait until in some different session the transaction has been committed.

Now I have started to read your article "Slow in the Application - Fast in SSMS?". It sounds interesting but it's quite long so it will take some time to read.

Answer accepted by question author

0 additional answers

Your answer

Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2023-12-05T22:34:57.5766667+00:00

You first need to determine, is the query taking a long time, or is it the processing taking a long time? You could use Profiler to trace the application, see the execution time of the queries on the SQL Server side.
Nic Thomas 20 Reputation points

2023-12-06T06:23:03.53+00:00

Here are the times I was able to measure in my sample application:
SQLPrepare: < 4 ms
SQLExecute: <= 124675 ms
SQLFetch: < 4 ms
Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2023-12-06T21:50:25.9466667+00:00

Yeah, but what Duration do you see in Profiler? Presumably, most of the time for SQLExecute is spent in the database, but since you say that it is fast in SSMS, and you also get this behaviour with a free-standing application, I want to rule out that there is something going on in the client.
Rühl, Wolfgang 0 Reputation points

2023-12-07T12:47:50.7366667+00:00

I checked the output of SQL Server Profiler. Unfortunately Profiler shows me the start time of the execution of the query (Exec Prepared SQL: 13:39:25) but neither an end time nor a duration. I only can tell you the start time of the next output line of SQL Server Profiler (Cursor Close, which is what our application executes immediately after the call to SQLExecute: 13.41:07). I checked the times with the demo application attached to this topic; it is a single threaded console application which doesn't do anything else than doing a lot of ODBC calls one of which is the call to SQLExecute. The times I measured yesterday were the times of Visual Studio Debugger (I pressed F10 "execute next statement" on the line containing the call to SQLExecute; after having executed the statement Visual Studio Debugger tells me the time used to execute it).
Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2023-12-07T21:49:53.95+00:00

It sounds that you may have not selected the correct events. You need RPC:Completed and SQL:BatchCompleted. It seems that you selected the corresponding Starting events. If you use the template "Standard (default)", you get the events you need.

Would it be possible for you to share the trace file? (To upload it here, you would need to change the extension to something which is accepted by the forum software.)
Rühl, Wolfgang 0 Reputation points

2023-12-08T06:18:35.6+00:00

OK, I selected the SQL-Events but not the RPC-Events. Now I selected the correct events, and I received the duration of the query. Interestingly this time the query took only about 71 seconds (which is still much more than 2 seconds). This is the trace file (the file extension renamed from .trc to .txt): query-2023-12-08.txt
Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2023-12-08T22:11:15.02+00:00

OK, we have confirmed that execution in SQL Server is slow.

On my web site, I have an article entitled Slow in the Application - Fast in SSMS? which discusses situations like the one you are seeing. However, I am not sure that any of the reasons I discuss in the article applies to your case. The most common reason for this effect is parameter sniffing, but your query has no parameters.

Can you try running the below and tell me how it goes when it comes to performance? When I tested this, the call to sp_cursorfetch failed, but it is sp_cursorexecute that is of interest.

DECLARE @sql nvarchar(MAX) = '<your query here>' declare @p1 int, @p2 int, @p3 int, @p4 int, @p5 int, @p6 int EXEC sp_cursorprepare @p1 OUTPUT, NULL, @sql, 1, @p5 OUTPUT, @p6 OUTPUT SELECT @p1, @p5,@p6 EXEC sp_cursorexecute @p1, @p2 OUTPUT, @p3 OUTPUT, @p4 OUTPUT, @p5 OUTPUT SELECT @p2, @p3, @p4, @p5 EXEC sp_cursorfetch @p1, 2, 1, 100 EXEC sp_cursorunprepare @p1

If it runs swiftly, add the command

SET DATEFORMAT YMD

on top. If it runs slow now, try running the query directly, without the mumbo-jumbo above.

By the way, I note that you have SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED in your program. This is very bad. This essentially means Mr SQL Server, just send me some rows, but I don't care too much if the result is correct or not. With READ UNCOMMITTED, all sorts of bad things can happen. Not only may you read uncommitted inconsistent data, but you may also fail to chunks of read committed data or read some data twice.
Rühl, Wolfgang 0 Reputation points

2023-12-12T06:58:36.18+00:00

Here are the results (of course, I replaced the "<your query here>" with the text of my query):

Execution in Management Studio
(0 rows affected)
(1 row affected)
(0 rows affected)
(1 row affected)
Message 16909, step 16, status 1, procedure sp_cursorfetch, row 1 [batch start row 0]
sp_cursorfetch: The value specified for cursor ID (40000001) is invalid.
Execution time: 1 minute 18 seconds (without SET DATEFORMAT ymd)
No result set was displayed.

Execution in SQLEditor (with SET DATEFORMAT ymd)
Execution time: 1.19 minutes
Fetch time: 0 microseconds
No rows selected
Fetch time: 31 microseconds
No rows selected
Fetch time: 0 microseconds
1 row selected: @p2 = 1073741826, @p3, @p4, and @p5: null

Execution in the test application:
56758 milliseconds (without SET DATEFORMAT ymd)
77874 milliseconds (with SET DATEFORMAT ymd)
I measured the execution time using Visual Studio debugger (pressing <F10> on the line "CHECK (SQLExecute (stmt));" and looking at the time displayed by the debugger).
Rühl, Wolfgang 0 Reputation points

2023-12-12T08:32:10.3233333+00:00

Let me add one word about "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED": The query is used to display a list to allow the user to select a row. So this query must not block. I don't care about if the query returns uncommitted data or not; the important thing is that the user doesn't have to wait until in some different session the transaction has been committed.

Now I have started to read your article "Slow in the Application - Fast in SSMS?". It sounds interesting but it's quite long so it will take some time to read.

Answer 1

So the issue seems to be that your ODBC code drags sp_cursorexecute into the mix. You will need to figure out how to avoid that. I guess SQLExecDirect is the way to go, but I have not done ODBC programming myself.

Let me add one word about "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED": The query is used to display a list to allow the user to select a row. So this query must not block.

If so, why not just bring up some random selected rows? That's more or less what you are doing anyway. As I mentioned, you could fail to return data that should have been returned.

If you don't like blocking, consider setting your database in READ_COMMITTED_SNAPSHOT.

Share via

ODBC Query Performance Discrepancy

0 additional answers

Your answer