VB6 and SQL Server 1

[dfs8463]

Why does
"DECLARE @rtnRecs int;EXEC ss_GetStatementData @sAccountNr = '1234ABC', @iValidRecords = @rtnRecs OUTPUT"
run in 50ms in a query window and get a timeout (command timeout set to 120) in VB6?

The query goes through around 900k records and returns 100.
VB6 statement is "rs.open sql" where sql is the above statement. "set rs=conn.execute(sql)" has the same effect.
When the SQL service is restarted the problem vanishes.

Thank you for your help

 

[genomon]

Show us your connection string - looks like you are connecting to SQL Server? Could be a data provider issue (ODBC vs OLEDB)...

Beir bua agus beannacht!

 

[dfs8463]

Hi
Excuse the late reply.
The connection string is:
Provider=SQLNCLI.1;Password=pass123;Persist Security Info=True;User ID=user001;Initial Catalog=myData;Data Source=192.2.99.99;
Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=MYPC;Use Encryption for Data=False;Tag with column collation when possible=False;MARS Connection=False;DataTypeCompatibility=0;Trust Server Certificate=False
The italicised section is added after connection is established.

I don't know why you need this. The problem arises every few months (regularly). Another important thing to note is that the same query run from SQL Query window, runs without any problem whatsoever.

Thanks in advance.

 

[gmmastros]

When you run a sql query in VB6, you should get similar performance as when you are running in through SQL Server Management Studio. It may be a little slower, but probably nothing noticeable.

Since the problem occurs infrequently, I would guess that you may be running in to a blocking situation. With SQL Server, blocking occurs when one query is modifying the data in a table and another query tries to read or modify the same data. In fact, it doesn't even need to be the same data because SQL Server will lock "pages" at a time. Each page is roughly 8kb of data and could contain 1 row or thousands depending on your table.

Generally speaking, I think the best method to prevent blocking is to make each query as fast as possible. If a query needs to update data, it will lock that data, make the changes and then unlock it. If the process takes 10 minutes, and query run by another process within that 10 minutes will be blocked. If the update process takes 2 milliseconds, then another query would need to be run within that 2 milliseconds.

So, the trick is to determine the query that is taking a long time to execute. The query you mentioned runs in 50 milliseconds, which is pretty fast already. That's not to say it couldn't be faster, but the real problem is the query that is blocking yours.

One way to determine the query that is blocking yours is to run a query like this:

sp_who2

This query should be run directly within a SQL Server management studio query window. It will return a result set with all of the active queries. An query with a SPID less than 50 can be ignored because those are built-in processes. You will see a column labeled BlkBy. This will be the SPID that is blocking your query. Once you identify the SPID that is blocking your query, you can run the following query.

DBCC INPUTBUFFER(The SPID here)

This will show you the exact query that is blocking yours. At this point, you can take a look at it and determine if there is a way to make it faster. One way to make updates faster (and less likely to block other queries), is to perform the update in batches. For example, suppose the offending query is updating all the data in a table (and that table has 900k rows). SQL Server will likely lock the whole table, perform the updates, and then unlock it. If you do the updates in batches it will only lock the data it needs to, perform the update, and then unlock the data.

Please understand that there is a lot of conjecture within my response. To know for sure, I would need to see the definition of the stored procedure, and also know for sure if the problem is with blocking.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[dfs8463]

Thanks George
I keep forgetting the sp_who2 'utility' as my clients would be panicking at the time! I normally have an SQL Deadlock Detector running, but it never shows anything at that moment. And I don't really run any big updates. May I remind you that when I bring up a regular SQL Server query window, the same Stored Procedure (which is a query not an update) runs in no time (well almost). Anyway, since this happens every few months, I guess I'll have to wait a while (I hope that I won't forget sp_who2 too)

I'll let you know how it goes though, if you're patient enough to wait that long.

Cheers

 

[dfs8463]

Hi George
Still there? Merry Christmas.
The query problem re-occurred Again, the SP runs smoothly (and fast) in a query window but NOT via VB (6 and .NET)! sp_who2 does not show anyone in the BlkBy column. My deadlock detector shows nothing of interest. As it's holiday season, usage in general is slow. There are around 130 connections (SPIDs) including the 50 or so 'system' ones. So I don't know what else to try.
Anyway, Happy New Year!
Thanks
DFS

 

[dfs8463]

Parameter sniffing was the culprit!

 

[gmmastros]

I'm glad you were able to figure this out, and thanks for posting the results.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom