General information
Category:
Forms & Form designer
>Howdy,
>I'm using VFP5.0a.
>
>I've got a view that looks like this: create sql view "lvclientcharges" as select trim(clast)+', '+trim(cfirst),csac_id,chosp_id,dbirth,dcontact,nclient_id from clients where nclient_id in (select nclient_id from charge_visits) order by clast,cfirst
>
>The Clients table has 49,000ish records and has indexes on the nclient_id and deleted() and cfirst and clast fields - to name a few. The Charge_visits table has 41,000ish records with indexes on nClient_id and deleted() also to name a few. This view takes at least 1 minute to run. I'd like to speed it up. Any ideas?
>
>I've tried just creating a view with an inner join on these two tables, but that's even slower...
Some ideas:
1. Leave the TRIM(...)s for the client side; that is, SELECT CLAST,CFIRST and do the trimming in your client program.
2. Instead of ordering on the server side, index the view in your program.
3. When I have slow SELECTs, I try separating them. Here, you might have a view with SELECT DISTINCT NCLIENT_ID FROM CHARGE_VISITS, and then define your SELECT so it (inner) joins this view with CLIENTS.
HTH!
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only