[RESOLVED] only need to read from a sql database

[Crash893]

Hi all

At work we have a database that is read only that keeps track of the work we do ( who completed what ticket and so on) I need to take a chunk of the database for the work done by my group then count up certain types of information like who has done how many tickets or how many of one type of problem there was during a certain time.

question 1) can i use the fact that this is read only to my advantage in terms of speed or memory i saw something called sqlreader but i haven't had time to play with it yet


question 2) given the senerio above what would be the most efficient work flow? should i read the database into a dataset then do my filtering from there or something else


unfortunately this is not my strongest subject so all suggestions are appreshated

[mar_zim]

1. I assume it's sqldatareader. Yeah you're right, sqldatareader is quite faster in retrieving database values.
2. or you could filter it w/ your sql statement.

Here's a good start. http://samples.gotdotnet.com/quickst...sOverview.aspx

[jmcilhinney]

If all you want is a number of various scalar values then you should perform a number of scalar queries. For instance, if you want the number of records closed on a particular date you would do this:

SELECT COUNT(*) FROM MyTable WHERE ClosedDate = @ClosedDate

Create an SqlCommand with that code in the CommandText and call its ExecuteScalar method.

Alternatively you could read all the data into a DataTable using an SqlDataReader and then use the table's Compute method, but I'd go with the first option.

[Crash893]

im not sure that would be possible unless i had like 30 or 50 queries

our boss wants to plot the data several diffent ways

type of call vs analyst
root cause vs user
type of call vs user by date

stuff like that. wouldnt it be slower to have to call out to the db for each one?

btw where do you guys live that you up all hours of the night?

[jmcilhinney]

What would be fastest depends on exactly what you want to do, where your database is located and what data it contains. Databases are specialised pieces of software, highly optimised for performing specific types of operations. If you have to perform the calculations anyway, would it be better to do them in your app or on the database that's optimised for the purpose? If what you want to do would involve getting the same data over and over then that's something you want to avoid, but if you just want scalar values then the database is likely to be more efficient than your .NET code. If you're connected to the database over a fast medium then the latency will be small for multiple queries too.