Query a large MS SQL table for just a few records.

Hi,

I understand that Xojo (using ODBC) cannot run stored procedures / functions stored in an MS SQL database because it cannot pass parameters to the database AND get a recordset returned (ODBC limitation I believe).

But I need to get a small number of records (average around 10) from a table that has a much larger number of records (100K + and growing).

I could just use a select query on the table but if my grasp of how this works (based on access development) is that it will be done locally so the entire table would be sent from the server to client and then query excited on the local machine.

Is there any way to be able to get the select done on the server to reduce network load and speed the query etc.

Basic example:

Table with stock movements (in and out) of 1000 items over past 12 months (10 movements per item per day), I want to get a list of the movements of one item for yesterday. Simple query (item reference would be an index) and simple stored procedure but what is the best approach for Xojo.

Note - Windows only (in this case), can’t change database and will be multi user.

Thanks,

Paul

Boy I sure hope that would not be the case…that the query is done remotely and only the RESULTS are returned… and even then it might even return single records as you transverse the recordset.

I cannot see it returning the whole table especially since you could have a multi-join table that would take DAYS to transmit

I’d like to hear the answer to this too because my understanding (or maybe my assumption) was that such queries were performed at the server, not locally.

Have you actually tested this?

MS SQL will perform the query on the server

Even with ODBC, MS SQL queries are performed on the server and only the results are sent to the client.

You guys are fast …

I am glad I stand corrected. My assumption was based on my MS Access experience from the past and I am not technically minded enough to accurately test.

So if I understand correctly, there is no large impact in performance or network traffic if I just do a select query on a large table against creating a stored procedure and calling it (if I could), ignoring the server being able to tune the query (very basic query on a single table).

Thanks for quick answers.

Paul

Correct. The server processes the SQL and sends the results back to the client. A stored procedure that just selects data would offer no real benefit over the SQL query itself. Make sure your large table is properly indexed and you’ll get back the subset of rows you want very quickly.

Store procedures have other benefits of course, such as allowing other processing to occur on the DB server, encapsulation of business rules, more complicated data retrieval and other things I’m sure I’m forgetting.

Can you not use some pass through SQL to execute the stored procedure? I think that I’ve done it over ODBC in the past, but in with XOJO…

Thanks Paul for the confirmation.

James -
As far as I can see in my searching over the past few days/weeks Xojo using ODBC against a backend MS SQL server may be able to pass parameters via SQLExecute but it will not return anything.
Cheer,
Paul

Paul, how about trying it with the Select statement? Perhaps that would allow pass through as well… otherwise I suppose you could always try calling the ODBC API directly, as we used to do back in the days of VB6 :slight_smile:

Its unlikely it’s ODBC - SQLExecute is NOT intended to return anything.

In general if you want result sets use SQSELECT
If you don’t (i.e./ its a delete, update or something that does NOT return record sets) use SQLEXECUTE