slow sql data adapter fil(.) when grouping

A

Andrew

All,
Apologies if off topic.
I'm new to .net, although an experienced programmer, and am working on
the client end of a sql application. It holds every single request
that goes through our internet server at work, and the application
allows you to search and view this information.
One of the main searches is to view the number of requests and
filesize downloaded per day over a range of days.
As you can imagine, the tables get quite large. The requests table
already has 4m rows, and we've only been running it for 3 weeks
(original aim was 3 months before backup, that'll have to change!).
To view the data, I'm using the select statement of a data-adapter to
populate the dataset, and using crystal to report on it. The select
summarises the day's requests roughly as follows:

SELECT
DISTINCT CONVERT(CHAR, AccessTime, 103) AS dates, Count (*) as
requests,
SUM(filesize) AS sizeOfFiles, UserID
FROM
Request
WHERE
(userID = {0}) and AccessTime between '{1} 00:00:00' and '{2}
23:59:59'
GROUP BY
CONVERT(CHAR, AccessTime, 103), UserID
ORDER BY requests

{} - the parameters set by the searcher, e.g. user 37575, between
06/01/04 and 12/01/04

My question is that; is there a better way to do this? The select
looks messy and un-optimized to my novice eyes, and I'm sure there's a
better way than using the data adapter.

If all else fails, is there a way to extend the timeout value, to give
the server a better chance of getting the data back?

Cheers for helping me avoid any actual work, and solving all life's
problems.
You Rock.
Andrew Fray
 
H

Hermit Dave

first
create a stored procedure. that would reduce the time required to parse and
generate a query plan

second
check the indexes on the table.
you should have indexes on both UserID and AccessTime.
That would essentially speed up the execution as it will use the indexes
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,731
Messages
2,569,432
Members
44,835
Latest member
KetoRushACVBuy

Latest Threads

Top