DA) vs SQL

[NigelShaw]

Hi,

so which is the best solution? i'll be honest and say ive used both. i thought SQL was fast but flakey. Flakey becuase in some applications, methods dont seem to work. I had 1 app and tried to used the same type of sql execute but it only worked on one of the two routines even though, the structure was identical.

Personally i think DAP works every time but the doenside here is the length of code required for complex routines.

i wont even mention parameters?@"*^^


Cheers

N

 

[Banana]

Here's one problem with the comparison:

How do you open a recordset? In order to open a recordset, you have to pass in a SQL statement. Sure, you can just drop in a name of query (which is just SQL), or table (which is of course, just "SELECT * FROM <tablename>;").

In short, there's no getting around SQL; everything you do with any data is through SQL, whether directly or indirectly.

Furthermore, because SQL is a set-based operation, it's much more likely to trump any iterative-based operation in terms of performance and efficiency. The cases for using a iterative-based operation is pretty few and far between. Two examples I can think of offhand is 1) when you're working in a form; the form already has a recordset opened so there's no sense in making another roundtrip when you can just go through the recordset. 2) when we need some kind of reference to relative records. An example is looking up values from an adjacent previous row to use in updating the current row. Obviously, this implies that the query must be ordered (there's never a guarantee that the result are ordered same way without a explicit ORDER BY). While #2 could be done in SQL, there are cases where it get too slow that doing it iterative is just faster.

Now, if one finds doing a lot of iterative processing, it may be actually indicative of not sufficiently normalized structure because large majority of processing can be done in SQL.

You'll have to elaborate on SQL being flakey. My experience is that SQL is pretty consistent.