Select Query From SQL Server Slow In Access Fast In Excel, Access 2016 (1 Viewer)

philfer

Registered User.
Local time
Today, 06:36
Joined
Dec 15, 2008
Messages
29
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
 

Minty

AWF VIP
Local time
Today, 13:36
Joined
Jul 26, 2013
Messages
10,354
What driver is your DBCOnn using and do you need to use adUseCLient as the cursor? And what version of SQL Server?
 

Isaac

Lifelong Learner
Local time
Today, 06:36
Joined
Mar 14, 2017
Messages
8,738
I am not sure of the difference between Access and Excel in this case - but I like where Minty is going because it might speak to the Client's varying abilities to render the recordset - another thing I would suggest is you attempt to refactor this line
Code:
sqlDeleted = "SELECT colA,colB FROM tblA WHERE CAST(colB AS Datetime) > '" & deletedDate & "' AND colC='TIME';"

By using CAST in the Where clause like that - on the table column - you are probably generating a full table scan and/or making the engine do a lot more work.. It would be better to accept what the colB is and perform the criteria directly against it.
Let's say colB is some text type of column. It would probably run faster to say "Where colB=' and go ahead and use whatever text syntax makes sense. Might be a bit harder to code and even require OR statements, but should be faster than WHERE CAST(COL) = CRITERIA.

EDIT: Of course, the ideal thing would be to actually use a date or datetime in colB. But I was not getting into that.
 

Users who are viewing this thread

Top Bottom