Query takes long only the first time

[guillermo88]

Query takes long only the first time

-I have a transacctions table
-One of the index tag of this table is by account id
-I also have a data entry form where it check previous transacctions
of a customer (by account_id)
- transacctions table is about 90,000 records
-This is a multiuser application
-My sql statements is getting 2 columns only:
SELECT column1,colum2
FROM transacctions
WHERE account_id = variable

Why is it that takes long only the first time?
Is there a way to avoid this behavior?
Do views behave in the same way?

Can anyone give me some suggestions how I should do this?

Thanks

 

[rgbean]

Actually, a "long" time is probably only relative to the 2nd query. The 1st time all the real IO has to take place to load up the indexes into the workstations' local cache so that Rushmore can do it's magic. There really isn't any way to shortcut this - of course you could do the first query before the user is expecting it, so that they are actually doing the 2nd one. On the other hand, if your data changes on disk, subsequent queries will require some additional time - it all depends on the number of changes to the index. Note: The amount of cache space and number of concurrent users on the network file server can also make major/noticable differences on queries.

Views can be quicker, especially if they are more limiting in regards to total number of records "retreived".

Rick

 

[guillermo88]

Thanks Rick for you answer,
I was using "set filter to" before and I think the response was quicker:

use transacctions
set order to trans1
set filter to account_id = cAcount_id
go top

I query this transacctions table based on 3 columns:
date,account_id,referenceNo.
Do I have to create 3 views for this or is there a way to create junt one view that I can use for these 3 cases?

Thanks again Rick

 

[chpicker]

According to the MSDN library, Rushmore works faster if you do NOT have a filter set.

As for your second question...if you mean that you need to have the resulting query ORDERED by each of those 3 columns, you will have to make 3 different views. You cannot use a parameter for the ORDER BY clause.

Ian

 

[gozap]

The Table is being held in cache memory so running it the second time shows an increase in speed. I guess that you are NOT using an index. This will speed up the Query no end! as Rusbhmore optimisation will be used. Create an index nased on the account Id field and just see!!
Dave Crozier
DaveC@Replacement-Software.co.uk Dave Crozier

 

[guillermo88]

Thanks Ian,
I forgot to mention that in my query ALLTRIM function is included:

SELECT column1,colum2
FROM transacctions
WHERE account_id = Alltrim(variable)

And I was thinking maybe using a function within the WHERE statement will make not to use the index.

The reason I am saying this is because I other form that allows me query the same table but By reference # and it is very fast (same workstattion)

Thanks again

 

[chpicker]

ALLTRIM is NOT Rushmore optimizable. By using it, you are forcing VFP to scan EVERY record...it cannot use the Index.

Hope that helps!

Ian

 

[guillermo88]

Thanks a lot Ian

 

[chpicker]

By the way...

Since a record is fixed length, there is actually no need to include ALLTRIM. As long as the variable being compared is the same length as the record field, it will match exactly. For example, if your account_id field is 10 characters long, and one of your account IDs was "ACCHT", the actual value being compared would be "ACCHT " (5 spaces after it).

Ian

 

[guillermo88]

Ian,
I got it , thanks