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