|
|
Databases and Drivers
Started by elriba at 08-22-2008 11:06 AM. Topic has 1 replies.
  
|
|
Sort Posts:
|
|
|
|
 08-22-2008, 11:06 AM
|
elriba
Joined on 05-19-2006
Panama
Posts 6
|
Browsing a large table in SQL databases
|
|
|
|
|
Hi,
In some circumstances browsing a large table for a SQL database is too slow.
I have this problem in a number of browses. The typical scenario is where I wan the user to be able to select from the (>million records) invoices table by InvoiceNumber.
When the browse opens, Clarion sends something like:
SELECT A.InvoiceNumber,A.InvoiceDate,A.InvoiceCustomer FROM Invoices A ORDER BY A.InvoiceNumber
to the database. This has the impact of reading the whole table sequentially.
I have tried a number of solutions. The closest I have come is by developing a modified filtered locator which starts the browse empty and waits until the user enters something before displaying the data. Here Clarion sends something like:
SELECT A.InvoiceNumber,A.InvoiceDate,A.InvoiceCustomer FROM Invoices A WHERE (A.InvoiceNumber LIKE 'A%') ORDER BY A.InvoiceNumber
assuming that I typed "A" on the locator.
The problem however is that the database in some cases will still interpret the above query as needing a FULL TABLE SCAN.
The only way to "force" the backend (in my case ASA9) to use the available index and only read a subset of records is to use the TOP X clause:
SELECT TOP 15 A.InvoiceNumber,A.InvoiceDate,A.InvoiceCustomer FROM Invoices A WHERE (A.InvoiceNumber LIKE 'A%') ORDER BY A.InvoiceNumber
How can I enter the "TOP xx" right after the SELECT statement?
This is a major problem for me right now as I have at least 2 applications that my users are complaining about.
Best regards,
Edgard L. Riba
|
|
|
|
|
Report
|
|
|
|
|
08-23-2008, 1:59 PM
|
RexK
Joined on 11-03-2005
Posts 6
|
Re: Browsing a large table in SQL databases
|
|
|
|
|
|
My solution to this problem is to write my own sql statement to extract the data, write it to a memory file then browse the memory file. The advantage is that I can add data (such as running totals) or keys to the memory file. The disadvantage is that if you are changing or adding records yoy have to manage this yourself.
Cheers Rex
|
|
|
|
|
Report
|
|
|
|
|
|