Query timeout and indexes

[RobHVB6Sql]

I am recieving timeouts here and I cant work out why.
Only retrieving on a table.

1) Table Creation script:

if exists (select * from sysobjects where id = object_id('tblIdentityMapping_Person') and sysstat & 0xf = 3)
	drop table tblIdentityMapping_Person
GO

CREATE TABLE tblIdentityMapping_Person (
     FromID         INT     NOT NULL,
     ToID           INT     NOT NULL
)
GO

2) The table has now been filled with 38996 rows and I have created two indexes.

CREATE CLUSTERED INDEX tblIdentityMapping_PersonPrimIdx ON tblIdentityMapping_Person(FromID)

CREATE INDEX tblIdentityMapping_PersonAltIdx ON tblIdentityMapping_Person(ToID)
GO

UPDATE STATISTICS tblIdentityMapping_Person
GO

PS: exec sp_helpindex 'tblIdentityMapping_Person'
--returns the following
index_name index_description index_keys
--------------------------------- ------------------------------- -------------
tblIdentityMapping_PersonPrimIdx clustered located on PRIMARY FromID
tblIdentityMapping_PersonAltIdx nonclustered located on PRIMARY ToID

3) The query that is giving timeouts:

SELECT ToID FROM tblIdentityMapping_Person WHERE FromID = 10498

4) NB: This is running through VB 6 and an ADO recordset.
Can I make this any more efficient, it times out after 120 seconds!


Rob Hasard
Data Manager -Genetic Services
(VB6 /SQL 7.0)

 

[vongrunt]

(?!?) This is clustered index seek. It should last 120 microseconds tops

Did you get timeout in query analyzer too or VB only?

 

[RobHVB6Sql]

No timeouts only in VB.

The VB code is: {simplified here}

TheSQL2 = "SELECT ToID FROM tblIdentityMapping_Person WHERE FromID = 10498"
    
'execute supplied SQL
Set adors3 = adoOdysseyConn.Execute(TheSQL2)
    
'cheat below
adors3.ActiveConnection.CommandTimeout = 120 '2 minutes minutes!
    
CurrentField = "ToID"
If adors3.EOF = False Then
    GetNewIdentityMapping = CLng(adors3(CurrentField))
Else
    strErrorText = "a_NEWIDENTITYMAPPING (ToID) not found related to " & strTableName & " in tblIdentityMapping."
    GetNewIdentityMapping = 0
    Call AddConvertErrorRow(FromTable, Str(strOrigCode), _
    CurrentField, "NO VALUE", strErrorText)
    Exit Function
End If

Rob Hasard
Data Manager -Genetic Services
(VB6 /SQL 7.0)

 

[vongrunt]

This is very probably ADO/VB problem. To be absolutely sure, you can launch profiler and monitor it's output while running VB code.

Which SQL provider and version of ADO/MDAC are you using?

Btw. in code above, CommandTimeout is set after Execute().

 

[RobHVB6Sql]

I haven't used profiler before.

Versions:
* SQL 7.0 (7.00.623) (t-sql I suppose)
* ADO 2.1 (msado21)
* MDAC 2.6 SP2 (I think)
* MS DAO 3.6


CommandTimeout - If this is before the exec I recieve "Object variable or With block variable not set"

Rob Hasard
Data Manager -Genetic Services
(VB6 /SQL 7.0)

 

[vongrunt]

Huh... ADO 2.1 is ancient... and DAO is kind of history:

http://www.sql-server-performance.com/visual_basic_performance.asp

Can you show DSN/connection string data (without passwords of course)?

About code.. you can apply timeout on connection

adoOdysseyConn.CommandTimeout = 120
Set adors3 = adoOdysseyConn.Execute(TheSQL2)

 

[SQLSister]

Well if it works in query analyzer, could you create stored procedureand then call that from you VB app?

Questions about posting. See faq183-874