Design Solution Required

[Sathya]

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

Requirement: Turn around time of the SQL query should be as far as
possible minimum.


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

We are Microsoft Partner. We use only Microsoft technology for our
product development.


Your Help is much appreciated

With Regards
S a t h y a R

[Dan Guzman]

Re: Design Solution Required

Could you please advice us which technology we should use, such that

users get the resultset in few seconds.

Pay particular attention to index and query tuning. Make sure you have
indexes that the optimizer can use to generate the most efficient plan.
Prioritize tuning so that the most often executed and expensive queries are
addressed first. Also consider indexed views, which are especially
appropriate for aggregated data. Keep in mind that too many indexes can
hurt performance if you do a lot of inserts/updates so you'll need to
perform cost-benefit analysis.

I suggest you get a good book that covers query and index tuning in depth.
I recommend Inside Microsoft SQL Server 2005: T-SQL Querying, ISBN
9780735623132.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Sathya" <sathyamca@gmai l.comwrote in message
news:1164801200 .132687.7070@j7 2g2000cwa.googl egroups.com...

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
>
Requirement: Turn around time of the SQL query should be as far as
possible minimum.
>
>
Could you please advice us which technology we should use, such that
users get the resultset in few seconds.
>
We are Microsoft Partner. We use only Microsoft technology for our
product development.
>
>
Your Help is much appreciated
>
With Regards
S a t h y a R
>

[lucm]

Re: Design Solution Required


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