November 17, 2007 at 4:46 pm
Hi,
I just want to know if there are specific guidelines to follow for the following scenario:
1. Generate approx. 1000 records per day +- 300 from a flat text file. This happens seven days a week
2. I have about 25 fields most are varchar(xxx), but I also have some INT(s) and some MONEY and DATE fields
3. For a given week this table(s) generate 7,000 records, 28,000 per month, 336,000 records per year
4. These tables are the source for daily reporting and monthly reporting
My Questions:
1. What are the best guidelines for managing these records AKA transactions
2. Should I create a summary table for monthly reports. I currently have a SQL Script that sums daily transactions
3. Do I incorporate truncation and when
4. Users Export data on a daily basis, but also need to Export the monthly data
Please advise,
Thanks
November 17, 2007 at 5:56 pm
My Questions:
1. What are the best guidelines for managing these records AKA transactions
2. Should I create a summary table for monthly reports. I currently have a SQL Script that sums daily transactions
3. Do I incorporate truncation and when
4. Users Export data on a daily basis, but also need to Export the monthly data
1. Seems odd, but with that low number of rows per day, the worst thing you may have to do is a dupe-check. Always import the new data to a staging table, check it for dupes against the main table, and only insert rows that are not already present. If you need to update rows that may already exist, that would be the time to do that, as well. But, never import directly into the final table... always use a staging table.
2. No. Again, with that small number of rows, it's really not necessary. Correctly written reporting code in the presence of proper indexing will very quickly render summary reports for weekly, monthly, and quarterly reports.
3. I almost never incoporate truncation... Archive? Yes. Truncate? No. Especially when such a small number of rows are present.
4. What is the question here? And why are users exporting data? What for and to where? Virtually everything anyone needs to do to this data can be done by the database. If they need it in a spreadsheet, use OPENROWSET to populate copies of spreadsheet templates. But the real key is, they shouldn't need it in a spreadsheet... there's no reason why generating reports from the database should not suffice. If you want to make it real pretty, either install (for free) Reporting Services, do pass-through queries from Access, or learn how to use sp_MakeWebTask.
Of course, all of that is just my opinion 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
November 17, 2007 at 8:56 pm
I'm talking about the final table generating about 1,000 records per day. I didn't want to describe the entire process but I pre-load to a staging table as you mention; check for dupes and then load the new records into the final transaction table. I'll be generating some 336,000 per year just for this table. There are other tables in the database unfortunately I don't know the storage capacity of those tables. I know in Oracle 336,000 records are not that many records however I have not dealt with this many records in SQL Server and I guess my real question is can SQL Server handle 336,000 transactions as my dataset for the year (that is after proper indexing ...)?
The ability to export to Excel from within the Web Reporting System that I've built was a "hard core" requirement. This balancing tool that I've built is the foundation for the reconciliation process. For this version I need to have the export capability. I want to introduce reporting services but I feel we're not there yet. My goal is to have a reporting system that provides the type of reporting that provides a finish product. Unfortunately the financial system does not provide this type of reporting.
Thanks,
November 18, 2007 at 9:21 am
I guess my real question is can SQL Server handle 336,000 transactions as my dataset for the year (that is after proper indexing ...)?
Heh... I've got 'little" IVR systems with an SQL Server backbone... we capture just about everything a user does on the system to meet PUC, FCC, and a couple of other audit requirements... the IVR logs more than 12 MILLION detail rows per year... the main report which contains a month-sensitive output by day by hour of the day for 9 different measurements and includes an overall summary for the month takes less than 12 seconds to render.
Our "big" system captures 4-6 million call records per day... that's 120 million rows per month and 1.44 billion rows per year. I chum around with some DBA's who envy me because my system is so "small". 😀 Compared to theirs, it is!
So, I'm pretty sure that SQL Server will handle your requirement of 336K rows even if other tables are involved. 😉
The ability to export to Excel from within the Web Reporting System that I've built was a "hard core" requirement. This balancing tool that I've built is the foundation for the reconciliation process. For this version I need to have the export capability. I want to introduce reporting services but I feel we're not there yet. My goal is to have a reporting system that provides the type of reporting that provides a finish product. Unfortunately the financial system does not provide this type of reporting.
Heh... had to laugh a bit... I know a "management directive" when I see one...
My suggestion here would for you to read up on and become an absolute "Ninja" at the use of OPENROWSET combined with a little VBS to copy spredsheet "templates" to new files to be populated by OPENROWSET. It's a little bit difficult to find all the information you'll need in a single Google hit, but it's worth the time. Some will suggest the use of "sp_OA*" procs... I wouldn't use those if my life depended on it... they say they've fixed all the memory and connection leaks associated with those procs, but we still have problems everytime someone at work tries to use them.
Another way to do it is to simply save the rendered "spreadsheet data" in a table and a couple of views of that table (or tables) to present the data... then, build a spreadsheet that calls the appropriate views using the "External Data" functionality of the spreadsheet. It can even be made to self-refresh as quick as once a minute while the user has the sheet open.
Or, you could learn how to do it using DTS (I don't know how... I've seen it done... and it's too slow for me).
--Jeff Moden
Change is inevitable... Change for the better is not.
November 19, 2007 at 2:51 am
Joe Contreras (11/17/2007)
I guess my real question is can SQL Server handle 336,000 transactions as my dataset for the year (that is after proper indexing ...)?
🙂 I've got several tables that load over three times that a day, with a minimum retention of 5 years. 300 000 rows is nothing for SQL
The ability to export to Excel from within the Web Reporting System that I've built was a "hard core" requirement.
Thanks,
have you considered reporting services? It's an add-on for sQL 2000, free providing you have an enterpise licence for SQL server. It's got native excel export (though on the SQl 2000 version you will want the lastest servicepack, as there were issues earlier on)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 19, 2007 at 7:32 am
I have looked into it. I need buy in from Sr. Mgmt. Also I'll need to integrated on the web via .NET? Do you have any links that demonstrate this capability?
That's good that 300K is nothing to SQL Server.
Thanks,
November 19, 2007 at 11:22 pm
I haven't done any integrating with Reporting Services myself. I know it can be done, but that's about all. I just use it as an automated report delivery tool.
Maybe post in General, or in the Reporting Services forum about this. I'm sure there are experts on RS around here. I'm not one of them.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 20, 2007 at 7:41 am
Thanks for the information. I just found out that my boss is leaving the company and he's the only one that supports and buys into my design ideas, i.e. Design Patterns, best database practices ... This becomes a tougher sell to Sr. Mgmt.
Thanks,
November 21, 2007 at 4:01 pm
Joe Contreras (11/20/2007)
Thanks for the information. I just found out that my boss is leaving the company and he's the only one that supports and buys into my design ideas, i.e. Design Patterns, best database practices ... This becomes a tougher sell to Sr. Mgmt.Thanks,
Find out where he's going and see if you can go with him... sounds like not only a powerful allie in the world of databases, but sounds like maybe a good guy to work for. Sounds like he knows what "value" is whereas the others sound like they know the cost of everything and the value of nothing 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
November 21, 2007 at 4:55 pm
Yeah, I know people like him are tough to come by. Funny thing is he's my second boss that has left. And I'm talking very high level positions.
I don't want to deviate from this topic which I believe has been resolved, but people like these two guys I worked for are hard to find (they get it). Now I'm back playing the political game, and the work that needs to be done becomes secondary.
This company I'm with is very bureaucratic (bank) and pushing builds to production takes a week or more versus an hour in other companies I've been with.
Anyway, I'm just getting on my "soapbox".
Thanks,
November 21, 2007 at 8:04 pm
Heh... personally, I'm glad banks are over-cautious with changes to software.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 21, 2007 at 9:07 pm
One last thing to consider... Since Excel is so "eager" to hijack the handler for .CSV files, often enough I simply create those as my "excel integration" solutions. That is something that any number of thing can crank out. Otherwise - use xml with a .XSLT and change the output type to be excel. Works well as well.... And neither really compromise your speed.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 21, 2007 at 11:36 pm
Joe Contreras (11/21/2007)
Now I'm back playing the political game, and the work that needs to be done becomes secondary.
Time for some polishing of the CV?
I'm in almost the same boat, except I still have a boss who gets it. (for now, at least)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 22, 2007 at 7:09 am
Personally I rather not deal with the text files at all. I understand about converting to XML the reason I don't do this is because it's still my responsibility to re-format the text files. And I feel that this is up to the person delivering the text file which is an outside company. My thoughts are to have them deliver an XML file that way I can almost trust that the file will be accurate (I already found issues with the text files I receive).
Ideally I'd like to have a service which I then simply go out and get new or updated transactions "real-time" and avoid the batch processing altogether.
Use reporting services as my web client and provide a "finish product".
Questions:
Has anyone done the XML feeds - receiving XML files from the partner? If so how was the collaboration done, what type of agreements were setup, etc.
Has anyone done services, i.e using SOA to load real-time transactions?
Thanks,
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply