Optimizing SQL Query performance

Hi,
I have collected SQL Trace events into a trace table by exporting the trace
into a table.
I have a table Trace1 with following columns:
-RowNumber
-ApplicationName
-DatabaseName
.
.
.
-StartTime
-EndTime
Clustered Index on RowNumber Column.
NonClustered Index on StartTime Column.
Trace1 table contains 100,000 of rows.
Now I am firing a query something like
"select * from Trace1 where StartTime > 'Date1' and StartTime < 'Date2'"
But above query gives me timeout error for most of the cases. I have
specified timeout value as 60 seconds.
How can I solve this timeout issue?
Do I need to have some other proper indexes, if current indexes are not
proper?
Do I need to increase the timeout value? What is the optimum value of
Timeout in such scenarios? I am expecting that this table is going to grow
to contain atleast 5 crores of rows. So please suggest what strategy should
I adopt?
Thanks,
PushkarIf you have an index on StartTime, this should be the most efficient way to
retrieve the data. I would not expect 100,000 rows to take more than a
moment. Even without an index, I would expect the query to finish in
seconds.
Check your execution plan and see if it is using the index.
You could try regenerating your statistics on this table, which should get
it to use this index.
Post the full DDL of your table including the indexes themselves, just so we
are perfectly clear on what you have.
http://www.aspfaq.com/etiquette.asp?id=5006
I think the likely culprit here is the use of "Select * ". If this is a
very wide table, you may be timing out moving all of that data across the
network. Also, how many rows does your typical date range select? If you
usually return 90,000 rows, that makes a big difference. If every row
contains 1 meg of data (an extreme case, just to illustrate a point), for
example, that would be 90 gigs moving over the network, and would timeout
every time.
Also, what application are you using to run the query?
"Pushkar" <pushkartiwari@.gmail.com> wrote in message
news:%23aMdJOHaGHA.1192@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have collected SQL Trace events into a trace table by exporting the
trace
> into a table.
> I have a table Trace1 with following columns:
> -RowNumber
> -ApplicationName
> -DatabaseName
> .
> .
> .
> -StartTime
> -EndTime
> Clustered Index on RowNumber Column.
> NonClustered Index on StartTime Column.
> Trace1 table contains 100,000 of rows.
> Now I am firing a query something like
> "select * from Trace1 where StartTime > 'Date1' and StartTime < 'Date2'"
> But above query gives me timeout error for most of the cases. I have
> specified timeout value as 60 seconds.
> How can I solve this timeout issue?
> Do I need to have some other proper indexes, if current indexes are not
> proper?
> Do I need to increase the timeout value? What is the optimum value of
> Timeout in such scenarios? I am expecting that this table is going to grow
> to contain atleast 5 crores of rows. So please suggest what strategy
should
> I adopt?
> Thanks,
> Pushkar
>
>
>|||Pushkar,
It depends on the relative amount of rows that the query returns.
I would start with adding a (nonclustered) index on StartTime. If the
query returns just a few percent of all rows and tables rows are
relatively wide as compared to the StartTime column, then this index
will probably be used.
If the query returns more than a few percent, or the rows are narrow,
then a nonclustered index on StartTime might be ignored. If this query
is one of the most important queries in your system, then you could
create a clustered index on StartTime (you will need to change the
current clustered index to nonclustered).
HTH,
Gert-Jan
Pushkar wrote:
> Hi,
> I have collected SQL Trace events into a trace table by exporting the trac
e
> into a table.
> I have a table Trace1 with following columns:
> -RowNumber
> -ApplicationName
> -DatabaseName
> .
> .
> .
> -StartTime
> -EndTime
> Clustered Index on RowNumber Column.
> NonClustered Index on StartTime Column.
> Trace1 table contains 100,000 of rows.
> Now I am firing a query something like
> "select * from Trace1 where StartTime > 'Date1' and StartTime < 'Date2'"
> But above query gives me timeout error for most of the cases. I have
> specified timeout value as 60 seconds.
> How can I solve this timeout issue?
> Do I need to have some other proper indexes, if current indexes are not
> proper?
> Do I need to increase the timeout value? What is the optimum value of
> Timeout in such scenarios? I am expecting that this table is going to grow
> to contain atleast 5 crores of rows. So please suggest what strategy shoul
d
> I adopt?
> Thanks,
> Pushkar

Optimize the query performance

