Very Computer

by **Jay Sing** » Thu, 08 Jun 2000 04:00:00

I have a table with 2.6 million records against which I am performing
the following query and it is taking 13.28 minutes

Is it normal or is this really long time. The Server is dual pentium
with 1 GB RAM

SELECT UserName, app, sum(datediff(minute, Start_time, end_time)) as
Usage
FROM trackapp
WHERE start_time > '5/1/2000' and end_time < '5/30/2000'
AND app_path not like 'c:\%'
GROUP BY UserName, app
ORDER BY UserName.

I have following indexes defined:
1) trackapp ;Clustered ; Located on Primary; Process_ID, Start_time,
WServer
2) TRACKAPP3 ; NON CLUSTERED; Located on Primary; Wserver
3) TRACKAPP4 ; NON CLUSTERED; Located on Primary; App
4) TRACKAPP5 ; NON CLUSTERED; Located on Primary; App_Path
5) TRACKAPP6 ; NON CLUSTERED; Located on Primary; WDomain, UserName
6) TRACKAPP7 ; NON CLUSTERED; Located on Primary; Star_Time, End_time,
App_Path

Examination of the Query Plan shows that it is doing a Clustered Index
Scan.

I have also run the Update Statistics on all indexes but no change in
execution time.

Any help or guidance would be greatly appreciated.

Thanks
Jay

Sent via Deja.com http://www.deja.com/
Before you buy.

by **David K. Brow** » Thu, 08 Jun 2000 04:00:00

Quote:> Examination of the Query Plan shows that it is doing a Clustered Index
> Scan.

A clustered index scan is the same thing as a table scan. So, you are not
using an index in your query. This could explain why you are taking 13
minutes to complete the query.

Dave Brown

by **Tibor Karasz** » Fri, 09 Jun 2000 04:00:00

So, depending on the selectivity of the query, you might want to create an index on
the column used in the WHERE clause (the date columns).

--
Tibor Karaszi, SQL Server MVP
Please reply to the newsgroup only, not by email.

Quote:> > Examination of the Query Plan shows that it is doing a Clustered Index
> > Scan.

> A clustered index scan is the same thing as a table scan. So, you are not
> using an index in your query. This could explain why you are taking 13
> minutes to complete the query.

> Dave Brown

by **Jay Sing** » Fri, 09 Jun 2000 04:00:00

I tried creating another index, however it seems that the query refuses
to utilize any other index other than Clustered Index Scan. I then
transfered the table onto another server, in this case on the other
machine the query used the index that I created and not the clustered
index scan.

It appears that the query execution plan is cached and the engine
refuses to acknowledge the presence of another index that is more
suitable.

How do I make the engine acknowledge the other index. I have already
run the update stats.

Thanks Jay

> So, depending on the selectivity of the query, you might want to
create an index on
> the column used in the WHERE clause (the date columns).

> --
> Tibor Karaszi, SQL Server MVP
> Please reply to the newsgroup only, not by email.

> > > Examination of the Query Plan shows that it is doing a Clustered
Index
> > > Scan.

> > A clustered index scan is the same thing as a table scan. So, you
are not
> > using an index in your query. This could explain why you are
taking 13
> > minutes to complete the query.

> > Dave Brown

Sent via Deja.com http://www.deja.com/
Before you buy.

by **Keith Kratochvi** » Fri, 09 Jun 2000 04:00:00

Jay, you could
drop and recreate the stored procedure
stop and start SQL Server
exec sp_recompile 'tablename'
exec sp_recompile 'stored procedure name'
dbcc freeproccache (I think that this is only available on 7.0)

Keith

> I tried creating another index, however it seems that the query refuses
> to utilize any other index other than Clustered Index Scan. I then
> transfered the table onto another server, in this case on the other
> machine the query used the index that I created and not the clustered
> index scan.

> It appears that the query execution plan is cached and the engine
> refuses to acknowledge the presence of another index that is more
> suitable.

> How do I make the engine acknowledge the other index. I have already
> run the update stats.

> Thanks Jay

> > So, depending on the selectivity of the query, you might want to
> create an index on
> > the column used in the WHERE clause (the date columns).

> > --
> > Tibor Karaszi, SQL Server MVP
> > Please reply to the newsgroup only, not by email.

> > > > Examination of the Query Plan shows that it is doing a Clustered
> Index
> > > > Scan.

> > > A clustered index scan is the same thing as a table scan. So, you
> are not
> > > using an index in your query. This could explain why you are
> taking 13
> > > minutes to complete the query.

> > > Dave Brown

> Sent via Deja.com http://www.deja.com/
> Before you buy.

by SJ » Sat, 22 Jul 2000 04:00:00

Other than recompiling the stored proc, you can user optimizer
hints in your query to specify the index you want the query to
use. Got to books online, and search for "hints" or "optimizer
hints", and you'll see the title "hints" in the topic list.

-----------------------------------------------------------

Got questions? Get answers over the phone at Keen.com.
Up to 100 minutes free!
http://www.keen.com

Very Computer

Long Query Time

Long Query Time

Long Query Time

Long Query Time

Long Query Time

Long Query Time

Long Query Time