[RESOLVED] Large SQL Database, how best to read it.

Hi,

Been a long time since I fired up Visual Studio or posted here but I've got the bug again.

I'm currently writing a reporting application and some of the queries I need to run will return about 600K rows, the application works but it's pretty slow when grabbing the data from the server, obviously I recognize there will be some delay but I'd like to implement the best methods for this type of application.

I'm currently using a Sqladapter to fill a dataset which I then use to fill a DataView which I then use to populate a chart (possible not the best way to be doing this ! :blush:)

What would be the 'Best Practice' for doing this kind of large data application.

Thanks in advance

Marc

I would start with the query that is running on the database? Has that been tuned as well as possible? Have you traced the activity on the database and look at Read/Writesd (I/O), CPU time, total Duration? Are there missing indexes that will help? Are statistics upto date? What you gotten an Execution plan and looked thought that yet?

If you can get away with forward only, read only, then a datareader would be faster than filling a dataset. It would mean that you would only be making ONE pass through the data, though. If that constraint isn't acceptable, you would probably lose ground if you had to use a datareader twice.

Thanks for the replies and tips.

I'm only reading from the database there's never going to be any writing back to it so I'll give the reader a go and see what sort of difference it makes.

Also the dataset although slow in comparison does have the advantage that once the dataset has been populated it's readily accessible (but not always guaranteed to contain the data I'm looking for).

I guess it's about the pros and cons of each method but speed is a large factor for me.:D

Thanks

Marc.

Very much so.

By the way, I have a thread over in the .NET CodeBank that has a mini profiler class that I wrote. It could be of some value for timing things.

One very important issue: Do you need all the rows from the table or are you filtering after reading it? if that is the case, try creating a SELECT query With a WHERE clause so you only get a subset of rows with all the data you need.

Hi,

I''m using a select statement as I only need to grab a couple of columns from the database table but there are lots of rows :sick:

I recoded the application today to use the SqlDataReader and the results surprised me (not in a good way).

To retrieve 2 months worth of data (approx 240,000 rows) and create the graph took 43 seconds with the SqlDataAdapter and 40 seconds with the SqlDataReader I'd hoped for better. I also tried it with 5 months of data and the difference was only a few seconds again.

One thing that might be a factor is I'm using the .DataManipulator property of the chart object to group the results by IntervalType.Months, does this add any significant time overhead ?

Marc

There's no practical way to graph 240,000 points of data. If your purpose for the procedure is graphing, then try sumarizing is some way before you plot the data to the graph, even better, before you read the table.

If you are throwing this info into a chart, can you not define a SQL statement to return you the data in a simpler form, ie groupby counts?

What was the time it took on the database? Do you know? Do you care?

Hi,

Sorry for not being clear, the query returned 240,000 rows and the two columns I'm getting via the select statement are an eventID and a Created Time.

I'm then grouping these using the DataManipluator property, basically doing a count and grouping on IntervalType.Months so in effect I'm just displaying the total number of events in each month.

As I'm not sure what sort of delay to expect I suppose I'm asking does this delay seem about right for the amount of data I'm consuming ?

Also how would I check the time the database operation took, would it be as simple as putting a couple of breakpoints at the start and conclusion of the data retrieval and just timing the gap between these ?

Marc

To test the speed of the database operation, run the same query in the SQL Server tools (such as Management Studio), but bear in mind that due to the complexity of the work within your application it is likely to be a relatively small part of the time.

The overall process is likely to be significantly quicker if you follow the suggestions above, and do the work by implementing a Group By in the query - because the database does a bit less work overall (even tho it is doing more steps), and there is much less data to transfer between the database and your app.

The query could potentially be like this:

Code:

---

SELECT Year(datefield) + '/' + Month(datefield),
      Count(otherfield)
FROM tablename
GROUP BY Year(datefield) + '/' + Month(datefield)

---

Hi,

Looks like the SQL query is relatively fast around 10 seconds, the major bottle neck seems to be the dataManipulator operation.

I'll re write the application so the counting and grouping are done by the sql server (time to dust off the sql books :ehh:)

Thanks again for all your help.

Marc

I would actually consider 10 seconds fairly slow.

Hi,

I've now re written the application so that all the grouping and counting is done by the SQL server and the graph is now more or less instant :D

Thanks to you all :thumb: