Making better queries

Hello,
I am in process of reworking a web application, that is quite data-base
intensive. Right now I am focused on the view that executes the highest
number of queries. Telling you the full picture would be very verbose - the
database structure is quite compex, etc. What I am looking for, is some kind
of buide, how to write a better queries. Why one approach is better than the
other.
For example, the decisions I face, are:
- What is the most effective way to get only subset of records from ordered
query where are tens of thousands elements? I need this to enable paging
through the recordset.
- Why executing many queries like "SELECT myField FROM myTable WHERE
id=nn", just changing nn, sometimes is faster than creating a query with
subquery like "SELECT (SELECT myField FROM myTable WHERE id=outTable.id)
FROM outTable where id in (...)"
- Or, perhaps, alternative to subquery is a join, like in "SELECT * FROM
outTable LEFT JOIN myTable ON outTable.id=myTable.id WHERE outTable.id in
(...)"
Well, I am sure there is no short answer to questions like this, therefore I
need some wisdom, maybe there is some online article about this? I came to
conclusion that I need serious theory to complete my work, because my local
SQL server here executes my "improved" queries faster than the old, but the
SQL server of the webhoster executes them slower than the old ones. It
escapes me, why there should be such a difference, given that the data and
indexes are exactly the same on the two databases.
I will be gratefuly for any hint.
Pavils>What is the most effective way to get only subset of records from ordered
>query where are tens of thousands elements? I need this to enable paging
>through the recordset.
> - Why executing many queries like "SELECT myField FROM myTable WHERE
>id=nn", just changing nn, sometimes is faster than creating a query with
>subquery like "SELECT (SELECT myField FROM myTable WHERE id=outTable.id)
>FROM outTable where id in (...)"
>- Or, perhaps, alternative to subquery is a join, like in "SELECT * FROM
>outTable LEFT JOIN myTable ON outTable.id=myTable.id WHERE outTable.id in
>(...)"
I'd say it depends on what your trying to accomplish. The frist query
is the fastest and the last query is good if the join is needed.
Otherwise don't use joins if you don't need too, unless necessary, try
to use inner joins.
Other considerations too look at when comparing performance of the two
servers is memory, hard drive types and speeds, location, cpu speed and
server load.|||Have you looked at the execution plan for each of the queries. that will
tell you what SQL Server is doing behind the scenes, which will tell you
which query is the most efficient to run.
"Izzy" wrote:

>
> I'd say it depends on what your trying to accomplish. The frist query
> is the fastest and the last query is good if the join is needed.
> Otherwise don't use joins if you don't need too, unless necessary, try
> to use inner joins.
> Other considerations too look at when comparing performance of the two
> servers is memory, hard drive types and speeds, location, cpu speed and
> server load.
>