Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2008
    Posts
    13

    Unanswered: Speeding up a search using indexes

    Is it possible to speed up a select process using indexes?

    I know it is faster but I am wondering if my select query is already using the indexes or not.

    here is screen shot of the properties of the table I am working with the table has about 500000 records:


    my query that takes a long time and sometimes times-out my ASP.net code is:

    SELECT AppointID, APPOINT.PatientID FROM APPOINT
    WHERE APPOINT.PatientID = '122222' AND (APPOINT.xDate >= CurDate()) ORDER BY APPOINT.xDate

    I just need to know if my select statement is optimized. It is important to know that this is a ASP.net app using ODBC

  2. #2
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    A few questions and a couple comments:
    1. What version of PSQL are you using?
    2. What is the size of the resultset (how many of the 500,000 records are returned by the query)?
    3. How long does the query take through the Pervasive Control Center or ASP.NET when it runs to completion?

    It looks like it's going to use an index. You could always run the Query Plan Viewer to generate a query plan. That will tell you which index, if any, is being used.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  3. #3
    Join Date
    Feb 2008
    Posts
    13
    I am using 9.5
    it is a very small return usually less than 10

    Can I use a "hint" such as:

    SELECT * FROM TABLE WITH (INDEX = Index_Name) where something = something

  4. #4
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    You might be able to use the "hint" but I would suggest using the Query Plan first to find out what index it's currently using.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  5. #5
    Join Date
    Feb 2008
    Posts
    13
    How do I set up the Query plan. I opened the query plan in Control Center but then it was blank and I had nothing to open.

    I am pretty sure it is using the PrimaryKey of AppointID

    if I do this:
    select * from "APPOINT" where AppointID > 990000 and patientid = '231843' and xDate >= '2008-01-01' Order by xDate desc

    I get a very quick result of one record. This table I am now testing from has over a million records

    Thanks for your help!

  6. #6
    Join Date
    Feb 2008
    Posts
    13
    here is the result of my query plan


  7. #7
    Join Date
    Feb 2008
    Posts
    13
    I'm not sure where that statement comes from. This is the statement I execute when I create the query plan file:

    SELECT APPOINT.AppointID, APPOINT.PatientID, APPOINT.ProviderID, APPOINT.LocationID, APPOINT.xDate, APPOINT.xTime, APPOINT.Duration, APPOINT.Type, APPOINT.Reason, APPOINT.SourceID, APPOINT.Declined, APPOINT.NoShow, APPOINT.NoShowLet, APPOINT.VisitID FROM APPOINT WHERE APPOINT.PatientID = '231843' AND (APPOINT.xDate >= '2008-01-01') ORDER BY APPOINT.xDate

  8. #8
    Join Date
    Feb 2008
    Posts
    13
    I think I figured it out. I moved the grouping. I don't know if this actually should work or why it worked but it took a query from 15 seconds down to 2 or 3 seconds

    I didn't think it mattered or I would have sent the whole query. Before, the select statement I put in this thread was slow and it was the same amount of slow as the whole query statement: Here is what I had before:

    SELECT APPOINT.AppointID, APPOINT.PatientID, APPOINT.ProviderID, APPOINT.LocationID, APPOINT.xDate, APPOINT.xTime, APPOINT.Duration, APPOINT.Type, APPOINT.Reason, APPOINT.SourceID, APPOINT.Declined, APPOINT.NoShow, APPOINT.NoShowLet, APPOINT.VisitID, SERVLOC.Name AS Expr1, SERVLOC.Address1 AS Expr2, SERVLOC.Address2 AS Expr3, SERVLOC.Zip AS Expr4, PROVIDER.ProviderID, PROVIDER.Name, PROVIDER.Suffix
    FROM APPOINT, SERVLOC, PROVIDER WHERE PROVIDER.ProviderID = APPOINT.ProviderID AND APPOINT.PatientID = '122222' AND APPOINT.LocationID = SERVLOC.LocationID AND (APPOINT.Declined <> 'N') AND (APPOINT.xDate >= CurDate()) ORDER BY APPOINT.xDate

    This is the faster one:

    SELECT APPOINT.AppointID, APPOINT.PatientID, APPOINT.ProviderID, APPOINT.LocationID, APPOINT.xDate, APPOINT.xTime, APPOINT.Duration, APPOINT.Type, APPOINT.Reason, APPOINT.SourceID, APPOINT.Declined, APPOINT.NoShow, APPOINT.NoShowLet, APPOINT.VisitID, SERVLOC.Name AS Expr1, SERVLOC.Address1 AS Expr2, SERVLOC.Address2 AS Expr3, SERVLOC.Zip AS Expr4, PROVIDER.ProviderID, PROVIDER.Name, PROVIDER.Suffix
    FROM APPOINT, SERVLOC, PROVIDER WHERE (APPOINT.PatientID = '122222' and APPOINT.xDate >= CurDate()) and PROVIDER.ProviderID = APPOINT.ProviderID AND APPOINT.LocationID = SERVLOC.LocationID ORDER BY APPOINT.xDate

    Does that make sense that it would speed up or am I fooling myself?

  9. #9
    Join Date
    Feb 2008
    Posts
    13
    Here is what that query plan result looks like:


  10. #10
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    Looking at the Query Plan, the query is performing a table scan (reading all of the records) on the APPOINT table and generating a Temp table. Is the second query plan showing the fast or the slow version?
    Looking at your WHERE clause:
    WHERE (APPOINT.PatientID = '122222' and APPOINT.xDate >= CurDate()) and PROVIDER.ProviderID = APPOINT.ProviderID AND APPOINT.LocationID = SERVLOC.LocationI
    Because you are using AND statements, you can reorder the conditions and still get the same result. I would suggest putting the conditions with the smallest resultset first. For example, if "APPOINT.LocationID = SERVLOC.LocationI" returns only 10 records and LocationID and LocationI are both indexes but PROVIDER.ProviderID = APPOINT.ProviderID returns 100 records, you should put the LocationID condition first.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  11. #11
    Join Date
    Feb 2008
    Posts
    13
    The first query plan is the most simlpe but they both seem to return at about the same speed. The second one just gets descriptive fields from other table too. Oddly enough though they process at about the same speed and the same number of records.

    I started looking at this statement because I was getting a timeout error when it ran. But after messing with it all day yesterday I think I discovered that it is only slow sometimes. I am getting the following ODBC error message:

    System.InvalidOperationException: The connection has been disabled. --->
    System.Data.Odbc.OdbcException: ERROR [HYT00] [Pervasive][ODBC Client Interface][LNA][Pervasive][ODBC Engine Interface]S1T00Timeout expired.\n

    When this happens the search application goes from about 2 second normal process time to 40 seconds and then throws the error. So I don't know if this server is too busy or out of licenses or what. but I think the statement was close to ok.

    I ran the query on a dataset with over 1.5 million records in this table and it was fine all day long on a new server with no users and 2 gig ram and dual core porc and Server 2008.

    The slow server is only 500 meg ram and an older 2 ghz processor on Windows 2000.

    Thanks again for your replies.

Posting Permissions

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