Simple query taking too long to execute

try this code. It will give 100 limit data

set rowcount 100 
select * from mf_inovatta m  where m.DepartureStation = 'MEX' and m.ArrivalStation = 'IAH'  and m.EffectiveDate <= '20080626' and m.DiscontinuedDate > '20080626'  and m.ThursdayOperationFlag = 'Y'
set rowcount 0

Select allOpen in new window

I only have 2 indexes:

ID
ArrivalStation - DepartureStation

However when i try to create a new index it takes forever!!! It is not doing it!!

Joe: I am sorry the computer doesn't crash, is the software and DBArtisan that crashes when running the query. I am using ASE 15.0

I am starting this proyect, so any suggestions of what database to use are welcome. I started with mySql and since the query was taking so long I thought that maybe sybase witll be better but is not working. I am trying to create the indexes for both mySql and Sybase databases. But in your expert opinion which database should I use? SQL Server, Sybase or Mysql?

You definitely need an index on that table. You can tune Sybase ASE to make index creation faster. But it all depends on the number of processes and actual physical memory you have as well. How many CPU's do you have? What is the size of your data cache? (If you are working with default values it takes forever).

Also have you created large buffer pools? You need 16K buffer pool and 4K buffer pool to make that sort of operations faster (assuming that you are using default 2K page size, otherwise you can use larger pools )

You can create such an index in minutes by using multiple CPU's. However, you need to configure worker processes, sort buffers and large data cache pools. I don't think that other options gives more flexibility than ASE 15.

One of your indexes (ArrivalStation,DepartureStation) looks like it should be helping the query.

Did you create the indexes on the empty table before putting data in it? If so, Sybase is missing some useful statistics on the data and you need to refresh that. Run:

    update index statistics  <table_name>

in that database.

I suspect if this is a new install of ASE 15.0 out of the box, there are many important things you haven't changed. Sybase ships ASE with default settings that are barely enough to even start it and certainly aren't suited to any serious use. At a minimum you would want to look at these ones, tuned with sp_configure:

max memory
procedure cache size
max online engines
number of engines at startup

and this, configured with sp_cacheconfig:

default data cache


Sybase vs. mysql is no contest. Sybase vs. MS SQL Server is a more difficult call, but portability and scalability are important factors here. MS locks you into Windows platforms forever.

One of the problems you have with the Arrival/Departure index is that it is terribly non-unique.  You will have thousands of rows associated with each value pair.  Indexes built like that typically perform poorly for creation as well as maintenance.  To make matters worse, if the optimizer decides that the index is not very selective (as it might well do in this case), it might not use the index at all.

I would append the ID onto the end of the Arrival/Departure index and create it as unique.  I think you will find things work a lot better that way.

You may also want to think about what other data fields might make sense in the index; departure date/time is probably useful for any number of different queries and, while it won't make things unique (I am guessing this is more about tickets than trains) it may help performance.  You do, of course, still have to leave the ID on the end of the index in order to keep it unique.

FWIW

Bill

Thank you, I added the index on the table and worked fine

Author,

You marked two comments that do not provide the apparent solution.

Can you add a comment telling us what you finally did to fix the problem.

Many thanks,
Bill