At 12:48 PM 29/01/2008, Federico Bobbio wrote:

>> > or should i bring all the data i need in one big query?
>>
>> That's the wrong question to ask. For all of the data that the user
>needs to work with, all of the sets (output of SELECT statements) and
>executable statements (insert/update/delete) needed for a single task
>should be defined within the context of a single connection |
>transaction. If you make separate *connections* for each statement
>and then try to "hook them up" you will have access violations in the
>client. When the client crashes, the connection to the server is
>broken and you get the 10054 (and other) network errors that you are
>seeing.
>
>When i said "one big query" i meant: use join as Ann told before,
>would that be right?

Ummmm...right...for what? An SQL join enables you to define a set that has data retrieved from multiple related tables. Far from being "one big query" it should be as small as possible - limited, like your single-table queries, by specifying only the columns of interest for the output row and by specifying which rows you want (by way of your WHERE clause).

When you are working with data from multiple tables, you more often work with single-table sets that your data access layer can link, one to another. It totally depends on what you want your user to see and to do. Each of these sets has a SELECT statement behind it. You keep all of these statements inside one transaction to preserve your "view" of database state until you are ready to end the transaction.

./heLen