Post reply

Date filter

  • April 27, 2007 at 7:06 am

    #172216

    When I try to filter a table on a date field in an open table in SQL 2005, it takes a very long time to return the response. If I filter on a field that is not a date field, the response time is much faster Does anyone know why that is?

     

    The date field is date time type and there are less than 100,000 records in a table.

    Regards,

    Carla Hepker
    Documentation: A Shaft of light into a coded world.

  • April 27, 2007 at 8:30 am

    #702942

    How are you filtering the data? Can you please post the T-SQL statement(s)?

  • April 27, 2007 at 8:42 am

    #702949

    I am using the criteria pane to fill in the criteria.

    So I put the date in the filter field as: > 4/1/07.

    It creates this SQL:

    SELECT     *

    FROM         tblProduction

    WHERE     (ProdDate > CONVERT(DATETIME, '2007-04-01 00:00:00', 102))

    Regards,

    Carla Hepker
    Documentation: A Shaft of light into a coded world.

  • April 27, 2007 at 8:49 am

    #702953

    What is the data type for the ProdDate column?

    How about using the following statement instead? There is no need to convert datetime back to itself and the 102 doesn't help here either since the hour/min/sec values are already zero.

    SELECT *

    FROM tblProduction

    WHERE ProdDate > '4/1/2007'

    Now, if this is slow then there's very little you can do (unless you wish to apply an index on the ProdDate column).

  • April 27, 2007 at 9:03 am

    #702962

    I agree there is no need to convert it back, but that is the SQL that SQL server creates when I enter the date in the criteria pane. While I am not averse to typing in the SQL statement, I would just as soon just enter the data in the criteria pane.

    Also, it is still slow when I do that.

    I may have to put an index on that column. It is just odd that other non-date columns don't exhibit the same slowness even though they are not indexed either.

    Regards,

    Carla Hepker
    Documentation: A Shaft of light into a coded world.

  • April 27, 2007 at 9:12 am

    #702964

    If you'd like to dig into the speed problems with this particular column- can you please post the DDL (schema) of the table?

  • April 29, 2007 at 9:44 am

    #703161

    Hi buddy,

    Try to use this format of date while checking date....either '01-April-2007' Or '04012007'

  • April 29, 2007 at 6:29 pm

    #703184

    Carla,

    I just used your query on a million row table and it returned all the columns for 275K rows in less that 6 six seconds... what is the datatype of the ProdDate column?  Also, are you actually looking at a table... or a view?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • April 30, 2007 at 11:09 am

    #703359

    I am using a table. The table is being used by an internal application that is running on 50 to 60 machines. The datatype is datetime.

    There is another interesting thing, I don't know if it has any bearing on this or not. The internal application is an Access 2003 data project compiled front end (an ade). The back end is the SQL server, which I recently migrated from 2000 to 2005. When it was in 2000, I did not have this problem of being slow when I filtered by date. I also have another problem, now that it is in 2005, and that is that if I have the application open on my computer and I try to filter the table in SQL server management studio, I get errors in the application, like this:

     "The record source '{ call dbo.procControlHistGet(?, ?, ?) }' specified on this form or report does not exist.

    You misspelled the name, or it was deleted or renamed in the current database, or it exists in a different database.

    In the Form or Report's Design view, display the property sheet by clicking the Properties button, and then set the RecordSource property to an existing table or query.

    This same type of error message pops up for each of the subforms in the application. Then, I can refresh and everything is all happy again. I don't understand why there should be any relationship between what I am doing in Management Studio and the application. And, I don't know if that is what may be slowing down the process when I filter.

    Regards,

    Carla Hepker
    Documentation: A Shaft of light into a coded world.

  • April 30, 2007 at 3:21 pm

    #703419

    You have to upgrade to Access 2007 or rewrite from scratch in 2007.

    Access 2003 + SQL 2005 database (migrated from sql 2000) = nightmare.

    That compatibility feature does not apply for ADP's.

    You cannot use Access 2003 native tools to manage sql server 2005 tables.

    It has something to do with system tables which sql 2005 removed.

    Microsoft promised a FIX on this issue two years ago.

     

    Alex S

  • April 30, 2007 at 3:28 pm

    #703425

    I am not trying to manage the SQL server from Access 2003. I just notice that if I have the adp (ade) application open and I try to do anything to a table or view in SSMS then I get all the errors in the ade.

    I don't mind not being able to manage from Access, since I always used SQL server anyway. But I would like to know if I can make the errors go away. I don't understand what link there would be between using SSMS to filter a table and having an application open that might be using the same table through a stored procedure.

    Regards,

    Carla Hepker
    Documentation: A Shaft of light into a coded world.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply