Solved

Simple query taking too long to execute

Posted on 2008-06-16
10
657 Views
Last Modified: 2010-04-21
Hi,

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

0
Comment
Question by:ibone
  • 3
  • 2
  • 2
  • +3
10 Comments
 
LVL 2

Expert Comment

by:ayus80
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

0
 
LVL 19

Assisted Solution

by:frankytee
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
0
 
LVL 24

Accepted Solution

by:
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!!
0
 

Author Comment

by:ibone
ID: 21804926
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
0
 

Author Comment

by:ibone
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?
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 13

Expert Comment

by:alpmoon
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.
0
 
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.
0
 
LVL 19

Expert Comment

by:grant300
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.

FWIW

Bill
0
 

Author Closing Comment

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

Expert Comment

by:grant300
ID: 21852277
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
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
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…

747 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now