Hi,
I am saving the data of profiler into trace table every day and then export
this data into table called RepositoryTable.
RepositoryTable has exactly same structure as profiler trace table.
Every day I am inserting 10 lakhs rows of trace events in the Repository
table.
Repository table has primary key on RowNumber and non clustered index on
StartTime column.
I have specified timeout interval as 60 seconds.
I have a simple query based on time range filter only, but this query too
timeout after 60 seconds.
How can I optimize my query to get the best performance?
Is timeout interval too small? What should be optimum timeout interval
assuming repository table will have 10 crores of rows within some time
period?
What is optimum interval to update the statistics of the table?
Thanks in advance.
PushkarSorry, but what are lakhs and crores?
Also, post DDL for your table and indexes, along with your query. See
instructions here:
http://www.aspfaq.com/etiquette.asp?id=5006
"Pushkar" <pushkartiwari@.gmail.com> wrote in message
news:u0bb2R2eGHA.1272@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I am saving the data of profiler into trace table every day and then
export
> this data into table called RepositoryTable.
> RepositoryTable has exactly same structure as profiler trace table.
> Every day I am inserting 10 lakhs rows of trace events in the Repository
> table.
> Repository table has primary key on RowNumber and non clustered index on
> StartTime column.
> I have specified timeout interval as 60 seconds.
> I have a simple query based on time range filter only, but this query too
> timeout after 60 seconds.
> How can I optimize my query to get the best performance?
> Is timeout interval too small? What should be optimum timeout interval
> assuming repository table will have 10 crores of rows within some time
> period?
> What is optimum interval to update the statistics of the table?
>
> Thanks in advance.
> Pushkar
>
>|||Pushkar,
The nonclustered index on StartTime is probably not useful. Currently,
the query is probably using a table scan (or clustered index scan) which
will take more time as the table grows. Then can check this by viewing
the query plan.
If you make the index on StartTime the clustered index, then only these
rows will be read when copying a date range. After that, maybe the query
will finish within the timeout period of 60 seconds.
HTH,
Gert-Jan
P.S. Jim, you might want to have a look at
http://en.wikipedia.org/wiki/Lakh This page also explains crores.
Pushkar wrote:
> Hi,
> I am saving the data of profiler into trace table every day and then expor
t
> this data into table called RepositoryTable.
> RepositoryTable has exactly same structure as profiler trace table.
> Every day I am inserting 10 lakhs rows of trace events in the Repository
> table.
> Repository table has primary key on RowNumber and non clustered index on
> StartTime column.
> I have specified timeout interval as 60 seconds.
> I have a simple query based on time range filter only, but this query too
> timeout after 60 seconds.
> How can I optimize my query to get the best performance?
> Is timeout interval too small? What should be optimum timeout interval
> assuming repository table will have 10 crores of rows within some time
> period?
> What is optimum interval to update the statistics of the table?
> Thanks in advance.
> Pushkar|||Gert-Jan,
Thanks for the link.
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:446E08D7.83FF2ACE@.toomuchspamalready.nl...
> Pushkar,
> The nonclustered index on StartTime is probably not useful. Currently,
> the query is probably using a table scan (or clustered index scan) which
> will take more time as the table grows. Then can check this by viewing
> the query plan.
> If you make the index on StartTime the clustered index, then only these
> rows will be read when copying a date range. After that, maybe the query
> will finish within the timeout period of 60 seconds.
> HTH,
> Gert-Jan
> P.S. Jim, you might want to have a look at
> http://en.wikipedia.org/wiki/Lakh This page also explains crores.
>
> Pushkar wrote:
export
toosql

Optimize sql statements / find usefull indices

Dear all,

I try to find an easy method (like 'explain' in MySQL) to optimize my SQL statements and create usefull indices. I have created a trace table with the PROFILER and filtered the SQL statements by long DURATION time.

In the next step I used:

set SHOWPLAN_ALL ON;
my_sql_statment

to find the correct indices. Is there any tutorial available describes how to analyse such an output to find correct indices?

Best regards
febel

If you have SQL Profiler trace you could use Database Engine Tuning Advisor. Its wizard, that analyze trace and propose indexes, statictics etc

As tutorial you could use this book http://www.microsoft.com/MSPress/books/8565.aspx

|||Dear Konstantin,

thanks for your answer, but I have only the trace table and no possibility to use Database Engine Tuning Advisor. In addition I want use this in a programm which should set the indices in a automatic way depending on the results of the analysis of the trace table.

Best regards
febel
sql