Order By causes query timeout

Try by adding the following line to your code :

cmdObj.CommandTimeout = 120 (where cmdobj is your command object)

I already tried that.

	Set objConnection = CreateObject("ADODB.Connection")
	Set objRecordset = CreateObject("ADODB.Recordset")
        objConnection.ConnectionTimeout=900
	objConnection.Open "Provider=sqloledb;Data Source=" & Datasource & ";Initial Catalog=" & DatabaseName & ";User Id=" & DBUser & ";Password=" & DBPassword 

Select allOpen in new window

add index on LogicName

Don't have access to the database. I will ask the client for access.

ORDER BY is considered an expensive operator as it forces SQL Server to load all one million rows into memory, then sort all one million rows, which prevents streaming of data (a big deal in the SSIS-ETL world) and can cause memory to spike, although a million rows is generally not a big deal.

So the question is, do you REALLY need to sort one million rows??

Is there some kind of LogicID that you can sort on that is a smaller data type then LogicName?

Also, what's the data type for LogicName?  Char(10), varchar(25), nvarchar(eleventy billion), ...

Why is the timeout only 15 seconds?  Seems very low.

And as Huseyin points out if this is a critical task then having an index that covers LogicName would be good.

@Jim Horn

although a million rows is generally not a big deal.

it is not 1M, it is the number of records in that table... it may be 10M or 100M :)
and without index, you are hopeless...

Well, I got access to the database.
The LogicName field did not have an Index, so I add one.
No change.  The query without the "ORDER BY" clause runs fine.  The query with the "ORDER BY" gives me the error "Querytimeout expired" code 80040e31 in roughly 30 seconds.

A point of interest is the "ORDER BY" query runs fine if I place it inside SSMS. Go figure.

Jim,  LogicName is nvarchar(150)

also, if you run this

SQL = "SELECT top 1000000 LogicName, PointValue FROM Trends order by LogicName ASC"

Select allOpen in new window

does it work fine?

Huseyin, the query you posted produces the same error.
"Why 1M records?" Don't know. Have to ask the client.
"Hoe do you run this query?" It is a vbs file run using Windows Script Host.
"and are you using this query exactly?" Yes

Table

can you get the script for IX_Logic

rigth click, script index as > create to > new query editor

then copy/paste here

also, how many records do we have here in this table?

Sorry, on remote machine. Cannot copy-and-paste

Row count is 2985751

ok, can you please post the full code that shows

connection string
command settings
execution

I dont see any issue here...
did you try the queries w/wo order by on SSMS? whats the result?

With order by, there is an error.
Without order by, runs fine.

I need to see whole code...

connection string/command/query execution etc...

function strFileName()
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Special addition to filename
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
	yyyy = Year(Now)
	mm = Month(Now): If Len(mm) = 1 Then mm = "0" & mm
	dd = Day(Now): If Len(dd) = 1 Then dd = "0" & dd
	hh = Hour(Now): If Len(hh) = 1 Then hh = "0" & hh
	mmm = Minute(Now): If Len(mmm) = 1 Then mmm = "0" & mmm
	ss = Second(Now): If Len(ss) = 1 Then ss = "0" & ss

	strFileName = yyyy & mm & dd & hh & mmm & ss & ".csv"

End function
function strFTPName()
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Special addition to individual filenames that get FTPed
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
	yyyy = Year(Now)
	mm = Month(Now): If Len(mm) = 1 Then mm = "0" & mm
	dd = Day(Now): If Len(dd) = 1 Then dd = "0" & dd
	hh = Hour(Now): If Len(hh) = 1 Then hh = "0" & hh
	mmm = Minute(Now): If Len(mmm) = 1 Then mmm = "0" & mmm
	ss = Second(Now): If Len(ss) = 1 Then ss = "0" & ss

	strFTPName = yyyy & mm & dd 

End function

'Sub SyncShell(ByVal Cmd As String)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' We are now creating separate files for each LogicName,
' so each file must be FTPed individually while the script
' is running.
' SyncShell is used to force the script to wait for the
' FTP to finish
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'	'VBA.CreateObject("WScript.Shell").Run Cmd, 1, True
'End Sub

