473,323 Members | 1,570 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,323 software developers and data experts.

slow sql data adapter fil(.) when grouping

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
Nov 18 '05 #1
1 1200
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" <an*********@hotmail.com> wrote in message
news:27**************************@posting.google.c om...
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

Nov 18 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Roy Scarisbrick | last post by:
I have a programming issue on an ASP.Net web page. I suspect the issue would apply to any version of ASP though. I have a questionnaire page where a user will answer a bunch of questions with...
1
by: James | last post by:
Hi, Can anybody help? Is there any way to group data in DataTable? Thanks & Regards, James
3
by: Brad | last post by:
I have a response filter which injects "standard" html into my pages. The filter works fine when the initial stream is small enough not to buffer...or....if I have a large unbuffered stream (i.e. I...
3
by: Patrick Olurotimi Ige | last post by:
With the code below i get error:- Invalid attempt to read data when reader is closed. //Get a datareader SqlDataReader objDataReader; objDataReader =...
0
by: shailaja | last post by:
Hi, I am new to vb.net. I have created a web application in which I have dragged and dropped the Customers table on the aspx design page. Hence I have an adapter for the Customers table which...
5
by: antonyliu2002 | last post by:
I have 4 forms each on a separate page respectively form1.aspx, form2.aspx, form3.aspx, form4.aspx. On top of each of the four pages there are 4 links which link to the aforementioned 4 pages...
23
by: Bjorn | last post by:
Hi. Every time i post data in a form the contents are being checked for validity. When i click the back-button, all data is gone and i have to retype it. It's obvious that only a few or none of...
3
dlite922
by: dlite922 | last post by:
I'm building a dynamic reporting system. The report can of course query multiple tables. The query declares which tables it will access, the fields that it needs for display, and the dependent...
4
by: plaguna | last post by:
My computer is very slow when some Windows applications are running. I defragmented and fixed bad sectors of the C: drive, I deleted open and running applications through regedit, I removed all the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.