PHISSUG

in my previous job, i used to be the kind who relied much on ADO.NET, not because i like it more than stored procedures and sql scripts but because i found it easier and it is what im used to. Not realizing how my previous actions would result that my application suffered when i dealt with so much data.

I would like to share these experience to others who uses SQL Server only as a repository

as well as discussing it with those here that can give a good suggestion on what could be a better solution to it.

Given a textfile with more than 50 columns and around 60k to 80k rows a day and produce a monthly statistical report in different categories.

Here's what to do, and what i did.

i bulk copy the flat file to sql server for storage and retrieve it to do the statistical reports with a windows application

the table looks like this:

Date | Time | VehicleCode | Headway | VehicleClass | Speed | ViolationFlag | Lane | Station | AxleWeight1 | AxleSpacing1 | AxleWeight2 | AxleSpacing2 | AxleWeight3 | AxleSpacing3 | AxleWeight... | AxleSpacing... | AxleWeight20 | AxleSpacing20 |

given that all values are numeric with a sample data like this:

Date | 01012006, 01012006, 01012006
Time | 0123, 0123, 0124
VehicleCode | 123, 124, 125
Headway | 4, 4, 6
VehicleClass | 3, 3, 5
Speed | 80, 75, 100
ViolationFlag | 0,63,0
Lane | 1, 2, 4
Station | D761D, D761D, D761D
AxleWeight1 | 5655, 5887, 4225
AxleSpacing1 | 230, 260, 140
AxleWeight2 | 10335, 12544, 7100
AxleSpacing2 | 560, 600, 599
AxleWeight3 | 10560, 14400, 6210
AxleSpacing3 | 230, 150, 400
AxleWeight... | 5609, 0, 0
AxleSpacing... | 780, 0, 0
AxleWeight20 | 0, 0, 0
AxleSpacing20 | 0, 0, 0

The task for reporting is something like this

Single Axle Weights for the Month of January, 2006
Station: San Simon Northbound
Lanes: All lanes

         Tons                                                                                                                   
Day   <8     <13     <16     <20     <25     <30     <35     <40     <45      <50      >50    Total
1        6        3         1          0         0          0         0          0          0         0            0        10
2        0        0         0           0        0          0         0          0          0         0            0
3        0        0         0           0        0          0         0          0          0         0            0
4
5
6
7
8
9
10
.
.
.
30
Total    6     3       1
AVPD 6     3       1

Explanations: You pickout all the individual axles for the month of january, categorized it per day, and categorize it per weight, in my case above, 5655 which is axleweight1 is less than 8 tons, its devided by 1000 to get the ton equivalent. I only showed you an example data but in reality, on a day, it reaches around 60k to 80k vehicles depending on a weekday and weekend volume of vehicles passing by the express way.

i did this reporting thru sqldatareader and store axle counts in a matrix variable coordinate array to hold the counts and later pre formated the report with string expressions, if you were in my case as a sql server enthusiast which will recreate my solution to sql server way, how would you have done it?

Cheers.

hello again, i though of it and it will be a lot easier if you can experiment on an actual data, i extracted 3 days of data for your experiment that can be downloaded in this link for 7 days http://www.yousendit.com/transfer.php?action=download&ufid=FAD1C60A7C4B5328

Is this the one you presented on your "notepad" reporting engine? I remeber you mentioned this during one of our dinners or whenever PHISSUG folks are on a roadtrip. It's even more complicated than it sounds. If I were in your shoes then and I had so much time to spare, I would probably study cubes. I think this one involves cubes. I downloaded the file already. Thanks.

dreamlordzwolf:

hello again, i though of it and it will be a lot easier if you can experiment on an actual data, i extracted 3 days of data for your experiment that can be downloaded in this link for 7 days http://www.yousendit.com/transfer.php?action=download&ufid=FAD1C60A7C4B5328

hi mike,

looks interesting, however the site is blocked from where I'm at (saudi).. if possible you can send me the file? zipped of-course..

thanks

Is this the sample tde_rs_db.rar? Its .bak file as I extracted it. I can't read any data in it. Anyway, as I look at your situation you seems to do some cross tab query from your data. Many analysis tools provide pivot capabilities, such as Microsoft Excel's PivotTable, Microsoft Access's Transform command, and the Pivot Table ActiveX Control, if your using .NET client.

However, if you want to pivot or unpivot data in SQL Server 2000, you have to use and write complex T-SQL expressions.SQL Server 2005 provides native T-SQL PIVOT and UNPIVOT operators that simplify these operations.

I rather have to write a blog post for this soon enough since always encounter this scenario at various furoms.

I did ask someone last week to look at it and he manage to restore it in SQL Server 2005 express without any problem and were able to see the data, i will have to upload another for the extension since the last time it was only up for 7 days, sorry for the late replies, i was too sick the past few days.

@sir bonskjr, please pm me your email address so i can send it.

here's a screenshot of one of the report output example based on the 3 day data i posted