Sub deletefiles()
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' The last thing to do is delete the files.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim fs, fo, x

 Set fs = CreateObject("Scripting.FileSystemObject")
 Set fo = fs.GetFolder("D:\dbcsv\")
 target = strFPTName & "__"
 For Each x In fo.Files
    Debug.Print (x.Name)
   'Print the name of all files in the test folder
    
    If InStr(x.Name, target) > 0 Then
        Debug.Print (x.Name)
        fs.DeleteFile (x.Name)
    End If
 Next

 Set fo = Nothing
 Set fs = Nothing
End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''''
' Settings
'
' All you have to do is get these setting right.
'''''''''''''''''''''''''''''''''''''''''''''''''''
Dim DataSource
Dim DatabaseName
Dim DBUser
Dim DBPassword
Dim ftpSite
Dim ftpUser
Dim ftpPassword
Dim ftpExePath
Dim SQL
Dim FileName

DataSource = "127.0.0.1"
DatabaseName = "InetTrends0"
DBUser = "xxxxxx"
DBPassword = "xxxxxxxxx"
''' >>> SQL = "SELECT top 10000000 * FROM Trends"
'SQL = "SELECT top 10000000 TimeStamp, PointID, PointValue FROM Trends"
'SQL = "SELECT top 1000000 CONVERT(VARCHAR(10),TimeStamp,101) + ' ' + CONVERT(CHAR(8),(CONVERT(DATETIME,TimeStamp,113)),114), LogicName, PointValue FROM Trends"
SQL = "SELECT top 1000000 CONVERT(VARCHAR(10),TimeStamp,101) + ' ' + CONVERT(CHAR(8),(CONVERT(DATETIME,TimeStamp,113)),114), LogicName, PointValue FROM Trends order by LogicName"
''''''''''''''''''''''''''''''''''''''''''''''''''''


Dim objConnection,objRecordset,strSearchCriteria
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")

objConnection.Open "Provider=sqloledb;Data Source=" & Datasource & ";Initial Catalog=" & DatabaseName & ";User Id=" & DBUser & ";Password=" & DBPassword 


objRecordset.Open SQL, objConnection, adOpenStatic, adLockOptimistic

''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Add for writing to text file
''''''''''''''''''''''''''''''''''''''''''''''''''''''
dim fso
Dim tst
Dim i
Dim fld

i = 0
Set fso = CreateObject("Scripting.FileSystemObject")
''''''''''''''''''''''''''''''''''''''''''''''''''''''
FileName = strFileName()
Set tst = fso.OpenTextFile(FileName, 2, True)
while not objRecordset.eof
	
    Field_Cnt = 0
    For Each fld in objRecordset.Fields
        Field_Cnt = Field_Cnt + 1        
        if Field_Cnt = 3 then 'You have 4 fields? Or was it 3?  Adjust the field_Cnt accordingly
            tst.Write objRecordset(fld.Name)
        else
            tst.Write objRecordset(fld.Name) & chr(44)
        End If
    Next
	tst.Write vbcrlf
	i = i + 1
	objRecordset.MoveNext
Wend
tst.close
Set tst = Nothing

objRecordset.Close
objConnection.Close



'''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Now create separate files for each unique LogicName,
' then ftp the file.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''

'''fileName = D:\dbcsv\filename" & "20170407145539.csv"  ' <-- Testing only

Set fso = CreateObject("Scripting.FileSystemObject")
Set tst = fso.OpenTextFile(fileName, 1)

Set f = fso.CreateTextFile("D:\dbcsv\" & "NoNameFile.csv", True)
f.WriteLine ("NoNameFile")
Do While tst.AtEndOfStream = False
    
    TextLine = (tst.ReadLine)
    temp = Split(TextLine, ",")
    ''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Filter out forbidden characters for folder names
    ''''''''''''''''''''''''''''''''''''''''''''''''''
    LogicName = temp(1)
    LogicName = Replace(LogicName, ":", "_")
    LogicName = Replace(LogicName, "/", "_")
    LogicName = Replace(LogicName, "%", "_")
    LogicName = Replace(LogicName, "&", "_")
    LogicName = Replace(LogicName, "*", "_")
    LogicName = Replace(LogicName, "\", "_")
    LogicName = Replace(LogicName, "#", "_")
    LogicName = Replace(LogicName, "{", "_")
    LogicName = Replace(LogicName, "}", "_")
    LogicName = Replace(LogicName, "+", "_")
    LogicName = Replace(LogicName, "|", "_")

    If LastLogicName <> LogicName Or LastLogicName = "" Then
        ' A new file needs to be opened.
        ' Close current file.
        f.Close
        Set f = Nothing
        
        ' FTP the current file if LastLogicName <> ""
        if LastLogicName <> "" then
       	
        end if
        
        ' Create the new file
         Prefix = strFPTName
        Set f = fso.CreateTextFile("D:\dbcsv\" & Prefix & "__" & LogicName & ".csv", True)
        LastLogicName = LogicName     
    Else
        f.WriteLine (TextLine)
    End If
    
Loop

f.Close
Set f = Nothing

tst.Close
Set tst = Nothing
Set fso = Nothing

deletefiles

Select allOpen in new window

try this, line 99

objConnection.Open "Provider=sqloledb;Data Source=" & Datasource & ";Initial Catalog=" & DatabaseName & ";User Id=" & DBUser & ";Password=" & DBPassword & ";Connect Timeout=300"

Select allOpen in new window

also use adOpenForwardOnly

objRecordset.Open SQL, objConnection, adOpenForwardOnly, adLockOptimistic

Select allOpen in new window

also use adLockReadOnly

objRecordset.Open SQL, objConnection, adOpenForwardOnly, adLockReadOnly

Select allOpen in new window

also try this

Provider=SQLNCLI11;Server=" & Datasource & ";Database=" & DatabaseName & ";Uid=" & DBUser & ";Pwd" & DBPassword & ";Connect Timeout=300"

Select allOpen in new window

Huseyin, Thanks for the suggestions.  Very late here. Will try in the morning.

Huseyin,
The client's reason for 1000000 records was to get the last 7 days.  I replace it with a where clause WHERE TimeStamp >= DATEADD(day,-7, GETDATE())

It is working fine.  I did not try any of the other suggestions you made.

you should use something like this:

TimeStamp >= DATEADD(day,-7, GETDATE())

>>>

TimeStamp >= cast(getdate() -7 as date)

Can you explain why? I am interested in knowing.

first one gives like

TimeStamp >= DATEADD(day,-7, GETDATE())
>>>
TimeStamp >= '30/05/2017 16:23:56'

where as second one

TimeStamp >= cast(getdate() -7 as date)
TimeStamp >= '30/05/2017'

and most probably you will want to get records from beginning of the day

and if you run this daily, you dont miss any records this way...

Thanks!