"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
>
>
>

"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:
> >
> > 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 wrote:
>
> 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.