SQL Anywhere ultralite - bad query performance

Author

bad query performance

Brent Williams

2005-08-08, 8:23 pm

I'm running v9.0.2.3044

I'm getting slow performance on the Palm for a particular table. My
table definition and query are below. Can someone tell me what indexes
I need for optimum performance?

Here is my table definition:

create table "DBA"." PrescriptionsOrdered
"
(
"ID" unsigned bigint not null
default global autoincrement(10000)
,
"PatientID" unsigned bigint,
"MedID" unsigned int not null,
"DateWritten" timestamp,
"ProviderFirstName" char(15),
"ProviderLastName" char(35),
"DateFilled" date,
"DoseQuantity" char(6) not null,
"DoseUnit" char(6) not null,
"Route" char(6) not null,
"DoseFrequency" char(6) not null,
"DispenseQuantity" char(4) not null,
"DispenseUnit" char(6) not null,
"Refills" char(3) not null,
"SpecialInstructions" varchar(200) not null,
" DispenseAsWrittenInd
" char not null,
"SampleScript" char not null,
"ProviderNumber" unsigned int not null,
"AlternateName" char(30),
"AddTS" timestamp default null,
"PharmacyID" unsigned int,
"LastModified" timestamp default
TIMESTAMP,
primary key (ID)
);

Here is my query:

select ID, PatientID, MedID, DateWritten, ProviderFirstName,
ProviderLastName,
DateFilled, DoseQuantity, DoseUnit, Route, DoseFrequency,
DispenseQuantity,
DispenseUnit, Refills, SpecialInstructions,
DispenseAsWrittenInd
,
SampleScript,
ProviderNumber, AlternateName, PharmacyID from PrescriptionsOrdered

where AddTS > ? and PatientID = ? order by DateWritten ASC

David Fishburn

2005-08-09, 11:23 am

Brent Williams < brentwilliams@bcbsal
.org> wrote in
news:42f7d0ff$1@foru
ms-1-dub of sybase.public.sqlanywhere.ultralite:

BW> I'm running v9.0.2.3044
BW>
BW> I'm getting slow performance on the Palm for a particular table. My
BW> table definition and query are below. Can someone tell me what
BW> indexes I need for optimum performance?
BW>
BW> Here is my table definition:
BW>
BW> create table "DBA"." PrescriptionsOrdered
"
BW> (
BW> "ID" unsigned bigint not null
BW> default global autoincrement(10000)
,
BW> "PatientID" unsigned bigint,
BW> "MedID" unsigned int not null,
BW> "DateWritten" timestamp,
BW> "ProviderFirstName" char(15),
BW> "ProviderLastName" char(35),
BW> "DateFilled" date,
BW> "DoseQuantity" char(6) not null,
BW> "DoseUnit" char(6) not null,
BW> "Route" char(6) not null,
BW> "DoseFrequency" char(6) not null,
BW> "DispenseQuantity" char(4) not null,
BW> "DispenseUnit" char(6) not null,
BW> "Refills" char(3) not null,
BW> "SpecialInstructions" varchar(200) not null,
BW> " DispenseAsWrittenInd
" char not null,
BW> "SampleScript" char not null,
BW> "ProviderNumber" unsigned int not null,
BW> "AlternateName" char(30),
BW> "AddTS" timestamp default
BW> null, "PharmacyID" unsigned int,
BW> "LastModified" timestamp default
BW> TIMESTAMP,
BW> primary key (ID)
BW> );
BW>
BW> Here is my query:
BW>

select ID, PatientID, MedID, DateWritten, ProviderFirstName,
ProviderLastName, DateFilled, DoseQuantity, DoseUnit, Route,
DoseFrequency, DispenseQuantity, DispenseUnit, Refills,
SpecialInstructions,
DispenseAsWrittenInd
, SampleScript,
ProviderNumber, AlternateName, PharmacyID
from PrescriptionsOrdered

where AddTS > ?
and PatientID = ?
order by DateWritten ASC;

Please post the plans you are seeing with your questions.

Well, you are asking to limit your data by PatientID and AddTS, yet you
want it ordered a different way.

About the only thing I can think of is:

CREATE INDEX ix_test ON PrescriptionsOrdered
(PatientID, AddTS,
DateWritten);

That should limit the data, and allow the index to be used for ordering
instead of creating a temporary table (which I suspect the PLAN is
current using).

--
David Fishburn
Certified ASA Developer Version 8
iAnywhere Solutions - Sybase
Professional Services
Please only post to the newsgroup
Please ALWAYS include version and MORE importantly BUILD number with
EACH post (dbeng9 -v).

EBFs and Maintenance Releases
http://downloads.sybase.com/swx/sdmain.stm

Developer Community / Whitepapers
http://www.ianywhere.com/developer

CaseXpress - to report bugs
http://casexpress.sybase.com

CodeXchange - Free samples
[url]http://ianywhere.codexchange.sybase.com/servlets/ ProjectDocumentList[
/url]

Sponsored Links