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