slow sql data adapter fil(.) when grouping

[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

[Hermit Dave]

Re: slow sql data adapter fil(.) when grouping

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

--

Regards,

HD
"Andrew" <andrew_fray@ho tmail.com> wrote in message
news:276dea6d.0 401260900.3a8a8 ffd@posting.goo gle.com...[color=blue]
> 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[/color]