UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Select Query From SQL Server Slow In Access Fast In Excel, Access 2016    
 
   
philfer
post May 19 2020, 09:02 AM
Post#1



Posts: 1
Joined: 19-May 20



hi,

i am connecting to an instance of SQL server and selecting some records from a table. In Access the query takes 45 seconds to run. When I do the same thing in Excel it takes a second.

Does anyone have any idea why this would happen. Everything is the same between the access and excel query so Im not sure why it would be so much slower?

The query is a very simple one :-

myDate = DateAdd("d", -90, Date)
deletedDate = Year(myDate) & "-" & Right("000" & Month(myDate), 2) & "-" & Right("000" & Day(myDate), 2)


sqlDeleted = "SELECT colA,colB FROM tblA WHERE CAST(colB AS Datetime) > '" & deletedDate & "' AND colC='TIME';"
Set RSTDeleted = New ADODB.Recordset
With RSTDeleted
.ActiveConnection = DBConn
.CursorLocation = adUseClient
.Open sqlDeleted
End With

Many thanks
Phil


Go to the top of the page
 
PhilS
post May 20 2020, 07:38 AM
Post#2



Posts: 700
Joined: 26-May 15
From: The middle of Germany


Any differences in the connection string of DBConn?
Otherwise this is pretty inexplicable.

What's the original type of colB? Can it be changed to DateTime in the Table? The type conversion is what's making this query slow. - But it should be equally slow everywhere.

--------------------
A professional Access developer tool: Find and Replace for Access and VBA
Go to the top of the page
 
cheekybuddha
post May 20 2020, 07:55 AM
Post#3


UtterAccess Moderator
Posts: 13,021
Joined: 6-December 03
From: Telegraph Hill


FYI, Cross posted:
on AWF
and AccessForums

--------------------


Regards,

David Marten
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    8th July 2020 - 04:51 PM