Simple query taking too long to execute


I have a sybase database. I have a table called mf_inovatta containing about 48,000,000 values. I try to run this very simple query, which shouldn't return more than 1000 results and it takes so long that the computer crashes or the software times out.

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'

Can anyone help me please!

I also tried running this same query in mySql database an the same thing happened.

Thank you
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'

Open in new window

Who is Participating?
Joe WoodhouseConnect With a Mentor Principal ConsultantCommented:
Almost certainly this has to be an indexing problem... Can you show us what indexes (if any) you have on this table)

Which Sybase product and version, please? (Sybase has four major database products and they all work very differently...) I'm puzzled and concerned that you say your computer crashes because the query takes too long - no Sybase database I know does that!!
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

Open in new window

frankyteeConnect With a Mentor Commented:
have you indexed your table on the fields in your where clause? if not then your db will perform full table scan to process your query
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

iboneAuthor Commented:
I only have 2 indexes:

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
iboneAuthor Commented:
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.
Joe WoodhousePrincipal ConsultantCommented:
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.


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

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,
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.