Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Simple query taking too long to execute

Posted on 2008-06-16
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 1000 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 1000 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!!
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.


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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

596 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