Results 1 to 14 of 14

Thread: Date Format

  1. #1
    Join Date
    Feb 2005
    Posts
    24

    Unanswered: Date Format

    I have a database that is running really slow. Right now it has only about 10,000 records in each of the 2 main tables. My users are continually telling me that it needs to be faster. I have set the relationsips, indexed the fields compacted the database. I am at the end of my ideas. Anyone got anything else that i can try to speed this up.

    Thanks
    Mike

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try the Format function

    for example, Format(birth_date, 'dddd d MMMM yyyy') would display the date as 'Monday 23 January 1989'.

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Um...am I seeing things right here (I'm thinking thread title, question and Rudy's response)?
    Or is this a b0rker?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    well, mike said he indexed his columns, so, being unable to assist any further in helping him with the performance, i thought i'd take a stab at the question in the title

    didn't want him to feel totally neglected, eh

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Not sure where to start here;

    Think more detail might help people make suggestions;

    Is the database split between back-end / front-end?

    What environment are you working in - Just LAN?

    What are the relationships between the tables?

    How many users are there?

    Are the forms bound back to data or unbound?

    What do they mean by 'Faster' what sort of performance is acheived?

    Anything specific which is particuarly slow? Opening a certain report / form?

    If so, post the SQL which may shed some light.

    Back to you Mike
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  6. #6
    Join Date
    Feb 2005
    Posts
    24

    Date Format

    Sorry, mind on one thing and fingers typing another. What I really need help with is the speed of the database. Some forms open slowly, some open quickly, some queries take a long time to run others only take a few seconds.

    Sorry for the confusion.

    Mike

  7. #7
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Question Speed of the database

    Quote Originally Posted by ryne23
    Sorry, mind on one thing and fingers typing another. What I really need help with is the speed of the database. Some forms open slowly, some open quickly, some queries take a long time to run others only take a few seconds.

    Sorry for the confusion.

    Mike
    Without more information, people will struggle to give you assistance.

    Please post the details I requested in my previous post and we might make some progress...
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  8. #8
    Join Date
    Feb 2005
    Posts
    24

    Date Format

    The database is split frontend / backend.

    There are 6 users max at a time.

    Each user has a copy of the front end on their C Drive.

    here is the SQL of one that runs slow. This is running a list box on an unbound form.

    SELECT DISTINCT Calls.CallID, ([callid]+900000) AS NewConsumerID, ([FirstName] & " " & [LastName]) AS Name, ([Addressline1] & ", " & [City] & ", " & [State] & " " & [postalcode]) AS Address, Calls.ContactDate, Calls.FirstName, Calls.LastName, Calls.AddressLine1, Calls.City, Calls.State, Calls.PostalCode, Calls.PhoneNumber, Calls.Comments
    FROM Calls
    WHERE (((Calls.FirstName)=Forms!Call_Entry_Modify!FirstN ame) And ((Calls.LastName)=Forms!Call_Entry_Modify!LastName )) Or (((Calls.FirstName)=Forms!Call_Entry_Modify!FirstN ame) And ((Calls.AddressLine1)=Forms!Call_Entry_Modify!Addr essLine1)) Or (((Calls.LastName)=Forms!Call_Entry_Modify!LastNam e) And ((Calls.PostalCode)=Forms!Call_Entry_Modify!Postal Code)) Or (((Calls.AddressLine1)=Forms!Call_Entry_Modify!Add ressLine1) And ((Calls.PhoneNumber)=Forms!Call_Entry_Modify!Phone Number)) Or (((Calls.FirstName)=Forms!Call_Entry_Modify!FirstN ame) And ((Calls.PostalCode)=Forms!Call_Entry_Modify!Postal Code)) Or (((Calls.FirstName)=Forms!Call_Entry_Modify!FirstN ame) And ((Calls.PhoneNumber)=Forms!Call_Entry_Modify!Phone Number)) Or (((Calls.LastName)=Forms!Call_Entry_Modify!LastNam e) And ((Calls.PhoneNumber)=Forms!Call_Entry_Modify!Phone Number)) Or (((Calls.LastName)=Forms!Call_Entry_Modify!LastNam e) And ((Calls.AddressLine1)=Forms!Call_Entry_Modify!Addr essLine1)) Or (((Calls.AddressLine1)=Forms!Call_Entry_Modify!Add ressLine1) And ((Calls.PostalCode)=Forms!Call_Entry_Modify!Postal Code)) Or (((Calls.PostalCode)=Forms!Call_Entry_Modify!Posta lCode) And ((Calls.PhoneNumber)=Forms!Call_Entry_Modify!Phone Number));

  9. #9
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Are there any combo boxes on the form with large number of rows?

    How many records would by 'typically' returned for this query?

    Are you able to post a .zip of the database - perhaps without data?
    Last edited by garethdart; 05-01-09 at 11:40.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  10. #10
    Join Date
    Feb 2005
    Posts
    24
    There is one combo box but only have 5 entries in it.

    No I can't post a .zip. Company policy prohibits that.

  11. #11
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Consider what might happen if the controls being referred to on the form are null - Would this not return lots of irrelevent data?

    Maybe you should build the query on the fly and only reference controls which have data entered into them?

    Try testing this form by typing somehing in to each of the reference controls versus leaving lots of nulls.
    Last edited by garethdart; 05-01-09 at 11:46.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  12. #12
    Join Date
    Feb 2005
    Posts
    24
    This is something that i have never done before. Any tips on how to accomplish this?

    Thanks
    Mike

  13. #13
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    http://www.dbforums.com/microsoft-ac...ed-form-2.html

    Maybe have a look at this thread - I was explaining to RS142 a similer concept;

    RS142 had a button which opened a query, Im guessing you have a button which opens a form;

    Prior to the form opening, build your SQL query in the same manner as described and then perhaps.

    Have no rowsource for the listbox on the form, but apply the SQL as the rowsource once it has been constructed and the form has been opened.

    I would consider ONLY using each of the criteria if there is data in *'BOTH' referenced controls (*you appear to be checking for pairs of matching criteria) else I think (testing will confirm) you will end up returning a load of rows which MAY not be relevent?

    Think we need to identify whether the SQL is the source of the speed issue before we jump to any conclusions though.

    Do a test with a new SQL statement ONLY checking for first and last names - Ensure you enter data into both fields on your search form (and maybe ones you know will match SOMETHING in your database). Is this much faster than using the existing SQL?
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  14. #14
    Join Date
    Feb 2005
    Posts
    24
    Thank you I will give it a try. I really appreciate all your help.

    Mike

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •