[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 379
  • Last Modified:

SQL slow on DBase Table

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
Leon_b
Asked:
Leon_b
1 Solution
 
rwilson032697Commented:
I think you need to create separate indexes as the query may not use the expression index.

Cheers,

Raymond.
0
 
simonetCommented:
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
 
Michael_Benjamin_PerezCommented:
Can't you be more specific than '*', I know it seriously increases speed if you literally specify the columns you need...

G'Luck !
0
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
geobulCommented:
 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
 
Leon_bAuthor Commented:
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
 
rwilson032697Commented:
Did it have indexes on the fields in the order by clause?
0
 
Leon_bAuthor Commented:
Yes
0
 
RLancasterCommented:
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
 
Leon_bAuthor Commented:
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now