RODBC posixct date field makes query very slow

Question

I need help with RODBC sqlQuery. I run a SQL script that produces 11mill rows of data. It takes 78 secs to pull data using RODBC but unfortunately when I include a date/time field, it takes 180 secs to pull data from RODBC and only 78 secs in Management Studio. I would like to find out why please and what I could do about it.

The date/time field is of this format on Sql server: YYYY-MM-DD HH:MM:SS.000

I make sure I do Sys.setenv(TZ="UTC")before I run this query:

lossdata <- as.data.table(sqlQuery(dbhandle, qry)) 

qry is a string and dbhandle is set using odbcDriverConnect.

I get the date/time field in this format when I pull from R: YYYY-MM-DD HH:MM:SS (without .000)

I've tried RJDBC too but it takes same time. It also takes too long to convert the date/time field from character to posixct after using RJDBC so it's not a good option. Unfortunately I need the field to be in posixct as I use it in sorting and it takes too long if it is not of this data type.

Please help. I'm not sure how I can reproduce this example. Please let me know if you need any additional information.

SQL Queries

With EventDate

select pp.EventDate as EVENTDATE, pp.EVENTID as EVENTID 
from 
    (select * from set.dbo.events where setid in (16,32)) pp 
     inner join 
        (select eventid from databasename.dbo.rdm_port where anls = 93) y 
     on pp.EventId = y.EVENTID

Without EventDate

select pp.EVENTID as EVENTID 
from 
   (select * from set.dbo.events where setid in (16,32)) pp 
    inner join 
       (select eventid from databasename.dbo.rdm_port where anls = 93) y 
    on pp.EventId = y.EVENTID

Answer 1

DATEDIFF(s,'1970-01-01 00:00:00',pp.EventDate) as EVENTDATE

And using RJDBC instead solves this particular problem.

RODBC takes longer than RJDBC. Everything is stored as numeric using RJDBC.

The date is now in seconds since 1970-01-01 which is fast to sort. It can be converted to date if need be.