Call function when data is already available AND when data should be retrieved

[daviddoria]

I have two functions: GetDayTime and GetLunchTime. From some places in the program, all I have is the associate name and a date and I want to determine these quantities. So I just query the database inside the function:

Public Function CalculateLunchTime(ByVal DateToCalculate As DateTime) As Double

        Dim daEvent As New TTDataSetTableAdapters.PunchEventTableTableAdapter
        Dim EventTable As New TTDataSet.PunchEventTableDataTable
        daEvent.Fill(EventTable)

        Dim SelectString As String = "associate='" & Name & "' and timefield>=#" & DateToCalculate & "# and timefield <#" & DateToCalculate.AddDays(1) & "# and reason='LUNCH'"
        Dim SortString As String = "timeField"
        
'Return the calculated time

    End Function

However, I then also have a "GenerateReport" function which loops through all of the associates, and a big range of dates and calls GetDayTime and GetLunchTime for all of these. An you can imagine, this is quite expensive! (ie VERY slow). Clearly I would want to query the database once to get all the information and then just pass arrays of the information to these two functions. Is it that simple - I just have to make a second version of these functions that takes arrays of the data as input? It just seems like a bad idea to me because of the "code reuse" idea - if a year later I have to make a change, I have to make the same change in two places, which always seems like a bad idea.

Any suggestions/advice?

Thanks,

Dave

 

[Tom]

I'm not sure what your applying your sort string too or returning in your function since it doesnt show. But you can query the database once and then filter the datatable itself of convert to something like a dataview to filter further and show only the records after being filtered without the need of requerying the database.

 

[cjard]

Could you be more specific as to what youre actually trying to do? i.e. "I want to pull a list of all people from the database who have their lunchtime set to between 12 noon and 1pm. The database table structure is..."


All I can see here is you downloading an entire table's worth of data from a database and then using some client side filtering to return.. what?

The database was designed for filtering, so why use the client side stuff (much slower)?

 

[daviddoria]

So what is meant by "client side filtering"? I am using the DataTable.select(selectionString) method, is that client side (ie. slow)? How would I do the filtering in the database itself?

 

[Tom]

How large is this database/table(s)? Assuming that your generating a simple list of who's at lunch at a certain hour, I cant picture this being that many records that it is running slow even if filtering on the client side.

 

[Tom]

Filtering in the database simply means only querying the results you need rather then bringing back all records but again, I'm guessing that it isnt to many records...

 

[daviddoria]

There are about 15,000 records in the table, but I am only trying to retrieve about 10 of them right at the end.

So I usually make a DataAdapter, a DataTable, and Fill() the DataTable using the DataAdapter. No where there do I see the ability to pass a filter string? You're saying filtering the data during this step is what I need to do right?

With a typed dataset, I've been doing:

 Dim daEvent As New TTDataSetTableAdapters.PunchEventTableTableAdapter

        Dim EventTable As New TTDataSet.PunchEventTableDataTable
        daEvent.Fill(EventTable)

I remember when I used to use non-typed datasets doing:

Public dtCompany As New DataTable
Public daCompany As OleDb.OleDbDataAdapter
Public DatabaseSource As String = "C:\Time Tracker\timetracker.mdb" 'location of the database
Public DatabaseConnection As New OleDb.OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = " + DatabaseSource)

'open the database to read company data
Dim CompanyQuery As String = "SELECT * FROM Company"
daCompany = New OleDb.OleDbDataAdapter(CompanyQuery, DatabaseConnection)
daCompany.Fill(dtCompany)

Then the obvious question: how do I specify the query like I did there but in the typed DataAdapter?

Thanks
Dave

 

[cjard]

Could you be more specific as to what youre actually trying to do? i.e. "I want to pull a list of all people from the database who have their lunchtime set to between 12 noon and 1pm. The database table structure is..."

 

[daviddoria]

I have a table that contains "events" like
"DAVID IN DAY 3/3/09 8:00AM"
"DAVID OUT LUNCH 3/3/09 11:00AM"
"DAVID IN LUNCH 3/3/09 11:30AM"
etc.

And I am calculating the total time worked. At some points in the program, I just want to know how much time DAVID worked on 3/3/09. In that case it isn't too expensive to get the whole table, then filter it by the name and time and do the calculation. In other parts of the code, I want to to know how much time DAVID worked in the whole week. In this case its pretty expensive to load the entire table for each day. And even worse is when I want to know how much time everyone worked in a week.

So I think the solution is filtering the data during the fill() method, but I dont know how to do that with a typed dataadapter.

Dave

 

[cjard]

Oh dear. This would have been much easier if the IN and matching OUT had been on the same row.. Or if you were using a proper database instead of Access. I'll have a think..

This report, are you always going to be running it for a single user, or are you running it for everyone?

 

[cjard]

I can't think of a nice solution (read: in-database) that I like with your current table structure. Can you change the structure?

Example, for a DB where IN and OUT times are on the same row:

SELECT name, sum((out-in) * 24 ) as hoursworked WHERE name LIKE ? AND in BETWEEN ? and ? GROUP BY name

(the parameters are midnight on the day and midnight on the next day)

This query can be used for DAVE by giving "DAVE" as the name parameter, or all people by giving "%" as the name. The query can be used for days or weeks or whatever time period by setting the relevant date ranges.

As it is, because you've chosen a table structure that is quite inflexible and Access doesnt have analytics or an efficient way to pivot this data, you'll have to do it client side and write a LOT more code to add up, but you should still use parameterized queries and select only the data you need. Downloading 15,000 rows from a database just because you want to look at those pertaining to one guy on one day is NOT a good idea..

The query would still look like above, but without the group by, and with more columns. The client side code would traverse the results, performing sums on this row and the previous row. It will be slower than getting the database to do it.

FOr info on making a parameterized query with the dataset designer, read the DW2 link in my sig, section Creating a Form to Search Data

Personally, I'd:
rename the date column to in_date
add a column out_date
write a one time tool to select everything out of the table in date order, skip over the results and put this row's OUT data into the out_date column of the previous row, update the rows in the db
delete all OUT rows
delete the column that holds the IN/OUT data

 

[Tom]

CJ, from what I'm guessing CJ, he wants to group about 10 people altogether from the db to his report.

"DAVID IN DAY 3/3/09 8:00AM"
"DAVID OUT LUNCH 3/3/09 11:00AM"
"DAVID IN LUNCH 3/3/09 11:30AM"

David I didnt mean so much as filtering your query but rewriting your query to only bring back the results you want. Also is the above broken into fields or is it all in one field? If it is seperate fields, exactly what is in each field?

And yes you can change the fill query in a typed dataset. Double click on the dataset to open its window, then right click where it says Fill and choose Configure to bring up the tableadapter query window. Likewise you dont have to use the Fill at all and can assign your own command object w new query and new dataadapter to fill your typed dataset.

 

[cjard]

right click where it says Fill and choose Configure

I'd choose Add Query

 

[Tom]

I thought he wanted to edit the fill query but either way....

 

[cjard]

I tend to avoid editing the original fill because it has an impact on the other generated queries and may render your app unable to edit the db. I'd always make a default FIll with no params, and then add X number of other FillByX with varying parameters