Posted by lucm on 11/29/06 15:03

Sathya wrote:
> We are facing design issues, Could you please advice us how to proceed?
>
> Problem description: Web App will pass a complex dynamic SQL query to
> backend and it should return result set as fast as it can
> Issue 1: SQL query will have lot of JOINS and WHERE clause
> Issue 2: Each Table contain millions of records

> Could you please advice us which technology we should use, such that
> users get the resultset in few seconds.
>

Use sp_executesql to execute your dynamic SQL (not EXEC). Even better,
try to use a prepared statement. In your queries, make sure to use the
indexes, avoid calling functions and do not sort in SQL unless it is
absolutely necessary (sort on client side instead).

You could also save typical queries and run them through the Database
Tuning Advisor, which will suggest how to index your tables. This
wizard is available with SQL Server 2005 in the Management Studio, but
it can help to tune SQL Server 2000 databases as well.

If you can afford it, use SQL 2005 Enterprise Edition, which will allow
you to partition your tables. Partitions can greatly improve speed.
Again save a typical query and run it through the Database Tuning
Advisor, which can suggest how to create optimal partitions.

This wizard is just awesome, but of course if your queries are
completely random and different it won't be of much help since it need
a specific workload to make suggestions.

Regard,
lucm

[Back to original message]