Sql Query from Work station

[mstrcmtr]

Multi user environment

There are 15000 or more records in stock table

In Sale invoice whole stock table required for this use SQL- Query in form init method to get a stock cursor which contain more than

15000 records

When run this form From the Workstation this Sql-Query takes long time to complete due to which user has to wait every time when he wants to

make sale invoice because of this query

Q. Any other solution to solve this time taking problem in loading Form

 

[Scott24x7]

Are you only selecting the records relevant to the stock, or the entire table? If you're doing something like:

SELECT * FROM INVENTORY INTO CURSOR WORKINGINV

Then you're just selecting everything.

It's hard to tell because you don't post up what your SQL Select statement is gathering. IF you add a WHERE clause to it, and only select the related records, you don't then need the entire Stock (inventory) table.
But I'm guessing as we don't have details. Please post up your SQL code so we can see what might be able to optimize.

Best Regards,
Scott
MSc ISM, MIET, MASHRAE, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"Everything should be made as simple as possible, and no simpler."

 

[Mike Lewis]

In addition to what Scott said, it's not clear if you are running this query against Foxpro DBF files or a back-end database such as MySQL or SQLServer. Let us know.

I'd also like to know why you need to get 15,000 records into a cursor. Are you really going to use all those records to produce your invoices or reports or whatever?

In general, it is better to only retrieve the records you need for a particular job. For example, if you want your form to show the invoices for a particular customer, then you only need to retrieve the stock records belonging to that customer's orders. Doing so will make a big difference to how quickly you retrieve your data.

And, if that's the case, you can speed up the query by maintaining appropriate indexes in the database. Again, it's not clear from your question whether you are doing that.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[GriffMG]

I'm guessing the OP is looking to have a segment of grid or similar with all the possible stock items on it, with a search box to help find the ones he wants.

Thing is populating that is a nightmare if you've got to drag 15000 records from a remote server.

He might think about populating the cursor when the user starts to type in the search box, or types something and hits a find button.

Alternatively, he could populate the cursor once, before the form is opened and keep that cursor available after the form has closed.



Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[mstrcmtr]

Thanks All

Using Free table Stock.DBF with Compound CDX all required indexes of stock table created in CDX

In Invoice all items required no filter will apply e.g. ON Brand , Make , Available Stock etc. because all stock table required in Invoice because any item required for Invoice from Stock Free table no one judge before which item required and which not

In New Invoice if use direct Free table of stock than the process of selecting item from List box which is used to show records of stock table in Multi user environment ( Scrolling or Moving in List Box ) is very very Slow on the workstations therefore by creating stock cursor in form init method and show the cursor in List Box make ( Scrolling or Moving in List Box ) fast of selecting items in Invoice.

Slow movement in Free table is because of OP Lock System which make the process Slow of moving and scrolling within table records

Using the following Query

SELECT S1.Desc,S1.Szsd,S1.Mncrclr,S1.Acitno,S1.Prtno,S1.Linkpno,S1.Vndrcd,S1.On_hndq,S1.Avg_Rate,;
S1.MN,S1.MadeinN,S1.Bn,S1.CN,S1.GN,;
S1.G1,S1.G2,S1.G3,S1.G4,S1.G5,S1.G6,S1.G7,S1.G8,S1.G9,S1.S1,S1.S2,S1.S3,S1.S4,S1.S5,S1.S6,S1.S7,S1.S8,S1.S9,;
S1.Slgrte,S1.Slgrtemnm,S1.Slgrteqty,S1.SlgrteLst,S1.Slgrdst,S1.Slgrdstdlr,S1.Slgrdst1pc,;
S1.Prcrte,S1.PrcrteMnm,S1.PrcrteQty,S1.Lprate,S1.Lprate_Fc,S1.QtyT,S1.Ck_Lck_Lst,S1.Ck_Points,;
S2.Pckg,S2.Setcode,S2.CC_DtyP,S2.CC_WGHT,S2.CC_UNIT,S2.Grs_Wght,S2.Net_Wght,S2.CBM,;
S2.Schm,S2.SchmonQty,S2.SchmRwrd ;
FROM My_Stock S1 ;
INNER JOIN My_Stk_B S2 ON S1.Acitno = S2.Acitno ;
WHERE S1.Acitno > 0 ;
INTO CURSOR Lst_As_Nw ;
READWRITE

 

[Mike Lewis]

I'm finding it hard to understand what you are saying. Are you saying that you have a listbox with 15,000 records in it?

If so, then that will inevitably be very slow, possibly to the point of being unusable. It doesn't matter how you do your SQL. Populating a listbox with that many records is pushing it to its limits.

A better bet woul be to use a grid. In general, grids perform much better than listboxes when it comes to large amounts of data. Showing a grid with 15,000 rows should not be a problem.

I suggest you try that first, before you worry about optimising your SQL.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[mstrcmtr]

Yes worried about optimising SQL Query for this ,

Choice of List box or Grid is next

 

[Olaf Doschke]

The only index optimizing this query is with an index on Acitno in both tables. But there is no magic making this faster but adding a WHERE clause only selecting relevant stock items - you already said there is no such filter possible. And to fetch less columns.

You fetch a lot of fields for the use case of picking a record from a list box, is all that already necessary for the user to make his pick? Since you join two tables a strategy using two lists or combo boxes or grids would also help, most likely to only fetch a shorter list for the first choice and then just corresponding details from the second table. Even when you do this more than just once in init that could reduce the need to fetch data even down to not fetching more than with just one fetch, even when a lot of items are picked.

You don't find your way through 15000 items without something like the incremental search feature of the listbox. So rather make use of a textbox asking for at least 2 or 3 characters to search for and then only select those, that would be the usual search strategy you apply here, not first fetching all rows and then navigating to the one you want, but first telling a bit about what you know you seek and then only fetching those.

So in summary picking from long lists has two major strategies you implement: picking in two stages, first rough, then in detail or the search approach, first asking some partial value to use for your query WHERE clause.

Bye, Olaf.



Olaf Doschke Software Engineering

https://www.doschke.name