Simple query taking too long to execute

Posted on 2008-06-16
Last Modified: 2010-04-21

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

Question by:ibone
  • 3
  • 2
  • 2
  • +3

Expert Comment

ID: 21798897
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

LVL 19

Assisted Solution

frankytee earned 250 total points
ID: 21798898
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
LVL 24

Accepted Solution

Joe Woodhouse earned 250 total points
ID: 21799319
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!!
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.


Author Comment

ID: 21804926
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

Author Comment

ID: 21805192
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?
LVL 13

Expert Comment

ID: 21809247
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.
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 21809293
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.
LVL 19

Expert Comment

ID: 21825106
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.



Author Closing Comment

ID: 31467826
Thank you, I added the index on the table and worked fine
LVL 19

Expert Comment

ID: 21852277

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,

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Query group by data in SQL Server - cursor? 3 50
Complex Query - help please 5 52
SQL Syntax 14 35
SQL 2008 R2 2 8
This article describes some very basic things about SQL Server filegroups.
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question