?
Solved

SQL slow on DBase Table

Posted on 2000-02-14
9
Medium Priority
?
378 Views
Last Modified: 2013-11-24
I am running a SQL query on a DBase table with about 12 000 entries.  It is extremely slow.  The Query is as follows :
   SELECT * FROM TABLENAME ORDER BY VDate, TLN

I know it is the order by clause that is slowing the query down but I must have it in.

I have one expression index on my Table:
   VDate+TLN

VDate is a Date field And TLN is a number field(10, 0)

Can anybody please help me speed up this query?
0
Comment
Question by:Leon_b
[X]
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
9 Comments
 
LVL 12

Expert Comment

by:rwilson032697
ID: 2518091
I think you need to create separate indexes as the query may not use the expression index.

Cheers,

Raymond.
0
 
LVL 15

Expert Comment

by:simonet
ID: 2518230
Creating adequate indexes is an option... a very good one.

However, I must warn you that on file-based tables (like dBase, Paradox, etc), SQL is not necessarily faster than the TTable object. Neither dBase nor Paradox benefit from using SQL.

SQL is an advantage on database-based tables (like Access, MSSQL, Oracle, Iterbase, etc).

Alex
0
 

Expert Comment

by:Michael_Benjamin_Perez
ID: 2518577
Can't you be more specific than '*', I know it seriously increases speed if you literally specify the columns you need...

G'Luck !
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 17

Expert Comment

by:geobul
ID: 2519380
 If you can perform all actions with the set in one loop from top to bottom, you can set UniDirectional property to true. Set it before preparing or executing the query.
  This will speed up your query but you lose backward functionality.
  I presume that you are using this query for printing some kind of report and you can modify your code to work unidirectional through the SQL set.

Regards, Geo
0
 

Author Comment

by:Leon_b
ID: 2521747
Thanks for all the suggestions, but nothing really speeds up the query enough.  I have a new question though regarding the same subject.

I took the ORDER BY out of the statement and ran it from SQL Explorer.  So in affect I ran a simple
SELECT * FROM TABLENAME query. In SQL explorer this query runs a lot faster than in my application even though the queries are exactly the same.

Why is this?
0
 
LVL 12

Expert Comment

by:rwilson032697
ID: 2521774
Did it have indexes on the fields in the order by clause?
0
 

Author Comment

by:Leon_b
ID: 2521853
Yes
0
 
LVL 1

Accepted Solution

by:
RLancaster earned 400 total points
ID: 2522073
specify the column names for a dramatic speed increase

another point to note is that TypedBinary fields seem to slow down queries dramatically ... try excluding these field types from your queries

this should help :)
0
 

Author Comment

by:Leon_b
ID: 2522084
It was the typed binary fields.  Removing these fields from the query Made a dramatic speed increase.  Thanx to everyone else trying to help.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

MS Access 2003 or later To MySQL Migration Project Hello All, this is my second article in the category of MS-OFFICE Automation. In internet I am not able to find any comprehensive resource on the Migration of MS Access back-end to MySQL so I fin…
User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

650 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