Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
duanecwilson
Constraint Violating Yak Guru
273 Posts |
 Posted - 2010-04-14 : 11:22:42
|
| I have to create an application that allows a viewer to look at a screen with application names with counts on it (about 60) and then click on one to expand the selection to reveal increasing levels of detail, maybe 2 or 3. I thought of a tree view or Reporting Services drill down report. I know the fundamental SQL will determine the performance of this as there are more than 3,000,000 records. I don't expect Reporting Services or anything else to be able to display all of them at once, but I am just looking for some general principles on creating the queries or even sample queries. At this point, I have all the data in one table (an extract of other data). For example, applications are used by employees who have workstations. Each employee has a manager, and there is a cost center for each record. I may want to drill down from application name to cost center, and then manager to see what employees and workstations have this application. All the information is in different columns of this table. If someone could help me with some design principles and maybe what technology to use for reporting. For example, so far, Reporting Services runs really slow (at least what I designed so far). I tried an ASP.Net application (really basic) and it seems to run faster on the same data. Thanks for any response.Duane |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-04-14 : 12:07:05
|
| Sounds like you are effectively using the database as a giant spreadsheet. that's not such a hot plan.From your description I could picture workstation table and an application table... etc..Can you post a sample of your data?Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
 |
|
|
duanecwilson
Constraint Violating Yak Guru
273 Posts |
Posted - 2010-04-14 : 13:16:11
|
Here is some of my data:EmployeeID EMP_FULL_NAME COnum RCnum EMPL_EMAIL_ADDRESS PHONE_NUMBER MGR_EMPID MANAGER_NAME MANAGER_PHONE UID WorkstationName AppID Application_Name DetectMethod DetectString Versionc2378274 Smith, Angela 2105 14839 asmith@myemail.com 999-888-7777 C3184688 Burton, Robert 444-555-6666 8306 EWS-RDVL-2CHW14 52 Live Meeting exe-file pwconsole.exe 8.0.6362.128 built by: lcs_se_ls2007_mainc2378274 Smith, Angela 2105 14839 asmith@myemail.com 999-888-7777 C3184688 Burton, Robert 444-555-6666 8306 EWS-RDVL-2CHW14 15 MicroSoft Office Excel exe-file excel.exe 12.0.6514.5000c2378274 Smith, Angela 2105 14839 asmith@myemail.com 999-888-7777 C3184688 Burton, Robert 444-555-6666 8306 EWS-RDVL-2CHW14 17 MicroSoft Office Powerpoint exe-file powerpnt.exe 12.0.6500.5000c2378274 Smith, Angela 2105 14839 asmith@myemail.com 999-888-7777 C3184688 Burton, Robert 444-555-6666 8306 EWS-RDVL-2CHW14 28 Microsoft Office Visio Viewer add-rem %visio%viewer% 12.0.6425.1000c2378274 Smith, Angela 2105 14839 asmith@myemail.com 999-888-7777 C3184688 Burton, Robert 444-555-6666 8306 EWS-RDVL-2CHW14 31 Citrix Client exe-file wfcmoven.exe 6.30.1050c2378274 Smith, Angela 2105 14839 asmith@myemail.com 999-888-7777 C3184688 Burton, Robert 444-555-6666 8306 EWS-RDVL-2CHW14 37 Office Communicator exe-file communicator.exe 2.0.6362.129 built by: lcs_se_ls2007_mainc2378274 Smith, Angela 2105 14839 asmith@myemail.com 999-888-7777 C3184688 Burton, Robert 444-555-6666 8306 EWS-RDVL-2CHW14 38 Zenworks exe-file wm.exe v4.83c2378274 Smith, Angela 2105 14839 asmith@myemail.com 999-888-7777 C3184688 Burton, Robert 444-555-6666 8306 EWS-RDVL-2CHW14 46 Winzip exe-file winzip32.exe 10.0 (32-bit)c2378274 Smith, Angela 2105 14839 asmith@myemail.com 999-888-7777 C3184688 Burton, Robert 444-555-6666 8306 EWS-RDVL-2CHW14 53 Shockwave exe-file swinit.exe 1150595c2378274 Smith, Angela 2105 14839 asmith@myemail.com 999-888-7777 C3184688 Burton, Robert 444-555-6666 8306 EWS-RDVL-2CHW14 89 Media Player exe-file wmplayer.exe 10.00.00.3802c4302801 Jones, John G 2105 34936 j.jones@myemail.com 666-555-4444 C0871224 Parks, Barbara 777-666-5555 5136 EWS-SUM---8D9B65 81 MicroSoft Office Outlook exe-file outlook.exe 12.0.6514.5000c4302801 Jones, John G 2105 34936 j.jones@myemail.com 666-555-4444 C0871224 Parks, Barbara 777-666-5555 5136 EWS-SUM---8D9B65 10 Rumba exe-file rumbadsp.exe 3700,74,0,0c4302801 Jones, John G 2105 34936 j.jones@myemail.com 666-555-4444 C0871224 Parks, Barbara 777-666-5555 5136 EWS-SUM---8D9B65 13 MicroSoft Office Access exe-file msaccess.exe 12.0.6211.1000c4302801 Jones, John G 2105 34936 j.jones@myemail.com 666-555-4444 C0871224 Parks, Barbara 777-666-5555 5136 EWS-SUM---8D9B65 19 NetOp Host exe-file nhstw32.exe 7.65 (2004342)c4302801 Jones, John G 2105 34936 j.jones@myemail.com 666-555-4444 C0871224 Parks, Barbara 777-666-5555 5136 EWS-SUM---8D9B65 26 Adobe Acrobat Reader add-rem %adobe%acrobat%reader% 8.1.5c4302801 Jones, John G 2105 34936 j.jones@myemail.com 666-555-4444 C0871224 Parks, Barbara 777-666-5555 5136 EWS-SUM---8D9B65 29 DotNet add-rem Microsoft .NET Framework% 3.5.30729c4302801 Jones, John G 2105 34936 j.jones@myemail.com 666-555-4444 C0871224 Parks, Barbara 777-666-5555 5136 EWS-SUM---8D9B65 51 JVM exe-file jview.exe 5.00.3810c4302801 Jones, John G 2105 34936 j.jones@myemail.com 666-555-4444 C0871224 Parks, Barbara 777-666-5555 5136 EWS-SUM---8D9B65 52 Live Meeting exe-file pwconsole.exe 8.0.6362.128 built by: lcs_se_ls2007_mainc4302801 Jones, John G 2105 34936 j.jones@myemail.com 666-555-4444 C0871224 Parks, Barbara 777-666-5555 5136 EWS-SUM---8D9B65 32 Authorware add-rem %Authorware% 7.0.1 And, yes, it does look like a big spreadsheet. The problem is, that our company is going through a big merger with several sources of data with different formats, etc. I actually have this in different tables, like the appNames. I would look up the appNames and then join the detail table and it cranked along horribly slowly. I guess it was because I tried to have the appnames in ascending order with the appID. I would join the detail by appID and it took forever and would even hang. I tried to do this in Reporting Services. When I put it all in one table and used a quick ASP test application using combo boxes, it ran quickly with this one table. But it is not a true drill-downable view. I am open to anything. I am kind of stuck. Thank you.Duane |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-04-15 : 04:10:12
|
| There would be no reason for bad performance with a normalised many table approach. Before did you have any indexes and did you have well designed keys?Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
duanecwilson
Constraint Violating Yak Guru
273 Posts |
Posted - 2010-04-15 : 14:10:47
|
| I had some indexes, but probably not enough. I am not sure what constitutes too many, thereby inhibiting performance. Actually, the above data is a view. I probably shouldn't use a view at all in this. I don't know. I am not too familiar with optimization, though I need to learn. I am not sure what your definition of a "well designed" key is. I would be interested in some hints.Duane |
|
|
|
|
|
|
|
|
Drill down queries and reports