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 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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
by: James |
last post by:
Hi,
Can anybody help?
Is there any way to group data in DataTable?
Thanks & Regards,
James
|
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...
|
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 =...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
| |