Problem with CURDATE()

[PRMiller]

Hi all,

I have a DTS package pulling data from TotalView's IEX database. While I haven't been able to get a straight answer on the type of database IEX uses, I can tell you that my DSN uses the Simba Client driver.

I am having a problem querying records by date. Specifically, I am pulling records that are greater than or equal to yesterday's date. Here is my query:

SELECT     
  CUSTOMER_ID, CT_ID, BEGIN_PERIOD, END_PERIOD, 
  FIRST_DATE_TIME, MODIFY_DATE_TIME, DATE_TIME, 
  IN_TIME, OUT_TIME, INTERNAL_TIME, READY_TIME, 
  ACW_TIME, IDLE_TIME, LOGIN_TIME, BACKLOG_NOT_EXPIRED, 
  BACKLOG_EXPIRED, RIG_FCST_CTS_HNDL, REV_FCST_CTS_HNDL, 
  ORIG_ACT_CONTACTS_HNDL, REV_ACT_CONTACTS_HNDL, 
  ORIG_REQ_MAX_OCC, REV_REQ_MAX_OCC, ACT_STAFF, 
  MAX_OCC, ORIG_ABAND_CONTACTS, REV_ABAND_CONTACTS, 
  ABAND_BEFORE_SL, HANDLED_BEFORE_SL, OUT_CONTACTS, 
  INTERNAL_CONTACTS, ORIG_FCST_CONTACTS_RCVD, 
  ORIG_FCST_AHT, REV_FCST_CONTACTS_RCVD, REV_FCST_AHT, 
  ORIG_SL_PCT, ORIG_SL_SECS, ORIG_ACT_CONTACTS_RCVD, 
  ORIG_AHT, OCCUPANCY, SERVICE_LEVEL, 
  REV_ACT_CONTACTS_RCVD, REV_AHT, ORIG_ACTUAL_ASA, 
  REV_ACTUAL_ASA, ASA_GOAL, ORIG_REQ_ASA, REV_REQ_ASA, 
  ORIG_REQ_SL, REV_REQ_SL, BU_EFFICIENCY_FROM_SL, 
  BU_EFFICIENCY_FROM_ASA, ALL_QS_REPORTED, REQ_CALC_TYPE
FROM
  "."."result"
WHERE     
  DATE_TIME >= (CURDATE()-1)

Now, the query does return the records that I need, however it has some of the worst performance I've ever seen. For example, to pull 19,601 records matching that criteria, the query today ran for one hour and six minutes! This represents typical performance. However, if I pull the entire table, regardless of date, I can get it loaded in about the same amount of time.

I have tried using the following:

WHERE     DATE_TIME >= DATE_ADD(d, -1, GETDATE())

and other date functions, but I always receive an "Invalid scalar function" error.

So what gives? How can I optimize this query?

 

[SQLDenis]

You get the same time because you probably don't have an index on the DATE_TIME field
WHERE DATE_TIME >= (CURDATE()-1)
Should be
WHERE DATE_TIME >= DATEADD(d, -1, GETDATE())

If you are using SQL server

“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[PRMiller]

SQLDenis,

I apologize, I left the underscore in the DATEADD function. I am attempting to use

WHERE     DATE_TIME >= DATEADD(d, -1, GETDATE())

when receiving that error. I know the syntax is correct because I've used such queries elsewhere (plus, they appropriate keywords change color).

I believe the problem lies with the type of database I am querying. The full text of the error is:

"Error Source : Microsoft OLE DB Provider for ODBC Drivers

Error Description : [SimbaLNA][Simba][SimbaEngine ODBC Driver]Invalid scalar function: DATEADD."

When I first ran into this error (using the syntax you provided) several weeks ago, I naturally googled for scalar functions.

http://www.houseoffusion.com/scalar/scalar.htm

provides a list of Time and Date scalar functions. Unfortunately, DATEADD, diff, etc are not included.

Finally, I have to profess to being new to indexing. Since the database I'm querying is not under my control, am I correct in assuming that I cannot apply an index, even temporarily through SQL Server?

 

[SQLDenis]

But your query is running in Simba (or whatever it is called) not in SQL Server
This means you have to find out what DATEPART is called in that SQL Dialect

“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[PRMiller]

And that, I'm afraid, is the problem. I haven't been able to find an alternative to DATEADD or DATEPART.

All I've been able to find so far is the CURDATE() function.

Thanks for the feedback, though.

Be interested in hearing from anyone out there's who run into similar problems using Simba SQL.

 

[PRMiller]

Just for fits and giggles, I modified my where clause to the following:

WHERE     DATE_TIME >= '2005-10-01'

This query pulled 438,372 records in 18 minutes and 26 seconds.

I'm now not so sure that indexing is the primary problem here.

At any rate, while this solution isn't ideal in my mind, I think it will serve as a workaround.

Thanks for your help!