Reduction of Query Execution Time of Larger Database in SQL

I am having a database in SQL which is of size 6GB. My application will be
logging the data into database, which makes increases the size of
database.(currently it is growing by 10%).
I am running a query againest the database, which refers many tables, which
is taking around 5 Minutes. I am having many such queries which i use to
generate the reports. Now i want to reduce the Query Execution time, without
deleting the data. I am thinking of the below 4 possibilities:
1) Indexing has to be introduced/modified.
2.) We may restrict the database growth by specifying the size (currently it
is growing by 10%).
3.) Moving the inline sql to the database level as stored procedures
4. )Breaking the large and complex queries into fragments (Needs some
database expert
Which of the above options is better. Is there is any other way to reduce
the Query Execution time?> 1) Indexing has to be introduced/modified.
You can never have efficient queries on tables of that size without
proper indexing. This is a must.

> 2.) We may restrict the database growth by specifying the size (currently
> it
> is growing by 10%).
What are you going to do about new rows? You can't simply stop growth by
setting a limit on the size. Well you can but you will get errors when you
attempt to insert new rows after you reached that size limit.

> 3.) Moving the inline sql to the database level as stored procedures
That is always a good idea.

> 4. )Breaking the large and complex queries into fragments (Needs some
> database expert
Hard to say. I would tackle the others first.
Andrew J. Kelly SQL MVP
"uday" <uday@.discussions.microsoft.com> wrote in message
news:90B8A1F9-07A5-45E0-BB63-605BB4F86634@.microsoft.com...
>I am having a database in SQL which is of size 6GB. My application will be
> logging the data into database, which makes increases the size of
> database.(currently it is growing by 10%).
> I am running a query againest the database, which refers many tables,
> which
> is taking around 5 Minutes. I am having many such queries which i use to
> generate the reports. Now i want to reduce the Query Execution time,
> without
> deleting the data. I am thinking of the below 4 possibilities:
> 1) Indexing has to be introduced/modified.
> 2.) We may restrict the database growth by specifying the size (currently
> it
> is growing by 10%).
> 3.) Moving the inline sql to the database level as stored procedures
> 4. )Breaking the large and complex queries into fragments (Needs some
> database expert
> Which of the above options is better. Is there is any other way to reduce
> the Query Execution time?|||> 1) Indexing has to be introduced/modified.
Definitely a good idea. Use SQL Profiler and the Index Tuning Wizard on a
typical DML load.

> 2.) We may restrict the database growth by specifying the size (currently
it
> is growing by 10%).
Why? Just make sure to backup your data regularly, including frequent
Transaction Log backups.

> 3.) Moving the inline sql to the database level as stored procedures
That might seriously improve performance.

> 4. )Breaking the large and complex queries into fragments (Needs some
> database expert
Be careful not to break the ACID properties of any transaction. Read more
here:
http://msdn.microsoft.com/library/d...r />
_2it2.asp
ML|||Hi actually my database has the indexes andprimary keys. Only 1 0r 2 tables
are not having indexes , but they are having proper primary keys and foreign
keys. I only meant to modify them also. After proper indexing also it is
taking too long. For Ex one query took 25 minutes to execute today. I want t
o
reduce that execution time. So can u please say what are the other measures
which i can use.
"ML" wrote:

> Definitely a good idea. Use SQL Profiler and the Index Tuning Wizard on a
> typical DML load.
>
> Why? Just make sure to backup your data regularly, including frequent
> Transaction Log backups.
>
> That might seriously improve performance.
>
> Be careful not to break the ACID properties of any transaction. Read more
> here:
> http://msdn.microsoft.com/library/d.../>
06_2it2.asp
>
> ML|||For this we'd have to see the DDL and some sample data.
ML