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

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.

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

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.

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!

here is the result of my query plan

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

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?

Here is what that query plan result looks like:

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.

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.